Total Query

[Guest]

I am Brand NEW at Access and am having difficulty just trying to add the
number of Yes responses I'm getting in a field. I am using the "Total"
feature in a query. I have one field in my table called "Stroke" and it has
check boxes indicating yes or no. I would like to add the number of yes
boxes in the stroke field so I can get a "TOTAL" number of yes responses
using the design query . When I use the "sum" feature it gives me a -1 by
each yes entry in the view mode. When I use count it counts all the records
in the whole Stroke field.

Please help a newbie if you can

 

[Guest]

Add the Yes/No field to your query grid again. In the Total: area, select
Where. In the Criteria: area, enter =Yes. And UnCheck the Show box.

 

[Guest]

The values of Yes/No fields are Boolean TRUE or FALSE, which are implemented
in Access as -1 or 0. Its bad programming practice to rely on the
implementation, however, and one should always work with the Boolean values
(you'll sometimes see this done by summing the absolute value of the field,
which is an example of this frowned upon practice; its what the head of a
software company of my acquaintance once called 'being unduly chummy with the
implementation').

To count the TRUE values you can SUM the return value of an expression which
returns 1 or 0 depending on whether the value of the field is TRUE or FALSE.
The IIF function does this so you can use that. By summing the return values
of all rows you are in effect counting all those which are TRUE because they
return 1 while the FALSE rows return zero.

In query design view create a Totals query in the usual way and in the
'field' row of a blank column enter:

CountOfStroke:SUM(IIF([Stroke],1,0))

In the totals row select SUM.

However, I'm a little concerned that you might be using a Boolean (Yes/No)
field here inappropriately and be guilty of what's called in the jargon
'encoding data as a column heading'. If you'd care to explain in a little
more detail what your table represents and the role of this field in it I can
probably advise further.

Ken Sheridan
Stafford, England

 

[John Vinson]

When I use the "sum" feature it gives me a -1 by
each yes entry in the view mode. When I use count it counts all the records
in the whole Stroke field.

Sum the value, and put a - sign in front to get a positive number:

-Sum([yesnofield])

John W. Vinson[MVP]