Microsoft KB Archive/95326

{|
 * width="100%"|

ACC1x: Using a Subtract Query to Find Unmatched Records

 * }

Q95326

-

The information in this article applies to:


 * 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 &quot;Is Null&quot; (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.

The following is a sample SQL SELECT statement that uses the sample database NWIND.MDB. It returns all the customers who have not placed an order:

  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));