Access table relationships . from dave

[David Ward]

Can you help me,

I have 3 tables,

1 order entry . With 4 contacts, 1 for person name who gave me order,
second for person receiving order into access, 3 for person at a delivery
address, and 4, for person at a related site. ( no other info is required
for the site contact.

2/ Quotations. With two contacts, 1 for person I send quote to, and second
for entering name of person receiving into access.

3/ Contacts Table. to record names and phone numbers of the six different
contacts above,


The four different contact types which make up the above six contacts are
also related to CustomerID from customer table.

Question how do I do the relationships.

ie. one order can have many contacts. (4 off) but only two addresses, order
from one, delivery to another.
one contact can have many orders.or many quotations.
One quote can have many contacts. (2 off)

HOW do I set this relation ship up?


Help would be much appreciated,


Regards,

Dave

 

[Tim Ferguson]

ie. one order can have many contacts. (4 off) but only two addresses,
order from one, delivery to another.

No, this is not right. One Order can have one Giver; one Order can have one
Receiver, one Order can have one Deliveree; and one Order can have one
RelatedContact. This is four entirely separate and distinct relationships,
each one being a bog-standard one to many.

one contact can have many orders.or many quotations.
One quote can have many contacts. (2 off)

As above: each Contact can Give many Orders; and each Contact can Receive
many Orders; ditto Deliver and BeRelated. Notice how relationships tend to
have names made of verbs, while entities are nouns...

HOW do I set this relation ship up?

One at a time: you drag from Contacts.ContactID to Orders.Giver and set the
properties; and then drag from Contacts.ContactID to Orders.Receiver etc.
etc. You will end up with three pseudo-tables all over the relatioships
window called Orders_1, Orders_2 and so on, but you can ignore this. It
means that Access is keeping track of the different relationships entirely
separately, rather than trying to make one multi-field relationship.

Hope that helps


Tim F