Relating 2 tables to one table (1 Viewer)

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.

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

BeeJayEff said:

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

Click to expand...

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

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.

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/

When you add the second instance of tblContacts to the relationship window, Access will helpfully give it an Alias by suffixing the name with a number so tblContacts becomes tblContacts_1. If you add a third instance, it will be named tblContacts_2.

The same technique is used in queries. If you want to show the names of both contacts in a query, add tblContacts twice to the grid. If you have relationships defined, Access normally draws join lines for you. If it doesn't, you need to do it yourself. As you select the columns from each Contact table, be sure to alias the names to avoid confusion in the query. If you don't alias the names, Access does it by prefixing the column names with the tablename and it isn't pretty.