Microsoft Access - SubTotal on SubReport and Grand Total on Main Report

Posted on 2014-02-24
Last Modified: 2014-02-26
I am having difficulties presenting the grand total from the subreport amount fields on the main report. Currently, the subreport totals by customer is working fine but not the grand total amounts.

Please help!. I tried several ways but without success.

Attached is the access project file for your revision.

I will appreciate any help provided on this matter.


Question by:mmoralespr
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
LVL 85
ID: 39885505
Have you tried setting the value of RunningSum to "Yes" for Text43?

Also, when posting your database here, please be sure to Compact your database first. You posted a 30 MB file, and when I compacted it went down to approximately 950kb. Many users are still on low speed connections, and often they'll just pass by questions with large downloads.

Your database also contains linked tables, which means we cannot effectively troubleshoot problems and can really only provide best-guess suggestions. If you need further help, you'd be better off creating local tables from those links, scrubbing those table to remove any sensitive information, and then reports (AFTER compacting, of course).
LVL 37

Expert Comment

ID: 39887261
If you are trying to total what is in the subreports, you will probably need to add a new  subreport to the report footer that includes just the total line you want.

Author Comment

ID: 39887497

I tried putting on Tex43 the running sum using the Over Group and Over All option, but do not work on this case for some reason.

I converted the linked tables to local tables so you can be able to troubleshoot. I compressed the .accdb file to using .zip.

Attached you will find the file.

Thanks for your time and support.


Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.


Author Comment

ID: 39887749
Pat, I would prefer to not add more processing overhead to the report by adding one more subreport. Is there any other way to do it?
LVL 37

Expert Comment

ID: 39887873
I think you don't need to use a subreport at all.  change the main form query to select the detail fields and remove the group by.  You might want to include criteria to eliminate the customers without details.  Add a group by customer and force a page change if that makes sense.  Then you can just add totals in the report's footer section.

Author Comment

ID: 39887901
I understand what you mean but if I do that the customer and contact information will appear above the detail lines and I need the report to look like the Peachtree  aging reports where the customer information is in the left side of the detail lines.

I know is much less complicated eliminating the subreport and using groups, but I will use that method as last resource because it will change the format we are looking for.

Attached you will find an image of the report I am trying to do, this image is from a sample Aging Report of Peacthree. Using a subreport allowed me to put the customer information at the left side.
LVL 85
ID: 39888364
Have you tried the Hide Duplicates Duplicates property? You'd select that for each field where you do not want duplicates to occur.

It's on Properties - Format, down near the end of the listing.

Author Comment

ID: 39889143
Yes, I tried that already, but if I do that and don't use the subreport I am forced to put the customer and contact info on the detail line, it is true that customer and contact info wont repeat but after the first record it will create an unnecessary space between the first record and the others because of the space required by the customer and contact information.

I posted a compressed version of the access file in one of my previous post.

LVL 37

Accepted Solution

PatHartman earned 500 total points
ID: 39889248
I don't think there is a way to do this without using a subreport.  Even using hide duplicates and the can shrink/can grow properties, you will end up with a first line being as high as necessary to display the "left side" stuff with the subsequent lines being normal height.  That doesn't look very attractive.  You end up with the same result if you use a subform for the left-side stuff.  That leaves us with my original suggestion to create a recap subform that you put in the report footer.

Author Comment

ID: 39890344
Ok  i will do that and test it.

Author Closing Comment

ID: 39890789
Thanks. I did it with the subreport! Thanks for the support, again. Take care.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question