Count question

[joe789]

Hi Folks,

I have several fields in a table, each field in the table can be either a Yes, No, or Null. Is it possible to run a query that would display how many Yes answers exists in each field (group the Yes count by field). Is this possible, or do I have to create a TOTALS query for each and every field in the table and then another query to merge all the TOTAL counts for each field together and display it that way?

Any help is greatly appreciated.

Thank you,

Joe

 

[Pat Hartman]

Sounds like a db design issue. A typical spreadsheet solution in place of a proper 1-to-many relationship.

Since the values of yes/no fields are -1 for true and 0 for false, you can sum the absolute value to get a count of trues.

Sum(abs(Nz(SomeField,0)))

I added the Nz() because you have some null values.

However, if you wanted to count the occurances of the three values, you would need either three queries or three IIf() functions. Better to normalize your table and leave all these calculation issues behind. A query of a normalized table that counts all occurances of all values is:

Select YourField, Count(*) as CountOfYourField
From YourTable
Group By YourField;

 

[dcx693]

I created a single totals query that seemed to solve the problem. I assume that the field in question is a text field of some sort (boolean yes/no fields cannot be null).

I started with a select query, then made it a totals query. In the field line, I put an expression like this: [Field1]="Yes", and did the same for [Field2] etc....
In the Total line, I chose the Sum function. Don't put anything in the Criteria line. Run it. This should give you a count of all the Yes occurences in your fields. It won't matter if you have Nulls or not, it skips over them.