conditional count in report

  • Thread starter Thread starter 00KobeBrian
  • Start date Start date
0

00KobeBrian

Hi,

How do I put a conditional counter at the footer in a report? For
example, I got a list of students in a report and I want to know how many
male and female in total. Thanks.
 
Hi,

How do I put a conditional counter at the footer in a report? For
example, I got a list of students in a report and I want to know how many
male and female in total. Thanks.
Click to expand...

Base the Report on a Query with two calculated fields;

IsMale: IIF([Sex] = "M", 1, 0)
IsFemale: IIF([Sex] = "F", 1, 0)

In the report Footer put textboxes Summing these numeric values.

John W. Vinson[MVP]
 
Is there a way I can directly put the works on the report instead of the
query? Thanks.


John Vinson said:
Hi,

How do I put a conditional counter at the footer in a report?
For
example, I got a list of students in a report and I want to know how many
male and female in total. Thanks.
Click to expand...

Base the Report on a Query with two calculated fields;

IsMale: IIF([Sex] = "M", 1, 0)
IsFemale: IIF([Sex] = "F", 1, 0)

In the report Footer put textboxes Summing these numeric values.

John W. Vinson[MVP]
Click to expand...
 
Add a control in the report footer.
Set its source to

=Abs(Sum(Sex="Male"))

For female

=Abs(Sum(Sex="Female"))

That assumes that you have a text field named Sex and that it contains the
values Male or Female.
 
It works perfectly. Thanks.


John Spencer said:
Add a control in the report footer.
Set its source to

=Abs(Sum(Sex="Male"))

For female

=Abs(Sum(Sex="Female"))

That assumes that you have a text field named Sex and that it contains the
values Male or Female.
Click to expand...
 
Is there a way I can directly put the works on the report instead of the
query? Thanks.
Click to expand...

My friend John Spencer's answer solves this and simplifies the problem
anyway - go with it!

btw, the Abs() is because the value of True is -1. If you just add the
logical expressions you'll get -12 if there are 12 girls.

John W. Vinson[MVP]
 
00KobeBrian said:
By the way, how come the absolute value function works on this?
Click to expand...

Because, behind the scenes, Access uses -1 for True and 0 for False (and you
are having Access do the calculation). So, if there are any Trues, you'll
sum them and they will result in a negative number equivalent to the
positive number of Trues. Then the Abs function converts the negative number
to a positive number.

Larry Linson
Microsoft Access MVP
 
Back
Top