Simple Count Query Question

  • Thread starter Thread starter Doug VanDerMark
  • Start date Start date
D

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))
 
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
'====================================================
 
Doug said:
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))
Click to expand...


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.
 
thankyou

John Spencer said:
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
'====================================================
Click to expand...
 
Back
Top