You are here:

Using MS Access/Quality Assurance Database (Access 2007)


QUESTION: I am building a database to track employee quality and after reading the previous posts you have answered regarding the structure of survey/questionnaire databases I want to make sure that my understanding of normalizing the table structure is correct.

The database will need to store multiple evaluations each month per employee for several different job responsibilities (there are 15 different job functions and some employees complete 2 or more different job functions each month. A separate evaluation is completed for each function an employee performs and some questions apply to multiple job functions and would appear on multiple evaluation forms.

-For each question of the evaluation, the response will be "Correct" or "Incorrect". For some questions, if the response is "Incorrect" I'd like to be able to choose from a list to specify what was wrong.

For example, one of the questions would read:

Was the information entered correctly? Yes / No

If no, what was incorrect:

-Missing Information
-Incorrect Information
-Other: _________ (where I would be able to enter a text response)

Here is the current table structure I have drafted:

tblEmployees - Employee Info

tblTeamLeader - Team Leader Info

tblQuestions - Evaluation Questions

tblAnswers - Possible Answers

tblResponseType - Yes/No, Multiple Choice with "Other: _________" Option

tblEvalResponses - Completed Evaluation

Is this the correct structure? Also, if some questions need to be weighted more heavily than others what is the best way to do this?

For employee information I'd like to include the employee's title (which I believe would be done using a lookup table). If an employee changes positions and has a title change, what is the best way to retain the position they held on past evaluations?

Also, I am not sure how to achieve the "Other" response with the added text option. Will you please advise on how this should be done?

Thank you in advance for your help. I have spent many hours trying to figure this out and I just want to make sure I have the correct structure before I start creating it.

ANSWER: Not bad, but a few issues. First, each field should contain the smallest piece of info. For examples, names should be broken out, at least, into first, last (and middle name/initial). Second, I'm not sure whether you need a separate table for team leader. You could have a Team Leader Yes/No field in tblEmployee to indicate if an employee is a team leader and a TeamLeaderID field to indicate who that person's team leader is (which you already have). And while we are on tblEmployees, you should also have an employee history table. You use tblEmployees to hold demographic info about the employee (names, address, etc.). You use the history table to record changes in their job. So it would look like:

EmpHistoryID (PK Autonumber)
EmployeeID (FK)
TeamLeaderID (FK)
TeamLeader (Y/N)
TitleID (FK)

So you would move the team leader and title info into this table. You would also probably want top add TitleID to tblEvalResponses to capture the title at the time of the Eval.

If you need to add a weighting factor to the questions, I would add that as part of tblQuestions.

For the Other responses I would use a separate table:

OtherAnswerID (PK Autonumber)
AnswerID (FK)

This table would only be populated is an "other" answer was provided, that's why the FK is the AnswerID.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Thank you so much for your help. One follow-up question - with the TitleID and TeamLeaderID as foreign keys in the tblEmpHistory do I need to create a tblTitle and tblTeamLeader for them to be primary keys or what table would they be primary keys in?

Yes, these are considered lookup tables. A lookup table is defined by usage, but is otherwise the same as any table. The purpose of a lookup table is to ensure standardization of data entry. So you don't want one employee to have a title of Department Manager, another one, Dept Manager and another Mgr of Department. By using a lookup table with a code, you force users to choose from a list.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2017 All rights reserved.