Need to look up value by using weighting table 1

[jmhicsupt]

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.

 

[Lilliabeth]

Is this database already populated with records (too late to change the design of the tables?)

 

[jmhicsupt]

Yes, too late to change design because the data is being imported from Excel spreadsheets (a lot of them are being exported into one table).

So I have two tables - one that is a result of me importing the Excel spreadsheets and one that will act as the lookup table.

 

[Lilliabeth]

If I understand correctly, this query will get you results for non-managers

SELECT tblEEFile.EmployeeID, Sum([EEAnswer]*[EEAnswerW]) AS Result
FROM tblEEFile INNER JOIN tblWeighting ON (tblEEFile.QuestNo = tblWeighting.QuesNoW) AND (tblEEFile.EETitle = tblWeighting.EETitleW)
GROUP BY tblEEFile.EmployeeID;

And I think this will get you results for managers.

SELECT tblEEFile.EmployeeID, Sum([MgrAnswer]*[MgrAnswerW]) AS [Mgr Result]
FROM tblEEFile INNER JOIN tblWeighting ON (tblEEFile.EETitle = tblWeighting.EETitleW) AND (tblEEFile.QuestNo = tblWeighting.QuesNoW)
GROUP BY tblEEFile.EmployeeID;

 

[jmhicsupt]

I may have written my question in confusing way (sorry).

Table 1
QuesNoW DataEntry Mechanic Foreman Wrapper QA
1.1 3 2 4 5 1
1.2 4 4 3 3 2
1.3 5 5 5 3 3

Table 2
QuesNo EEName EETitle EEAnswer MgrAnswer
1.1 John Smith DataEntry 3 4
1.2 John Smith DataEntry 4 4
1.3 John Smith DataEntry 3 5
1.1 John Doe Mechanic 5 3
1.2 John Doe Mechanic 4 4

Now what I want to do is say for instance in question 1.1 John Smith answered 3. On the weighting table, question 1.1 for DataEntry is 3. So the EEFinalScore would be 9. For the manager’s answer, the final score would be 12.

For John Doe, in question 1.1 he answered 5. On the weighting table, question 1.1 for Mechanic is 5, so the EEFinalScore would be 10, the MgrFinalScore would be 6.

Is there a way to do this?

THANKS so much for any help you can give me.

 

[dhookom]

When you refer to "weighting table" in your text, consider not using "Table 1" as the title of the table in your example. It is way too confusing for an old guy like me.

You issue is that the weighting table is not normalized. Normalize it with a union query
SELECT QuesNoW, "DataEntry" as Job, [DataEntry] As Weight
FROM tblWeighting
UNION ALL
SELECT QuesNoW, "Mechnanic", [Mechanic]
FROM tblWeighting
UNION ALL
--other jobs--;

You can then join your "table 2" with the results from the union query.

It would be best to normalize your data since you are currently locked in to 5 jobs without having to change fields, forms, reports, queries,....


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jmhicsupt]

Thank you so much, it worked!