Forum Discussion
- ClaudeBowens2Qrew Trainee1. 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. - MCFNeilQrew Captain--> (Has many)
SubContractors --> Projects
SubContractors --> Contacts
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. - ZintJosephQrew CaptainSo no need for the Assigned Contractors table
- ClaudeBowens2Qrew TraineeThere are an endless number of solutions.
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 - ZintJosephQrew CaptainSo just to clarify
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- MCFNeilQrew Captain
- ClaudeBowens2Qrew TraineeThe best solutions would be to setup 2 relationships:
Relationship 1: (between Projects and Sub-Contractor's Tables)
1 Project - Many Sub-Contractor Assignments,
Relationship 2: (between Sub-Contractor's and Contacts Tables)
1 Sub-Contractor - Many Contacts
This will allow you to select multiple Sub-Contractor(s) for 1 project and multiple Contacts for 1 Sub-Contractor.