You can prevent duplicate values in a field in an Access table 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:
-
Set the field's Indexed property to Yes (No duplicates) You can do this by opening the table in Design view. This method is easy and a good choice if you only want to change one field at a time.
-
Create a data-definition query that creates the unique index You can do this by using SQL view. This method is not as easy as using Design view, but has an advantage: you can save the data-definition query and use it again later. This is useful if you periodically delete and re-create tables and want to use unique indexes on some of the fields.
What do you want to do?
Set a field's Indexed property to Yes (No duplicates)
-
In the Navigation Pane, right-click the table that contains the field, and then click Design View.
-
Select the field that you want to make sure has unique values.
-
In the Field Properties pane at the bottom of the table design view, on the General tab, set the Indexed property to Yes (No duplicates).
-
Save the changes to your table.
Note: If duplicates already exist in the field for your table records, Access displays an error message (Error 3022) when you try and save the table changes with the new index. You'll need to remove those duplicate field values from the table records before you can set and save the new unique index.
Create a unique index for a field by using a data-definition query
-
On the Create tab, in the Queries group, click Query Design.
-
On the Design tab for queries, in the Results group, click the arrow under View, and then click SQL View.
-
Delete all the existing SQL from the query. (Access most likely just displays SELECT; before you delete the existing text.)
-
Type or paste the following SQL into the query:
CREATE UNIQUE INDEX index_name
ON table (field); -
In the SQL, replace the variables as follows:
-
Replace index_name 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 phone numbers are unique, you might name it unique_phone.
-
Replace table 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 field 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.
-
-
Save and close the query.
-
Run the query to create the index. Note that you can run the query from a macro by using the RunSQL macro action.
