Search two tables for unmatched records

Sometimes you want to compare two tables and identify records in one table that have no corresponding records in the other table. The easiest way to identify these records is to run the Find Unmatched Query Wizard:

Click Create, and in the Queries group, click Query Wizard.
In the New Query dialog box, double-click Find Unmatched Query Wizard.

After using the wizard to build your query, you can modify the query's design. You can also create your own query to find unmatched records, without using the wizard.

Note: The Find Unmatched Query Wizard is not supported in Access apps or web databases.

Overview

The following are two common cases in which you may want to compare two tables and find unmatched records.

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

For example, in the Northwind database template, data about products is stored in the Products table, and data about the products that are included in each order is stored in the Order Details table. Because 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 records from the first table for which there are no corresponding records 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 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.

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

Note: Examples in this article use a database that was created by using the Northwind database template.

Show me how to set up Northwind

On the File tab, click New.
In the left pane, under Available Templates, click Sample Templates.
Click Northwind, and then click Create.
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.

Top of Page

Use the Find Unmatched Query Wizard to compare two tables

Click Create, and in the Queries group, click Query Wizard.

In the New Query dialog box, double-click Find Unmatched Query Wizard.
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.
On the second page, select the table that is related, and then click Next. To follow the example, select the Order Details table.
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.

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.
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 might 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 See Also section.

Top of Page

Modify a Find Unmatched Query to compare by more than one field

Open the query in Design view.

In the query design grid, note that the two tables are joined on the fields that you specified on the third page of the Find Unmatched Query 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 example, drag the List Price field from the Products table to the Unit Price field from the Order Details table.
Double-click a join (the line connecting the fields) to display the Join Properties dialog box. For each join, choose the Join Properties option that includes all the records from the first 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, by default the join 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.

Important: Make sure that all of the arrows on the joins are pointing in the same direction.
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.
Optionally, add criteria to the other query fields, or create calculated fields that are based on values from the first table.
On the Design tab, in the Results group, click Run.

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

Top of Page

Create your own query to find unmatched records

Click Create, and in the Queries group, click Query Design.
In the Show Table dialog box, double-click the table that has unmatched records, and then double-click the table that has related records.
Close the Show Table dialog box.
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).
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.

Important: 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 unmtached records.
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.
For the table that has related records, double-click each field that is joined to the first table, to add these fields to the query design grid. For each of these fields, clear the box in the Show row, and type Is Null in the Criteria row.
On the Design tab, in the Results group, click Run.

Top of Page

Microsoft Office Tutorials

Monday, December 19, 2016