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

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