Show AllShow All

Retrieve records that two tables don't have in common

If you have two tables that have similar data— such as two lists (tables) of customers, and each list has some customers that aren't included on the other list— you can create a query that returns only the records from the first table that do not match the records in the second table. This type of query is called a subtract query or subtract join.

  1. In Microsoft Query, create a query and add both tables to it.
  2. Join the fields that match between the two tables (for example, the Customer ID field from two customer lists) by dragging the field in the first table to the second table.
  3. Double-click the join line between the two tables.
  4. In the Joins dialog box, click option 2, click Add, and then click Close.
  5. Add the joined field to the query.
  6. Add a field other than the joined field from the second table. Select a field that has data for every record in the second table. For example, if you joined the Customer ID fields in the two tables, you might add the Customer Name field from the second table.
  7. On the View menu, click Criteria.
  8. In the first criteria field, click the field you added in step 6.
  9. In the first Value field, type Is Null.
  10. To see the records in the first table that aren't included in the second table, click Query Now Button image.
  11. Add any additional fields that you want in the query, and then return the data to Microsoft Excel.

Note  If you also want to see the records in the second table that don't match records in the first table, create a second query in which you follow these same steps, but reverse the order of the tables (make the second table the first table, and vice versa).