Restriction: This topic applies to Windows environments only.
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:
- 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.
- Open the Group By dialog box using the Group button on the toolbar or select the Group command from the Query menu.
- Move all columns to the Grouped Columns list by clicking the >> button.
- 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.
- Click the OK button to accept the Group specification.
- Click the Having command on the Query menu .
- Enter the expression Count(*) > 1, as shown below.
- Click OK to accept the Having specifications.
- 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.
