Help with creating manual subtotals

[buffaloguy]

This query(below) is used to build a report that is grouped first by school type, schoolname, then budget_code. In the report I hide details. In the budget_code footer I use the sum function to total [current teachers] and [proposed teachers] by budget code. What I cannot seem to do is when the school type breaks - is put subtotals by budget code for both [current teachers] and [proposed teachers] in the type footer. I am assuming I will have to create counters but I am not sure on how to proceed.

The report structure is as follows:
type of school - Elementary
school 1
total for budget code 1 current teachers and proposed teachers
school 2 and so on...
Type of school - Middle
school 1
total for budget code 1 current teachers and proposed teachers
when the type breaks I want to subtotal by budget code for all the schools within that type. there are six budget codes. Thanks for any ideas. See link for image of report in design mode

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;

 

[dhookom]

I would create a totals query similar to your existing record source that groups by SchoolType and budget code while summing the current and proposed. Use this query as the record source of a subreport that you can place in the school type footer. Set the Link Master/Child properties to the SchoolType field.

Duane
Hook'D on Access
MS Access MVP

 

[buffaloguy]

Duane:

Thanks. That worked perfectly. I never considered that approach. Another item for the toolbox

Steve