1. Its best to create a table for each category (Contractors, Sub-Contractors, Contacts). 2. Make sure each table has a key field that is unique. 3. Decide which table will be your main table. This table will be on the many side of you "one-many" relationships. For example: if projects is your main table, your first relationship would be a "1-project to many-contractor", "1-project to many-Sub contractor", and a "1-contractor to many-contacts" relationship.
The parent would be able to write to the child and allow you to add more than one contact.
Projects --> Sub Contacts Contacts --> Sub Contacts
"Sub Contacts" is the joined table that allows you to add many contacts to one project. You can make the selections conditional based on the Sub, so you have a narrow list to choose from.
The basic concept is to make sure that the tables that you are looking to reference from your main table are on the "one" side of your "one-many" relationships.
There also has to be a field on the main report that matches the key field on the reference table. Also, ensure that the key field on the reference table is unique.
If your main table is projects and your looking to reference contractors, ensure that contractors are referenced on your projects table in a common field.
Example:
Projects Table - record 1 Project (field): A Contractor (field): 1<-- "this would be the common field"
Contractors Table- record 1 Contractor (field): 1(key field)<-- Name (field): John Smith
1 Contractor - Many Projects Key field(Contractor)
Now you can bring over "look-up" fields to the Projects table from the Contractors table that reference the contractors.
Projects Table - record 1 Project (field): A Contractor (field): 1 Related Projects (field): 1 Contractors - Name: John Smith
Projects will have many Assigned Subcontractors Subcontractors have many Sub Contractor contacts
Tables
Projects Assigned Sub Contractors Subcontractors Subcontractor Contacts
My goal is to pick the subcontractors (could be multiple) for a project and then pick from a list of contacts for up to 4 people from each sub as subcontractors may have many contacts