databasedev.co.uk - database solutions and downloads for microsoft access

Microsoft Access Find Duplicate Query Wizard

Find Duplicate Query Wizard:

As the administrator of a Microsoft Access database, one of the jobs that you may need to do is the keep the database working as efficiently as possible. A major cause of performance issues and database slowdown is erroneous or duplicated data contained in a table. We can keep these problems to a minimum by validating data entry and preventing duplicate information from being entered.

We may have inherited a database that already contains duplicate information, and in the case we can use the Find Duplicates Query Wizard to search out this data.

Using this wizard we can create a query that reports which record(s) in a table are duplicated using some field or fields in this table as a basis for the query. Microsoft Access asks which fields that you want to use for checking duplication and also which other fields you would like to appear in the query results. We can also use the Find Duplicates Query Wizard to help find possible duplicate key violations; a valuable trick when you want to take an existing table of data and make a unique field with one of the existing data fields.

Creating a Find Duplicates Query

  1. From the Queries tab in the database window click on the New button
  2. Choose the Find Duplicates Query wizard and click OK
  3. Select the table or query that you want to search for duplicates and click Next
  4. Select the field(s) that might contain the duplicate entries and click Next
  5. Select any additional fields that you want to appear in the query results, click Next
  6. Name the query and click Finish to complete.

If we look at the following example, we can see how this works by checking a table for duplicate values. Here we will check a contacts table, that lists Company Contact Details:

  1. From the New Query list, select the Find Duplicates Query Wizard

    Choosing the Find Duplicates Query Wizard from the New Query dialog box
    Choosing the Find Duplicates Query Wizard from the New Query dialog box
  2. From the list of tables/queries choose the table which may contain the duplicate values.
  3. From the Available fields list, select the Company Name field, and move this to the Duplicate Value Fields list. Click Next to move on

    Which field contains the Duplicate Values?
    Which field contains the Duplicate Values?
  4. If you need to show other fields in the query results, select these in the next screen before moving on. In our example, we have included the CustomerID field

    Include Additional Query fields to display in your results
    Include Additional Query fields to display in your results
  5. Name the query and click Finish to complete the wizard. The query will now run and display the results, if there are any duplicates contained within the table:

    Query results displaying any duplicate records.

    As you can see above, the table contains duplicated values for the Company Name field, but unique CustomerID values (which is the Primary Key of the table).

Once you have verified the results, you can modify or delete the duplicated values contained in the table.

You may want to check out the following product, which overcomes some of the shortfalls in the Microsoft Access Find Duplicates Query Wizard:

Duplicates Finder | Microsoft Access Add-in

This Microsoft Access add-in is your ultimate tool to find, edit and delete duplicate records from your table. It is simple and easy to use yet powerful, versatile and comprehensive enough to be your one-stop resource as well as a real productivity-enhancing device. This utility works like Access' Find Duplicates Query Wizard(FDQW) while overcoming FDQW's shortcomings and expanding FDQW's functionality. It gets the job done quickly and efficiently. You need Access 2000 or higher to run this utility.

Check out Duplicates Finder's Key Features:

  1. Duplicates Finder works like Access’ Find Duplicates Query Wizard. You can use this five step self-explanatory add-in with no training.
  2. With Find Duplicates Query Wizard, you just get all your duplicate records, making it hard to distinguish among different instances of the duplicate records in each duplicate set, when you are trying to edit or delete your records if your table does not include a unique numeric field. Duplicates Finder automatically adds an AutoNumber field to your table if one is missing, enabling you to easily identify particular instances of duplicates.
  3. Duplicates Finder provides 12 different ways to query your table, facilitating your task of identifying, editing, and removing all duplicate records from your table quickly and efficiently.

    The Find Duplicates Wizard Add-In
  4. Find Duplicates Query Wizard also comes in handy when you need to identify the first or last order by each customer (Use a customer name field as a duplicate field).
  5. You can either get your query results in a datasheet view or make a table out of the results or delete your filtered data.
  6. You can adapt the 12 query techniques used for this add-in to your own needs.
  7. Duplicates Finder overcomes a Find Duplicates Query Wizard’s shortcoming: With Find Duplicates Query Wizard, if you do not select additional fields to display on top of your duplicate fields, you end up getting a list of first instances of duplicates as opposed to all instances of duplicates! With Duplicates Finder you do not have to select additional fields.

Order Now: Duplicates Finder | Microsoft Access Add-in