Home > Sample chapters > Microsoft Office > Access

Selecting Data Using Queries in Microsoft Access 2013

Joining tables to see unmatched or missing records

In Join multiple tables, on page 99, we saw how to join more than one table on a query. Tables can be joined in one of three ways. The method of joining is shown on the diagram, with either no arrow or an arrow at one or the other end of the join. When you click the join, you will see a text description of how each choice will affect the data.

There are two classic uses of changing the join. The first allows you to identify unmatched child records (which can prevent you from creating relationships that enforce rule checking), and the second is useful when you appear to have missing records in the query results.

Find unmatched child records

  1. Double-click the relationship.

  2. In the Join Properties popup window, click the option to include all child records. (In this case, the Orders table is a child of the Customers table.) This will now show all orders, whether or not there is a customer in the Customers table for the corresponding ID recorded in the Orders table. Click OK to close the Join Properties popup window.

  3. Add criteria to identify where the parent key (ID in Customers table) has no value (Is Null). This filters the query results to show only those records in the Orders table where there are no matching customers in the Customers table.

Display all parent records with and without child records

  1. Double-click the relationship.

  2. In the Join Properties popup window, click the option to include all parent records (from the Customers table).