Enforce Multiple Relationships Between Two Tables In Access

Question

I want to create (and enforce) multiple "one-to-many" relationships between the primary key of one table to attributes of another.

Hopefully this example will explain better. I have a table for a character in a game. The character can hold one item in each hand.:

Table: GamePlayer
gamePlayerID    Number  PK
name            Text    Unique
classID         Number  FK->PlayerClass.classID
leftHandItem    Number  FK->Items.itemID
rightHandItem   Number  FK->Items.itemID

How do I implement the leftHandItem -> Items.itemID and rightHandItem -> Items.itemID with enforcing referential integrity.

Answer 1

In the Relationships window, add the Items table twice. The second occurence will automatically get an alias. Use the 2 occurences like if they were 2 distinct tables to create relationships.