Getting total counts of types in column, for example gender

[artemetis]

I have a table (tblMain) with field Gender. I am trying to get a count in SQL of Female and Male personnel so I can update a form containing two fields "Male = and Female = ".

The values stored in the tbl are "M and F".

Thanks!

[zmbd]

What have you tried so far?

[Rabbit]

Use an aggregate query grouping by the gender. If you really need the results in two different columns, use a cross tab.

[zmbd]

ahh, rabbit... I was going to suggest that... just wanted to see what they had tried so far :)

(although I have a warm fuzzy that I was on the right track)

:)

-z

[artemetis]

I have tried the aggregate query.

This query gives me the correct counts, but not sure how to call the count number in the report field:

SELECT tblMain.Gender, Count(tblMain.Gender) AS CountOfGender
FROM tblMain
GROUP BY tblMain.Gender;
 

The report has a txtMale & txtFemale box.

Thanks!

[zmbd]

Are you adding the counts to a new report or an exsisting one?

* We should probabily split at this point given the question in OP appears to be answered.

[lyodmichael]

hmm, do you try to seperate the 2 gender for two query? exp: select tblmain.gender count(tblmain.gender) as ... where gender = "male,m,Male" . because when you use group, i think you can show the 2 fields .

its just my advice,

[zmbd]

lyodmichael
I wasn't very clear... if artemetis is creating a new report then simply using the query as the record source will be fairly easy to do by simply assigning the control source of the text fields to the correct query fields in the detail section of the report. The wizard actually doesn't do a bad job of creating a very simple report.

However, if artemetis is trying to add this information to an existing report then there are few hoops to jump thru to get the counts.

*This is why I have asked the thread to be re-posted as a new question as this is a seperate issue from the OP

-z

[artemetis]

Thanks folks.

ZMBD - I'm adding this to an existin report, which contains sub-reports.

Funny thing, when creating a "New Report" when using the SQL as a datasource, I'm able to get a report showing
F - 32
M - 40

As soon as I add a subform, the subform gets displayed twice with a genderCount as a header.

(feel free to split this thread as necessary)

[zmbd]

If you added the subform to the details section of the main form (or report) then yes, the subform will repeat for each of the grouped entries, in this case "m" and "f" so you get two... if you had "m", "f", and "h" you'd get three and so on.

If you need to get a count of the M and F to fall in the footer or header of the report there are several different ways to do that depending on how the report is bound to the record source and how it is layed out.. one way is to place a textbox in the header/footer of the form and set the control source of the text box so that you have something along the lines of

=Format(Count([ChangeToYourRSFieldName]),"Fixed")

and so forth.

-z

[artemetis]

My main report is bound to tblMain.
This tbl has empFirstName, empLastName, empGender

I have two text boxes in the header:
txtMaleCount and txtFemaleCount

I've tried several ways to get the counts into each text box, with no success. Zmbd's last respone^^^, second paragraph, is what I'm trying to do.

Thanks, in advance.

[Rabbit]

It may be simpler if you just used unbound textboxes and set the control source to a DCount().

[twinnyfo]

If you wanted ultra simple, have a tiny subform linked tothe following query:

SELECT Sum(IIf([Gener]= "M",1,0)) AS Male, Sum(IIf([Gender] = "F",1,0)) AS Female
FROM tblYourTable;
 

Then the values would be updated every time..... I find having an unbound control that executes a DCount() often seems to really slow down the form.

[artemetis]

Thanks, Twinnyfo!
That did it!!!