If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
one to many relationship problem
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 http://www.youtube.com/watch?v=8IAzR1dUj50 it is low quality thank you in advance |
#2
|
|||
|
|||
one to many relationship problem
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Jon" wrote in message ... 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 http://www.youtube.com/watch?v=8IAzR1dUj50 it is low quality thank you in advance |
#3
|
|||
|
|||
one to many relationship problem
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Jon" wrote in message ... 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 http://www.youtube.com/watch?v=8IAzR1dUj50 it is low quality thank you in advance |
#4
|
|||
|
|||
one to many relationship problem
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. |
#5
|
|||
|
|||
one to many relationship problem
sorry i forgot to post the link
youtube video: http://www.youtube.com/watch?v=y0WX1vdgLGo 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. |
#6
|
|||
|
|||
one to many relationship problem
|
#7
|
|||
|
|||
one to many relationship problem
I created a new post much better info on what i am trying to do.
http://www.microsoft.com/office/comm...7-908751792853 |
#8
|
|||
|
|||
one to many relationship problem
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Jon" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|