Linking tables with multiple primary keys 1

[Joallyn]

Hi listers,

yes, it's newbie time...this is no doubt an incredibly dumb question, but the problem has me stumped, and I couldn't find anything like it in the archives.

The large problem: how to link to a table that has dual primary keys.

The details: I am dealing with 4 tables: tblIncidents, tblActions, tblPersonnel, and tblActionPersonnel. In tblIncidents, the primary key is IncidentID. In tblActions, it's a double key of IncidentID and ActionID, since a unique incident may have multiple actions. There is a many-many relationship between tblActions and tblActionPersonnel, since any action can have multiple people working on it, and one person can work on many different actions. Sooooo...I set up a "link" table (tblActionPersonnel) with primary key ActionPersID (autonumber)and PersonnelID (number) and IncidentID and ActionID.

My question is...how do I handle the double-primary key feature of tblACtions when I try to establish a link to tblActionPers? I need to treat the dual key as a single one in order to link the tables correctly...right?

I would appreciate any suggestions...(even the ones that start out with, "Dear Idiot...&quot

thanks!

J

 

[sko]

I don't believe you need a 'double-primary key' in tblActions. The incidentId resides in tblActions as a foreign key like this:

tblIncidents
IncidentID -> primary


tblActions
ActionID -> primary key
IncidentFK - foreign key

Link one to many tblIncidentID -> tblActions.IncidentFK

tblActionPersonnel
ActionID -> Primary Key
PersonnelID -> Primary Key

tblPersonnel
PersonnelID -> Primary Key


Link tblActions.ActionID -> tblActionPersonnel.ActionID
one to many

Link tblPersonnel.PersonnelID -> tblActionPersonnel.PersonnelID
one to many

 

[MichaelRed]

to be a bit more simplistic, you cannot have more than one PRIMARY KEY, "Primary" is SINGULAR. Prinary keys in one table may be Foregin Keys in another table.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Rhonin]

MichaelRed:
The what is the proper terminology when I select five fields as my multi-field primary key?

Joallyn:
sko's explanation fits your scenario (if I am reading it right) quite well. Having a multi-field primary key is perfectly fine, you just have to logically think through the links.

Ohh... and by the way, the only dumb question is the one you don't ask and are clueless about. As most developers on here, I would rather answer a "dumb" question than try to fix the mistakes.

Rhonin
"too many questions, too little time..."

 

[Joallyn]

Hi all,

thanks for the help. I was trying to overcomplicate things, which I do with great enthusiasm, unfortunately. This cleared up the situation.

Next time I'll try to at least post in the appropriate forum (Access TAbles and Relationships).

thanks!

J