Enforcing a unique combination relationship in fields

Question

Summery: I need any combination of [Field_1] and [Field_2] to be unique and for that uniqueness to be enforced. Note: This is not for permutations - and that's the difficulty.

In Depth: I'm trying to track contacts for vendor software. I've set my DB up in the time old fashion such that a Vendor record may have many contacts. The trick is that contacts may be related to each other and may not be related to the parent vendor record. An example:

  1. SuperBrokenSoftware is a tool who's vendor I need to contact all the time. 
  2. WeMakeBadSoftware is the Vendor
  3. Fred works for WeMakeBadSoftware
  4. Gale works for WeHelpPeopleWhenOthersWont

Let's say Gale is the appropriate contact to fix my issue with the SuperBrokenSoftware.

There is no way using the current hierarchy to track Gales relationship to SuperBrokenSoftware.

My solution is to keep track of these relationships in a table like so:

Field1   Field2   Field3   
Fred     Gale     Gale handles specific issues for Fred

However given this solution Field_1 and Field_2 must be unique in combination. That is to say the records:

Field1   Field2   Field3
Fred     Gale     "Gale handles specific issues for Fred"
Gale     Fred     "Gale is awesome - Fred sucks"

Should be viewed as the same. Record 2 should not be allowed in the database because it is not unique.

What I have Tried:

Using the bijective - Szudzik's function: a >= b ? a * a + a + b : a + b * b; where a, b >= 0

I can calculate a unique identifier for every combination - but access cannot enforce uniqueness on a calculated field.

What is the best way to enforce a combination in Access?

Thanks in advance!!!

Answer 1

Create new field for unique identifier with unique index and create Before Change data macro, which should insert/change calculated identifier in new field.

Unique key can be just sorted concatenation of field1 and field2