Count Gender on report

[ranebow]

I have a table with a field called "GENDER". This can only have "F" or "M" in it. The table also has different class names in it. I've created a report grouped by class. Now I want to put the % of Female or Male students beside each class. PLEASE HELP!

 

[GingerR]

pretend your table name is Table. You have to have a unique ID in the table, mine is called ID.
in your report, you can put these things into three text boxes:

Count of all people in this class:
=DCount("ID","Table","Class = " & [Class])

Count of all Males in this class:
=DCount("ID","Table","Class = " & [Class] & " and Gender = 'M'&quot

Count of all Females in this class:
=DCount("ID","Table","Class = " & [Class] & " and Gender = 'F'&quot

now you can call each text box something, ie. txtTotal, txtMale, txtFemale. then make new text boxes txtMalePercentage (=txtMale/txtTotal) and txtFemalePercentage (=txtFemale/txtTotal).

or you can just make the two final text boxes and throw those formulas together: {the string that figures out the number of males}/{the string that figures out the total number in the class}) but that can get kinda messy.

you can make the first three fields invisible and stacked on top of each other if you want to, to get them out of your way.

hope this helps--g

 

[ranebow]

I've put this code on my Class/PROGID footer line on my report:

=DCount("key","participant_profile","progID = " & [progid] & " and Gender = 'M'&quot

For some reason it's counting all records, basically ignoring the AND GENDER = 'M' portion of the code.

 

[GingerR]

hmmmmm...funky.
is progid a number or text field?
so you're sure for whatever class you're looking at, there are both male and female people?
i suppose then it didnt work for Females either?
if you hardcode in a progid (..."progid = 6 and gender = 'M'&quot does it work?

 

[danvlas]

In the report footer, create two unbound boxes:

txtMales = Sum(IIf(Gender = 'M', 1, 0)
txtFemales = Count(*) - txtMales

Or the other way round:

txtFemales = Sum(IIf(Gender = 'F', 1, 0)
txtMales = Count(*) - txtFemales


Good luck



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[CosmoKramer]

Here's one way to get the percentages:

In the CLASS footer add an invisible text box (say txtClassCount) that will contain the count of students in the class:

=Count(*)

Add a group footer for GENDER. In that footer place two text boxes. One bound text box for GENDER, the other one should have this as the control source:

=Count(*)/[txtClassCount]

Set the Format for this text box to Percent.
This will display the GENDER percentages for each class.

Let me know if this helps.....