A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

conditional count in report



 
 
Thread Tools Display Modes
  #1  
Old November 6th, 2006, 02:27 AM posted to microsoft.public.access.forms,microsoft.public.access.formscoding,microsoft.public.access.reports
00KobeBrian
external usenet poster
 
Posts: 77
Default conditional count in report

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.


  #2  
Old November 6th, 2006, 03:22 AM posted to microsoft.public.access.forms,microsoft.public.access.formscoding,microsoft.public.access.reports
John Vinson
external usenet poster
 
Posts: 4,033
Default conditional count in report

On Mon, 6 Nov 2006 09:27:38 +0800, "00KobeBrian" wrote:

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.


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]


  #3  
Old November 6th, 2006, 03:41 AM posted to microsoft.public.access.forms,microsoft.public.access.formscoding,microsoft.public.access.reports
00KobeBrian
external usenet poster
 
Posts: 77
Default conditional count in report

Is there a way I can directly put the works on the report instead of the
query? Thanks.


"John Vinson" wrote in message
...
On Mon, 6 Nov 2006 09:27:38 +0800, "00KobeBrian" wrote:

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.


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]




  #4  
Old November 6th, 2006, 03:52 AM posted to microsoft.public.access.forms,microsoft.public.access.formscoding,microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 110
Default conditional count in report

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.

00KobeBrian wrote:

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.

  #5  
Old November 6th, 2006, 04:02 AM posted to microsoft.public.access.forms,microsoft.public.access.formscoding,microsoft.public.access.reports
00KobeBrian
external usenet poster
 
Posts: 77
Default conditional count in report

It works perfectly. Thanks.


"John Spencer" wrote in message
...
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.

00KobeBrian wrote:

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.



  #6  
Old November 6th, 2006, 04:05 AM posted to microsoft.public.access.forms,microsoft.public.access.formscoding,microsoft.public.access.reports
00KobeBrian
external usenet poster
 
Posts: 77
Default conditional count in report

By the way, how come the absolute value function works on this?


"John Spencer" wrote in message
...
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.

00KobeBrian wrote:

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.



  #7  
Old November 6th, 2006, 04:15 AM posted to microsoft.public.access.forms,microsoft.public.access.formscoding,microsoft.public.access.reports
John Vinson
external usenet poster
 
Posts: 4,033
Default conditional count in report

On Mon, 6 Nov 2006 10:41:48 +0800, "00KobeBrian" wrote:

Is there a way I can directly put the works on the report instead of the
query? Thanks.


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]
  #8  
Old November 6th, 2006, 04:51 AM posted to microsoft.public.access.forms,microsoft.public.access.formscoding,microsoft.public.access.reports
Larry Linson
external usenet poster
 
Posts: 3,112
Default conditional count in report

"00KobeBrian" wrote

By the way, how come the absolute value function works on this?


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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:55 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.