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

[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

 

[Ken Snell [MVP]]

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

 

[Billy]

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

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

Ken Snell
<MS ACCESS MVP>

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

 

[Ken Snell [MVP]]

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>

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

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

Ken Snell
<MS ACCESS MVP>

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