One primary key to multiple foreign keys in the same table?

ClearlyAnIdiot

Registered User.
Local time
Today, 14:22
Joined
Aug 22, 2013
Messages
39
Right now, I have 4 related tables. There's a table with companies, one with people involved with companies, a table linking the two by having foreign keys of the company number and people names, and a table that indicates directors and their alternates.
Since there's a one-to-many relationship for companies/people to company-peopleID (A person can run multiple companies, a company has multiple directors, it's easier this way), a person's name can appear multiple times, as can a company, within that table.
In a company, a director may or may not have 1 and only 1 alternate director to him/herself. So, I thought the easiest way was to put an autonumber in the Company-personID table and have a table (alternates) that had two fields, "alternate" and "director", both using that autonumber to link them. However, it appears as though I can't link the same primary key twice to two foreign keys of the same table.
Is there any way to do so? Or is there a superior way to deal with this? In the end result, however, I want it to be easy to fill in a mailmerge with. For reference, a form involving these directors and alternates will look something like this: http://www.cr.gov.hk/en/forms/specified.htm
Specifically, the easiest one would be http://www.cr.gov.hk/en/forms/docs/ar1.pdf, with particular note on the 5th and 6th pages.
 
However, it appears as though I can't link the same primary key twice to two foreign keys of the same table.

Why not, just insert the table a second time in the relations window and create the secon link to that duplicate table
 
"Why not" makes this sound like it is optional when in fact it is not.

In the relationships window and also in the QBE, when you need to make additional relationships/joins to a table, you add the table to the grid a second, third, etc. time. This does not duplicate anything. It simply is a way in the GUI to allow you to describe clearly to Access what you want to do. As you add tables to the grid, Access suffixes them so they have unique names so we have tblA and then tblA_1 and then tblA_2. You now have the ability to describe three relationships to tblA from tblX.
 

Users who are viewing this thread

Back
Top Bottom