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 84
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 36

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.


Independent Software Vendors: 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!


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 36

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 84
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 36

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

733 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