I ask that you forgive me in advance for such a simple question, but I haven't used access since 1998 and am very very rusty.

I am trying to put together a data base to collect employee test results. The employee's manager will review their work and answer a number of yes/no questions. I can put the database tables together, what I am having problems with is this: Either 1) I would like to assign a specific value to each of the questions, and then based upon the result, add all the value of the correct answers and divide by the max points to get their %; or 2) if the first way is not possible or simply to complex, then I would like simply to add all the correct answers and divide by the total number of questions to get their % correct.

I have been trying to do this as a query, but have not had any luck.


Here is how I have the database set up. Currently, there are no relationships between the tables.

Table 1
Unique ID/Auto Number
Manager Name/text


Table 2
Unique ID/Auto Number
Employee Name/text

Table 3
Unique ID/Auto Number
Manager Name/lookup from Table 1
Employee Name/Lookup from Table 2
date/date
Q1/yes/no
Q2/yes/no
Q3/yes/no
.
.
.
.
.
.
Q50/yes/no

This way, I can have multiple managers record multiple results for for multiple employees over the course of time. This is meant to be run 2-3 time per month for each employee.

The final results will hopefull be this. 1) individual test results for each review; 2) cumulative results over time to make sure that the employee is improving and that the manager is makig them aware of what they need to improve upon; 3) and overall grade for the entire office staff to see if there is something that everyone is missing that needs to be addressed in a traing session.

I greatly appreciate any help with this.

Sincerely,

Iamnewbie

Welcome to Utter Access Forums! -o!

Since a manager is an employee I would put everyone into a single table and have a field where you can distinguish Managers from other Employees...

Your question table is wrong. This is what it should look like:

tblQuestions
QuestionID (PK and auto)
TestTypeID (FK)
Question

tblTestTypes
TestTypeID (PK and auto)
TestTypeDescription (Text)

tblResponses
ResponseID (PK and auto)
EmployeeID (FK)
ManagerID (FK)
QuestionID (FK)
TestDate (Date/Time)
Response (Yes/No)

This is a very simple setup, but should get you started...

hth,
Jack

Thank you very much for the feedback.

A couple of quick questions.

1) What does the (fk) stand for?
2) I understand how the tables work. I am still unsure how this is going to help me get the desired results. I am not understanding how I can now give [response] a specific value or just count the number of correct answers the employee got. What am I not understanding?

Thank you very much again for the assistance and the help.

Sincerely,

Iam newbie.

FK = Foreign Key (the Primary Key for the One side of a One-to-Many relationship).

tblResponses with have the record of each question answered by an employee so you can create a totals query and total by EmployeeID and the do a Count of Yes or No records... I have no idea what type of questions you will be asking what type of responses will be required for each question. A question can be Yes or No, rated between 1 an 10 or a written response. Since I don't know that information the suggest layout may not be right for what you are trying to accomplish.

I have attached a simple demo database that shows how you can ask a couple of different types of questions...

hth,
Jack

Jack,

This does help greatly. I am still having problems with sum functions though. I have attached a copy of my database. I just don't understand what the heck I am missing. I know it is probably something really silly.

Iamnewbie

Iamnewbie -

Are you having problems with "sum" functions or "some" functions?

What is the purpose of the database? What are you trying to do and what is not working? Please be specific with names of forms, etc. You know what you want to do but I haven't a clue....

Jack

Jack,

My apologies about not being clear.

I am having problems with the "SUM" function. I want to add all of the [Question.Weight] numbers for the answers that the employee got correct [answers.results]. The "Test Query" does give me all the answers that the employee got correct, but it is not giving me a "sum" of all the question.weight numbers for the correct answers.

In simple terms, here is what i am tyring to get.

Test score = ( ( sum(questions.weight) [where answer.result="yes"])/sum(questions.weight) ) * 100. This will give me the percent the employee got correct.

The reason for the "weight" field is so that certain questions are more of a pass/fail than other questions.

I hope that this clears up any questions that you may have.

Thanks,

Iamnewbie

Iamnewbie -

Your structure is not normalized. Your 'response' table (where the answers to the questions by employees are saved) should be:

tblResponses
ResponseID (PK and auto)
EmployeeID (FK)
QuestionID (FK)
Response (Yes/No)

Your current table uses a Lookup for the question and this is wrong. You do not want lookups at table level, only form level. I realize that Access gives you this option, but it is not a good one and should not be used...

If you set up your response table correctly and make some changes to your form you should be able to calculate the 'sum' of the weighted questions using a query based on the table above. I would suggest you rework your tables and I would also suggest that you not save full names of people, but FirstName and LastName... As long as you're taking the time to make this db you might as well follow some of the basic rules...

Hang in there as you will get this and it will work just the way you want...

Jack

Jack,

You were correct. My tables were the issues. Since I made the changes, I am able to do all the calculations that I need to do. Thank you very much for your assistance with this. I greatly appreciate it.

Iamnewbie

Iamnewbie -

You are most welcome! I'm glad I was able to assist you and that you are back in business....

Jack