473,848 Members | 1,590 Online

# Lookup value in weighting table

Bear with me – a long question but hopefully someone can help me.

I have a survey which uses weighting to get the final score. I’m trying to
find the best way to calculate the final score.

I have two tables
A table with all the employee information which includes the [QuesNo],

A table that will be used as a lookup to determine the weighting of that
particular question. The fields are [QuesNoW]; [EETitleW], [EEAnswerW]; and

For example,
For question 1.1, the weighting factor for a manager is 3.
If employee answered 9 for question 1.1 and the title is manager, then the
final score would be 27.

For question 1.1, the weighting factor for a data entry clerk is 5.
If employee answered 9 for question 1.1 and their title is data entry clerk,
then the final score would be 45.

First I would need to look up the [Quesno] in the Weighting Table. Once
[QuesnoW] is found, then lookup the [TitleWeight]. Once title is found,
multiply the value of [Answer1] by the value that is in
When the value in [Answer1]=9, then the total score would be 27.

If tblEeFile [quesno] = tblweighting[QuesNoW]
and if
tblEeFile [EETitle] = tblweighting [EETitleW]
then
multiply the value of

If this is confusing, please ask me more questions and I can explain better.

Thanks for any help I can get.
--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200510/1
Nov 13 '05 #1
1 1713
Sounds as though you want something like:

SELECT tblEeFile.QuesN o,
tblEeFile.EEAns wer,
IIf(tblEeFile.E ETitle = Nz(tblweighting .EETitleW, ""),
FROM tblEeFile
LEFT JOIN tblweighting
ON tblEeFile.QuesN o = tblweighting.Qu esNoW

This will score the answer as is if the titles don't correspond, and
multiply by the weight if they do.

I assume there are more fields in tblEeFile that would need to be added to
the query.

this...

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele

"jhicsupt via AccessMonster.c om" <u14456@uwe> wrote in message
news:5646fd5792 b96@uwe...
Bear with me – a long question but hopefully someone can help me.

I have a survey which uses weighting to get the final score. I’m trying
to
find the best way to calculate the final score.

I have two tables
A table with all the employee information which includes the [QuesNo],

A table that will be used as a lookup to determine the weighting of that
particular question. The fields are [QuesNoW]; [EETitleW], [EEAnswerW];
and

For example,
For question 1.1, the weighting factor for a manager is 3.
If employee answered 9 for question 1.1 and the title is manager, then the
final score would be 27.

For question 1.1, the weighting factor for a data entry clerk is 5.
If employee answered 9 for question 1.1 and their title is data entry
clerk,
then the final score would be 45.

First I would need to look up the [Quesno] in the Weighting Table. Once
[QuesnoW] is found, then lookup the [TitleWeight]. Once title is found,
multiply the value of [Answer1] by the value that is in
When the value in [Answer1]=9, then the total score would be 27.

If tblEeFile [quesno] = tblweighting[QuesNoW]
and if
tblEeFile [EETitle] = tblweighting [EETitleW]
then
multiply the value of

If this is confusing, please ask me more questions and I can explain
better.

Thanks for any help I can get.
--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200510/1

Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.