[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