summary table with counts

[jmj]

I've been asked to make a summary table of data.
It should show stats of students taking classes (m/f, coursetitle, immigrant, etc) It will take 4 tables-
the student table,
the stuclass table (what classes the student is taking)
the schoolclass table (what classes the school is offering)
and the courseRef table (the master reference table of courses)
I made a new table that has each courseID.
I now need to populate the fields using update queries.
When I run the update query I am getting the error about the subquery returning more than 1 value.
I feel like this should be easy to fix, maybe I'm just too tired with it the end of the day...
Here's my query:
UPDATE tbl_Summary
SET GenderM = (Select Count(Gender)
from Student S JOIN StuClass SC ON (S.Schoolnum=SC.Schoolnum
and S.stulink=SC.stulink)
Inner Join SchoolList SList on (SC.schoolnum=SList.schoolnum and
SC.classlink=SList.classlink) Inner Join
CourseREF cREF ON (SList.COURSE=cREF.Course) join tbl_Summary Report on SList.course=Report.Course
Where Gender =('M') and SList.course=Report.Course
Group By report.course, S.gender )

Thanks for any help.

 

[jiqjaq]

Due to your Group By, you're getting a count of male students for every course returned in the subquery.

You need to add a WHERE clause to the Update and link/repeat the fields in that clause to those in the subquery, so they're referencing the same course and gender.

You'd be better off creating the summary table via a SELECT INTO rather than an UPDATE to an existing table, if possible. That way you wouldn't need a subquery since you'd be able to do it all with Joins and no need to worry about returning a single value.

 

[jmj]

I thought about doing a select into type query, but I've got to split out the info- how many males are taking this course/how many females/etc. Some of the counts rely on in clauses also.
Because of how they want the data, I couldn't see doing it in one query.