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

 

[dhookom]

This is only a guest because we still don't know how your actual data is stored. Try add a column like:

Sum(Abs([age]<2)*[Sessions]) AS Under2Sessions

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Hi Duane

Thanks for all your help, i don't know how it works myself but it does by using the

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

.... i just figured it out, if the original expression was

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

and that showed a count of 17 which was how many children, then the new expression says for each line "1x6,1x3,1x6.... etc" which are the number of sessions for each child, this is then totalled for each month to get the correct total.

How would i have shown you how the actual data is stored and how would you have done this, i'm curious?

Thanks again Duane, you're a genius

Mikie

 

[dhookom]

I would have expected something like:
tblName (one record per ....)
FieldA primary key autonumber
FieldB Student Number
FieldC Nursery Number
FieldD Number of times the student ...
FieldE Date of ....

Then some sample records.

Then some sample of what you want it to look like.

Us database guys like to understand what you are storing where and how tables are related. We are rarely concerned about the format of a date field. It might be important to know if the date values also store a time component but we don't care if it "appears" as long or short format.

Duane
Hook'D on Access
MS Access MVP