Share via

Validation Rule that prevents Duplicates from being entered

Anonymous
2020-07-09T13:55:47+00:00

Hello, I am stuck

   I am very new to Access and need a rule to prevent duplicate records from being entered into a table. It needs to compare 2 Fields.

[Field1] and [Field2] when combined(or already exists) must be unique to enter a new record. 

I have tried IF statement but can quite get it to work. I'm missing something.

Any help would be great.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2020-07-09T16:22:54+00:00

    Sorry but this is the message I get when I try to do the indexing. 

    My guess would be that you have indexed one or both of the columns individually, not both columns as a pair in a single index.  If you follow the description I gave in my last reply of how to create a unique index on two columns you should be able to achieve your aim.

    On the subject of constraints in general, you might like to take a look at Constraints.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    This little demo file illustrates how database constraints can be applied through normalization of the tables, indexing and by the application of a CHECK CONSTRAINT to a table.

    1 person found this answer helpful.
    0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom van Stiphout 40,201 Reputation points MVP Volunteer Moderator
    2020-07-09T14:01:32+00:00

    Not a validation rule, but a unique index.

    Design the table, click on Indexes in the ribbon and enter a new one, select it to be unique, and select the two fields.

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2020-07-09T15:34:03+00:00

    The simplest solution is to make the two columns the composite primary key of the table.  To create a composite key on two or more columns (fields), in table design view hold down the Ctrl key and left click on each of the columns, being sure to click on the 'field selector' (the small square to the left of the column name).  Then right click and select 'Primary Key' from the shortcut menu or click the Primary Key icon in the ribbon.  You should then see the key symbol against each of the columns.

    If the table already has another candidate key as its primary key, then the two columns should be included in a single unique index as Tom pointed out.   

    For instance, it might be necessary to create a unique index on the EmployeeID and ProjectID in a ProjectEmployees table to prevent the same employee being assigned to the same project more than once.  This is done in table design view by selecting Indexes on the Design ribbon.  In the dialogue which opens enter a suitable name for the index in the first empty row of the Index name column, ProjectEmployee say.  In the Field Name column of the same row enter ProjectID, and with this row still selected, in the Index Properties below, select 'Yes' as the Unique property.  Then enter EmployeeID in the Field Name column in the row immediately beneath ProjectID.  Don't enter anything in the Index name column for this row.

    0 comments
  3. George Hepworth 22,765 Reputation points Volunteer Moderator
    2020-07-09T15:23:59+00:00

    Reread Tom's suggestion, please.

    0 comments
  4. Anonymous
    2020-07-09T15:18:18+00:00

    So [Field 2] can not be unique in that it maybe associated with more than one item in [field 1] which may have many combinations with [Field 2]. I need to be able to basically say if Field 1 and Field 2 combined already exist then no new record can be created otherwise a new record can be created. Thanks for your help.

    0 comments