Adding up checkboxes in a field

[clickster]

In one of my tables, I have a large number of fields that are Yes/No fields and I need to find out how many records have a certain field checked (i.e 14 records have field 4 checked YES). Is there an easy way of doing this? I can't change the layout of the table or I would simply make the fields into records and the records into fields

 

[JPMontreal]

Create a Query for the field you wish to count:


SELECT Count(MyTable.Field4) AS CountOfField4
FROM MyTable
WHERE (((MyTable.Field4)=Yes));

Jean-Paul
Montreal
mtljp2@sympatico.ca

 

[clickster]

I tried this, but I got the following error after typing "CountOfFieldWin95":

Compile Error:
Expected: Case

It had highlighted "COUNT".

Any ideas?

I switched the table and field names for my table and field names.

 

[clickster]

nevermind. I was making a stupid mistake.

 

[WildHare]

Depending on how many fields you need to count, you can do this rather easily in a query. Load your table, and bring each field you want to count up in to the query grid TWICE.

Turn on TOTALS query. Set the modifier to COUNT. Set the Criteria to YES for one of the paired fields, and NO for the other one. Run the query. It will give you how many FOOs are set to YES, and how many FOOs are set to NO. If necessary, you might need FOO a third time to count IS NULLs....

Jim Jim Hare
"Remember, you're unique - just like everonone else"

http://www.jmhare.com

 

[clickster]

I've got the TOTALS query selected. But when I enter Yes and No in their respective criteria boxes, the query returns no information (not even empty boxes, just field names). I have even tried -1 and 0 instead of yes and no. How should I format the information in the Criteria box? I also tried it with a third whose criteria was Is Null

 

[TheWildHare]

Let's assume your field is called Field1, and your table is called Table1.Your query grid should look like this:

Field         Field1       Field1
--------------------------------------
Table         Table1        Table1
--------------------------------------
Total         Count         Count
--------------------------------------
Sort          unsorted      unsorted
--------------------------------------
Show             X             X
--------------------------------------
Criteria       YES           NO

If Field1 is a valid YES/NO field, and you actually have data in it, you should get an accurate count...