Chat with us, powered by LiveChat

Find Unmatched Records

How to Find Unmatched Records in Access

Find Unmatched Records

The Find Unmatched Query Wizard helps you find the records in one table that do not have matching records in another table. For example, you could find customers who have never booked a tour.

Some scenarios when you might need to create such a query include:

  • To find customers who have never placed an order.
  • To find products that have never been purchased.
  • To find "orphan" records. If you haven't enforced referential integrity in your related tables, deleting a record in one table could leave one or more orphan records in a related table.

  1. Click the Create tab on the ribbon.
  2. Click the Query Wizard button.

    The New Query dialog box appears, displaying different kinds of Query Wizards.

  3. Select Find Unmatched Query Wizard and click OK.
    Find Unmatched Records

    Now you need to choose the table or query whose values you want to display in the query. For example, if you ran a tour company and wanted to find customers without any tour packages, you would select the tblCustomers table.

  4. Select the table whose values you want to display and click Next.
    Find Unmatched Records

    Here you have to tell Access which table contains the related records. For example, you might select the tblCustomerTours table.

  5. Select the table that contains the related records and click Next.
    Find Unmatched Records

    Here you should specify the related field to join the records in the first table to the records in the second table. To do this, you need to select the same field in both lists.

  6. Select the same field in both tables, click the <=> button to join the two tables, and click Next.
    Find Unmatched Records

    Now you need to specify which fields you want to see in the query.

  7. Double-click any additional fields that you want to appear in the query results and click Next.
    Find Unmatched Records
  8. Give your query a name and click Finish.
    Find Unmatched Records

Access saves the query and displays its results.

FREE Quick Reference

Click to Download