Weighted calculations

[mmcintire]

I have a form with multiple questions. I need to give a weighted value to
each question (Q1=20pts, Q2=10pts). Based on the answer (Yes, No, NA) I want
a value assigned to the question (Yes=20, No=0). Once all questions are
answered, I have a total score field that I would like to display the final
score as a percentage. Percentage is calculated by totalling all the weights
of questions that do not have an answer of NA (this would be the
denominator). Numerator is the sum of the values assigned based on Yes or No
answer. I am not even sure how to start this calculation.

 

[Sprinks]

Hi.

I think the easy way to do this is to store the points for each question and
the correct answer in a table like the following. Then you can use a
continuous form based on a query between the table that stores the responses
and Questions, and have access to these fields for calculating the
AnswerValue and PointsPossible for each question. Sum these values in the
form footer.

Questions
QuestionID AutoNumber (PK)
Question Text
PointValue Integer
Answer Integer

Assuming the responses Yes, No, and NA are in an option group named
Response, whose values are 1, 2, and 3, respectively, the summary calculation
is:

=Sum(IIf([Response]=[Answer],[Points],0))/Sum(IIf([Response]=3,0,[Points]))

, formatted as Percent.

So that the calculation occurs after each response is entered, enter the
following in the AfterUpdate event procedure for Response:

Me.Recalc

If this strategy is not feasible for some reason, I think you will need to
create a custom function. If you need help with this, please post your table
structures and their relationships.

Hope that helps.
Sprinks