Summing unbound controls at several group levels, and grand total

[Guest]

I have seen several posts very near to my problem, which is that I am
calculating a value in a group footer, that is based on the sum of records
for a case#. Once this calculation is performed, I would like to summarize
this at the next group level up (Office) but, not based on the sum, rather
the calculated value (which is the rounded up, weeks in process, calculated
for each case) which would end up being a greater value than performing the
calculation at the office level.

I would then like to generate a grand total of the indivudual office totals
for this calculated number.

Must I use subreports? or can this be done in a normal report?

Thanks.

 

[Larry Linson]

Summing calculated fields can either be done by using a Sum function and
specifying the calculation, rather than the controls containing the
calculated field; or calculating in the Query and summing that field in the
Footer; or it can be done in VBA code in a Report event. You need to be
_very_ careful trying to total in code, because some Report events fire more
than once for each record, and may fire many times if you are viewing in
Preview and paging forward and back.

Larry Linson
Microsoft Access MVP

 

[Guest]

Larry,

What I am trying to do is sum a value (elapsed days) from multiple records,
grouped by a field (Case#), and convert that sum to rounded up weeks, again
for that particular Case#. That is working fine. Next I would like to sum
all of the rounded up weeks, for multiple case#s, which are grouped by
Office. This is where I am getting stuck.

My best solution so far, is to export the rounded up weeks per case in a CSV
file, and then report on that file, by office to generate the next summary
level. It would be nice to do this in one report.

Thanks,
Chris