Advanced Database – Create, run a query to show unmatched records in related tables.

Again, there is no automatic way to do this in Base, unlike Access.

The only way to do it is to use a subtract join which we covered in the relationships section.

28 Unmatched Records Table

As a reminder, let’s set a new one up.

Open the Mechanics folder and add a new staff member.

29 Query Design

Let’s say we want to display the mechanics that haven’t worked on any jobs

Create a new query and add tables Mechanics and Jobs.

Create a Right join between the Staff Number fields.

In the first column show all fields from Mechanics, in the second column select the Staff Number field from Jobs with the Criterion ‘IS EMPTY’ (uncheck visible as we don’t need to see the empty record)

30 Empty Criterion

Press F5 to run the query.

The query will return any records from the Mechanics table that have unmatched staff numbers in Jobs table.