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

Query to Count Gender of Active Employees by region

P: 20
I'm using Access 2007 with Vista. I'm trying to figure out how to write a query to take information in the fields GENDER, RETIREMENT DATE, and REGION to get a count of the number of missionaries that are active (not retired) by each region.

This is what I haev so far, but it returns a zero value for each gender. If I take out the male - it works.
Expand|Select|Wrap|Line Numbers
  1. SELECT Count(*) AS TotalFemale, Count(*) AS TotalMale
  2. FROM tblMissionaries
  3. WHERE (((tblMissionaries.Gender)="Female") AND ((tblMissionaries.Gender)="Male"));
How do I get this to actually count the number of each male and female missionaries who are active ( the retirement date is not within 6 months of the date the query is ran) in each region?
Aug 13 '08 #1
Share this Question
Share on Google+
13 Replies


P: 50
This should work a lot better than what you are trying:
Expand|Select|Wrap|Line Numbers
  1. SELECT (SELECT COUNT(*) FROM tblMissionaries WHERE tblMissionaries.Gender="Female")  AS TotalFemale,
  2. (SELECT COUNT(*) FROM tblMissionaries WHERE tblMissionaries.Gender="Male")  AS TotalMale
You are creating two subqueries that are getting the data and then rolling it up to the final answer.
Aug 13 '08 #2

P: 20
This should work a lot better than what you are trying:

SELECT (SELECT COUNT(*) FROM tblMissionaries WHERE tblMissionaries.Gender="Female") AS TotalFemale,
(SELECT COUNT(*) FROM tblMissionaries WHERE tblMissionaries.Gender="Male") AS TotalMale

You are creating two subqueries that are getting the data and then rolling it up to the final answer.
I get an error message "Query input must contain at least one table or query"
Aug 15 '08 #3

Expert Mod 2.5K+
P: 2,545
Hi. You would need to include the other parts of the SQL statement - the FROM clause and so on as well - which the previous poster may have assumed would be included.

However, there is an easier way to total the males and females - from the query editor select View, Totals to turn on grouping and totalling, then group your data by gender and add a computed field to get the total number for each gender.

This is along the lines of

Expand|Select|Wrap|Line Numbers
  1. SELECT REGION, GENDER, Count(*) as GenderCount 
  2. FROM tblMissionaries
  3. GROUP BY REGION, GENDER;
Note that in your first post you were trying a WHERE clause which would only work if the GENDER value was male and female at the same time - an impossibility. Hence why it only worked when you removed one of the two values.

Assuming the retirement date for non-retired missionaries is null you can restrict the query to return only the gender for non-retired persons using the following HAVING clause (which is much the same as a WHERE but for some reason group-by queries use a different syntax):

Expand|Select|Wrap|Line Numbers
  1. HAVING ([Retirement Date] Is Null)
-Stewart
Aug 15 '08 #4

P: 20
Hi. You would need to include the other parts of the SQL statement - the FROM clause and so on as well - which the previous poster may have assumed would be included.

However, there is an easier way to total the males and females - from the query editor select View, Totals to turn on grouping and totalling, then group your data by gender and add a computed field to get the total number for each gender.

This is along the lines of

Expand|Select|Wrap|Line Numbers
  1. SELECT REGION, GENDER, Count(*) as GenderCount 
  2. FROM tblMissionaries
  3. GROUP BY REGION, GENDER;
Note that in your first post you were trying a WHERE clause which would only work if the GENDER value was male and female at the same time - an impossibility. Hence why it only worked when you removed one of the two values.

Assuming the retirement date for non-retired missionaries is null you can restrict the query to return only the gender for non-retired persons using the following HAVING clause (which is much the same as a WHERE but for some reason group-by queries use a different syntax):

Expand|Select|Wrap|Line Numbers
  1. HAVING ([Retirement Date] Is Null)
-Stewart
Thanks, that ogt me further along. It still didn't group them by Region. The Field is there, but they are blank.
Aug 15 '08 #5

P: 20
Never mind - it worked. My error. Thanks for the help.

- Justin
Aug 15 '08 #6

