As a newbie, I'm under the impression that data typically flows throughout a database from Parent to Child.
I've run into cases where I need the flow to be the opposite. In the example shown below there is a need to assign a Contact to a Site. I cannot assign a Site to a Contact because a single contact may be related to many Sites.
Here's what I did:
- I created a field in Sites table called SiteContact.
- A table occurrence of Contacts is connected to Sites via fkCompanyID. This limits the contacts to only those who are with a related company.
- A drop-down list on the Sites layout is on the field SiteContact (in Sites table).
- A value list based on pk_ContactID (from Contacts table) shows the second field Contact_FirstLastName (from Contacts table).
- Selecting a contact from the drop-down list, populates the SiteContact field with a ContactID.
The contact is now related to the site.
The Site layout displays the contact as it should. Other fields related to the contact via the table occurrence are also on the Site layout (contact phone, contact email). These fields do not relate to the specific ContactID that was populated via the drop-down (someone else's phone number...).
I joined the table occurrence with 2-joins:
Contacts_TO_Sites::fkCompanyID = CompanySites::fkCompanyID And
Contacts_TO_Sites::pkContactID = CompanySites::SiteContactID
Now the contact phone and email agree with the selected contact.
Is this the proper way to approach this or am I way off the mark. Perhaps my table relationships are making this more complex? Maybe a join table would be better?
To me it's like I'm pushing the mule with the cart i.e. forcing the data (SiteContactID) to make the relationship work.
Any comments would be welcome. Thank you.