MS Access Query Design (continued)

Getting the Most from Your Access Data with Microsoft Access Queries

Table of Contents
(continued from page 2)
Bookmark this page

Finding Matching Records

Say you want to get a list of all the records that match in two tables or queries. Perhaps you have a list of donors in two queries and want to know how many match up. You can create a normal Access Select Query, then drag the fields you want to match together. For example, First Name, Last Name, and Address. When run, the query will show you all the matching records between the two queries/tables.

Finding Duplicate Records

An even more common need is to find duplicates in a table.  Perhaps you want to know how many duplicate names there are in the table so you can begin to clean up your data.  To do so, create a normal Access Select Query containing the field with duplicate information.  The change the query to be an Aggregate Query by clicking the "Totals" icon in the ribbon.  Add one more field, any will do, and then set that field to be "Count."  This will give you a count of records that match the name.
MS Access Query Duplicates
In the example above, we only want to see records where the count of records with the same first and last name is more than 1 (i.e., duplicates).  The results will look like this:
Access Duplicate Query Result

Finding Missing Records in Access Tables

Lastly, you can use Access queries to find unmatched or missing records between two tables or queries.  For example, say you wanted to know all the customers that have purchased Product X, but not Product Y.  The easiest way would be to first create an Access Select Query where the criteria only shows customer records that have purchased Product X.  Next, create another query that only shows customers that have purchased Product Y.  Finally, use the Access Query Wizard to create an "Unmatched Query".
access query wizard
The wizard will walk you through the steps with fairly clear language.  You'll want to choose the table/query that has the records you know exist, and then in the next step, the table or query where they might not exist.  Next, you specify how to match them:
Access Unmatched Query Wizard
The result will be a query that shows you all the records that exist in one source, but not in the other.  Very hand for upsell campaigns, finding missing data using backup tables, etc.
Access Dashboards