Table design - multiple entries for a field

[Carrie]

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

 

[DDM]

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

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?

 

[Steve Schapel]

Carrie,

You would simply add each error for each employee as a separate record.
I.e. your Performance Evals table will have fields (I presume this is
already more or less the structure) like this...
EvalID
EmployeeID
ErrorDate
ErrorCode

I am not 100% clear on the actual meaning of your question, but as
regards "how would I capture", you can make a form based on this table,
for the entry of the data, and it can either be a "stand-alone" form in
continuous view, or it can put a subform on the Employee form.