Software

Use a query to check referential integrity in Access

Learn how to create a select query to find records in one table that don't have related records in another table. This capability will save you loads of time when you need to query data.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

When working with tables in Access, you often have to refer to data in various tables—even if the tables aren't related. The good news is that it's simple to create a select query to find records in one table that don't have related records in another table.

For example, suppose your database contains a table named Computers 101-Registered Students, which lists the students who have registered for a Computer course. Your database also has a second table named Computers 101-Attendance, which lists the students who are currently attending the course. Let's say that you want to find out if any students attending the course have not yet registered.

Follow these steps to set up a query that will answer your question:

  1. Select Queries under Objects in the Database Window.
  2. Go to New | Find Unmatched Query Wizard and then click OK.
  3. Select Computer 101-Attendance and then select Computer 101-Registered.
  4. Select the matching field, for example, Student ID.
  5. Select the student first_name and last_name fields and click Finish.

The results will list the names of the students who are listed as attending the course but have no record of being registered.

Editor's Picks

Free Newsletters, In your Inbox