Lookup value in weighting table

[jhicsupt via AccessMonster.com]

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],
[EETitle], [EEAnswer] and [MgrAnswer].

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

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
tblEeFile [EEAnswer] X tblweighting [EEAnswerW]

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.com
http://www.accessmonster.com/Uwe/For...ccess/200510/1

[Douglas J. Steele]

Sounds as though you want something like:

SELECT tblEeFile.QuesNo,
tblEeFile.EEAnswer,
IIf(tblEeFile.EETitle = Nz(tblweighting.EETitleW, ""),
tblEeFile.EEAnswer * tblweighting.EEAnswerW,
tblEeFile.EEAnswer) AS WeightedAnswer
FROM tblEeFile
LEFT JOIN tblweighting
ON tblEeFile.QuesNo = tblweighting.QuesNoW

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.

I don't pretend to understand how MgrAnswer and MgrAnswerW enter into
this...

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"jhicsupt via AccessMonster.com" <u14456@uwe> wrote in message
news:5646fd5792b96@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],
[EETitle], [EEAnswer] and [MgrAnswer].

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

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
tblEeFile [EEAnswer] X tblweighting [EEAnswerW]

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.com
http://www.accessmonster.com/Uwe/For...ccess/200510/1