I am seeking help with creating a query that will return the total number of classes, total number of classes broken down by age group, total number of participants for each class and total number of participants for each class broken down by age group. I seem to have everything except the total number of participants broken down by age group. I have it to where it returns the total number of children but it doesn't list the correct number of children for each class, it just lists the correct number of children for the first class and then repeats the same number for the rest of the classes.
I'm sure i'm going about the number of participants part all wrong. Please help me if you can.
Count(ClassName) AS [Total # of Classes],
Count(IIf(AgeOfParticipants=1,1,0)) AS [# for Children],
Count(IIf(AgeOfParticipants=2,1,0)) AS [# for Youth],
Count(IIf(AgeOfParticipants=3,1,0)) AS [# for Adults],
Sum(NumberOfParticipants) AS [Total # of Participants],
WHERE AgeofParticipants=1) AS [# of Children]
GROUP BY ClassName;
I want count for the first part (number of classes given for each category of participants-children, youth and adults) and then I want sum for the last part, the part that's in question-The total number of of participants that fall into each category (children, youth and adults).
The problem is that I need to return the sum of a field called NumberOfParticipants which is a number field. So for instance this field may contain 12 children in class A that took place 2 weeks ago in one record, 6 children in class B a week ago in the next record and 23 youth in class A a week ago in another record. I need this portion of the query to be able to return the fact that 35 children total have attended Class A and 6 children total have attended class B. I will need to run this report every 6 months.
, COUNT(*) AS [Total # of Classes]
, SUM(IIf(AgeOfParticipants=1,NumberOfParticipants,NULL)) AS [# for Children]
, SUM(IIf(AgeOfParticipants=2,NumberOfParticipants,NULL)) AS [# for Youth]
, SUM(IIf(AgeOfParticipants=3,NumberOfParticipants,NULL)) AS [# for Adults]
, SUM(NumberOfParticipants) AS [Total # of Participants]