Count query to show 2 values

[spartansFC]

Hi

i have a list of 100 people in a query who have a start date (qry1), startdate is in the format dd/mm/yyyy.

I need to show the number people who start in a certain month, to get the month year, on a column on (qry1) i added:

monthdate: format([startdate],"mmm yy"

so this works fine, when i run (qry2) which is based off (Qry1) i can get it show the various months (April 2011 to March 2012) and how many people started.

I'm now trying to say, in April 2011, if there were 20 people who started, of those 20, who had a category of "CIN" for example.

the categories are all in (qry1).

So the query will hopefully eventually have 3 columns.

Month, People Count, Category Count
April 2011 20 3

Anyone have any ideas.

thanks

Mikie

 

[SkipVought]

Hi,

How about some sample source data and the expected results from this data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

What are the SQL code of your queries ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PWise]

add

sum(case when category  = 'CIN' then 1 else 0 end ) As CategoryCount

to your select statment

 

[PWise]

sorry that is sqlserver syntex
try
Category Count: Sum(iif(category = 'CIN',1,0))

 

[dhookom]

Or
CategoryCount: Sum(Abs(Category="CIN"))


Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Hi

thanks for all your responses, i'll try to answer them. Please see below a breakdown of the data.

the main data comes from qryOccupancyUnder2s, this has the data:

Nursery StartDate DOB Age Month strFundByMainDesc
Nursery1 26 Sept 11 14 Oct 10 0 Sept 11 CIN
Nursery2 12 Sept 11 21 Jul 10 1 Sept 11 Parent
Nursery3 8 Aug 11 30 Oct 10 0 Aug 11 CIN

i then created qryOccupancyUnder2sCount based off the above data

qryOccupancyUnder2sCount

MonthSort: DatePart("m",[StartDate]) - Group by
Month - Group by
ChildId - Count

i tried adding CategoryCount: Sum(Abs(Category="CIN"))
but i got an error message:

