1 Reply Latest reply on Aug 9, 2010 11:57 AM by philmodjunk

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

    TimRaines
    TimRaines Aug 9, 2010 11:47 AM

      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

      • 137 Views
      • Tags:
        • 1. Re: Multiple fields in one table referencing the same field in a second table
          philmodjunk
          philmodjunk Aug 9, 2010 11:57 AM (in response to TimRaines)

          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.