1. SELECT interview.location, Count(interview.location) AS [Total # of events], Count(IIF(interview.AgeOfParticipants=1,1,NULL)) AS [# for Youth], Count(IIF(interview.AgeofParticipants=2,1,NULL)) AS [# for Adults], SUM(interview.NumberOfkiosks), SUM(interview.NumberOfParticipants),
2. (SELECT SUM(NumberofParticipants)
GROUP BY location),
GROUP BY location)
3. FROM interview
GROUP BY interview.location;
Hello, I am trying to create a query that will give me the total number of events, total number of events held for youth, total number of events held for adults, total number of participants at all events, total number of youth interviewed, total number of adults interviewed and the total number of kiosks used. I want all of this info generated for each location all displayed in a single report based on this query. I have everything in the query working except for the total number of youth and adults interviewed. Section 2 above is what I was trying to use to get these values but it returns the message "At most one record can be returned by this subquery." If I remove the "GROUP BY location" at the end of section 2 I get a return but it returns the total number of participants for all locations that meet the criteria, added together and then that total is listed for each location instead of the appropriate numbers listed for the appropriate locations. In other words the query should list 12 participants for location 4, 12 for location 5 and 46 for location 7 but instead it simply lists 70 participants for all locations.
Any idea how I can get the correct output of the number of youth and number of adults that were interviewed at each location in this query?