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.
Who is Participating?
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
OK, ...
To Sum "Air Transprot", for example, add a textbox control to the bottom of the detail section, and make this the controlsource:

Do this for the other columns as well
Upload a sample database, and show the expected result.
Jeffrey CoachmanMIS LiasonCommented:

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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


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!!!"
Jeffrey CoachmanMIS LiasonCommented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.