Cannot have aggregate function in GROUP BY clause (Sum(Abs(Category="CIN"))

this is what the SQL code looks like:

SELECT DatePart("m",[StartDate]) AS MonthSort, qryOccupancyUnder2s.Month, Count(qryOccupancyUnder2s.ChildId) AS CountOfChildId, Sum(Abs([strFundByMainDesc]="CIN")) AS CategoryCount
FROM qryOccupancyUnder2s
GROUP BY DatePart("m",[StartDate]), qryOccupancyUnder2s.Month, Sum(Abs([strFundByMainDesc]="CIN"));

anyone know why this isn't working, i know it's me

Mikie

 

[spartansFC]

Ooops, i figured it out, i used Duane's suggestion.

CategoryCount: Sum(Abs([strFundByMainDesc]="CIN"))

but didn't realise i had to change the total line to Expression.

it works..

thanks for everyone's help

Mikie

 

[spartansFC]

ok so with alot of help i've got the query running, i've even added totals based off 2 criteria:

CINCategoryover2s: Sum(Abs([strFundByMainDesc]="CIN" And [age]>=2 And [age]<3))

but now when i run the query, it displays only the months that have data in, which is what it's been told to do.

But i need to have it show months from April 2011 - March 2012 even when there is no data in those months.

I have read on various google searches using the Nz function but couldn't figure out how to use it in the query.

Mikie

 

[dhookom]

Nz() won't do you much good. You have to have a query/table with all months and use a JOIN type that includes all the records from the query/table with all months.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Ok, so i created a new table, tblMonths which has in

MonthID (PK)
MonthDesc (Apr 11, May 11, Jun 11.....)

I added this table to qryOccupancyNewStartersCINCount and joined it by Month (qryOccupancyNewStarters) in Left Table and MonthDesc (tblMonths) in Right Table.

I click on Include All records on the join properties but it still won't display "Apr 11"?

Not sure why

Mikie

 

[dhookom]

We can't see the SQL of qryOccupancyNewStartersCINCount.
Are you actually storing values in your table like "Apr 11, May 11, Jun 11....."?

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Hi Duane

here's the sql:

SELECT tblMonths.MonthID, tblMonths.MonthDesc, DatePart("m",[StartDate]) AS MonthSort, qryOccupancyNewStarters.Month, Count(qryOccupancyNewStarters.ChildId) AS CountOfChildId, Sum(Abs([age]<2)) AS Under2s, Sum(Abs([strFundByMainDesc]="CIN" And [age]<2)) AS CINCategoryunder2s, Sum(Abs([age]>=2 And [age]<3)) AS Over2s, Sum(Abs([strFundByMainDesc]="CIN" And [age]>=2 And [age]<3)) AS CINCategoryover2s, Sum(Abs([age]>=3 And [age]<=4)) AS 3and4s, Sum(Abs([strFundByMainDesc]="CIN" And [age]>=3 And [age]<=4)) AS CINCategory3and4s, qryOccupancyNewStarters.Child_nurserystatus, qryOccupancyNewStarters.Nursery, qryOccupancyNewStarters.NurseryId
FROM qryOccupancyNewStarters RIGHT JOIN tblMonths ON qryOccupancyNewStarters.Month = tblMonths.MonthDesc
WHERE (((qryOccupancyNewStarters.NurseryId)=4))
GROUP BY tblMonths.MonthID, tblMonths.MonthDesc, DatePart("m",[StartDate]), qryOccupancyNewStarters.Month, qryOccupancyNewStarters.Child_nurserystatus, qryOccupancyNewStarters.Nursery, qryOccupancyNewStarters.NurseryId, qryOccupancyNewStarters.Month
HAVING (((qryOccupancyNewStarters.Child_nurserystatus)=2))
ORDER BY tblMonths.MonthID;

the date is stored as a normal date field dd/mm/yyyy but on the query i'm using:

MonthSort: DatePart("m",[StartDate])

the above changes the date into "Apr 11, May 11...." so maybe thats where the problem is.


Mikie

 

[spartansFC]

ooops again, don't ask me how but i've managed it. i had to add another expression column as i think that's what was causing the problems, i originally had childnurserystatus_desc as a group on the query, but i changed it to:

ExistingPlaces: Sum(Abs([childnurserystatus_desc]="Existing Place"))

and it now displays all the months from April 2011 to March 2012

thanks for everyone help

Mikie

 

[dhookom]

You are removing all months without records with

WHERE (((qryOccupancyNewStarters.NurseryId)=4))
...
HAVING (((qryOccupancyNewStarters.Child_nurserystatus)=2))

Remove the HAVING clause because it is redundant and try:

WHERE qryOccupancyNewStarters.NurseryId = 4 or qryOccupancyNewStarters.NurseryId is Null

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Hi

I've got a new problem based on this new query method i've been taught.

within this query, there is a field that shows the number of sessions a child does in a month. I've tried:

Under2Sessions: Sum(Abs([sessions] And [age]<2))

but this only shows a count for the number of children under 2 in that calender month.

If i removed the

And [age]<2

it does show 500 sessions which is the correct total for all children but i need to just show the under 2s for example.

What am i missing or is there no way of doing a count/sum in this way in a query.

Mikie

 

[dhookom]

but this only shows a count for the number of children under 2 in that calender month.

but i need to just show the under 2s for example

It seems that you have what you want. I'm confused.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

I'm very confused......

Okay so my query is built like this:

nurserynameShort : Group by
strPlacementMonthYear : Group by
Sessions2: Sum(Abs([sessions])) : Expression

so your quite right Duane, the above expression does sum the total number of sessions per calender month.

I then need to know of that total which in April-11's case is 70, how many sessions for under 2's, i've copied the data to a spreadsheet so that i could filter it and try and count it manually to make sure the totals were matching what i had on the database, which they dont.

So, in April-11, there is a total of 70 sessions, the total sessions for under 2s is 10 (i got this number by using the spreadsheet), i thought that by using the expression below that it would show me this:

Under2s: Sum(Abs([sessions] And [age]<2))

but what the above does is count the number of children, it displays a total of 2 for April-11, which is partly true as there are only 2 children under 2 in April-11.

So i'm not sure what i'm doing wrong, this new query and using expression's is very confusing. Here's the sql:

SELECT qrySummaryOccupancyCurrentYear.nurseryShort, qrySummaryOccupancyCurrentYear.dteMonthYear, qrySummaryOccupancyCurrentYear.strPlacementMonthYear, Sum(Abs([sessions])) AS Sessions2, Sum(Abs([sessions] And [age]<2)) AS Under2s, Sum(Abs([strFundByMainDesc]="CIN" And [age]<2)) AS CINCategoryunder2s, Sum(Abs([age]>=2 And [age]<3)) AS Over2s, Sum(Abs([strFundByMainDesc]="CIN" And [age]>=2 And [age]<3)) AS CINCategoryover2s, Sum(Abs([age]>=3 And [age]<=4)) AS 3and4s, Sum(Abs([strFundByMainDesc]="CIN" And [age]>=3 And [age]<=4)) AS CINCategory3and4s
FROM qrySummaryOccupancyCurrentYear
WHERE (((qrySummaryOccupancyCurrentYear.childnurserystatus_desc) Like "Existing*"))
GROUP BY qrySummaryOccupancyCurrentYear.nurseryShort, qrySummaryOccupancyCurrentYear.dteMonthYear, qrySummaryOccupancyCurrentYear.strPlacementMonthYear
HAVING (((qrySummaryOccupancyCurrentYear.nurseryShort) Like "Dunstanb*"))
ORDER BY qrySummaryOccupancyCurrentYear.nurseryShort, qrySummaryOccupancyCurrentYear.dteMonthYear;

thanks for any help

Mikie

 

[dhookom]

I guess my issue is not knowing anything about your data. I don't know what the tables and fields are or how your are storing your values. What are you storing in the field Sessions?

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Hi

ok so i've been getting really confused by this new query and so i've started from scratch.

I've created a query which shows all the children across all nurseries with occupancy figures, that query is called:

qryOccupancyAllChildren

i've then based this new expression query on that query and built a smaller query to make sure things are working first.

Nursery: group by
strPlacementMonth: Group by
sessions: Sum
Under2s: Expression

or in SQL:

SELECT qryOccupancyAllChildren.Nursery, qryOccupancyAllChildren.dteMonthYear, qryOccupancyAllChildren.strPlacementMonthYear, Sum(qryOccupancyAllChildren.sessions) AS SumOfsessions, Sum(Abs([age]<2)) AS Under2s
FROM qryOccupancyAllChildren
GROUP BY qryOccupancyAllChildren.Nursery, qryOccupancyAllChildren.dteMonthYear, qryOccupancyAllChildren.strPlacementMonthYear
ORDER BY qryOccupancyAllChildren.Nursery, qryOccupancyAllChildren.dteMonthYear;

so this produces the following data:

Nursery dteMonthYear strPlacementMonthYear SumOfsessions Under2s
Nursery Ave 01/04/2011 Apr-11 452 17
Nursery Ave 01/05/2011 May-11 436 17
Nursery Ave 01/06/2011 June-11 448 18
Nursery Ave 01/07/2011 July-11 446 18
Nursery Ave 01/08/2011 Aug-11 425 20
Nursery Ave 01/09/2011 Sep-11 388 22
Nursery Ave 01/10/2011 Oct-11 382 22
Nursery Ave 01/11/2011 Nov-11 382 22
Nursery Ave 01/12/2011 Dec-11 382 22
Nursery Ave 01/01/2012 Jan-12 382 22
Nursery Ave 01/02/2012 Feb-12 382 22
Willow Ave 01/03/2012 Mar-12 382 22

so as you can see, it does sum up correctly the number of sessions per month, but then when i use an expression to count the under 2's, it's counting the actual children and not the sessions, for April-11, the under 2 session count should be 77.

i've used

Under2s: Sum(Abs([age]<2))

to count the number of sessions for under 2s

I've checked the field type of sessions and it is a number.

so can someone please help. Is there a better way to do a query like this, should i be basing one query on another. How would other people do this?

Mikie