Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-02-24
11
Medium Priority
?
2,089 Views
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.

Thanks,

Michael
PharmacyHospital.accdb
0
Comment
Question by:mmoralespr
[X]
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
11 Comments
 
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).
0
 
LVL 39

Expert Comment

by:PatHartman
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.
0
 

Author Comment

by:mmoralespr
ID: 39887497
Scott,

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.

Regards,

Michael
PharmacyHospital1.zip
0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:mmoralespr
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?
0
 
LVL 39

Expert Comment

by:PatHartman
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.
0
 

Author Comment

by:mmoralespr
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.
agedreceivablesreport.jpg
0
 
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.
0
 

Author Comment

by:mmoralespr
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.

Thanks
0
 
LVL 39

Accepted Solution

by:
PatHartman earned 2000 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.
0
 

Author Comment

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

Author Closing Comment

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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

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.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

610 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