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  

Link Primary Key to two fields in second table



 
 
Thread Tools Display Modes
  #1  
Old July 29th, 2008, 03:28 PM posted to microsoft.public.access.tablesdbdesign
DewSweeper
external usenet poster
 
Posts: 2
Default Link Primary Key to two fields in second table

I have a "People" table that I would link to an "Orders" table. The People
table has a record for each individual. A record in the orders table will
have multiple "People" in it (e.g., Person Placing the Order, Technical
Contact, Budget Officer, etc.). I would like to link the Primary Key of the
"People" table (personID) to multiple fields in the orders table and enforce
referential integrity. Access and SQL Server both disallow this. Is there a
way around this? Any thoughts are appreciated.
  #2  
Old July 29th, 2008, 03:58 PM posted to microsoft.public.access.tablesdbdesign
Steve[_57_]
external usenet poster
 
Posts: 598
Default Link Primary Key to two fields in second table

An order has multiple people involved. You have a one-to-many relationship.
The following tables are suggested:

TblPeople
PeopleID
other people fields

TblOrderPeopleType
OrderPeopleTypeID
OrderPeopleType

where OrderPeopleType a
Person Placing the Order
Technical Contact
Budget Officer
etc

TblOrder
OrderID
other order fields

TblOrderPeople
OrderPeopleID
OrderID
OrderPeopleTypeID
PeopleID

TblOrderPeople identifies the Person Placing the Order, Technical Contact,
Budget Officer, etc involved in an order. The relationships a
OrderID in TblOrder === OrderID in TblOrderPeople
OrderPeopleTypeID in TblOrderPeopleType === OrderID in TblOrderPeople
PeopleID in TblPeople === PeopleID in TblOrderPeople

Steve




"DewSweeper" wrote in message
...
I have a "People" table that I would link to an "Orders" table. The People
table has a record for each individual. A record in the orders table will
have multiple "People" in it (e.g., Person Placing the Order, Technical
Contact, Budget Officer, etc.). I would like to link the Primary Key of
the
"People" table (personID) to multiple fields in the orders table and
enforce
referential integrity. Access and SQL Server both disallow this. Is
there a
way around this? Any thoughts are appreciated.



  #3  
Old July 29th, 2008, 04:00 PM posted to microsoft.public.access.tablesdbdesign
strive4peace
external usenet poster
 
Posts: 1,670
Default Link Primary Key to two fields in second table

Hi Dew,

Access does allow this...

when you are in the relationship window, drag the relationship from
People to the first field, check Referential Integrity (RI)

now drag a relationship from People to the second field

A dialog box will come up that says:

A Relationship already exists
Do you want to edit the existing relationship? To create a new
relationship, click No

just click NO -- and you will create another relationship and you can
check RI. Access will put a second copy of the People table on the diagram.


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day
*




DewSweeper wrote:
I have a "People" table that I would link to an "Orders" table. The People
table has a record for each individual. A record in the orders table will
have multiple "People" in it (e.g., Person Placing the Order, Technical
Contact, Budget Officer, etc.). I would like to link the Primary Key of the
"People" table (personID) to multiple fields in the orders table and enforce
referential integrity. Access and SQL Server both disallow this. Is there a
way around this? Any thoughts are appreciated.

  #4  
Old July 29th, 2008, 04:13 PM posted to microsoft.public.access.tablesdbdesign
DewSweeper
external usenet poster
 
Posts: 2
Default Link Primary Key to two fields in second table

Thank you Steve and Strive4Peace for the fast and very helpful responses.

Both suggestions work for me.

"strive4peace" wrote:

Hi Dew,

Access does allow this...

when you are in the relationship window, drag the relationship from
People to the first field, check Referential Integrity (RI)

now drag a relationship from People to the second field

A dialog box will come up that says:

A Relationship already exists
Do you want to edit the existing relationship? To create a new
relationship, click No

just click NO -- and you will create another relationship and you can
check RI. Access will put a second copy of the People table on the diagram.


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day
*




DewSweeper wrote:
I have a "People" table that I would link to an "Orders" table. The People
table has a record for each individual. A record in the orders table will
have multiple "People" in it (e.g., Person Placing the Order, Technical
Contact, Budget Officer, etc.). I would like to link the Primary Key of the
"People" table (personID) to multiple fields in the orders table and enforce
referential integrity. Access and SQL Server both disallow this. Is there a
way around this? Any thoughts are appreciated.


  #5  
Old July 29th, 2008, 04:36 PM posted to microsoft.public.access.tablesdbdesign
strive4peace
external usenet poster
 
Posts: 1,670
Default Link Primary Key to two fields in second table

you're welcome, Dew happy to help

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day
*




DewSweeper wrote:
Thank you Steve and Strive4Peace for the fast and very helpful responses.

Both suggestions work for me.

"strive4peace" wrote:

Hi Dew,

Access does allow this...

when you are in the relationship window, drag the relationship from
People to the first field, check Referential Integrity (RI)

now drag a relationship from People to the second field

A dialog box will come up that says:

A Relationship already exists
Do you want to edit the existing relationship? To create a new
relationship, click No

just click NO -- and you will create another relationship and you can
check RI. Access will put a second copy of the People table on the diagram.


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day
*




DewSweeper wrote:
I have a "People" table that I would link to an "Orders" table. The People
table has a record for each individual. A record in the orders table will
have multiple "People" in it (e.g., Person Placing the Order, Technical
Contact, Budget Officer, etc.). I would like to link the Primary Key of the
"People" table (personID) to multiple fields in the orders table and enforce
referential integrity. Access and SQL Server both disallow this. Is there a
way around this? Any thoughts are appreciated.

 




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 04:07 PM.


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