Solved

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

Posted on 2014-02-24
11
1,908 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
  • 6
  • 3
  • 2
11 Comments
 
LVL 84
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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
 

Author Comment

by:mmoralespr
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:mmoralespr
Comment Utility
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 84
Comment Utility
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
Comment Utility
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 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
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
Comment Utility
Ok  i will do that and test it.
0
 

Author Closing Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now