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
- From the Queries tab in the database window click on the New button
- Choose the Find Duplicates Query wizard and click OK
- Select the table or query that you want to search for duplicates
and click Next
- Select the field(s) that might contain the duplicate entries and
- Select any additional fields that you want to appear in the query
results, click Next
- 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:
- From the New Query list, select the Find Duplicates Query Wizard
Choosing the Find Duplicates Query Wizard from the New Query dialog
- From the list of tables/queries choose the table which may contain
the duplicate values.
- From the Available fields list, select the Company Name field,
and move this to the Duplicate Value Fields list. Click Next to
Which field contains the Duplicate Values?
- 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
- 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:
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
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:
- Duplicates Finder works like Access’ Find Duplicates Query
Wizard. You can use this five step self-explanatory add-in with
- 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.
- 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.
- 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).
- You can either get your query results in a datasheet view or make
a table out of the results or delete your filtered data.
- You can adapt the 12 query techniques used for this add-in to
your own needs.
- 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