Expression in a Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jennie - 27 Jun 2007 22:26 GMT

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

Reply to this Message

SteveM - 27 Jun 2007 23:46 GMT

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

> 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
[quoted text clipped - 7 lines]
>
> Jennie

Reply to this Message

Jennie - 28 Jun 2007 20:36 GMT

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.

> To count the number of 'yes' values use this formula in your query:
> IIf(Nz(FieldName, 0) = Yes, 1, 0)
[quoted text clipped - 18 lines]
> >
> > Jennie

Reply to this Message