Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access help in setting footer subtotals in a report

Posted on 2013-01-27
7
Medium Priority
?
164 Views
Last Modified: 2013-01-28
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;

Open in new window

0
Comment
Question by:Sbovino
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 58
ID: 38825079
First, your SQL statement has an error in it:

 <="forms]![select_dates]![current_year]")

  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)<=forms]![select_dates]![current_year])

<< 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.
0
 

Author Comment

by:Sbovino
ID: 38825096
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
0
 

Author Comment

by:Sbovino
ID: 38825144
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 16

Expert Comment

by:kmslogic
ID: 38825455
Just an addition to jdettman's comment about the bug in the query:

The reason "forms]![select_dates]![current_year]" is appearing in quotes is because the square bracket after forms should not be there.  It should read:

forms![select_dates]![current_year] 

Open in new window

not

forms]![select_dates]![current_year]

Open in new window

0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 38826690
<<  The budget footer currently is summing totals for each budget code under each school.  I want to total each budget amount for school type.>>

 OK, that makes more sense now.  Still it is the same overall technique though:

1. Have a set of variables or an array to hold the totals.

2. Initialize those variables at the start of the report in the report header.  If you don't have a report header section currently, add it but set it's height to zero.  That way your code gets execeuted.

3. In some section, be it detail, a group footer, page header/footer or whatever (whenever the value you want is available),  grab the value and add it to the total.  You *must* do it as:

   If formatCount = 1 then
      ' Add to totals
   End If

  as the report engine often retreats up the page to re-format it and will call the OnFormat event multiple times for a given section.

  It also must be the OnFormat event.   If you happen to force 2 pass printing mode by referencing the pages property in the report, totals will be incorrect on the first pass if code is placed in the OnPrint events because OnPrint is not fired on the first pass.

  This is also the reason you init the variables in the Report Header rather then the OnOpen.  A report only opens once, but the report header section always occurs with a two pass print.

3. Place the values into unbound controls when you need them using the OnFormat event.   In this case it would be the school type footer.

Jim.
0
 
LVL 58
ID: 38826701
<<  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.
0
 

Author Closing Comment

by:Sbovino
ID: 38827884
Thank you for the assistance.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question