applied tips : may 2008

microsoft access : queries 201

Use the Query Wizard to find repetitive and orphaned records in your database.

Over time, the information stored in a database can get messy. Two common database problems are duplicate records and unmatched records. A duplicate record is the same information that have been extraneously entered in two or more records—usually the result of a data entry error. An unmatched record is a record in one table that does not have matching records in another related table.

Fortunately, you can easily create queries to find duplicated and unmatched records. In this issue we’ll take a look at how to create Find Duplicates and Unmatched Records queries.

Find duplicates queries

The Find Duplicates Query Wizard helps you find records that have the same value in one or more fields. So when would you need to search for duplicates in your database? Here are a few reasons:

Here’s how to create a Find Duplicates query:

  1. From the Database window, click the Queries icon in the Objects bar and click the New button. The New Query dialog box appears.
  2. Select the Find Duplicates Query Wizard and click OK.  The first step of the Find Duplicates Query Wizard appears. You need to choose the table or query that you want to sift through for duplicate records.
  3. Select the table or query you want to search and click Next. The second step of the Find Duplicates Query Wizard appears. Tell Access which field or fields might contain the duplicate information.
  4. Double-click the field(s) that may contain the duplicate values and click Next. The next step of the Find Duplicates Query Wizard appears. You can select any field (other than the ones you specified in Step 3) that you want to be displayed in the query.
  5. Double-click any additional fields that you want to appear in the query results and click Next.
  6. Click Finish.

That’s it! Microsoft Access will create the query and display the duplicate records found in the query.

Find unmatched queries

The Find Unmatched Query Wizard helps you find the records in one table that do not have matching records in another table. Some scenarios when you might need to create such a query include:

To create an unmatched query:

  1. From the Database window, click the Queries icon in the Objects bar and click the New button. The New Query dialog box appears.
  2. Select the Find Unmatched Query Wizard and click OK. The first step of the Find Unmatched Query Wizard appears. You need to choose the table or query whose values you want to display in the query.
  3. Select the table whose values you want to display and click Next. The second step of the Find Unmatched Query Wizard appears. Here you have to tell Access which table contains the related records.
  4. Select the table that contains the related records and click Next. The third step of the Find Unmatched Query Wizard appears. Here you have to specify the related field to join the records in the first table to the records in the second table. Once you have selected the matching record in both tables, click the  button to join the two tables.
  5. Specify the related fields that join the two tables and then click the  button to join the two tables and click Next. The Matching Field area displays the fields used to join the tables.
  6. Double-click any additional fields that you want to appear in the query results and click Next. Almost there! Now you have to specify which fields you want to see in the query.
  7. Click Finish.

The Query Wizard is a valuable resource. Don’t forget to use this quick and easy tool in your databases!

schedule a class or get consulting on access

Request an on-site Access class from Applied Office or get some helpful consulting. Learn more here

upcoming classes on access

Classes on Microsoft Access are scheduled at University of the Pacific, and you can attend!  Review the Upcoming Classes for more information.

quick reference card

Get the Quick Reference Card on Microsoft Access! Download it for free and print it on your own printer. You might even want to laminate it.