Expression in a Query

[Guest]

I have a table that has about 35 columns in it. The columns are labelled
BM01, BM02, BM03, etc... Each column has either Yes, No, or is blank in each
row. I am hoping there is a way to create a query that will show me the
number of Yes's divided by the sum of Yes+No for each column. I have about 30
different tables that are the same way so I am hoping there is a somewhat
painless way to do it.

I was playing with the Total and Criteria fields in the query but I don't
really understand them so that didn't work out for me. Any ideas?

Jennie

 

[Guest]

To count the number of 'yes' values use this formula in your query:
IIf(Nz(FieldName, 0) = Yes, 1, 0)
If the field is Null, Nz() will return 0, otherwise it will return the value
of the field.
Then you can use 'View Totals' with a Sum on the field. That will give you
the count of Yes values in each column.

Why do you then want to divide it by the sum of yes+no?

Steve

 

[Guest]

I need to get the percentage of Yes's out of the total number of answers. If
there was no answer then I do not need to count it.