A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

one to many relationship problem



 
 
Thread Tools Display Modes
  #1  
Old December 7th, 2008, 03:31 AM posted to microsoft.public.access.tablesdbdesign
jon
external usenet poster
 
Posts: 640
Default 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  
Old December 7th, 2008, 04:37 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default 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  
Old December 7th, 2008, 04:37 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default 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  
Old December 7th, 2008, 08:48 PM posted to microsoft.public.access.tablesdbdesign
jon
external usenet poster
 
Posts: 640
Default 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  
Old December 7th, 2008, 08:51 PM posted to microsoft.public.access.tablesdbdesign
jon
external usenet poster
 
Posts: 640
Default 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  
Old December 7th, 2008, 08:52 PM posted to microsoft.public.access.tablesdbdesign
jon
external usenet poster
 
Posts: 640
Default one to many relationship problem

this is the link to the youtube video

http://www.youtube.com/watch?v=y0WX1vdgLGo
  #7  
Old December 8th, 2008, 12:44 AM posted to microsoft.public.access.tablesdbdesign
jon
external usenet poster
 
Posts: 640
Default 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  
Old December 8th, 2008, 01:27 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:01 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.