Query to display non matching records.

AJ · April 7th, 2005, 03:05 PM

Can a query be constructed in MS Access to display non matching records from two tables?

BrianWren · April 7th, 2005, 05:05 PM

SELECT *
FROM tbl1
WHERE tbl1.ID NOT IN (SELECT ID
                       FROM tbl2);

SELECT *
FROM tbl1
WHERE tbl1.ID NOT IN (SELECT ID
                           FROM tbl2) OR
       tbl1.FName NOT IN (SELECT FName
                                  FROM tbl2);

You also can concatenate fields:

Code:

SELECT *
FROM   tbl1
WHERE  tbl1.ID & tbl1.FName NOT IN (SELECT ID & FName 
                                    FROM   tbl2);