Hey there Jake! Thanks for the response. I think I see what you are saying, but let me clarify a bit more.
I have a Client Table. It has a one to many relationship with my Contacts table. One client has many contacts.
Among those contacts, there are three different types: Site Supervisor, Job Contact, and Home Owner.
A Client can have many Site Supervisors, many Job Contacts, and many Home Owners.
Also, I have a Work Orders table. It has a many to one relationship with the Client Table. One client has many work orders.
I need to set up the Work Order table so that it has the following fields:
- Site Supervisor
- Job Contact
- Home Owner
Then from within the Work Order form, when I select the Client from the drop down, I need the Site Supervisor, Job Contact, and Home Owner drop down lists to only list the contacts (for that type) associated with the selected client.
I can do a conditional drop down, no problem.
The issue is setting up the relationship correctly between the Contacts and Work Orders tables and setting up the Work Orders table with the correct fields for each of the Contact types.
I hope that clarifies things and helps.
Any thoughts?
Currently, I have all contacts, regardless of type, in a single Contacts table (as described above). But I am thinking that maybe I need to have a separate Contacts table for each type ... but then that gets cumbersome in my opinion ..... yuck ....
-Andrew
P.S. I just re-read your comment Jake, and yes, I need to create a relationship dependent on the contact type .... that way I can have 3 different relationships, one for each contact type ... but wouldn't that mean having to setup three different tables, one for each contact type?