Multi-Field Primary Key

E

E.Q.

A bit of background:
I configured a database for goals and objectives for my personal use to
generate a required monthly activity report that my boss requested. It has
the following structure:
tblGoalComp
-lngGCID (PK)
-chrGoalName
-....

tblObjective
-lngObjID (PK)
-lngGCID (FK)
- chrObjName
- ...

tblGoalLog
-lngLogID (PK)
-lngObjID (FK)
-dtmEventDate
-chrEventDescrip

I've use it for a year and my boss likes it. He has asked me to expand this
for my fellow supervisors. For now I'm going to cheat and give them all a
blank copy of the database I use. (They're going to be facing a March 10
deadline to start using it.) But I know that really limits what might be
done with the database. So I'd like to expand.

I've taken a copy of the database and added a table (imported from another
project) with employee data, tblEmployee. I've then created a table to assign
the goals to the supervisors,tblGoalAssign, to deal with a many-to-many
relationship.
Though I've managed to avoid multi-field primary keys in any effort so far,
I believe I ran into a use for one here . So I have
tblGoalAssign
-chrEmpID (from tblEmployee - chr to allow leading zeros)
-lngGCID (From tblGoalComp)
-intYear

These three fields uniquely identify each record, so I made them a multiple
field primary key. My problems is in relating these fields "downstream" to a
table that performs like tblGoalLog in the original database.
I want to make sure that the reports generated reflect the activity of the
individual supervisors. So the tracking log needs to reference the goal
assignment. But I don't have anything to link to (since none of the three
fields individually identify a record in tblGoalAssign). Do I need to add an
"Autonumber" field to tblGoalAssign to provide such a linking? Would that key
then be a forth field in the primary key? Or is there a better way to address
tracking work activities associated with the assigned goal.
Peace.
EQC
 
E

E.Q.

I did a search on the "General Questions" Access and found a reference to
creating a multiple-field index. That would solve the problem of maintaining
unique records for the three fields (which I'll join in a single index); then
I'll create an autonumber primary key to link to the tracking log.
I think this should solve the issue; but if anyone has any more tips for me
(other than scanning the general questions before posting here), I'd welcome
any advice.
Peace.
EQC
 

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

Top