Count on a yes/no field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alex Martinez - 15 Sep 2005 06:43 GMT

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.

Reply to this Message

John Vinson - 15 Sep 2005 07:58 GMT

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

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]

Reply to this Message

oozyscab - 15 Sep 2005 16:13 GMT

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

Reply to this Message