MS Access Query Design (continued)
Getting the Most from Your Access Data with Microsoft Access Queries
Table of Contents
What is a Query in MS Access
Building a Basic Query
Sorting and Filtering Data
Editing Data in a Query
Why Are Records Missing in My
Access Query!
Special
Types of Queries
Calculating Data in a Query
Grouping Data with Access Aggregate Queries
Find Matching Records in Access Tables
Finding Duplicate
Records
Find
Missing Records in Access Tables
(continued from page
2)
Bookmark this page
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.
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:
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".
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:
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.
