Count in a Query

  • Thread starter Thread starter Julie Gilliam
  • Start date Start date
J

Julie Gilliam

I have a database that I do audits in and the answers are either yes or no.
I was wondering how I could build a query with the Sum line to show number of
No's. The response for the report that I put out there yesterday worked but
it is very time consuming to set up formulas for each field. Thanks
 
In your query criteria line, you can put Like No or possibly use an IIF
statement:
IIF([yourfieldname]="no","no","")
Or find Yes's and No's:
IIF([yourfieldname]="No","No","Yes")
 
I have a database that I do audits in and the answers are either yes or no.
Click to expand...

If you have one field per question, your table design is WRONG and needs to be
reconsidered! How many yes/no fields are there? Are you encoding each question
in a fieldname? If so, consider normalizing into two tables: you should have a
one-to-many relationship to a table with one *record* for each answer, not one
*field*.
I was wondering how I could build a query with the Sum line to show number of
No's. The response for the report that I put out there yesterday worked but
it is very time consuming to set up formulas for each field. Thanks
Click to expand...

Yes is stored as -1, No as 0 - so you can count Yesses with

=-Sum([yesnofield])

and Nos with

=Sum([yesnofield] + 1)
 
Back
Top