How do I get the opposite of an unmatched query

[Guest]

I have two tables in the first I want to identify records that match in the
second table and those that do not, in one query.

 

[Douglas J. Steele]

If I'm understanding you correctly, there are 3 possibilities:

1) the record is in both tables
2) the record is in table1, but not table2
3) the record is in table2, but not table1

Getting those records that are in both is easy:

SELECT Table1.Field1, Table1.Field2, ... ,
"In both" AS Category
FROM Table1
INNER JOIN Table2
ON Table1.Field1 = Table2.Field1

You can extend that to get the records in categories 1 and 2 by using a Left
Join:

SELECT Table1.Field1, Table1.Field2, ... ,
IIf(IsNull([Table2].[Field1]), "In Table1, not Table2", "In both") AS
Category
FROM Table1
LEFT JOIN Table2
ON Table1.Field1 = Table2.Field1

The records in category 3 above can be determined as:

SELECT Table2.Field1, Table2.Field2, ... ,
"In Table2, not Table1" AS Category
FROM Table2
LEFT JOIN Table1
ON Table2.Field1 = Table1.Field1
WHERE Table1.Field1 IS NULL

Union those two subqueries together to get everything.