one to many relationship problem

[Jon]

How to create a "one to many" relationship between one primary key in one
table and 2 or more foreign keys in another table?

it is hard to explain what I mean in words so I created this video to better
explain what I mean

it is low quality

thank you in advance

 

[Allen Browne]

Hi Jon

I did not go through your video, but it seems that you have repeating fields
(package1, package2, etc, or perhaps PackageReceived, PackageSent, etc), and
this is why you need multiple relationships.

It is actually possible to drag the Client table into the Relationship
window multiple times, and then create a relationship from each one to the
table that has multiple foreign keys. However, a far better solution would
be to create a relational design.

A shipment can contain multiple items. Can we assume that a shipment is
always from one client, to one client? If so you would have a shipment table
with fields:
ShipmentID autonumber
ShipperID
ShippeeID
SentDate
and then a ShipmentDetail table for the packages within the shipment:
ShipmentDetailID autonumber
ShipmentID Number this shipment this row belongs to
and so on. Each item in the shipment is a separate record in this table, so
one shipment contains multiple items.

If the shipments are containers where a shipment can have muliple
consignees, you would move the ShippeeID into the ShipmentDetail table.

Post back if that does not address what you asked.

 

[Jon]

Hi Allen Browne

thank you for responding to my question, unfortunately I don't think my
explanation was good enough to get my point across, so I created a far better
video explaining what i mean please watch it and see if you can help me out.

Yes we can assume a package is always from one customer to another.
the reason your suggestion wouldn't work for me is that it doesn't matter
what is in each package, what I want is to be able to relate a package to a
customer weather he sends or receives. A customer may have several packages
related to him ex) he sends a package and he receives a package.

 

[Jon]

sorry i forgot to post the link
youtube video:

Hi Allen Browne

thank you for responding to my question, unfortunately I don't think my
explanation was good enough to get my point across, so I created a far better
video explaining what i mean please watch it and see if you can help me out.

Yes we can assume a package is always from one customer to another.
the reason your suggestion wouldn't work for me is that it doesn't matter
what is in each package, what I want is to be able to relate a package to a
customer weather he sends or receives. A customer may have several packages
related to him ex) he sends a package and he receives a package.

 

[Jon]

this is the link to the youtube video

 

[Jon]

I created a new post much better info on what i am trying to do.

http://www.microsoft.com/office/com...sign&mid=9bab7c06-1d42-4f4c-9057-908751792853

 

[Allen Browne]

It doesn't suit me to watch videos on-line, but I will try to answer what
you asked here.

So your deliveries always consist of a single package? Therefore the package
is always sent from only one client to only one client?

Client table:
- ClientID autonumber primary key
- ...

Delivery table:
- DeliveryID autonumber
- SenderID number
- RecipientID number
- DeliveryDate date/time

You can create the 2 relationships between Delivery and Client by adding a
2nd copy of the Client table to the Relationship window. Now create a
relationship from Client.ClientID to Delivery.SenderID, and another
relationship from Client_1.ClientID to Delivery.RecipientID.

I C U have started a new thread, so I won't follow up here again.