Link one table to 2 already joined tables

[GabrielloG]

I have 1 table named city linked to another table named location. (1-many relationship). My contacts table has data that needs to be shared on both the city and the location table. How do I do that?

[JoeM]

In a query, you can join many tables (you are not just limited to 2). They key is identifying how all the tables are linked and setting up the joins accordingly.
If you need with that, please list the key fields in each table and explain how they are related.

[GabrielloG]

My tblCity linkes to tblLocation (one to many). And the tblContacts are linked to both tables. This is how the table should be connected.

Table tblCity
LookupLocation
LookupContact (Regional Manager)
LooKupContact (HR)


Table tblLocation
LookupContact (StoreManager)
LookupContact (StoreClerk)

Table tblContacts

[JoeM]

What are the key fields in your tblContacts table?
Also, can you post the SQL code for the query you currently already have.

I am not sure, but we might actually need to see some data samples. Note that you can upload your database to this website.

[GabrielloG]

Database--Web.accdb

I attached the simplified version

[JoeM]

OK. I can take a look at it tonight when I get home (I cannot download files from my current location).

[GabrielloG]

Thanks, Joe

[JoeM]

I am kind of confused by the need for the City table. Is there really a need for a separate table for that?
Or are you wanting to limit it to certain cities, or really have more information than that on the City table?

In looking at your table structure, I am also not 100% clear on exactly what it is you are trying to do).

[GabrielloG]

The city table can be added as a field in the Facility table. But what I am trying to do is connect the 3 tables

• Facilities
• Sites
• Contacts

The tblsite is a child of the tblfacility and the tblcontacts should link to both tables, sometimes multiple times. i.e. the same contact could be the site manager and the security representative or HR Rep.

[JoeM]

Are you familiar with how to use the Query Builder in Access to add tables/queries together and join them?
In Access, you can add the same table multiple times. It will give each succeeding copy an "alias". You can then build your joins like you would between any table/query objects.

[June7]

Just as the Relationships window shows 3 copies of tblContacts, the query needs to do the same. However, when bringing all three tables into the query builder I see that only one tblContacts is presented and is joined to all three fields in tblSites. Delete two of the links and add tblContacts 2 more times. Manually create the other 2 links.