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

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
LVL 7
valmaticAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

Hamed NasrRetired IT ProfessionalCommented:
Upload a sample database, and show the expected result.
0
Jeffrey CoachmanMIS LiasonCommented:
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
0
valmaticAuthor 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
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Jeffrey CoachmanMIS LiasonCommented:
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
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
valmaticAuthor 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?
0
Jeffrey CoachmanMIS LiasonCommented:
< 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
0
valmaticAuthor 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!!!"
0
Jeffrey CoachmanMIS LiasonCommented:
;-)
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.