Total Mulitple Yes/No Fields

[dcurtis]

This DB that I have inherited has several Yes/No fields in one table. Each record has some information then 5 or so Yes/No.

RecNo recDate Chk1 Chk2 Chk3......
1 1/1/01 Yes No No
2 1/1/01 No Yes Yes
3 1/2/01 Yes No Yes


I need to write a query that will total the number of "yes" records for each check box.

So the one query would tell me there are:
2 records with chk1 = Yes
1 record with chk2 = Yes
2 records with chk3 = Yes.

Any help is appreciated.

----
Access 2002 on a mixed Windows OS network.

 

[lespaul]

SELECT COUNT(IIF CHECK1 = 'Yes', 1, 0) AS CHECK1Count, COUNT(IIF CHECK2 = 'Yes', 1, 0) As CHECK2Count FROM TABLENAME GROUP BY CHECK1, CHECK2

(add each field the same way to the select AND the Group By)

HTH

Leslie

 

[SteveR77]

If you set up your query so all the Yes/No fields are selected then make sure that the criteria is set to be Yes for all 5 fields. The next step would be to change the select query into a group by. This is done by clicking on the sigma (It looks like a stylized E) in the query design.
Finally change all of the group by options to count.

HTH,

Steve

 

[dcurtis]

I couldn't get either of those 2 to work, but was able to use DCount to get the results directly on the report. Thanks for your time.