Survey question

[RTimberlake]

I am creating an employee evaluation survey. Three people fill out the survey
for one person. Is there any way to combine the answers from all three
surveys to get one master report? I need the answers to be averaged together
to form a grand total. I have the formulas that will work to pull a report on
one survey, but not on three. Please help.

 

[Roger Carlson]

Duane Hookom has created a great, general purpose survey database called
"AtYourSurvey.mdb". I've used it as-is for a number of surveys. The code
is all open, so you can modify it if you want, but I've never had the need.
You can find a copy here:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[RTimberlake]

I based my survey off of Duane's "AtYourSurvey." Everything works great
except for the fact that I cannot figure out how to combine the results of
three surveys on one report.

 

[Amy Blankenship]

A little more info on your table structure would help.

-Amy

 

[John Vinson]

I am creating an employee evaluation survey. Three people fill out the survey
for one person. Is there any way to combine the answers from all three
surveys to get one master report? I need the answers to be averaged together
to form a grand total. I have the formulas that will work to pull a report on
one survey, but not on three. Please help.

It sounds like you need a Totals query on the Answers table - but
without knowing how you've implemented Duane's database or what you
mean by "combine", I'm not certain what to recommend.

How are the "three surveys" stored? How do you intend to identify
which three (out of many I presume) surveys should be combined? How do
you want them combined?

John W. Vinson[MVP]

 

[RTimberlake]

I have five tables: tblEmployee; tblEvalDetails; tblEvalFactors;
tblEvaluations; tblStudents.
The scores are stored in a table with the following feild names: DetailID;
EvaluationID; FactorID; Rating. The scores are on a scale of 1-5 using an
option group. I need to be able to pull the scores for each Factor and get an
average of the three surveys. For example...If the factor was Teamwork and
the Employee scored a 4, 3, and 5 on the three surveys his/her score would be
a 4. If the factor was Attitude and the Employee scored a 1, 3, and 4 his/her
score would be a 2.7.
I guess the only way to get average scores would be to filter for
EmployeeID, Date and FactorID. Other than that I am drawing a blank.

 

[Amy Blankenship]

I have five tables: tblEmployee; tblEvalDetails; tblEvalFactors;
tblEvaluations; tblStudents.
The scores are stored in a table with the following feild names: DetailID;
EvaluationID; FactorID; Rating. The scores are on a scale of 1-5 using an
option group. I need to be able to pull the scores for each Factor and get
an
average of the three surveys. For example...If the factor was Teamwork and
the Employee scored a 4, 3, and 5 on the three surveys his/her score would
be
a 4. If the factor was Attitude and the Employee scored a 1, 3, and 4
his/her
score would be a 2.7.
I guess the only way to get average scores would be to filter for
EmployeeID, Date and FactorID. Other than that I am drawing a blank.

Would you mind listing out the full table structure? From what you're
saying, it's not clear where the tables are relating. For instance, I
assume somewhere you have something that connects your score (detailID,
evaluationID, FactorID, Rating) to your EmployeeId, but if you don't draw
out the full table structure for us, we can't help you.

 

[RTimberlake]

I will do my best:

This is the table layout for an evaluation that I am working on for our
students. The employee evaluation that Irefered to in the previous question
will be set up the exact same way.

tblEmployee: empEmpID(autonumber); empLastName(Text); empFirstName (Text)
tblStudent: stuStudentID(autonumber); stuLastName(text);
stuFirstName(text); stuActive(yes/no)
tblEvalFactors: evfFactorID(autonumber); evfSort(number); evfFactor(text);
evfDescription(memo); evfActive(yes/no)
tblEvalDetails: evdEvalDetailID(autonumber); evdEvalID(number{link to
tblEvaluation}); evdFactorID(number {link to tblEvaluation});
evdRating(number); evdComments(memo)
tblEvaluation: evaEvalID(autonumber); evaStudentID(number);
evaDate(Date/Time); evaByEmpID(number); evaComments(memo); evaRecommend(memo)

Please let me know if you need more info.

 

[Amy Blankenship]

I will do my best:

This is the table layout for an evaluation that I am working on for our
students. The employee evaluation that Irefered to in the previous
question
will be set up the exact same way.

tblEmployee: empEmpID(autonumber); empLastName(Text); empFirstName
(Text)
tblStudent: stuStudentID(autonumber); stuLastName(text);
stuFirstName(text); stuActive(yes/no)
tblEvalFactors: evfFactorID(autonumber); evfSort(number);
evfFactor(text);
evfDescription(memo); evfActive(yes/no)
tblEvalDetails: evdEvalDetailID(autonumber); evdEvalID(number{link to
tblEvaluation}); evdFactorID(number {link to tblEvaluation});
evdRating(number); evdComments(memo)
tblEvaluation: evaEvalID(autonumber); evaStudentID(number);
evaDate(Date/Time); evaByEmpID(number); evaComments(memo);
evaRecommend(memo)

Please let me know if you need more info.

The directions I'm giving you will allow you to show the results in a query
that can be used in a report, but I don't think you can make a form out of
it:

Open up the query builder. Show the following tables:

tblStudent, tblEvalFactors, tblEvalDetails, tblEvaluation

If there's not a join line going from tblStudent.stuStudentID to
tblEvaluation.evaStudentID , click on tblStudent.stuStudentID and drag it
across to create a join.

From tblEvalFactors, select evfDescription, evfActive, and any other things
in that table you want shown in your query
Now, from tblStudent, select stuLastName, stuFirstName, stuActive
From tblEvalDetails: evdRating

tblEvaluation is just there to make sure all the joins work...you can't show
that level of detail and still get the aggregation you want from your query.

Now, go to the View menu and select "totals."

Once you've done that, go to the stuActive and evfActive columns and in the
Totals row select "where". In the Criteria row, put "Yes"

Under evdRating, in the Totals row, select "Avg."

That should get you pretty close...as close as I can come without having
your database in front of me.

HTH;

Amy

 

[RTimberlake]

Amy-- I tried what you said. When I tried to run the query a message box
popped up that said, "Data type mismatch in criteria expression" What do I
need to do to fix this?

 

[Amy Blankenship]

Try -1 instead of "Yes".

HTH;

Amy

Amy-- I tried what you said. When I tried to run the query a message box
popped up that said, "Data type mismatch in criteria expression" What do I
need to do to fix this?

 

[RTimberlake]

Thanks...it works beautifully.
How did you become so proficient with Access? I would love to learn how to
properly use this program.

 

[Amy Blankenship]

Surfing the Access websites, and need ;-).

This is a great website to pick up some hints
http://www.datapigtechnologies.com/AccessMain.htm

HTH;

Amy

 

[RTimberlake]

That is one of my most visited sites. I only wish that he would take requests.