Report sub total problem

[SBovino]

This query is used to build a report that is grouped first by 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.

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.

See attached 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;

 

[PaulO]

You should be able to copy and use the same Total fields in the TYPE footer

ps: rather than SUM([Field]) you should be using the expression COUNT([Field]) as these appear to be non-numeric fields you are summing? Maybe this is part of your problem?

 

[SBovino]

Thanks for the idea. Actually the item I am summing is a numeric.

Steve