Count on a yes/no field

A

Alex Martinez

Hi,

I am using Access 2002 and I have a query that I would like to count a
particular field, the problem is that my table has 500 records and I know
that there are 100 records that are "yes" , but I get the whole 500 records
in the count. How do I count the Yes only? Thank you in advance.
 
J

John Vinson

Hi,

I am using Access 2002 and I have a query that I would like to count a
particular field, the problem is that my table has 500 records and I know
that there are 100 records that are "yes" , but I get the whole 500 records
in the count. How do I count the Yes only? Thank you in advance.
Click to expand...

The Count operator doesn't count values - it counts records, so that's
normal.

Two choices: if you don't care about the No records, use a criterion
of True on the yes/no field; use the Where operator in the Totals
line.

If you want to see all records but only count the Yes ones, you can
use the fact that Yes is stored as -1, No as 0; use

-Sum([yesnofield])

Sum will add up the records, adding 0 for the Nos and -1 for the
Yesses; that will give you (say) -103 as a sum. The minus sign will
change this to 103.

John W. Vinson[MVP]
 
O

oozyscab via AccessMonster.com

Try this as a new column in your query... CountYesColumn: IIF([YesNoField] =
True, Count([YesNoField]))
 
Top