Need help with table normalization please

[Dani]

Hi,
I'm developing and HR database with Access 2010. I've hit a snag with designing a Performance Evaluation table.

I have various lookup tables for:

EmployeeID
SupervisorID
PerformanceRati ngID (eg. Average, Outstanding)
EvaluationAreaI D (eg. Attendance, Housekeeping)

Each employee will have multiple evaluations from different supervisors. Each evaluation will contain contain a rating of several EvaluationAreas .

I'm trying to figure out how to identify a single record that contains these multiple evaluations by one supervisor. I'm thinking I need to use a composite key but I'm not 100% clear on how to go about it.

Alternately, I could hardcode all of the evaluation areas into the Performance table but that seems wrong to me.

Am I overthinking? Any perspective/comments/suggestions would be appreciated.

[Seth Schrock]

It is difficult to say with so little information, but I would stay away from composite keys if possible, especially if it would be more than two fields. Instead just add an autonumber field to be the Primary Key. If you are then looking for the evaluations by one supervisor, then you just select that supervisor's ID in the WHERE clause of your query.

[Rabbit]

You wouldn't want to store the multiple evaluations in a single record. If you want to view multiple evaluations as a single record, you can just use a cross tab query. But I would still store it as multiple records.

[Dani]

I did think of a crosstab query after I posted. I think you're right that once I isolate the SV, Employee and date of evaluation, I'll get the appropriate record.
thanks so much for your input.
Dani

[Rabbit]

No problem. Let us know how you get along and if you have trouble setting up the crosstab.