sum of yes's in yes/no fields

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?
 
J

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

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

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?

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

Frits van Soldt

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?

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Counting a count 1
How to Import Spreadsheets and Filter data into Reports 1
Printing with OLE objects 2
Conditional Formatting 1
Printing Problems 1
Sum Expression 2
IIf 2
Crosstab query by date range 7

Top