Relationships - linking reference table field to 2 main table fields (1 Viewer)

My database has staff reference table, listing all the current staff.

On my main table, there are 2 separate staff fields - one for the person who did initial contact, and one for the person who later was assigned the case. Both of these fields store numeric data (since they appear on the form as option groups).

When I set up the relationship to link the two tables, Access won't let me link my staffcode field from my reference table to both of the staff fields on my main table, unless I deactivate the "enforce referential integrity" box. I want to enforce referential integrity for both relationships, in case of staff changes or name changes. How can I do this?

I've attached an image of the error message and relationships.

Thanks in advance.

Add StaffRef to Relationships twice, linking each copy to ONE of the indicated fields. That way you get two individual outer joins rather than one double outer join, which, as you see, gives Access twitching fits. That way you'll be able to maintain integrity.

You'll need to do the same to any query pulling up both fields.

SOLVED: Relationships - linking reference table field to 2 main table fields

I could have sworn I tried that before and it didn't work, but now it did. Many thanks!