[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