add like column totals in 3 separate subreports for grand total on main form

valmatic
valmatic used Ask the Experts™
on
Hi.  I have a main report with 3 separate sub-reports.  Each subreport consists of separate data but has the same columns headers in each.  I am calculating a grand total in the report footer of each of my subreports but I also need to calculate a grand total by adding subreport grand totals and would like to add this grand total to my main report page footer.  I'm having a hard time trying to figure this out.  

Would it make sense to create a 4th subreport that carried my grand totals only?  I'm having trouble putting that logic together.  Is there a better way?  I seem to recall reading that there is some limitation to linking totals from subreport to main report in access.  I'm attaching a screen shot of my report as an example of what I'm trying to accomplish.
Trip-GrandTotal.pdf
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hamed NasrRetired IT Professional

Commented:
Upload a sample database, and show the expected result.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
valmatic,

I am not quite sure that subreports are a good idea here.
1. If you ever add a new "cost listing" , you must add in a new subreport and change all the totals to include the new cost listing.
2. The same would be true if a cost listing as changed or deleted.
This generates too much work that must be done manually each time a cost listing changes.

This may all be made easire if you used a "Grouped" Report.
Then no manual editing is needed, and the Grand totals will appear automatically.

Here also, with a grouped report, you can set the page to break after each grouping (Cost Listing)

See the attached sample

JeffCoachman
Access-EEQ28227498GroupedReport-.mdb

Author

Commented:
Hi. Sorry, I'm a little late responding..

I've included my database.  My switchboard includes a report call for this report.  It's the only report in the list.  Use date range of August 1 to August 31 2013 for report run.

I did play around with report grouping at first but didn't think it would work. I'm spending a little more time with it today though and maybe I can make it work with some tweaking of my existing database.  If I want to stick with my original plan though, is it possible to sum columns over multiple subreports and plot the grand totals on the parent report?
TestDB2.mdb
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

MIS Liason
Most Valuable Expert 2012
Commented:
OK, ...
To Sum "Air Transprot", for example, add a textbox control to the bottom of the detail section, and make this the controlsource:
=NZ([subrptEmplyeeTripDetail_Crosstab].[Report]![ttlAirTransport])+NZ([subrptEmployeeTrip_TradeYes_Crosstab].[Report]![ttlAirTransport])+NZ([subrptTravelRecordTradeView_Crosstab].[Report]![ttlAirTransport])

Do this for the other columns as well

Author

Commented:
Jeff,  you're brilliant...  pull the data from the initial crosstab-queries instead..  My brain just wouldn't connect those dots...  

The NZ means if it runs into a null value then ignore it?  I'm guessing since you're not specifying 0 as a value if null?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
< pull the data from the initial crosstab-queries instead..>
Well I am actually pulling the values from the subreport control...
ex: NZ([subrptEmplyeeTripDetail_Crosstab].[Report]![ttlAirTransport])

<The NZ means if it runs into a null value then ignore it?  I'm guessing since you're not specifying 0 as a value if null? >
...Good question!
;-)

The function NZ(), actually means "Convert (N)ulls to (Z)ero", so there is no need to specify "0".
So it does not "ignore" nulls, ...it literally will convert them to zeros.
So while it might be good practice to provide the zero, it is presumed if no second argument is provided.

;-)

JeffCoachman

Author

Commented:
My mistake.  geez that's too easy.   Thanks for the note on the NZ function too.  Good to know.

As always Jeff, "You Rock!!!"
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
;-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial