Query: how to count up data from different fields

[Mikhail Bogorad]

Hi,
I have a query that brings a bunch of text fields from a table, each
fields value can be either Yes or No. It's a kind of an audit
questionnaire and No means an error. So i'm trying to count a number
of No's in my query.
Any idea how it could be done?

Thanks

 

[John Spencer]

You need to give a bit more information on the structure of your table.

Do you have multiple fields in one record that contain Yes or No?
If so, are you trying to count the number of No in the record or the Number of
No responses in all the records for each question?

Assuming you want a count of NO reponses in a record, you would need an
expression like the following in a field "cell" as a calculated value.

IIF(FieldA="no",1,0) + IIF(FieldB="no",1,0) + ... + IIF(FieldDD="no",1,0)


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Mikhail Bogorad]

You need to give a bit more information on the structure of your table.

Do you have multiple fields in one record that contain Yes or No?
If so, are you trying to count the number of No in the record or the Number of
No responses in all the records for each question?

Assuming you want a count of NO reponses in a record, you would need an
expression like the following in a field "cell" as a calculated value.

IIF(FieldA="no",1,0) + IIF(FieldB="no",1,0) + ... + IIF(FieldDD="no",1,0)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County






- Show quoted text -

Yes, each record has approx 30 fields that have Yes or No. It's going
to be a very long expression. Thanks a lot.

 

[Duane Hookom]

IMO, the reason it's "a very long expression" is your table structure is not
normalized.

Typically you should "count up data from different records" rather than
"count up data from different fields".