Link Primary Key to two fields in second table

D

DewSweeper

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.
 
S

Steve

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 are:
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 are:
OrderID in TblOrder ===> OrderID in TblOrderPeople
OrderPeopleTypeID in TblOrderPeopleType ===> OrderID in TblOrderPeople
PeopleID in TblPeople ===> PeopleID in TblOrderPeople

Steve
 
S

strive4peace

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 :)
*
 
D

DewSweeper

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

Both suggestions work for me.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Trying to create a report that will list unmatched records by Ticket Primary Key 0
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
Why won't Access assign a Primary Key ID to my linked SQL Table? 2
Auto-Numbering in a Table in MS Word 1
Do I have to set the primary key to include primary key fields? 3
Referential Integrity – Import of Data from a purchased package 9
referential integrity and lookup 2
Unable to display values in 2nd list box from 1st list box selection 2

Top