"Countif"

B

bbddvv

There is a column in my table that each record must answer either "Yes" or
"No". My question is, how do i count the number of "Yes"s (across all
records) in a query?

Step by step instructions would be very helpful, as i am a newbie to your
fascinating MS Access world.

Thanks!
 
S

Steve Schapel

Bbddvv,

Well, it depends a bit where you want this count to be used.

Here's one approach...
1. Make a query based on your table.
2. Add the Yes/No field to the query design grid.
3. Make it a Totals Query (select Totals from the View menu).
4. In the Totals row of the grid, change it from Group By to Count.
5. In the Criteria row, put -1
6. Run the query (click the toolbar button with the red [!] icon.

Here's another approach...
In the Footer section of a continuous view form which is based on this
table, put an unbound textbox. In its Control Source property, put the
equivalent of this...
=Abs(Sum([YourYesNoField]))
 
B

bbddvv

Steve:
The 1st method only counts the numbers of YES's in a single record, it does
not total all of the YES's in the whole table.

The 2nd method yields a total = (# of YES's) - (# of NO's). It needs to
simply count the # of YES's.

Any other ideas? Thanks!




Steve Schapel said:
Bbddvv,

Well, it depends a bit where you want this count to be used.

Here's one approach...
1. Make a query based on your table.
2. Add the Yes/No field to the query design grid.
3. Make it a Totals Query (select Totals from the View menu).
4. In the Totals row of the grid, change it from Group By to Count.
5. In the Criteria row, put -1
6. Run the query (click the toolbar button with the red [!] icon.

Here's another approach...
In the Footer section of a continuous view form which is based on this
table, put an unbound textbox. In its Control Source property, put the
equivalent of this...
=Abs(Sum([YourYesNoField]))

--
Steve Schapel, Microsoft Access MVP
There is a column in my table that each record must answer either "Yes" or
"No". My question is, how do i count the number of "Yes"s (across all
records) in a query?

Step by step instructions would be very helpful, as i am a newbie to your
fascinating MS Access world.

Thanks!
Click to expand...
Click to expand...
 
S

Steve Schapel

Bbddvv,
The 1st method only counts the numbers of YES's in a single record, it does
not total all of the YES's in the whole table.
Click to expand...

This is not correct. It should give you a count of all the Yesses in
the table, if you did the steps I told you. If you go to the query, in
design view, and select SQL from the View menu, it should look something
like this...
SELECT Count([YourYesNoField] As CountOfYourYesNoField
FROM YourTable
WHERE YourTable.YourYesNoField = -1;
The 2nd method yields a total = (# of YES's) - (# of NO's). It needs to
simply count the # of YES's.
Click to expand...

This is not correct. It should give you a count of all the Yesses in
the table, if you do the steps I told you.
 
S

Steve Schapel

Bbddvv,

Ah! I see that maybe we are talking about a field with a Text data
type, and the data is being entered literally as "Yes" or "No"... Sorry,
I had assumed a Yes/No data type.

If so, then the first method needs to be adjusted at step 5 to put "Yes"
in the Criteria instead of -1. The SQL view will be like this...
SELECT Count([YourYesNoField] As CountOfYourYesNoField
FROM YourTable
WHERE YourTable.YourYesNoField = "Yes";

And the second method would need to be adjusted like this...
=Abs(Sum([YourField]="Yes"))
 
Top