Database Help for Tough and Common Problems

Need help on something you don't see here? E-mail us!

How do I create a query that shows all the entries that are NOT part of a relationship?

The problem:

You've got a one-to-many relationship between two tables. You want to show all the elements in the first table (the "one" side) that do not appear in the second table (the "many" side).

A solution:

What you're trying to do an "exclusive" query, rather than the "inclusive" queries that we've done before. The type of joins that we've done so far ("Only include rows where the joined fields from both tables are equal") only allow us to do inclusive queries. This type of join is called an "inner join".

We need something called an "outer join" to do an exclusive query. An outer join includes all the elements of one table plus the matching elements of the second table. There are two types of outer joins, a "left outer join" and a "right outer join", both of which are options in Access.

What you want to do is to

  1. Make a query as normal, using both tables.
  2. Modify the join properties of the one-to-many relationship in the query (not in the main relationship diagram!) such that you include all the rows in the "one" table and those rows in the "many" table where the joined fields are equal. This may be choice 2 or 3, depending on how you put the tables in the query. See the pictures below.
  3. You will notice that the one-to-many line has changed to a line with an arrow pointing to the many side. Make sure that it is pointing this way and not the other way.
  4. In the query, be sure to include the key from the table on the many side, but don't bother displaying it.
  5. That key will have a criteria however. The criteria is that it "Is Null". This will cause the query to select only those records that do not appear in the many table. The outer join ensures that all the records from the "one" table are present and thus when the records from the many table are excluded by the "Is Null" criteria, the records remaining are the ones you want.

The above example can be downloaded here: ExclusiveQuery.mdb

To better understand what the join and criteria are doing, have the above query show ID2. Then compare the results with and without the "Is Null" criteria.