Searching for Duplicate Values

Before you can create a unique index on a column, you must make certain that there are no duplicate values in the column. An easy way to do this is to use a summary query that groups records based on the index fields, and include a HAVING condition that tests to see if the count of records in any group is greater than 1.

To search for duplicate values:

1. Open or create a query that includes just the columns on which you want to build a unique index. These are the columns you need to test for duplicates.
2. Open the Group By dialog box using the Group button on the toolbar or select the Group command from the Query menu.
3. Move all columns to the Grouped Columns list by clicking the >> button.
4. The order of the columns in the GROUP BY clause is not important in this particular query. However, it is always a good idea to list the columns in hierarchical order. For example, City should come after State, because each state can contain many cities.
5. Click the OK button to accept the Group specification.
6. Click the Having command on the Query menu .
7. Enter the expression Count(*) > 1, as shown below.
8. Click OK to accept the Having specifications.
9. The query is now ready to run or save. When you run the query, you see the list of records that have duplicate values in the candidate index columns.