ACC1x: Using a Subtract Query to Find Unmatched Records

• Microsoft Access versions 1.0, 1.1

SUMMARY

This article describes how to create a Subtract query to compare two tables and return a dynaset that includes only those records from the first table that do not have matching records in the second table.

MORE INFORMATION

The following example demonstrates how to create a query on two joined tables that subtracts the matching records, returning only those records that do not match on the joined field:

NOTE: The following example assumes that you have two tables in your database called Table1 and Table2 that can be joined on at least one field.

1. Create a new query based on Table1 and Table2.

2. Join the two tables on the appropriate field. Microsoft Access will join the tables automatically if there is an underlying relationship between them.

3. Double-click the join line between the tables. In the Join Properties dialog box, select either type 2 or type 3, whichever is most appropriate for your data. For this example, you want all the records from Table1 and only those that match from Table2--a type 2 join.

4. Drag the joined field from Table2 to the query grid. Clear the Show check box. Set the Criteria field to "Is Null" (without quotation marks).

5. Drag any other needed fields from Table1, such as the primary key field, to the query grid. These are the columns that identify unmatched records.

6. Run the query. The Is Null condition in the first column ensures that only those records from Table1 that do not have a match in Table2 will be returned.

   SELECT DISTINCTROW Customers.[Company Name]
   FROM Orders, Customers,
   Customers LEFT JOIN Orders
   ON Customers.[Customer ID] = Orders.[Customer ID]
   WHERE ((Orders.[Customer ID] Is Null));

REFERENCES

For more information about unmatched records in version 2.0, search for "Find Unmatched Query Wizard" then "Creating a Query with a Wizard" using the Microsoft Access Help menu.