Adding values based on 3 values eg YES , No and N/A

  • Thread starter Thread starter Billy
  • Start date Start date
B

Billy

I have a form with a series of questions. There can be 3 ways to answer. YES
NO and N/A

I need to calculate the response as a percentage. The problem for me is that
I don't want to include any N/A answers in the final %.

eg

Question 1 = Yes
Question 2 = No
Question 3 = N/A
Question 4 = N/A

Therfore the % correct is 50%

Any ideas?


Regards

Billy
 
PercentageValue =
Sum(IIf([QuestionField]<>"N/A",1,0))/Count([QuestionField])*100
 
Thanks for the quick reply.
How do I apply this for a summary of all the fields?


Ken Snell said:
PercentageValue =
Sum(IIf([QuestionField]<>"N/A",1,0))/Count([QuestionField])*100
--

Ken Snell
<MS ACCESS MVP>

Billy said:
I have a form with a series of questions. There can be 3 ways to answer. YES
NO and N/A

I need to calculate the response as a percentage. The problem for me is that
I don't want to include any N/A answers in the final %.

eg

Question 1 = Yes
Question 2 = No
Question 3 = N/A
Question 4 = N/A

Therfore the % correct is 50%

Any ideas?


Regards

Billy
Click to expand...
Click to expand...
 
Having no firm idea of how your database is structured, one way might be to
create a union query to get all the fields in a single "field", and then use
that union query as the "table" for the query that uses the expression I
gave.

Select Field1 AS AllFields FROM TableName
UNION ALL
Select Field2 FROM TableName
UNION ALL
Select Field3 FROM TableName
etc.

Save the above query and then use it in the second query.
--

Ken Snell
<MS ACCESS MVP>


Billy said:
Thanks for the quick reply.
How do I apply this for a summary of all the fields?


Ken Snell said:
PercentageValue =
Sum(IIf([QuestionField]<>"N/A",1,0))/Count([QuestionField])*100
--

Ken Snell
<MS ACCESS MVP>

Billy said:
I have a form with a series of questions. There can be 3 ways to
Click to expand...
answer.
YES
NO and N/A

I need to calculate the response as a percentage. The problem for me
Click to expand...
is
that
I don't want to include any N/A answers in the final %.

eg

Question 1 = Yes
Question 2 = No
Question 3 = N/A
Question 4 = N/A

Therfore the % correct is 50%

Any ideas?


Regards

Billy
Click to expand...
Click to expand...
Click to expand...
 
Back
Top