(continued from
page
2)
Bookmark this
page
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.
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:
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.