By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,584 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Getting total counts of types in column, for example gender

P: 76
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!
Jul 16 '12 #1

✓ answered by twinnyfo

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

Expand|Select|Wrap|Line Numbers
  1. SELECT Sum(IIf([Gener]= "M",1,0)) AS Male, Sum(IIf([Gender] = "F",1,0)) AS Female
  2. FROM tblYourTable;
  3.  
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.

Share this Question
Share on Google+
13 Replies


zmbd
Expert Mod 5K+
P: 5,287
What have you tried so far?
Jul 16 '12 #2

Rabbit
Expert Mod 10K+
P: 12,316
Use an aggregate query grouping by the gender. If you really need the results in two different columns, use a cross tab.
Jul 16 '12 #3

zmbd
Expert Mod 5K+
P: 5,287
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
Jul 16 '12 #4

P: 76
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblMain.Gender, Count(tblMain.Gender) AS CountOfGender
  2. FROM tblMain
  3. GROUP BY tblMain.Gender;
  4.  
The report has a txtMale & txtFemale box.

Thanks!
Jul 18 '12 #5

zmbd
Expert Mod 5K+
P: 5,287
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.
Jul 18 '12 #6

P: 75
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,
Jul 18 '12 #7

zmbd
Expert Mod 5K+
P: 5,287
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
Jul 18 '12 #8

P: 76
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)
Jul 19 '12 #9

zmbd
Expert Mod 5K+
P: 5,287
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
Expand|Select|Wrap|Line Numbers
  1. =Format(Count([ChangeToYourRSFieldName]),"Fixed")
and so forth.

-z
Jul 19 '12 #10

P: 76
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.
Jul 23 '12 #11

Rabbit
Expert Mod 10K+
P: 12,316
It may be simpler if you just used unbound textboxes and set the control source to a DCount().
Jul 23 '12 #12

twinnyfo
Expert Mod 2.5K+
P: 3,063
If you wanted ultra simple, have a tiny subform linked tothe following query:

Expand|Select|Wrap|Line Numbers
  1. SELECT Sum(IIf([Gener]= "M",1,0)) AS Male, Sum(IIf([Gender] = "F",1,0)) AS Female
  2. FROM tblYourTable;
  3.  
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.
Jul 24 '12 #13

P: 76
Thanks, Twinnyfo!
That did it!!!
Jul 25 '12 #14

Post your reply

Sign in to post your reply or Sign up for a free account.