CODEWITHSUNDEEP
ms-accessms-access-2007
Hold The Door
Hold The Door

Reputation: 39

Is there a way to create a constraint in a table column to prevent duplicate entries?

I am trying to have access constrain duplicate entries in a column.

The reason why is because I have multiple years of salary that can be entered any number of times.

For example,

ID Salary Year
22 $20000 2013
22 $15000 2012
22 $20000 2013 <- Causes duplicate entries in my report
23 $20000 2013 <- Need to have 2013 here though

I want it so that a user cannot enter 2013 more than once in the column of my COMP table.

EDIT: Added ID numbers because I have multiple people in my COMP table. This changes the question.

Upvotes: 0

Views: 1616

Answers (3)

Johnny Bones
Johnny Bones

Reputation: 8404

Make your Primary Key based on one (or more) columns. If you need to base it on 2 columns, go into the Design View of the table, highlight the fields you want it based off of, and right click. Choose "Primary Key" from the popup menu. It looks, from a previous comment, you need to prevent duplicates to the combination of ID and Year. So highlight those 2 rows from Design View and make them the Primary Key. This will prevent you from having 2 records in the table with identical information in ID and Year.

Make sure your report is GROUPED by ID and Year, which will prevent duplicates.

Upvotes: 0

HansUp
HansUp

Reputation: 97131

"I want it so that a user cannot enter 2013 more than once in the column of my COMP table."

  1. Open COMP in Design View.
  2. Select the Year field.
  3. Select the General tab from the "Field Propeties" (down below).
  4. Choose "Yes (No Duplicates)" from the Indexed property dropdown.
  5. Save the changes.

If you prefer to do it with code instead ...

CurrentDb.Execute "ALTER TABLE [COMP]" & vbCrLf & _
    "ADD CONSTRAINT uniq_year UNIQUE ([Year])"

Note that statement will not succeed unless the existing [Year] values are unique. Also, both COMP and Year are reserved words. Enclose them in square brackets so the db engine will recognize them as field names and not complain.

If you decide the constraint should be based on 2 fields ...

CurrentDb.Execute "ALTER TABLE [COMP]" & vbCrLf & _
    "ADD CONSTRAINT uniq_id_year UNIQUE (ID, [Year])"

You could also create the same unique index/constraint from table Design View, but that is more difficult for me to describe. It may be easier for you to execute the DDL statement, then examine the result in Design View. Just make sure the table is not in Design View when you try to execute the statement.

I created table COMP with Long Integer fields, ID and Year. Then executed this statement as a new query in the query designer:

ALTER TABLE [COMP] ADD CONSTRAINT uniq_id_year UNIQUE (ID, [Year])

Here is a screen capture of my table in Design View after executing that statement.

unique index in table design view

I don't understand why the same statement is not working for you. So open the table in Design View and find the UI methods to create the unique index as displayed in that picture.

Upvotes: 3

Adriaan Stander
Adriaan Stander

Reputation: 166486

You can Create a unique index on this field

If you create a unique index, Access doesn't allow you to enter a new value in the field if that value already exists in the same field in another record. Access automatically creates a unique index for primary keys, but you might also want to prohibit duplicate values in other fields. For example, you can create a unique index on a field that stores serial numbers so that no two products have the same serial number.

Upvotes: 0

Related Questions