Unique ID's across multiple fields

[mackoski]

I am working on a database of biological samples and test data. A problem is that many times the same subject has multiple ID's. For example, one subject may have the ID "ID234" but they previously had the ID "Sub84" or something like that. I want to be able to have 3 or 4 fields that have ID's for a subject, and I don't want a single one of them to be repeated. So I have column a, b, c, and d, and I don't want to have one record to have the same ID in column a as another record has in column d and so on. Is there a way to do this? All I have been seeing is a way to make sure all of a, b, c, and d are unique combinations, but I want none of the fields to be repeated.

[June7]

Maybe ValidationRule property:

for fieldA: <>[fieldB] AND <>[fieldC] AND <>[fieldD]

for fieldB: <>[fieldA] AND <>[fieldC] AND <>[fieldD]

etc.

If that doesn't work, then probably need VBA behind a form.

[mackoski]

Maybe ValidationRule property:

for fieldA: <>[fieldB] AND <>[fieldC] AND <>[fieldD]

for fieldB: <>[fieldA] AND <>[fieldC] AND <>[fieldD]

etc.

If that doesn't work, then probably need VBA behind a form.

Thanks for your reply, but would this work for multiple records? This seems like it only works within a single record, making sure those fields are different within that record but not for the whole table.

[June7]

ID234 or Sub84 should not repeat in any field across all records? Each would be unique in the entire dataset?

Have you set each field as Indexed Yes(No Duplicates)?

Then along with the above ValidationRule, think will prevent duplicates.