Table design - multiple entries for a field

Hello,

I have 3 tables - employees, performance evals, and error codes. The error codes is more of a look-up table that is indexed by a unique number. The performance eval table will contain the unique employee id and then the error code(s) for a given date. It is possible that an employee will make multiple errors on a given day - how would I capture the multiple error codes in my performance evals table? Any ideas?

Thanks,
Carrie

Carrie, you need one more table. Let's call it, say, eval details. It will
be the junction table between performance evals and error codes, allowing
you to capture multiple errors on a given day. So:

tblEmployees
EmployeeID (PK)
....

tblPerformanceEvals
EvalID (PK)
EmployeeID (FK): One-to-many with tblEmployees.EmployeeID;
tblPerformanceEvals on the "many" side
Date

tblEvalDetails
DetailID (PK)
EvalID (FK): One-to-many with tblPerformanceEvals.EvalID; tblEvalDetails on
the "many" side
ErrorID (FK): One-to-many with tblErrors.ErrorID; tblEvalDetails on the
"many" side

tblErrors
ErrorID (PK)
....

Note that when an employee commits an error, you create a record in the
PerformanceEvals table, capturing the EmployeeID and date. Then you create a
record in the EvalDetails table, capturing the ErrorID plus any other
relevant error-related data. So you create one record in the
PerformanceEvals table for each date on which a given employee commits an
error, and one record in the EvalDetails table for each error the employee
commits on that date.

--
DDM
"DDM's Microsoft Office Tips and Tricks"
Visit us at www.ddmcomputing.com


"Carrie" wrote in message
news
Hello,

I have 3 tables - employees, performance evals, and error codes. The
error codes is more of a look-up table that is indexed by a unique number.
The performance eval table will contain the unique employee id and then the
error code(s) for a given date. It is possible that an employee will make
multiple errors on a given day - how would I capture the multiple error
codes in my performance evals table? Any ideas?

Thanks,
Carrie