Count multiple yes/no field in a query

[Sangeetha]

Hi,

I have a table, which has mutiple fields for yes/no, a classification and a
date field. The users will select, the classification and a date and click,
yes /no button for various field. This is basically for document checking.
The user will check a document, enter the classification, based on what
document is checked and the date of checking, and check the various
attributes.

End of the day, i want a query to group by date and by classification and on
the count of each attributes. How can i do this.

I tried using the group by and made a count for the attributes (yes/no
filed), by entering criteria as "yes". i also tried putting 0 and true. But ,
it pulls up all the records and does not meet the criteria.

 

[Graham Mandeno]

Hi Sangeetha

You can use a trick here, based on the fact that the value for "No" is 0 and
the value for "Yes" is -1.

Assuming you already have a "totals" query grouping by date and
classification, you can include a field in your query:
NumberOfYes: -Sum([YourYesNoField])

This will add up all the 0 and -1 values, and then reverse the sign, giving
you the number of "yes" values.

 

[Sangeetha]

Thanks ! It works. But,it works only for true records. How to do it for false
records. Also,when we use this function, how will one, know, whether it is a
null field or a false field.

Thanks
Sangeetha.

Hi Sangeetha

You can use a trick here, based on the fact that the value for "No" is 0 and
the value for "Yes" is -1.

Assuming you already have a "totals" query grouping by date and
classification, you can include a field in your query:
NumberOfYes: -Sum([YourYesNoField])

This will add up all the 0 and -1 values, and then reverse the sign, giving
you the number of "yes" values.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,

I have a table, which has mutiple fields for yes/no, a classification and
a
date field. The users will select, the classification and a date and
click,
yes /no button for various field. This is basically for document checking.
The user will check a document, enter the classification, based on what
document is checked and the date of checking, and check the various
attributes.

End of the day, i want a query to group by date and by classification and
on
the count of each attributes. How can i do this.

I tried using the group by and made a count for the attributes (yes/no
filed), by entering criteria as "yes". i also tried putting 0 and true.
But ,
it pulls up all the records and does not meet the criteria.

 

[Paul Shapiro]

You can also use count(iif([yourYesNoField], 1, Null) and switch the 1 and
Null for counting false values. Count(*) - Count([yourField]) will be the
number of rows where yourField is null, or you could use
count(iif(IsNull([yourField]), 1, Null). Etc.

Thanks ! It works. But,it works only for true records. How to do it for
false
records. Also,when we use this function, how will one, know, whether it is
a
null field or a false field.

Thanks
Sangeetha.

Hi Sangeetha

You can use a trick here, based on the fact that the value for "No" is 0
and
the value for "Yes" is -1.

Assuming you already have a "totals" query grouping by date and
classification, you can include a field in your query:
NumberOfYes: -Sum([YourYesNoField])

This will add up all the 0 and -1 values, and then reverse the sign,
giving
you the number of "yes" values.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,

I have a table, which has mutiple fields for yes/no, a classification
and
a
date field. The users will select, the classification and a date and
click,
yes /no button for various field. This is basically for document
checking.
The user will check a document, enter the classification, based on what
document is checked and the date of checking, and check the various
attributes.

End of the day, i want a query to group by date and by classification
and
on
the count of each attributes. How can i do this.

I tried using the group by and made a count for the attributes (yes/no
filed), by entering criteria as "yes". i also tried putting 0 and true.
But ,
it pulls up all the records and does not meet the criteria.

 

[Douglas J. Steele]

Personally, I prefer to use Sum(IIf([yourYesNoField], 1, 0)) to count the
Trues and Sum(IIf([yourYesNoField], 0, 1)) to count the Falses. While Count
does ignore Nulls, I feel the Sum better documents what you're doing.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You can also use count(iif([yourYesNoField], 1, Null) and switch the 1 and
Null for counting false values. Count(*) - Count([yourField]) will be the
number of rows where yourField is null, or you could use
count(iif(IsNull([yourField]), 1, Null). Etc.

Thanks ! It works. But,it works only for true records. How to do it for
false
records. Also,when we use this function, how will one, know, whether it
is a
null field or a false field.

Thanks
Sangeetha.

Hi Sangeetha

You can use a trick here, based on the fact that the value for "No" is 0
and
the value for "Yes" is -1.

Assuming you already have a "totals" query grouping by date and
classification, you can include a field in your query:
NumberOfYes: -Sum([YourYesNoField])

This will add up all the 0 and -1 values, and then reverse the sign,
giving
you the number of "yes" values.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,

I have a table, which has mutiple fields for yes/no, a classification
and
a
date field. The users will select, the classification and a date and
click,
yes /no button for various field. This is basically for document
checking.
The user will check a document, enter the classification, based on
what
document is checked and the date of checking, and check the various
attributes.

End of the day, i want a query to group by date and by classification
and
on
the count of each attributes. How can i do this.

I tried using the group by and made a count for the attributes (yes/no
filed), by entering criteria as "yes". i also tried putting 0 and
true.
But ,
it pulls up all the records and does not meet the criteria.