Selecting Data Using Queries in Microsoft Access 2013
- 3/15/2013
- Selecting all columns from a table
- Selecting individual columns from one or more tables
- Joining tables to see unmatched or missing records
- Filtering by single and multiple combinations of choices
- Adding calculations with the expression builder
- Returning the top matched records
- Eliminating duplicate values
- Creating a summary calculation
- Prompting to filter data with parameters
- Creating a crosstab query with the Query Wizard
- Simplifying a problem with a query by using other queries
- Adding two sets of query results together
- Resolving ambiguous outer joins
- Creating an additional query to resolve a problem with mixed joins
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
Double-click the relationship.
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.
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
Double-click the relationship.
In the Join Properties popup window, click the option to include all parent records (from the Customers table).
)