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