sum of yes's in yes/no fields

[kc]

i have a yes/no field for something called fee waivers. I
want to know how many fee waivers there have been in a
day. all the records contain a date and whether or it was
a fee waiver. How can i put that in a report?

 

[John Vinson]

i have a yes/no field for something called fee waivers. I
want to know how many fee waivers there have been in a
day. all the records contain a date and whether or it was
a fee waiver. How can i put that in a report?

You can use a sneaky trick. A YesNo field is stored as -1 for YES, 0
for NO; so you can set the Control Source of a textbox in a footer to

= -Sum([Fee Waivers])

to sum the Yes values and then change the negative number that results
to positive.

 

[HSalim]

Use this SQL statement as your recordsource.
You can create a query out if it too. You will ofcourse substitute your
table and column names
the where clause is not necessary but you can use any where clause
such as date between a and b, (YesNoField = -1 if you just want to count yes
values) etc

Select DateField, YesNoField, Count(*)
From YourTable
Where DateField Between...
Group by DateField, YesNoField
Order By DateField, YesNoField

The result set will look like this
9/1/2003, 0, 10
9/1/2003, 1, 15

HS

i have a yes/no field for something called fee waivers. I
want to know how many fee waivers there have been in a
day. all the records contain a date and whether or it was
a fee waiver. How can i put that in a report?

You can use a sneaky trick. A YesNo field is stored as -1 for YES, 0
for NO; so you can set the Control Source of a textbox in a footer to

= -Sum([Fee Waivers])

to sum the Yes values and then change the negative number that results
to positive.

 

[Frits van Soldt]

i have a yes/no field for something called fee waivers. I
want to know how many fee waivers there have been in a
day. all the records contain a date and whether or it was
a fee waiver. How can i put that in a report?

You can use a sneaky trick. A YesNo field is stored as -1 for YES, 0
for NO; so you can set the Control Source of a textbox in a footer to

= -Sum([Fee Waivers])

to sum the Yes values and then change the negative number that results
to positive.

Why a sum? Just use count(*) of those records that have a fee waiver.

 

[John Vinson]

Why a sum? Just use count(*) of those records that have a fee waiver.

I believe the poster wanted to count the yesses along with all the
records - the Sum allows both.