MS Access Query Design (Part 3)

Finding Matching Records, Identifying Duplicates, and Locating Missing Data in Access

Ensuring data integrity is one of the most critical responsibilities when managing a relational database. In this final part of our Access Query series, we explore how to find matching records between datasets, isolate duplicate rows for cleanup, and use unmatched queries to pinpoint missing records.

Finding Matching Records in Access Tables

A frequent requirement in data analysis is comparing two tables or saved queries to find common records. For instance, you might have separate donor lists from two fundraising campaigns and need to identify individuals who contributed to both.

To locate matching records:

  1. Create a standard Access Select Query.
  2. Add both tables or queries to the Query Design window.
  3. Drag the matching criteria fields between the two field lists (e.g., connect First Name, Last Name, and Address).
  4. Select the fields you want to view from either table and click Run.

Access executes an Inner Join between the datasets, returning only rows where all specified criteria fields match across both sources.

Finding Duplicate Records

Duplicate data compromises reporting accuracy and wastes resources. You can quickly detect duplicate records in a table by combining an Aggregate (Totals) Query with the Count function and a criteria filter.

Step-by-Step Duplicate Detection:

  1. Create a new Select Query containing the fields you suspect contain duplicates (such as FirstName and LastName).
  2. Click the Totals (Σ) button on the ribbon to convert it into an Aggregate Query.
  3. Add an additional field (such as the Primary Key ID field) to the grid.
  4. Set the Total: row for this added field to Count.
  5. In the Criteria: row beneath the counted column, enter > 1.
MS Access Query Duplicates Click to view query criteria for duplicate detection

When you execute the query, Access groups records by name and filters the output to show only names that appear more than once:

Access Duplicate Query Result Click to view duplicate search results

Finding Missing Records in Access Tables

Finding unmatched records is essential for upsell campaigns, database audits, and reconciliation. For example, you may want to identify all customers who bought Product X but have not yet purchased Product Y.

The most reliable way to create this comparison is using the built-in Find Unmatched Query Wizard:

  1. Create a Select Query filtering customers who purchased Product X.
  2. Create a second query filtering customers who purchased Product Y.
  3. On the Create ribbon tab, click Query Wizard and select Find Unmatched Query Wizard.
Access Query Wizard Selection Dialog Click to view Query Wizard options

The wizard guides you through selecting your base table/query (the source with records you know exist) and the comparison table/query (where records may be absent). Next, specify the shared identifier field to link both datasets:

Access Unmatched Query Wizard Step Click to view unmatched field matching

The resulting query outputs records that exist in the primary source but have no matching entries in the target table. This technique is invaluable for customer re-engagement, verifying data imports against backup tables, and auditing relational integrity.

Turn Query Insights Into Visual Analytics

Easily convert complex queries, summaries, and KPIs into interactive executive charts and graphs inside Microsoft Access.

Explore Dashboard Builder for MS Access