sum of yes's in yes/no fields

  • Thread starter Thread starter kc
  • Start date Start date
K

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?
 
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?
Click to expand...

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

John Vinson said:
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?
Click to expand...

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.
Click to expand...
 
John Vinson said:
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?
Click to expand...

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.
Click to expand...
Why a sum? Just use count(*) of those records that have a fee waiver.
 
Why a sum? Just use count(*) of those records that have a fee waiver.
Click to expand...

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