Lesson6.11.Finding Unmatched Records

Lesson 6.11. Finding Unmatched Records

Figure 6-22. Select the table or query with the values you want to display in the query.

Figure 6-23. Specify the field that will join the records in the first table to the records in the second table.

Figure 6-24. The query displays those records in the Customers table that do not have any matching records in the tblCustomerTours table.

The helps you find the records in one table that do not have matching records in another table. 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. For example, if you delete a customer record from a Customer table, you may leave several for that customer in an Order table.

In this lesson you will use the Find Unmatched Query Wizard to create a query to find customers who have never booked a tour.

1. In the Database window, click the Queries icon in the Objects bar and click the New button.

   The New Query dialog box appears.

2. Select the Find Unmatched Query Wizard and click OK.

   The first step of the Find Unmatched Query Wizard appears, as shown in Figure 6-22. You need to choose the table or query whose values you want to display in the query. Since you want to find customers without any tour packages, you would select the tblCustomers table.

3. Select the tblCustomers table and click Next.

   The second step of the Find Unmatched Query Wizard appears. Here you have to tell Access which table contains the related records. Let's select the tblCustomerTours table.

4. Select the tblCustomerTours table and click Next.

   The third step of the Find Unmatched Query Wizard appears, as shown in Figure 6-23. Here you have to specify the related field to join the records in the first table to the records in the second table. Once you have selected the matching record in both tables, click the button to join the two tables.

5. Verify that the CustomerID field is selected in both tables and click the button.

   The Matching fields area displays the fields used to join the tables (CustomerID = CustomerID).

6. Click Next.

   Almost there! Now you have to specify which fields you want to see in the query.

7. Double-click the LastName, FirstName, City, and State fields.

   The LastName, FirstName, City, and State fields should all appear in the "Selected fields" list.

8. Click Next.

   Here you can give your query a nameor you can accept the default name that Access gives the query.

9. Click Finish.

   Access saves the query with the name "tblCustomers Without Matching tblCustomerTours" and displays the results of the query, as shown in Figure 6-24.

10. Close the query.

QUICK REFERENCE

TO USE THE FIND UNMATCHED QUERY WIZARD:

1. IN THE DATABASE WINDOW, CLICK THE QUERIES ICON IN THE OBJECTS BAR AND CLICK THE NEW BUTTON.

2. SELECT FIND UNMATCHED QUERY WIZARD AND CLICK OK.

3. SELECT THE TABLE WHOSE VALUES YOU WANT TO DISPLAY AND CLICK NEXT.

4. SELECT THE TABLE THAT CONTAINS THE RELATED RECORDS AND CLICK NEXT.

5. SPECIFY THE RELATED FIELDS THAT JOIN THE TWO TABLES, THEN CLICK THE button to join the two tables and click Next.

6. DOUBLE-CLICK ANY ADDITIONAL FIELDS THAT YOU WANT TO APPEAR IN THE QUERY RESULTS AND CLICK NEXT.

7. CLICK FINISH.