Show subgroup totals in report footer with grand total

Hi.  My database is used to track asset information and associated maintenance.  I have created a multi-level report that groups by Equipment Type and then by AssetID and displays maintenance information for each specific assetID, including costs, for a date range entered by my users.  I have totaled on maintenance cost for each group level and have created a grand total over maintenance cost in my report footer but because this report could be so big, I also want to show the maintenance cost for each the Asset Type in the footer section of my report along with my grand total so all necessary totals are in the same place on the report at a glance..  How can I show my top level group totals in my report footer?   I attached a screen shot of my report in design mode.MaintCostSubTotals.pdf   You can see what I'm trying to do in the footer section of the report.  I've created text boxes with the sub-totals I'm trying to pull in from my top level groups.  thanks
LVL 7
valmaticAsked:
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.

PatHartmanCommented:
Create another report that just shows the totals.  Put it in the report footer as a subreport.
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
Jeffrey CoachmanMIS LiasonCommented:
If you don't have any filters, (and your recordsource is simple, ...etc) you may be able to use the Aggregate function: DSum() to pull the group totals into the report footer.
In your Report Footer textboxes, make the control sources something like this:
=DSum("MaintenanceCost","YourTable","EQType=1")
And
=DSum("MaintenanceCost","YourTable","EQType=2")
0
Jeffrey CoachmanMIS LiasonCommented:
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

PatHartmanCommented:
Jeff,
That method requires that you code a separate control for each Type.  I personally don't like solutions like that because they require ongoing maintenance.  I always have to be called in if a new type is added whereas the subreport, while a little more work at the beginning, is self expanding.
Pat
0
Jeffrey CoachmanMIS LiasonCommented:
Pat
1. This is the way the OP presented it... (see the pdf screenshot)

2. In a lot of cases, hardcoding is OK because the "Types" may never, (or very rarely), change.
Ex: Blood types, US States, and until fairly recently, ..."Sex" only had two options (M or F)
;-)
In this specific case, all that would be required is a 1 minute addition of another control...
But, yes, ...if they had a flurry of new "Types", (or types were being constantly added, removed or renamed) then yes, a subreport would be best.
;-)
3. In a lot of cases, I will try to find a solution without a subreport.
Many new Access users will reach for a subreport when a simple report grouping will work as well (or better),
Ex: Customers and Orders
...Grouped reports require less resources and need no linking fields, or complicated references,

Like anything else, ...there is always more than one way to do something, and no single methodology will always be a good fit in every situation.

What I provide was simply another option...
;-)

Jeff
0
valmaticAuthor Commented:
Thanks Pat,  I read this someplace awhile back and should have remembered that.  sub-report worked great.  
Hi Jeff,  thanks for your input.  I did not see your comments until just now so am giving points to Pat.  Let me know if you guys disagree though and I'll push to split.  thanks much.
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.