If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Table design - multiple entries for a field
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 |
Thread Tools | |
Display Modes | |
|
|