Can someone explain me, how can I have different fields on a table related to the same multiple choice field of other table. Basically:
Three tables - Site, Main Contractor and Sub Contracto.
Each Site have one Main Contractor and may have many different Subcontractors for each of its tasks.
Main Contractor 1-to-Many to Sub Contractors ( Each main contractor have its own subcontractors)
Site many-to-1 Main Contractors (many sites belong to one Main Contractor) and Site Many-to-1 Sub Contractors.
I can have the correct lookup field in sites from the main contractor - OK
The issue is that I need to have the possibility to choose different subcontractors (related with the One Main Contractor) for the diverse tasks of site.
Main Contractor Table
Main Contract 1
Main Contract 2
Main Contract 3
Main Contract n
Main Contract 1 SubContract ALPHA
Main Contract 1 SubContract BETA
Main Contract 1 SubContract CHARLIE
Main Contract 2 SubContract ALPHA
Main Contract 2 SubContract TANGO
I would like to have my Site Table as the follow:
SIte - Main Contractor - Sub Task1 - Sub Task2 - Sub Task3 ..... Sub Task n
Site1 - Main Contract 1 - ALPHA - BETA - ALPHA - Charlie
Site2 - Main Contract 1 - BETA - BETA - BETA - BETA
Site3 - Main Contract 2 - ALPHA - TANGO - ALPHA - ALPHA
I hope that I was clear.....
Thanks for the help and ideas.....