AnsweredAssumed Answered

Multiple fields in one table referencing the same field in a second table

Question asked by TimRaines on Aug 9, 2010
Latest reply on Aug 9, 2010 by philmodjunk
  • Comment • 1

Title

Multiple fields in one table referencing the same field in a second table

Post

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.

Any thoughts??

Snap1.jpg

philmodjunk
philmodjunk Aug 9, 2010 11:57 AM
Correct Answer

You can have as many relationships between the same two tables as you need. You just need some extra "boxes" on your relationships tab that all refer to your contacts table to make this happen.

  1. Click Contacts to select it
  2. Click button number 3 at the bottom (It as two green plus signs). And you'll get a new "table occurrence" for contacts. Drag from Backup Site Contact to Contacts 2::Contact ID and you'll have a link for your backup site contact. You don't have to leave this table occurrence with a name like Contacts 2, you can double click the box and give it a more descriptive name if you wish.
  3. When you want to add fields for the Backup Site Contact to a layout, select the fields from Contacts 2 (or whatever you choose to name it) instead of Contacts.
  4. Repeat the process for the remaining two relationships.
See the reply in context
No one else had this question

Outcomes

    Visibility: Discussions139 Views
    Last modified on Aug 9, 2010 11:47 AM