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.

Starting up the Find Unmatched Query 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.

Tip

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 ...

Get Access Hacks now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.