Multiple fields in one table referencing the same field in a second table
I'm not an expert by any means, so I'm asking for anyone who can help. I have a db with a Locations table. This table contains fields for: Site Contact, Backup Site Contact, Gatekeeper, and Backup Gatekeeper. I would like to be able to reference a seperate Contacts table so if I update a contacts information I only need to do it once. Other info: a single contact could fulfill all roles.
A relationship doesn't work because it wants to "and" the site contacts and gatekeepers together to reference a single contact record, and I cannot have multiple relationships between tables. I tried a Value List, but how do I keep the reference intact?
I have uploaded a picture that I hope helps to show what I'm trying to accomplish.