P: 20
Now I just need to query the list further to only include active missioanries. Here's where I'm stumpped.
Expand|Select|Wrap|Line Numbers
  1. SELECT tblMissionaries.REGION, tblMissionaries.GENDER, tblMissionaries.[Retired Date], Count(*) AS GenderCount
  2. FROM tblMissionaries
  3. GROUP BY tblMissionaries.REGION, tblMissionaries.GENDER, tblMissionaries.[Retired Date]
  4. HAVING (((tblMissionaries.[Retired Date])<#6/1/2008#));
But I don't want to have to go into the database each year and change the 2008 to the next year. Our fiscal year starts June 1. So I need to include every missionary who does not have a retired date of May 31st of the current year.
Aug 15 '08 #7

NeoPa
Expert Mod 15k+
P: 31,494
...
Assuming the retirement date for non-retired missionaries is null you can restrict the query to return only the gender for non-retired persons using the following HAVING clause (which is much the same as a WHERE but for some reason group-by queries use a different syntax):

Expand|Select|Wrap|Line Numbers
  1. HAVING ([Retirement Date] Is Null)
-Stewart
WHERE and HAVING clauses are both valid and can be used in a GROUP BY query. Access is lazy in that it always defaults to applying criteria as a HAVING clause. This is changable though (not the default - just the value for a particular field).

I look at the difference between WHERE and HAVING as being that WHERE is applied before the GROUPing is done, whereas HAVING is applied to the dataset AFTER the GROUPing is done.

GROUP BY fields can use either with little or no effective difference, but other checks are applied before (WHERE) or after (HAVING) the GROUPing.

The data to consider :
Expand|Select|Wrap|Line Numbers
  1. Matthew; 15
  2. Matthew; 30
  3. Matthew; 45
  4. Matthew; 60
  5. Bob;      5
  6. Bob;     10
  7. Bob;      3
  8. Bob;      6
  9. Bob;      4
Consider the following SQL :
Expand|Select|Wrap|Line Numbers
  1. SELECT [Student],
  2.        Sum([Mark]) AS TotMarks
  3.  
  4. WHERE [Mark]>30
  5.  
  6. GROUP BY [Student]
Results :
Matthew 105
Whereas for :
Expand|Select|Wrap|Line Numbers
  1. SELECT [Student],
  2.        Sum([Mark]) AS TotMarks
  3.  
  4. GROUP BY [Student]
  5.  
  6. HAVING Sum([Mark])>30
Results :
Matthew 150

Notice the difference between what is compared in the WHERE and HAVING clauses ([Mark] & Sum([Mark])).

NB. Poor old Bob simply failed all his exams and didn't register on either report :(
Aug 15 '08 #8

NeoPa
Expert Mod 15k+
P: 31,494
Try :
Expand|Select|Wrap|Line Numbers
  1. SELECT [REGION],
  2.        [GENDER],
  3.        Count(*) AS GenderCount
  4.  
  5. FROM tblMissionaries
  6.  
  7. WHERE [Retired Date]<CDate('1 Jun ' & Year(Date()))
  8.  
  9. GROUP BY [REGION],
  10.          [GENDER]
PS. GROUPing by [Retired Date] makes no sense. A count would produce a number of 1s. Without GROUPing by it, you cannot SELECT it directly. It would have to be within an aggregate function (like Sum() or Max() or such like). Does this make sense?
Aug 15 '08 #9

P: 20
NeoPa,

Thanks - that works like I was trying to get it. Thanks for getting me over another hump. God bless.
Aug 16 '08 #10

NeoPa
Expert Mod 15k+
P: 31,494
No worries - Pleased I could help :)
Aug 16 '08 #11

P: 20
Well, my bad. I didn't further check the query by adding more information into the my tables. Now that I have the information in the tables I see that my query brings back a count of 1 in each GenderCount field. However, what I'm looking for is to use my tables to query a total number of male and a total number of females. He waht I have so far,
Expand|Select|Wrap|Line Numbers
  1. SELECT REGION,
  2.        GENDER,
  3.        [First Name],
  4.        [Last Name],
  5.        Count(*) AS GenderCount,
  6.        DateDiff("yyyy",[BirthDay],Now())+Int(Format(Now(),"mmdd")<Format([Birthday],"mmdd")) AS Age,
  7.        Campus
  8.  
  9. FROM tblMissionaries
  10.  
  11. WHERE ([Retired Date]>CDate('1 Jun ' & Year(Date()))
  12.  
  13. GROUP BY REGION,
  14.          GENDER,
  15.          [First Name],
  16.          [Last Name],
  17.          DateDiff("yyyy",[BirthDay],Now())+Int(Format(Now(),"mmdd")<Format([Birthday],"mmdd")),
  18.          Campus,
  19.          Birthday
Thanks.

Justin
Aug 24 '08 #12

NeoPa
Expert Mod 15k+
P: 31,494
Justin,

I've had to add the [ CODE ] tags again to your post. This is mandatory, and I can only assume that your difficulty is knowing how to. Here's a general run down.
  • When posting, select all the text in your post that needs to be treated as code.
  • Click on the button that looks like a hash (#) just to the right of the {Insert Email Link} button (They all have ToolTips text associated with them to show what they do).

Now I can start to look at your problem.
Aug 25 '08 #13

NeoPa
Expert Mod 15k+
P: 31,494
Right. Having had a better look at your SQL it seems to me that you have missed something about GROUP BY queries.

Any field displayed in a GROUP BY query has to be one of two things :
  1. A value from a record, if the value is something that is common across the group.
    Typically this will either be one of the fields included in the GROUP BY clause itself, or a static expression (the value 1 for instance).
  2. An aggregated result. This is often a simple aggregate function (Sum(); Count(); Min(); Max(); First(); Last(); etc), but can also be an expression including aggregated results.
Every field included in the GROUP BY clause, further restricts the group of records that is aggregated across. In this case, including a [Birthday] field in the GROUP BY clause stops it from counting all records for a single gender, because you're not using [GENDER] as THE field in the GROUP BY clause.

If you think about it, asking for a [Birthday] field to display at the same level (output record) as all the males or all the females really doesn't make sense.

I can't suggest a solution for your dilemma, as there is none. Actually having the dilemma in the first place is the problem that you need to look at resolving.

Does that help?
Aug 25 '08 #14

Post your reply

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