No Duplicates - Across multiple fields only.

[Torinjr]

I have a table, displayed above, that is updated using a form. As you can see the 2nd field has 3 duplicates for (100 mm white.) with field 4 being different for ID 86.

I have purposefully allowed for duplicates by setting the Indexed as Yes (Duplicates OK) because the records are different in field 4. However, i want to disable duplicates when the Records are identical accross all fields, as shown by ID 84 and 85. Is this possible?

Would it be easier to set up a query for the Form that check for these duplicates across all fields instead?


Cheers

[JeroenMioch]

I think you can run a delete duplicates query and select the fields you want to be in it.
So you would leave the ID number out of the query.

[CJ_London]

in your table design, click on indexes in the ribbon, give it a name (any name) then add your fields you want to be unique i.e. whatever your fields are called for 100mm white, Marshland... and road marking (not ID). Then click back on the index name and set Unique=yes

Note you will need to remove any existing duplicates before you can set this up.

[Torinjr]

Thanks Ajax although this only helps me with duplicates in individual fields. I want to remove items that are duplicate across all fields. I think the query might be the best way to go.

[CJ_London]

Thanks Ajax although this only helps me with duplicates in individual fields

Clearly you have not tried my suggestion - the example in my post will prevent duplicates across fields A,B and C -so in your example the row with ID=85 could not be entered.

[Torinjr]

Oh yes it worked. Sorry i mis-read your solution and over looked it. Thank you this has helped lots.