Solved

Adding totals in subreport to main report

Posted on 2004-08-15
6
406 Views
Last Modified: 2009-04-29
I know this has been asked before and I've tried every one of the solutions.  So here I am.  I need to bring a sum from my subtotal into my main report.  This is what I tried (and failed):  =[Qry_Count_Unit_Awards_by_Unit subreport].[rpt_Award_Totals_by_Unit]![SumofAwards]

That is trying to just bring my sum field into the main report to use it.  The first field is my subreport, the second is my main report and the third is my sum field.  

Any suggestions welcome - and I'm an Access novice, so type slowly ;-)

- Sarah
0
Comment
Question by:Kindir
[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
  • 3
  • 2
6 Comments
 
LVL 16

Expert Comment

by:ahmedbahgat
ID: 11803875
Hello Sarah

i do it this way :

1. in the main report make the Grand Total box non bund to any thing, make its name GrandTotal and is located in the report footer
2. in the sub report, assume the sub total box is named "SubTotal1"
3. in the main report assume the sub total box is named "SubTotal2"

4. use the OnFormat event for the main report footer section as follow:

me.GrandTotal= Me.SubTotal2 + Me.SubReport.Report!SubTotal1

cheers
0
 
LVL 5

Expert Comment

by:peterpuscas
ID: 11803995
Just make the text field wich shows the SumOfAwards invisible on the subreport,than
use something like this on the main report:

=[Qry_Count_Unit_Awards_by_Unit subreport].Report.[textFieldFromSubreport]

you have to replace textFieldFromSubreport with the invisible field from subreport

Peter
0
 

Author Comment

by:Kindir
ID: 11804673
Thanks, I have it working somewhat but when I put it in the main report it shows #error when there are no records.  When it was in the subreport it didn't do that.  Any idea how to get rid of the #error when there is no sum?  A zero would be fine, but not the error!

- Sarah
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 5

Accepted Solution

by:
peterpuscas earned 500 total points
ID: 11804828
Use this for the control source:

=IIf( IsError( [Qry_Count_Unit_Awards_by_Unit subreport].Report.[textFieldFromSubreport])," ",=[Qry_Count_Unit_Awards_by_Unit subreport].Report.[textFieldFromSubreport])

Peter
0
 

Author Comment

by:Kindir
ID: 11805955
Thanks for your continued help Peter!  Now it's giving me invalid syntax - any idea what that could be?  

- Sarah
0
 
LVL 5

Expert Comment

by:peterpuscas
ID: 11815706
You have to change [textFieldFromSubreport] with the name of your text field,
it has two appearences.

Peter
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

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…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

738 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