Hack 42. Find Unmatched Records on Multiple Field Keys
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.
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:
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.
Figure 5-10. The correct unmatched records