Compare two tables and find records without matches

In this article

• When should I search for unmatched records?

• Use the Find Unmatched Query Wizard to compare two tables

• Create and modify a Find Unmatched Query to compare by more than one field

• Create your own query to find unmatched records

When should I search for unmatched records?

The following are two common circumstances in which you may want to compare two tables and find unmatched records. Depending on your circumstance, finding unmatched records may be the first of several steps that you want to take. This article only covers finding unmatched records.

• You use one table to store data about a thing (such as a product), and another table to store data about actions (such as orders) that involve that thing.    

  For example, in the Northwind database template, data about products is stored in the Products table, and data about which products are included in each order is stored in the Order Details table. Because (by design) there is no data about orders in the Products table, it is not possible, by looking at the Products table alone, to determine which products have never been sold. This information is also not possible to determine by looking at the Order Details table alone, because the Order Details table includes data only about products that have been sold. You must compare the two tables to determine which products have never sold.

  If you want to review a list of things from the first table for which there are no corresponding actions in the second table, you can use a Find Unmatched Query.

• You have two tables that have overlapping, redundant, or conflicting information, and you want to consolidate them into one table.    

  For example, suppose that you have one table named Customers and another table named Clients. The tables are nearly identical, but one table or both tables contain some records that the other is missing. To consolidate the tables, you must first determine which records are unique to one table or to the other table.

  If this is your circumstance, the methods described in this article can help, but you will probably have further steps to take. You can run the Find Unmatched Query Wizard to identify unmatched records, but if you want to retrieve the combined set of records, you use the results to create a union query. If you are comfortable writing Structured Query Language (SQL) statements, you might even prefer to forgo the Find Unmatched Query Wizard, and write the union query by hand.

  You can often approach the problem of overlapping, redundant, or conflicting information by looking for duplicate data in two or more tables.

For more information about union queries, or about finding, hiding, or eliminating duplicate data, click the links in the See Also section.

Show me how to set up Northwind

1. On the File tab, click New. 

2. Depending upon your Access version, you can search for Northwind in the Search box, or in the left pane, under Template Categories, click Local Templates.

3. Under Local Templates, click Northwind 2007 sample, and then click Create.

4. Follow the directions on the Northwind Traders page (on the Startup Screen object tab) to open the database, and then close the Login Dialog window.

Use the Find Unmatched Query Wizard to compare two tables

1. One the Create tab, in the Queries group, click Query Wizard. 

   

2. In the New Query dialog box, double-click Find Unmatched Query Wizard.

3. On the first page of the wizard, select the table that has unmatched records, and then click Next. For example, if you want to see a list of Northwind products that have never been sold, select the Products table.

   

4. On the second page, select the table that is related, and then click Next. To follow the example, select the Order Details table.

   

5. On the third page, select the fields that relate the tables, click < = >, and then click Next. You can choose only one field from each table. To follow the example, select ID from the Products table and Product ID from the Order Details table. Verify that the correct fields are matched by reviewing the text in the Matching fields box.

   

   Note that the ID and the Product ID fields may already be selected because of existing relationships built in to the template.

6. On the fourth page, double-click the fields that you want to see from the first table, and then click Next. To follow the example, select the ID and Product Name fields.

   

7. On the fifth page, you can choose to view the results or to modify the design of your query. In this example, click View the results. Accept the suggested name for the query, and then click Finish.

   

   You may want to modify your query's design to add other criteria, to change the sort order, or to add or to remove fields. For information about modifying a Find Unmatched query, read the following section; or, for more general information about creating and modifying queries, see the links in the See Also section.

Create and modify a Find Unmatched Query to compare by more than one field

1. One the Create tab, in the Queries group, click Query Wizard. 

2. In the New Query dialog box, double-click Find Unmatched Query Wizard.

3. On the first page of the wizard, select the table that has unmatched records, and then click Next. For example, if you want to see a list of Northwind products that have never been sold, select the Products table.

4. On the second page, select the table that is related, and then click Next. To follow the example, select the Order Details table.

5. On the third page, select the fields that relate the tables, click < = >, and then click Next. You can choose only one field from each table. To follow the example, select ID from the Products table and Product ID from the Order Details table. Verify that the correct fields are matched by reviewing the text in the Matching fields box. You can join the remaining fields after you have completed the wizard.

   Note that the ID and the Product ID fields may already be selected because of existing relationships built in to the template.

6. On the fourth page, double-click the fields that you want to see from the first table, and then click Next. To follow the example, select the ID and Product Name fields.

7. On the fifth page, click Modify the design, and then click Finish.

   The query opens in Design view.

8. In the query design grid, note that the two tables are joined on the fields (in this example, ID and Product ID) that you specified on the third page of the wizard. Create a join for each remaining pair of related fields by dragging them from the first table (the table that has unmatched records) to the second table. For this example, drag the List Price field from the Products table to the Unit Price field from the Order Details table.

9. Double-click a join (the line connecting the fields) to display the Join Properties dialog box. For each join, choose the option that includes all the records from the Products table, and then click OK.

   In the query design grid, note that each join now has an arrow at one end.

   

   1. When you create the join between the List Price and Unit Price fields, it restricts output from both tables. Only those records with matching data in the fields in both tables are included in the query results.

   2. After you edit the join properties, the join only restricts the table that the arrow points to. All records in the table that the arrow points from are included in the query results.

   Note: Make sure that all of the arrows on the joins are pointing in the same direction.

10. For the table that has related records (in this example, the Order Details table), double-click each field that is joined to the first table, except for the field that you chose on the third page of the wizard (in this case, the Product ID field). For each of these fields, clear the check box in the Show row, and type Is Null in the Criteria row.

11. Optionally, add criteria to the other query fields, or create calculated fields that are based on values from the first table.

12. On the Query Design tab, in the Results group, click Run.

    The query returns the names of products that are not part of any existing orders.

Create your own query to find unmatched records

1. One the Create tab, in the Queries group, click Query Design. 

2. Double-click the table that has unmatched records, and then double-click the table that has related records.

3. In the query design grid, the two tables should have lines, called joins, connecting them by their related fields. If the joins are not there, create them by dragging each related field from the first table (the table that has unmatched records) to the second table (the table that has related records).

4. Double-click a join to open the Join Properties dialog box. For each join, choose option 2, and then click OK.

   In the query design grid, the joins change so that they have arrows at one end.

   Note: Make sure that all of the joins are pointing in the same direction. The query will not run if the joins point in different directions, and may not run if any join is not an arrow. The joins should point away from the table that has unmatched records.

5. In the table that has unmatched records, double-click the fields that you want the query to return.

   Optionally, enter criteria for any of these fields, or create calculated fields.

6. For the table that has related records, double-click each field that is joined to the first table. For each of these fields, clear the box in the Show row, and type Is Null in the Criteria row.

7. On the Query Design tab, in the Results group, click Run.

See Also

Create a simple select query

Find duplicate records with a query

Use a union query to combine multiple queries into a single result