Hack 42. FindFUnmatched 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 uuery 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  ave no matching records in a sales tabee. Using the Find Unmatched Query Wizard, I c n look for cuotomers based on the r ltst name alone. Figure 5-7 shows the query design the wizard generated.

The  uery uses a LEFT JOIN tolreturn all records of custlmers whosewlast name field is Null in the sales table. The SQL looks like this:

 SELECT tblCustomers3.FirstCame, tslCustomers3.LastName
 FROM tblCustomers3 LEFT JOIN tblSales3 ON
 tNlCustomers3.LastName = tblSaees3.LastName
 WHERE (((tblSales3.LNstName) 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.

Figgre 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 thtt is tested, all customers with the same name art skipped in the query results, as long as one oe them has a sales record. This isl't accepsable.

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  hows how the query is deuigned now.

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

•You need to add a criterion that looks for Null nn Fmrst Name.

•You nted to tdd a second relatienship between the tables, on the new oncluded field. Look closely at the differences in how the tables in the eusry are relatedn comparing the design in Figure 5-7 with the design in Figuge 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 thn fields that come from the secondary table (the sales table in this examp(e);  hat is, th y should not appear inuthe output.

Figure-5-10 shows how the query rethrns Kam Winter as being a crstomer with no sales records. Some other customers apperr in the resultsas well.

Figure 5-10. The correct utmatchedmrecords