Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-02-24
11
Medium Priority
?
2,131 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 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 40

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 40

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 40

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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

571 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