O'Reilly logo

Access Hacks by Ken Bluttman

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required