General table setup/design question (1 Viewer)

[nstratton]

I am in the process of creating a database component for the contractors allowed on location. The idea behind the end product will be allowing the user to select a work type (Electrical, Mechanical, Environmental, etc) and then display all the contractors of that type.

The part I am confused about is some of the contractors cover multiple work types (max of 5 types for one contractor). I am not sure the way to 1) put it in the database and 2) allow the search if the work types per supplier are in different columns. Below is my idea of how to set up the table for later use.

I understand the use of relationships and all but not sure how if someone searches for Mech it will return both 1 and 2 since Mech is the second type for the first contractor.

Hopefully that makes sense. I'm still new to Access and not confident enough yet to trust what I am thinking is actually correct and the time deadline I am under as well does not help my rational thinking.

ContractorID        Work1       Work2     etc
         1           Elec        Mech
         2           Mech
         etc
         etc

 

[Minty]

In this instance you need a separate table with the contractor ID and a Work Type ID take from a Work Type Table.
This way you can have as many or few work types per contractor as you require.

 

[nstratton]

That's what I have done. (Guess I should have explained that before.)
I guess I am confused then how the relationship will work. I tested a query to see what would happen and only one work type displays the name instead of the ID used in the table. If I understand my own problem, I can't have multiple relationships between the same two tables. It is possible I did not set them up quite right but that is where I am at now.

I would post an image but the internet is down and I'm having to reply via phone. I think I got the idea right just failed at the execution

 

[Minty]

Your relationship should be
tblContractors
ContractorID - PK
ContractorName
Etc

tblWorkType
WorkTypeID - PK
WorkTypeText
CostCode...
Etc

tblWorkContractorJunction
ContractorID - FK
WorkTypeID - FK
DateAdded
etc.

The relationship should be one to many to the Junction Table

 

[nstratton]

Correct me if I am wrong in my understanding here: in tblWorkContractorJunction there are not multiple columns for WorkTypeID. Every contractor-work type relation is on a separate row. So the junction table would look something like below.

JunctionID    ContractorID    WorkTypeID
        1          1          1
        2          1          3
        3          2          5
        4          3          4
        etc
        etc

If that much is correct I think I can handle it from there. I appreciate your input on this

 

[Minty]

Correct. It accommodates flexible numbers of entries and if your forms and queries are designed sensibly they also grow in line with the data regardless of the data.