Using the Find Unmatched Query Wizard

The Find Unmatched Query, as its name implies, displays records in one table or query that have no match in a related table or query. For example, the Find Unmatched Query can be used to detect existing records in an inherited table that break rules of referential integrity for the database. In a sales database, for example, these might be records of orders made by a customer who does not exist in the primary customers table. When the user attempts to create a relationship and enforce referential integrity between these two tables or if he/she attempted to append new orphaned records to a table with a relationship established, Access will display an error message because of a referential integrity violation. The message states that current records in the table violate referential integrity rules, but it does not identify those records. A Find Unmatched Query will quickly discover the problem records.

Finding Unmatched Records

We will create a Find Unmatched Query to find any sections of courses that don't have enrollments. In other words, we will check to see which sections listed in tblSections are not listed in tblEnrollments.

Step1. Switch to the Create tab on the Ribbon, if necessary.

Step2. To start a new query wizard,

Click

In the list of options for building a new query, we see the wizard for the purpose of finding unmatched records. We will use the wizard.

Step3. To select and begin the wizard,

Double-Click Find Unmatched Query Wizard

The first step of the wizard appears. It asks us to name the table holding the records we want to review. tblSections is the correct choice.

Step4. To select the table and move to the next step of the wizard,

scroll down and Double-Click Table: tblSections

The next wizard window asks for the table or query that might hold matching records.

Step5. To choose the second table and move to the next step of the wizard,

Double-Click Table: tblEnrollments

You will see:

NOTE: As seen in the previous screen shot, some of the dialog boxes in newer versions of Access have a display bug that causes the far right side of the dialog box to be cut off. As a result, your dialog box may look slightly different than what is shown in the screenshots.

The new window asks for the name of the field that joins these two tables, the field on which the tables are related. Access correctly identifies that the tables are related by SectionKey and SectionID since a relationship between these two tables has already been created on those fields.

Step6. To move to the next step of the wizard,

Click

In this window, we can indicate the fields that we want displayed about the records in this search.

Step7. To choose all fields to display,

Click

Step8. To move to the next step of the wizard,

Click

This window asks for a query name.

Step9. To name the query and finish the wizard, in the Name field, type:

qrySectionsWithoutEnrollments Enter key

The query result shows 32 sections without enrollments.

Step10. Close the query.