This query is used to produce a report that is grouped by school type, then by school name. It sums teacher assignment hours([current teachers] and [proposed teachers] by budget code for each individual school.
In the footer of each school type I want to place the sum of current teachers and proposed teachers for each of the budget codes for the individual schools in that type. Can't seem to find a way to do that. There are six total codes. Thanks for any insight.
SELECT gradelevels.Budget_code, Sum(IIf([trYear]=forms!select_dates!current_year,[TeacherFTE],0)) AS [Current Teachers], Sum(IIf([trYear]=forms!select_dates!proposed_year,[TeacherFTE],0)) AS [Proposed Teachers], ([Proposed Teachers]-[Current Teachers]) AS [+/- Teachers], Teacherresources.trschool_id, schools.schoollName, schools.Type
FROM schools INNER JOIN (gradelevels RIGHT JOIN Teacherresources ON gradelevels.grade_subjectID = Teacherresources.grade_subjectid) ON schools.school_id = Teacherresources.trschool_id
WHERE (((schools.open_date)<="forms]![select_dates]![current_year]") AND ((schools.close_date)>=[forms]![select_dates]![proposed_year]))
GROUP BY gradelevels.Budget_code, Teacherresources.trschool_id, schools.schoollName, schools.Type
ORDER BY schools.schoollName;