Excel skills subjectively stopping - How to pull related tables together?

[smndnm]

Hello,

I have intermediate excel skills but I feel this knowledge is preventing me from understanding some fundamental aspects of databases.
So, if I may, i will describe something fairly simple.

3 tables:

1. Construction sites with addresses
2. Builders company name with area of expertise ie plumber, sparky, chippy etc
3. Onsite contact(s) with phone number and role.

These tables fulfill Roger Carlson's criteria for smallest data in fields, non-duplicated records, and single purpose of tables.

The relationships are:

1. one construction site has many builders AND one builder works on many construction sites.
2. one builder has many contacts AND one contact only works for one builder.

How do I now create a record(s) that pull together these tables together such that i have a database(?) that describes who the site contact is for a builder on a construction site.

And that I may also ask?

1. Which builders are working on a construction site?
2. Which construction sites does a builder work on?

Is this pulled together in a fourth table? Do i use a form, or an append query to create this table/record?
I have this information entered into a Workbook and filters are applied to answer the questions, but i have similar/duplicated information across a handful of other workbooks that i feel ought to be consolidated into a database.
I can imagine the database and how it would be used, I look at the Access 2016 GUI and it's all MS familiar and i am comfortable with its use.

I feel I am missing something fairly obvious and basic here and i blame my excel-centric thinking, a hard push in the right direction will be appreciated.

[CJ_London]

Is this pulled together in a fourth table?

yes - it is called a join table to represent a many to many relationship and is simply a table of records with the PK's of the relevant tables - typically two, but sometimes more.

Use a form with subform. One table for the form, the join table in the subform which uses a combobox to select the other records in the relationship

[Peter M]

You might need a fourth table, but it might also make sense to allow some duplication depending on total database size.
You need a data field that overlaps to allow a Query that pulls the data in a "filtered" result.

e.g.
Tbl_Construction_Sites with the fields
fld_SiteID (unique key, only 1 construction site allowed)
fld_Address

tbl_Construction_Work
fld_SiteID (could be duplicated, one site with multiple builders)
fld_BuilderID (could be duplicated, one builder on multiple sites)


Tbl_Builders
fld_BuilderID (unique)
fld_Expertise
fld_ContactID (could duplicate, but only 1 ContactID per BuilderID)


Tbl_Contacts
fld_contactID
fld_phonenumber


You then make a query that joins on the appropriate ID fields:
tbl_Construction_Sites.SiteID -> tbl_Contstruction_Work.SiteID AND
tbl_Construction_Work.BuilderID -> tbl_Builders.BuilderID AND
tbl_Builders.ContactID -> tbl_Contacts.ContactID

Does this help to get you started?

[smndnm]

this is super, yes it helps. I see a way forward.

last question for this thread.

from the three tables mentioned earlier , I need to make new additional tables
1. "join" Sites and Builders to create a unique relationship record pair
2. "join" Builders and Contacts to create a unique relationship record pair
3. Join these two new tables into a final joined table to create the actual record upon which i run queries.

is this usual and sensible?

[ssanfu]

Consider:

tblConstructionSites:
SiteID_PK (Autonumber PK)
SiteName (Text)
SiteAddress1 (Text)
SiteAddress2 (Text)
SiteCity (Text)
SiteState (Text)
SiteZip (Text)
(other fields)

---------------------------------------------------------------------------------

tblBuilders:
BuilderID_PK (Autonumber PK)
BuilderName (Text)
BuilderExpertise (Text)
(other fields)

---------------------------------------------------------------------------------

tblContacts:
ContactID_PK (Autonumber PK)
ContactPhone (Text)
ContactCell (Text)
(other contact fields)

---------------------------------------------------------------------------------
(jcnt = junction table)

jcntBuilderSites: (many-to-many relationship between Sites and Builders)
BuilderSiteID_PK (Autonumber PK)
SiteID_FK (Number - Long)
BuilderID_FK (Number - Long)
(other fields)

---------------------------------------------------------------------------------

jcntBuilderContacts: (many-to-many relationship between Builders and Contacts)
BuilderContactID_PK (Autonumber PK)
BuilderID_FK (Number - Long)
ContactID_FK (Number - Long)
(other fields)

Set an compound index (NOT compound PK) on BuilderID_FK & ContactID_FK to eliminate entering duplicates

[smndnm]

Yes, super, all your replies confirm my suspicion that I am loosely stumbling along the right avenues.
Thank you all very much. I will start a new thread about how queries add and amend all the different possible combinations.
Cheers.