Sbovino
asked on
Access help in setting footer subtotals in a report
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.
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;
ASKER
Thanks - I will take a look at your solution. BTW - the SQL came right from MS ACCESS query SQL view. The query works as designed.
Steve
Steve
ASKER
Jim:
I don't think I have explained too well what I am trying to doing. Attached is a copy of the report in design mode which may make it clearer. I am hiding details. The budget footer currently is summing totals for each budget code under each school. I want to total each budget amount for school type. I get the gist of your suggestion. I can see where I am calculating a total. Not sure on the me.control statement.
The report is structured as follows:
school 1
budget code 1 totals
and so on.
When they school type breaks I want to have a total for each code for the schools in that type
Thanks. Steve
budget-report.jpg
I don't think I have explained too well what I am trying to doing. Attached is a copy of the report in design mode which may make it clearer. I am hiding details. The budget footer currently is summing totals for each budget code under each school. I want to total each budget amount for school type. I get the gist of your suggestion. I can see where I am calculating a total. Not sure on the me.control statement.
The report is structured as follows:
school 1
budget code 1 totals
and so on.
When they school type breaks I want to have a total for each code for the schools in that type
Thanks. Steve
budget-report.jpg
Just an addition to jdettman's comment about the bug in the query:
The reason "forms]![select_dates]![cu rrent_year ]" is appearing in quotes is because the square bracket after forms should not be there. It should read:
The reason "forms]![select_dates]![cu
forms![select_dates]![current_year]
notforms]![select_dates]![current_year]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<< BTW - the SQL came right from MS ACCESS query SQL view. The query works as designed.>>
It's still wrong. As kmslogic indicated, you typed the expression at some point incorrectly. If Access can't figure something out, it automatically puts it in quotes assuming it is a literal value.
Syntax wise it is OK as far as SQL concerened, so you won't get an error, but that date criteria will never work right.
Jim.
It's still wrong. As kmslogic indicated, you typed the expression at some point incorrectly. If Access can't figure something out, it automatically puts it in quotes assuming it is a literal value.
Syntax wise it is OK as far as SQL concerened, so you won't get an error, but that date criteria will never work right.
Jim.
ASKER
Thank you for the assistance.
<="forms]![select_dates]![
You don't want quotes around that. Literally your telling it that the date must be <=
forms]![select.....
rather then the value in that control.
It should be:
WHERE (((schools.open_date)<=for
<< 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.>>
You'll need to do that totaling on your own.
1. In the report code module in the declaration section, do:
Dim curCodeTotals(6,2) as Currency
2. In the OnFormat even of the Report header, do:
Erase curCodeTotals
3. In the Schools Footer the OnFormat event do:
If FormatCount = 1 then
CurCodeTotals(<code>,1) = CurCodeTotals(<code>,1) + [Current Teachers]
CurCodeTotals(<code>,2) = CurCodeTotals(<code>,2) + [Proposed Teachers]
where <code> is the value for a specific budget code. Ie.
Select Case BudgetCode
Case "ABC"
intCode = 1
Case "EFG"
intCode = 2
...
Case Else
intCode = 6
End case
4. In the budget code footer, place the data in un-bound text controls:
Me.<some control> = CurCodeTotals(<code>,1)
Me.<some control> = CurCodeTotals(<code>,2)
and so on.
Seems though from what you asked your school/budget groups are reveresed.
Jim.