ACC2000: How to Use the Find Unmatched Query Wizard (322798)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q322798
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

For a Microsoft Access 2002 version of this article, see 319017.
For a Microsoft Access 97 version of this article, see 322799.

SUMMARY

You can use the Find Unmatched Query Wizard to create a select query that finds records in one table that do not have related records in another table.

For example, in the Northwind.mdb sample database, you can find customers who do not have orders. The query compares the Customer ID field in the Customers table with the Customer ID field in the Orders table. The query finds all instances of Customer ID entries that appear in the Customers table but that do not appear in the Orders table.

If the Enforce Referential Integrity feature has been selected, a Customer ID cannot exist in the Orders table (on the "many" side of the relationship) unless it exists in the Customers table (on the "one" side of the relationship). A company that has not placed an order can be listed as a customer, but each order must have a customer.

MORE INFORMATION

To use the Find Unmatched Query Wizard, follow these steps.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

  1. Open the Northwind.mdb sample database.
  2. In the Database window, click Queries under Objects, and then click New on the Database window toolbar.
  3. In the New Query dialog box, click Find Unmatched Query Wizard, and then click OK.
  4. In step 1 of the wizard, click Table: Customers in the list of tables and queries, and then click Next.

    NOTE: The Customers table contains the records that you want in the query results.
  5. In step 2 of the wizard, click Table: Orders in the list of tables and queries, and then click Next.
  6. In step 3 of the wizard, click CustomerID under Fields in 'Customers'.

    Under Fields in 'Orders', click CustomerID, and then click the <=> button to designate these fields as the ones to be compared. Click Next.

    NOTE: Access selects the CustomerID fields because they have the same name. However, fields that match might have different names.
  7. In step 4 of the wizard, select the files that you want to include in your results. For this example, click the Add All Fields (>>) button, and then click Next.
  8. In the final step of the wizard, change the title of your query. For this example, accept the default name Customers Without Matching Orders, and then click Finish.

    The query returns two records.
NOTE: If the resulting query is not exactly what you want, you can rerun the wizard or change the query in Design view.

REFERENCES

For additional information about how to enforce referential integrity, click the article number below to view the article in the Microsoft Knowledge Base:

304467 ACC2000: Defining Relationships Between Tables in a Microsoft Access Database

Modification Type:MajorLast Reviewed:6/30/2004
Keywords:kbhowto KB322798
©2004 Microsoft Corporation. All rights reserved.