Counting in reports

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I have a table that has employees Gender, age group and
other data. I am trying to build a report that lets me
count the number of Females and Males acording to their
organization. =count([gender]) gives me the total
population. Is there a formula that lets me perform a
countif formula similar to that found in excel?
 
Bob,
Count all in the table, or count all that are in the report?
What datatype is the Gender field?
Text? Yes/No?
It all makes a difference.

All records in the table:
=DCount("*","TableName","[Gender] = 'Male'")
=DCount("*","TableName","[Gender] = 'Female'")

In the report (which may or may not contain all the table records).
In the Report Footer ...
=Sum(IIf([Gender] = "Male",1,0))
=Sum(IIf([Gender] = "Female",1,0))

Since one is either Male or Female, I would use a Yes/No check box.
Check = Yes for Male (or Female), value is -1
Check = No for the opposite gender, value is 0
=DCount("*","TableName","[Gender] = -1")
or
=Sum(IIf([Gender] = -1,1,0))
use = 0 for the opposite gender.
 
Thanks for the reply Fred. I was a bit vague. I am
importing the data from excel into an access table. The
field name is "Gender" with "Male" or "Female" results.
Let me play around with the great info you gave me and
I'll let you know what happens. :-)


-----Original Message-----
Bob,
Count all in the table, or count all that are in the report?
What datatype is the Gender field?
Text? Yes/No?
It all makes a difference.

All records in the table:
=DCount("*","TableName","[Gender] = 'Male'")
=DCount("*","TableName","[Gender] = 'Female'")

In the report (which may or may not contain all the table records).
In the Report Footer ...
=Sum(IIf([Gender] = "Male",1,0))
=Sum(IIf([Gender] = "Female",1,0))

Since one is either Male or Female, I would use a Yes/No check box.
Check = Yes for Male (or Female), value is -1
Check = No for the opposite gender, value is 0
=DCount("*","TableName","[Gender] = -1")
or
=Sum(IIf([Gender] = -1,1,0))
use = 0 for the opposite gender.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Bob said:
I have a table that has employees Gender, age group and
other data. I am trying to build a report that lets me
count the number of Females and Males acording to their
organization. =count([gender]) gives me the total
population. Is there a formula that lets me perform a
countif formula similar to that found in excel?
Click to expand...


.
Click to expand...
 
Nope. That is just giving me the total count of Male /
Female. Here is what I am trying to make:

Orgs Female Male <21 21 - 30 31 - 40
Org A # # # # #
Org B # # # # #
Org C # # # # #
Org D # # # # #

All of the data is from the same table. TheMale/Female
Field is text and age is just that, the persons age.
I think you are on the right track with DCOUNT but is
there a way to make that like an array formula? something
like {SUM((Gender="Female")*(Org="Org A"))}

-----Original Message-----
Thanks for the reply Fred. I was a bit vague. I am
importing the data from excel into an access table. The
field name is "Gender" with "Male" or "Female" results.
Let me play around with the great info you gave me and
I'll let you know what happens. :-)


-----Original Message-----
Bob,
Count all in the table, or count all that are in the report?
What datatype is the Gender field?
Text? Yes/No?
It all makes a difference.

All records in the table:
=DCount("*","TableName","[Gender] = 'Male'")
=DCount("*","TableName","[Gender] = 'Female'")

In the report (which may or may not contain all the
Click to expand...
table
records).
In the Report Footer ...
=Sum(IIf([Gender] = "Male",1,0))
=Sum(IIf([Gender] = "Female",1,0))

Since one is either Male or Female, I would use a Yes/No check box.
Check = Yes for Male (or Female), value is -1
Check = No for the opposite gender, value is 0
=DCount("*","TableName","[Gender] = -1")
or
=Sum(IIf([Gender] = -1,1,0))
use = 0 for the opposite gender.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Bob said:
I have a table that has employees Gender, age group and
other data. I am trying to build a report that lets me
count the number of Females and Males acording to their
organization. =count([gender]) gives me the total
population. Is there a formula that lets me perform a
countif formula similar to that found in excel?
Click to expand...


.
Click to expand...
.
Click to expand...
 
Back
Top