[AccessD] How to match values that don't match from two tables

...goes into my library.
William Hindman

----- Original Message ----- 
From: "Jim Lawrence" <accessd at shaw.ca>
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Sent: Wednesday, April 04, 2007 6:06 AM
Subject: Re: [AccessD] How to match values that don't match from two tables


> Gustav:
>
> Now that is a very slick piece of code.
>
> Jim
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
> Sent: Wednesday, April 04, 2007 1:24 AM
> To: accessd at databaseadvisors.com
> Subject: [AccessD] How to match values that don't match from two tables
>
> Hi all
>
> You have two tables each holding a field of some value, say an amount.
> You wish to match the values in the first table with the closest value in
> the other table.
>
> Here is one method with a subquery using Abs() to calculate the 
> difference:
>
>  SELECT
>    tblA.*,
>    tblB.*
>  FROM
>    tblA,
>    tblB
>  WHERE
>    tblB.ID=
>      (Select Top 1
>        B.ID
>      From
>        tblB As B,
>        tblA As A
>      Where
>        A.ID = tblA.ID
>      Order By
>        Abs(A.ColumnA - B.ColumnB));
>
> The IDs are the unique keys for the tables.
>
> /gustav
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>