Relating 2 tables to one table

[bd528]

Hi,

I have a feeling this should be simple, but for some reason it has me stumped...

I have the following 2 tables (and fields) :-

tblquotes
QUOTEID
CUSTOMER_CONTACT_ID
BROKER_CONTACT_ID

tblContacts
CONTACTID

• A quote can have just one Customer contact and one broker contact.
• Both Customer and Broker contacts can have multiple quotes

Can anyone clarify how I need to lay these out on the relationship window? I just can't get it to work. (I can restructure either table if required.)

Thanks in advance.

 

[BeeJayEff]

Do Customers and Brokers have the same attributes (fields) ? If not, they should probably be in separate tables.

 

[bd528]

Do Customers and Brokers have the same attributes (fields) ? If not, they should probably be in separate tables.

They do have the same fields. There is a CONTACT_TYPE field in tblcontacts

 

[BeeJayEff]

OK, add both tables into the relationships window, then drag from Customer_Contact_Id in tblQuotes to ContactId in tblContacts. Check to Enforce Referential Integrity (and Cascade Update/Delete Related Fields/Records if required). Then do the same from the Broker_Contact_Id field. I think this will add a second instance of tblContacts, called tblContacts_1.

 

[Cronk]

This situation is not uncommon. Consider system with parent/child or supervisor/subordinate relationship. And as BJE suggests, add a second instance of the same table. Unless you have can have the same contact acting as both broker and customer, I'd create a unique composite index on the respective fields/