Report grouping and subtotals

Dear experts -
I have ca. 16 complex totals that display in a group footer - these (I learned the hard way) are not calculated 'on-the-fly', but each is the sum of a field calculated already in the SQL record source.
NOW - I have a subgrouping, before we get to the major grouping. The totals appear JUST FINE in these subgroupings.
For example, let's say the major group is by person, and we have Joe, Mary, Katie.
Within each person, we have a job type: type 1, 2, 3. (It is a finite list of just 3).
I would like to display the subtotals for job TOGETHER, in the group footer.
Is there some way to do this that does not require me to create ANOTHER 48 FIELDS in the record source (!) - can I do something like have the subtotal on the report, but make it invisible and then display that value in the group footer?
Thanks for your counsel!
terpsichoreAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Is there some way to do this that does not require me to create ANOTHER 48 FIELDS in the record source (!) - can I do something like have the subtotal on the report, but make it invisible and then display that value in the group footer?>>

 Yes, you can do that, but you'd have to do this totalling on your own with code in the report.

 In the reports module, you'd declare variables to hold the totals:

Dim curDollarTotal1 as Currency

 Then in the appropriate header (ie. People) OnFormt event, set it to 0:

 curDollarTotal1 = 0

 Then in the detail's or group footer's OnFormat event, add to it:

  If FormatCount = 1 then
     curDollarTotal1 = curDollarTotal + <some control>
  End If

 and last, push the total into a control in the footer's OnFormat event:

   Me![<some control>] = curDollarTotal1

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
terpsichoreAuthor Commented:
perfectly stated, works beautifully. thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.