Hack 42. Find Unmatched Records on Multiple Field Keys

The Find Unmatched Query Wizard looks for unmatched records based on a single related field. You can adapt this query to work on more than one related field.

The easiest way to find records in one table that has no related records in another table is to use Access's built-in Find Unmatched Query Wizard. Figure 5-5 shows the New Query dialog box, which accesses the wizard.

Figure 5-5. Starting up the Find Unmatched Query Wizard

A handful of wizard screens walk you through setting up the query. You select the two tables and even which way the query should work. For example, do you need to know which records in Table A have no related records in Table B? Or do you need to know which records in Table B have no related records in Table A? Either way, the key to making this possible is that the tables are related in the first place.

Strictly speaking, the tables selected to be in an unmatched query don't have to be formally related, at least in regard to setting up a relationship in the Relationships window. It's just that the fields being matched should be housing the same data; otherwise, all records are returned as unmatched.

Tables can be related on single field keys or on multiple field keys. Unfortunately, the wizard lets you specify only a single field to relate the tables, as shown in Figure 5-6.

Figure 5-6. Specifying a single field to be included for the match

Select a single field from each table, on the left and right, and then click the button between the two tables to set the match the query will use. The wizard generates a query that is saved in the database. This is convenient because it allows you to reuse the query without having to recreate it. You also can change the query, which I'll describe next.

5.4.1. Reviewing the Query

The example used here finds which customers have no matching records in a sales table. Using the Find Unmatched Query Wizard, I can look for customers based on their last name alone. Figure 5-7 shows the query design the wizard generated.

The query uses a LEFT JOIN to return all records of customers whose last name field is Null in the sales table. The SQL looks like this:

	SELECT tblCustomers3.FirstName, tblCustomers3.LastName
	FROM tblCustomers3 LEFT JOIN tblSales3 ON
	tblCustomers3.LastName = tblSales3.LastName
	WHERE (((tblSales3.LastName) Is Null));

Figure 5-7. The unmatched query design

There is a problem here, though. Two customers might have the same last name. In that case, as long as one of the customers has a record in the sales table, any other customers with the same last name don't appear in the query's results, even if they should.

Figure 5-8 illustrates this point. Left to right across the screen are the customer table, the sales table, and the query that looks for customers that have no sales. Starting on the left, there are two customers with the same last name: Kam Winter and Muriel Winter. In the sales table, in the middle, Muriel Winter has a sales record. In the query result on the right, Kam Winter is not listed as a customer with no sales, even though Kam should be there.

Because the last name is all that is tested, all customers with the same name are skipped in the query results, as long as one of them has a sales record. This isn't acceptable.

5.4.2. Changing the Query

All you need to do is alter the query so that both the last name and the first name are tested. We do this in the query design, in either the grid or the SQL pane. Figure 5-9 shows how the query is designed now.

It's important to make sure a few things are changed correctly:

You need to add a criterion that looks for Null in First Name.
You need to add a second relationship between the tables, on the new included field. Look closely at the differences in how the tables in the query are related, comparing the design in Figure 5-7 with the design in Figure 5-9.

Figure 5-8. Reviewing tables and the unmatched query

Figure 5-9. The unmatched query, now testing on two fields
You should uncheck the fields that come from the secondary table (the sales table in this example); that is, they should not appear in the output.

Figure 5-10 shows how the query returns Kam Winter as being a customer with no sales records. Some other customers appear in the result as well.