Simple Count Query Question

[Doug VanDerMark]

Access 2002

4 Fields, 1 text and 3 yes/no

Group on the text field and count the number of each yes/no fields
individually, the query wizard just adds all 3 yes/no and puts the count in
all 3 yes/no field

Field 2 Field 3 Field 4
Johnson x
Johnson x
Smith x
Johnson x
Smith x

Want to see for totals

Field 2 Field 3 Field 4
Johnson 2 1
Smith 1 1

I am getting

Field 2 Field 3 Field 4
Johnson 3 3 3
Smith 2 2 2

Here is the SQL command

SELECT Table1.Group1, Count(Table1.Field2) AS CountOfField2,
Count(Table1.Field3) AS CountOfField3, Count(Table1.Field4) AS CountOfField4
FROM Table1
GROUP BY Table1.Group1
HAVING (((Count(Table1.Field2))=True)) OR (((Count(Table1.Field3))=True)) OR
(((Count(Table1.Field4))=True))

 

[John Spencer]

SELECT Table1.Group1,
Abs(Sum(Table1.Field2)) AS CountYes2,
Count(IIF(Table1.Field3=True,1,Null)) AS CountYes3,
Sum(IIF( Count(Table1.Field4=True,1,0)) AS CountYes4
FROM Table1
GROUP BY Table1.Group1

The above shows three different methods for counting the number of Yes
(true) values.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Marshall Barton]

Access 2002

4 Fields, 1 text and 3 yes/no

Group on the text field and count the number of each yes/no fields
individually, the query wizard just adds all 3 yes/no and puts the count in
all 3 yes/no field

Field 2 Field 3 Field 4
Johnson x
Johnson x
Smith x
Johnson x
Smith x

Want to see for totals

Field 2 Field 3 Field 4
Johnson 2 1
Smith 1 1

I am getting

Field 2 Field 3 Field 4
Johnson 3 3 3
Smith 2 2 2

Here is the SQL command

SELECT Table1.Group1, Count(Table1.Field2) AS CountOfField2,
Count(Table1.Field3) AS CountOfField3, Count(Table1.Field4) AS CountOfField4
FROM Table1
GROUP BY Table1.Group1
HAVING (((Count(Table1.Field2))=True)) OR (((Count(Table1.Field3))=True)) OR
(((Count(Table1.Field4))=True))

Count just counts all records with a non-Null value. Since
a Yes/No field can never be Null, it is counting all the
records. Scrap the HAVING clause and try using one of these
variations to do the counting:

SELECT Group1,
Abs(Sum(Field2)) AS CountOfField2,
Sum(IIf(Field3, 1, 0)) AS CountOfField3,
Count(IIf(Field4, 1, Null)) AS CountOfField4
FROM Table1
GROUP BY Table1.Group1

Note that all three variations will calculate the correct
result. Pick the one that you think makes the most sense to
you.

 

[Doug VanDerMark]

thankyou

SELECT Table1.Group1,
Abs(Sum(Table1.Field2)) AS CountYes2,
Count(IIF(Table1.Field3=True,1,Null)) AS CountYes3,
Sum(IIF( Count(Table1.Field4=True,1,0)) AS CountYes4
FROM Table1
GROUP BY Table1.Group1

The above shows three different methods for counting the number of Yes
(true) values.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================