Prevent duplicate values in a field

You can prevent duplicate values in a field by creating a unique index. A unique index is an index that requires that each value of the indexed field is unique.

There are two basic ways that you can create a unique index:

What do you want to do?


Set a field's Indexed property to Yes (No duplicates)

  1. In the Navigation Pane, right-click the table that contains the field, and then click Design View.
  2. Select the field that you want to make sure has unique values.
  3. In the Field Properties pane, on the General tab, set the Indexed property to Yes (No duplicates).

The Indexed Field Property

Top of Page Top of Page

Create a unique index for a field by using a data-definition query

  1. On the Create tab, in the Other group, click Query Design.
  1. On the Design tab, in the Results group, click the arrow under View, and then click SQL View.
  2. Delete all the SQL from the query.
  3. Type or paste the following SQL into the query:
CREATE UNIQUE INDEX 
 ON  ()
  1. In the SQL, replace the variables as follows:
    • Replace with a name for your index. It is a good idea to use a name that helps you determine what the index is for. For example, if the index is to make sure that social security numbers are unique, you might name it unique_SSN.
    • Replace with the name of the table that contains the field to be indexed. If the table name has spaces or special characters, you must enclose the name in square brackets.
    • Replace with the name of the field to be indexed. If the field name has spaces or special characters, you must enclose the name in square brackets.
  2. Save and close the query.
  3. Run the query to create the index. Note that you can run the query from a macro by using the RunSQL macro action. For more information, see the See Also section.

Top of Page Top of Page

 
 
Applies to:
Access 2007