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. - SELECT Count(*) AS TotalFemale, Count(*) AS TotalMale
-
FROM tblMissionaries
-
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?
13 11759
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.
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"
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 - SELECT REGION, GENDER, Count(*) as GenderCount
-
FROM tblMissionaries
-
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): - HAVING ([Retirement Date] Is Null)
-Stewart
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 - SELECT REGION, GENDER, Count(*) as GenderCount
-
FROM tblMissionaries
-
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): - 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.
Never mind - it worked. My error. Thanks for the help.
- Justin
Now I just need to query the list further to only include active missioanries. Here's where I'm stumpped. - SELECT tblMissionaries.REGION, tblMissionaries.GENDER, tblMissionaries.[Retired Date], Count(*) AS GenderCount
-
FROM tblMissionaries
-
GROUP BY tblMissionaries.REGION, tblMissionaries.GENDER, tblMissionaries.[Retired Date]
-
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.
NeoPa 32,556
Expert Mod 16PB
...
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): - 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 : - Matthew; 15
-
Matthew; 30
-
Matthew; 45
-
Matthew; 60
-
Bob; 5
-
Bob; 10
-
Bob; 3
-
Bob; 6
-
Bob; 4
Consider the following SQL : - SELECT [Student],
-
Sum([Mark]) AS TotMarks
-
-
WHERE [Mark]>30
-
-
GROUP BY [Student]
Results :
Matthew 105
Whereas for : - SELECT [Student],
-
Sum([Mark]) AS TotMarks
-
-
GROUP BY [Student]
-
-
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 :(
NeoPa 32,556
Expert Mod 16PB
Try : - SELECT [REGION],
-
[GENDER],
-
Count(*) AS GenderCount
-
-
FROM tblMissionaries
-
-
WHERE [Retired Date]<CDate('1 Jun ' & Year(Date()))
-
-
GROUP BY [REGION],
-
[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?
NeoPa,
Thanks - that works like I was trying to get it. Thanks for getting me over another hump. God bless.
NeoPa 32,556
Expert Mod 16PB
No worries - Pleased I could help :)
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, - SELECT REGION,
-
GENDER,
-
[First Name],
-
[Last Name],
-
Count(*) AS GenderCount,
-
DateDiff("yyyy",[BirthDay],Now())+Int(Format(Now(),"mmdd")<Format([Birthday],"mmdd")) AS Age,
-
Campus
-
-
FROM tblMissionaries
-
-
WHERE ([Retired Date]>CDate('1 Jun ' & Year(Date()))
-
-
GROUP BY REGION,
-
GENDER,
-
[First Name],
-
[Last Name],
-
DateDiff("yyyy",[BirthDay],Now())+Int(Format(Now(),"mmdd")<Format([Birthday],"mmdd")),
-
Campus,
-
Birthday
Thanks.
Justin
NeoPa 32,556
Expert Mod 16PB
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.
NeoPa 32,556
Expert Mod 16PB
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 : - 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). - 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?
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Orion |
last post by:
Hi, This is kind of last minute, I have a day and a half left to figure
this out. I'm working on a project using ms-sqlserver. We are
creating a ticket sales system, as part of the system, I...
|
by: P B via AccessMonster.com |
last post by:
I have a list of 160,000 records with these fields:
fname, lname, address, city, state, zip, dob
I need to generate a list with all fields where the first initial of lname
and the dob are...
|
by: Dave |
last post by:
Hello
I've created a student database for our program that I also need to
get statistics from. It's in Access 2003.
The statistics would be things like Male-Female percentage, percentage
of...
|
by: Tom |
last post by:
Hello,
I have a database of employee data in access and I am trying to create
a form with combo boxes for criteria (ex. gender, office, position,
etc.) that let the user select criteria from...
|
by: jaswmil |
last post by:
I have a query (see SQL below) that essentially grabs a list of all
employees terminated during a specific period. What I am needing to do
is to be able to take this number and then divide it by...
|
by: jpatchak |
last post by:
Hi Guys,
Sorry if this is a really stupid question. I am trying to upsize my Access database to SQL server. When I used the Access upsizing wizard, some of my queries didn't get upsized so I am...
|
by: sajithamol |
last post by:
Table structure:
Region: region_id, name
Employee: employee_id, name, region_id
Sales: sales_id, employee_id, sale_date, sale_amount -there’s an individual...
|
by: mlcampeau |
last post by:
Hey guys,
I have been mulling over this problem for a few days and have yet to come up with a query that will give me the expected results. I am working on a database that stores employee...
|
by: zachster17 |
last post by:
Hi everyone,
First of all, sorry for the massive amount of SQL I am about to type. I have a database that has a "lives" table of insured employees and then another table (that links to the lives...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |