Taking sum of a field from sub report

Hi

On a sub report I have a field datetot which is the sum of two fields those 
two fields are in turn coming from two further sub reports of the sub 
report. How can I take the sum of datetot field to the main report?

Thanks

Regards 


0
John
9/13/2007 6:18:07 AM
access.reports 4434 articles. 0 followers. Follow

5 Replies
1374 Views

Similar Articles

[PageSpeed] 36

See:
    Bring the total from a subreport onto a main report
at:
    http://allenbrowne.com/casu-18.html

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John" <John@nospam.infovis.co.uk> wrote in message
news:OCWbU3c9HHA.4752@TK2MSFTNGP04.phx.gbl...
>
> On a sub report I have a field datetot which is the sum of two fields 
> those two fields are in turn coming from two further sub reports of the 
> sub report. How can I take the sum of datetot field to the main report? 

0
Allen
9/13/2007 6:39:23 AM
Thanks for that. My problem has one more level in that the field 'Amount' on 
sub report is defined as =[SumA] + [SumB] where SumA and SumB are themselves 
coming from sub reports on the sub report. Hopefully the diagram below 
explains it;

Report
|-------------Sub Report
                     |      Amount  =[SumA] + [SumB]
                     |
                     |     SumAmount = sum([Amount]) gives #Error
                     |
                     |--------- Sub Sub Report A
                     |                SumA = Sum([A])
                     |
                     |
                     |--------- Sub Sub Report B
                                     SumB = Sum([B])


When on sub report I try to do =Sum[Amount] I get #error. Any solution to 
that?

Thanks

Regards

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message 
news:O4B2TDd9HHA.1900@TK2MSFTNGP02.phx.gbl...
> See:
>    Bring the total from a subreport onto a main report
> at:
>    http://allenbrowne.com/casu-18.html
>
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "John" <John@nospam.infovis.co.uk> wrote in message
> news:OCWbU3c9HHA.4752@TK2MSFTNGP04.phx.gbl...
>>
>> On a sub report I have a field datetot which is the sum of two fields 
>> those two fields are in turn coming from two further sub reports of the 
>> sub report. How can I take the sum of datetot field to the main report?
> 


0
John
9/13/2007 7:00:52 AM
So you have the Amount field working in the main subreport, but when you try 
to sum this Amount field in the Report Footer section of the main subreport, 
it doesn't work?

Use a running sum text box to collect the total.
It's properties might look like this:
    Control Source        =[Amount]
    Running Sum          Over All
    Format                   Currency
    Name                     txtAmountRS
    Visible                    No

Now in the Report Footer section of the main subreport, add another text box 
with Control Source of:
    =[txtAmountRS]

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John" <John@nospam.infovis.co.uk> wrote in message
news:uOv8MPd9HHA.980@TK2MSFTNGP06.phx.gbl...
> Thanks for that. My problem has one more level in that the field 'Amount' 
> on sub report is defined as =[SumA] + [SumB] where SumA and SumB are 
> themselves coming from sub reports on the sub report. Hopefully the 
> diagram below explains it;
>
> Report
> |-------------Sub Report
>                     |      Amount  =[SumA] + [SumB]
>                     |
>                     |     SumAmount = sum([Amount]) gives #Error
>                     |
>                     |--------- Sub Sub Report A
>                     |                SumA = Sum([A])
>                     |
>                     |
>                     |--------- Sub Sub Report B
>                                     SumB = Sum([B])
>
>
> When on sub report I try to do =Sum[Amount] I get #error. Any solution to 
> that?
>
> Thanks
>
> Regards
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message 
> news:O4B2TDd9HHA.1900@TK2MSFTNGP02.phx.gbl...
>> See:
>>    Bring the total from a subreport onto a main report
>> at:
>>    http://allenbrowne.com/casu-18.html
>>
>> "John" <John@nospam.infovis.co.uk> wrote in message
>> news:OCWbU3c9HHA.4752@TK2MSFTNGP04.phx.gbl...
>>>
>>> On a sub report I have a field datetot which is the sum of two fields 
>>> those two fields are in turn coming from two further sub reports of the 
>>> sub report. How can I take the sum of datetot field to the main report? 

0
Allen
9/13/2007 7:07:09 AM
Thanks. The running total works when sub form is run on its own but not as 
part of the main report.

Regards

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message 
news:eaUI1Sd9HHA.5404@TK2MSFTNGP02.phx.gbl...
> So you have the Amount field working in the main subreport, but when you 
> try to sum this Amount field in the Report Footer section of the main 
> subreport, it doesn't work?
>
> Use a running sum text box to collect the total.
> It's properties might look like this:
>    Control Source        =[Amount]
>    Running Sum          Over All
>    Format                   Currency
>    Name                     txtAmountRS
>    Visible                    No
>
> Now in the Report Footer section of the main subreport, add another text 
> box with Control Source of:
>    =[txtAmountRS]
>
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "John" <John@nospam.infovis.co.uk> wrote in message
> news:uOv8MPd9HHA.980@TK2MSFTNGP06.phx.gbl...
>> Thanks for that. My problem has one more level in that the field 'Amount' 
>> on sub report is defined as =[SumA] + [SumB] where SumA and SumB are 
>> themselves coming from sub reports on the sub report. Hopefully the 
>> diagram below explains it;
>>
>> Report
>> |-------------Sub Report
>>                     |      Amount  =[SumA] + [SumB]
>>                     |
>>                     |     SumAmount = sum([Amount]) gives #Error
>>                     |
>>                     |--------- Sub Sub Report A
>>                     |                SumA = Sum([A])
>>                     |
>>                     |
>>                     |--------- Sub Sub Report B
>>                                     SumB = Sum([B])
>>
>>
>> When on sub report I try to do =Sum[Amount] I get #error. Any solution to 
>> that?
>>
>> Thanks
>>
>> Regards
>>
>> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message 
>> news:O4B2TDd9HHA.1900@TK2MSFTNGP02.phx.gbl...
>>> See:
>>>    Bring the total from a subreport onto a main report
>>> at:
>>>    http://allenbrowne.com/casu-18.html
>>>
>>> "John" <John@nospam.infovis.co.uk> wrote in message
>>> news:OCWbU3c9HHA.4752@TK2MSFTNGP04.phx.gbl...
>>>>
>>>> On a sub report I have a field datetot which is the sum of two fields 
>>>> those two fields are in turn coming from two further sub reports of the 
>>>> sub report. How can I take the sum of datetot field to the main report?
> 


0
John
9/13/2007 2:16:48 PM
If the main report has records of its own, the subreport "exists" in 
multiple separate instances, each indepenent of the others. You will not be 
able to accumulate the subreports totals across its multiple instances.

If that's the problem you are seeing, you will need to pass back the 
accumulated total from the subreport onto the main report. You can then use 
another text box to accumulate the subtotals into a grand total on the main 
report.

(Hope this approach works for you: there can be timing issues between when 
the main report reads the subreport's subtotal and when the subreport has 
completed accumulating the values.)

An alternative approach might be to just use a DSum() expression in the main 
report's Report Footer section, though this becomes more difficult if the 
report could be filtered (especially since Access doesn't maintain its 
FilterOn property reliably.)

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John" <John@nospam.infovis.co.uk> wrote in message
news:Or%23FzCh9HHA.3900@TK2MSFTNGP02.phx.gbl...
> Thanks. The running total works when sub form is run on its own but not as 
> part of the main report.
>
> Regards
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message 
> news:eaUI1Sd9HHA.5404@TK2MSFTNGP02.phx.gbl...
>> So you have the Amount field working in the main subreport, but when you 
>> try to sum this Amount field in the Report Footer section of the main 
>> subreport, it doesn't work?
>>
>> Use a running sum text box to collect the total.
>> It's properties might look like this:
>>    Control Source        =[Amount]
>>    Running Sum          Over All
>>    Format                   Currency
>>    Name                     txtAmountRS
>>    Visible                    No
>>
>> Now in the Report Footer section of the main subreport, add another text 
>> box with Control Source of:
>>    =[txtAmountRS]
>>
>> "John" <John@nospam.infovis.co.uk> wrote in message
>> news:uOv8MPd9HHA.980@TK2MSFTNGP06.phx.gbl...
>>> Thanks for that. My problem has one more level in that the field 
>>> 'Amount' on sub report is defined as =[SumA] + [SumB] where SumA and 
>>> SumB are themselves coming from sub reports on the sub report. Hopefully 
>>> the diagram below explains it;
>>>
>>> Report
>>> |-------------Sub Report
>>>                     |      Amount  =[SumA] + [SumB]
>>>                     |
>>>                     |     SumAmount = sum([Amount]) gives #Error
>>>                     |
>>>                     |--------- Sub Sub Report A
>>>                     |                SumA = Sum([A])
>>>                     |
>>>                     |
>>>                     |--------- Sub Sub Report B
>>>                                     SumB = Sum([B])
>>>
>>>
>>> When on sub report I try to do =Sum[Amount] I get #error. Any solution 
>>> to that?
>>>
>>> Thanks
>>>
>>> Regards
>>>
>>> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message 
>>> news:O4B2TDd9HHA.1900@TK2MSFTNGP02.phx.gbl...
>>>> See:
>>>>    Bring the total from a subreport onto a main report
>>>> at:
>>>>    http://allenbrowne.com/casu-18.html
>>>>
>>>> "John" <John@nospam.infovis.co.uk> wrote in message
>>>> news:OCWbU3c9HHA.4752@TK2MSFTNGP04.phx.gbl...
>>>>>
>>>>> On a sub report I have a field datetot which is the sum of two fields 
>>>>> those two fields are in turn coming from two further sub reports of 
>>>>> the sub report. How can I take the sum of datetot field to the main 
>>>>> report? 

0
Allen
9/13/2007 2:47:54 PM
Reply:

Similar Artilces:

Debt category in M04 budget reports
Noticed something odd when looking at my annual budget report in M04... In my budget set-up, the 'debt' category includes two things: debt reduction planner's pmt and my student loan pmt. When I view my annual budget report for the YTD however, the debt line not only includes these two txns, but also whatever cc pmts (transfers) I've made for the month/year, AND any returns that I've made to my cc. (When I log returns, I categorize them as credits to whatever expense category they were originally marked as. They look like they're showing up correctly as credi...

Sumif...SumProduct...Sum Something
I need a formula that will look at e5:e124 first, then at g5:g124. If e5:e124 is greater than 1 and g5:g124 is blank or zero then it will sum the number in e5:e124. =SUMPRODUCT(--(e5:e124 >1),--(e5:e124=0),e5:e124) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "heater" <heater@discussions.microsoft.com> wrote in message news:F0F907D8-9958-42F1-A79D-639E83822BCB@microsoft.com... > I need a formula that will look at e5:e124 first, then at g5:g124. If > e5:e124 is greater than 1 and g5:g124 is blank or zero then it will sum the > ...

Unbound Text Field
I need to set he value of an Unbound Text field on a report. Since this report is called from several different forms I can't set it on the report itself and it doesn't work setting it from the from. Any suggestions appreciated! Thanks DS Which version of ACCESS are you using? What is the actual value that needs to be set in the textbox -- is it a fixed value, or a value from a form's textbox, or what? -- Ken Snell <MS ACCESS MVP> "DS" <bootybox@optonline.net> wrote in message news:e1wPh.416$Hg1.311@newsfe12.lga... >I need to set he ...

Need help w/stock status report
We are on 9.0 using manufacturing with all items set up as average cost. The canned stock status reports no longer work to tie to GL in 9.0/10.0 so I am wriitng my own in either Smartlist Builder with a SQL view or Crystal Reports. I just want to get close in tying GL to inventory value. I am thinking on the IV10200, with this (qty received * Unit Cost) - (qty sold * adjusted cost) for each layer. Then the sum of each layer "should" be close to GL. Am I on the right track? Microsoft used to report that using the Average Costing valuation method is never going to tie to the ...

How do you get the sum of 2 different text values?
For instance, I need to add the amount of times "S" and "HS" appear in a row. I figured out that the COUNTIF function will add the amount of times "S" appears in a row, but I can't figure out how to make it add both and give me a number value as the answer. Is this even possible? Try this: =COUNTIF(A:A,"S")+COUNTIF(A:A,"HS") Regards, Paul "sunshine.girl" <sunshine.girl@discussions.microsoft.com> wrote in message news:4C63C684-D389-4BA0-A73F-8AC553B7F7D3@microsoft.com... > For instance, I need to add the amount o...

update check box fields in the table using array based on certain value
One of my table has _Ct values and I want to update other relevant fields in the same table ( basically check box) with ‘yes’ or ‘no’ depending on the _ct values. Can some one show me how I can do this in Access and correct the following logic? Public Const ct = "H1_Ct H3_Ct H5a_Ct H5b_Ct RP_Ct swInfA_Ct swH1_Ct" Public Const test = "H1_Tested H3_Tested H5a_Tested H5b_Tested RP_Tested” Public Const result = "H1_Result H3_Result H5a_Result H5b_Result RP_Result" Dim objDB As DAO.Database Dim mytbl As DAO.Recordset Set objDB = CurrentDb() Set myt...

Controlling a category in a report
I have been using M2006 for a year and an bit and have the following question. I want to be able to control the inclusion or exclusion of a category in a report from the category itself. I realize I can customize a report, but if I have a category that I do not want in any reports it would be easier to just exclude it. Meaning it would be excluded from all reports unless I specifically customized a report to include it. Ying and yang so to speak. Basically, I want to control the reporting characteristics of the category from within the category and not from within the report. Sounds lik...

Report Options #2
Hi, What is the maximum reports options can I create in GP? Cheers Hi Kbres, Each report in GP can have a maximum of 32 different report options based on my past experience and have not tried creating beyond this number in GP 10. Hope this helps! Cheers, Dennis Araullo, MACS MCTS GP,CRM,AX MCTIP Database Administrator 2008 CCEA Thanks a lot! A Dexterity drop-down list control can display up to 32,767 items. So you should be able to have up to that number of report options for any given report. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http:/...

Hiding labels on reports
How do you hide the labels on a report when the text box next to it is null? And does anyone have the code to make the alternating background color for each record result in details? BazEWR wrote: >How do you hide the labels on a report when the text box next to it is null? >And does anyone have the code to make the alternating background color for >each record result in details? If the label is not attached to another cotrol, hide the label: Me.thelabel.Visible = Not IsNull(Me.thetextbox) If the label is attached to a text box and you want the text box and its section to sh...

Track in CRM
Hi All, If I receive an email in Outlook that has been sent to me because my email address is in the BCC field and I click on "Track in CRM", it doesn't appear in My assigned queue. Is this by design? You can find it by using Advance Find from within CRM. Is there a way I can make these emails appear? Thanks MarkB See Advance Find and check that. -- Regards, MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "MarkB" wrote: > Hi All, > > If I receive an email in O...

Group by field the Inbox
If I group by "Recevied" field the Inbox folder, Outlook 2002 (10.xxx ver.) create a gruop for every message (!!!) because it gruop by year-month-day-hour-minute and second too (like in the example image at http://web.tiscali.it/errediemme/img.jpg ). Is there a way to group only by day (year-month-day) and not even by second (hour-minute and second)? Thanks and sorry for my "englian" D�vide from Italy Try going to View | Arrange By | Custom. Click the Group By option and make sure that the check box on top is selected (Automatically Group According to Arrangement). ...

need to extract the subject field from email folders
I need to extract (copy) the subject field from email folders and the name field from directories or file folders. I need to use this information so that I can write about the files in a readme file, etc. I know how to do this in Unix shell scripts but I do not know how to do it on Microsoft products. I know how to do a screen capture of the info but then I can't manipulate the information without OCR software etc. That's a waste of time. TIA, Gloria .. ...

Crystal Reports: Information is needed before this report can be processed
When selecting any of the crystal reports, I always get this message: CrystalReportViewer: Information is needed before this report can be processed Has anyone seen this message or know how to fix it? Hi, This will happen if your AD server are 2000 Servers and your CRM Server is 2003 Server. There is a patch available, You have to contact CS by creating an incident(Free of charge). -Qureshi "ewoiruweo@wpeirwpe.com" wrote: > When selecting any of the crystal reports, I always get this message: > > CrystalReportViewer: Information is needed before this report can be &g...

Outlook "from:" field
Does anybody know how to obtain "a new row" while writing a message under Microsoft Outlook I got To: CC: BCC: But I would love also to have From: as I got 3 email accounts configured in my outlook. Every email is sent out from "default" email account � I wish to have a choice from which email account I send out an email Thanx Marcin >Does anybody know how to obtain "a new row" while writing a message >under Microsoft Outlook > >I got >To: >CC: >BCC: > >But I would love also to have From: as I got 3 email accounts >configure...

Crystal reports #6
I have Crystal reports v10 - what version does RMS V 1.3 use for report writing? Is this the same for RMS V2.0? Danny Dear Denny, Crystal report v8.5 works with RMS V 1.3. I have already gone through and at initial develop report on v10 but it not works then I develop the report on V. 8.5 it works. Let me know if you need any help for the report writing b/c lot of consideration are require when you develop the report to integrate with RMS. Rate please. "Danny" wrote: > I have Crystal reports v10 - what version does RMS V 1.3 use for report > writing? Is this the s...

Limit the data selection based on two fields?
Hello, I have a data entry form where user input the data. Within the data fields, I have field call Item, Side, sequence field where any combination of these 3 fields are unique. What I would like to achieve on the sequence field is limit the selected based on selection of Item and Side. This is to prevent user from inputting wrong values on that field. Any help is appreciated. Example fields: Item Side Sequence 11A1300-1 1st side 50 11A1300-1 1st side 100 11A1300-1 1st side 200 11A1300-1 1st side 400 11A1300-1 1st side ...

Add Last Edited Field to Document
Hi, I would like to know if there is a way for a non-developer to add a field for "last edited by" to a visio document. I need something that would update automatically every time a document was updated. I found insert Creator, but what I really need is to insert UserName, but apparently UserName can only be used through VBA scripting. Any help would be appreciated. Thanks, Ron Unfortunately that feature is not available and must be coded. Al "rongleclair" <r1ec1air@c0mcast.net> wrote in message news:46csa0pttk4ko1hcvjhvuroqpp5tigv2rj@4ax.com... > Hi, > >...

Multiple indexes on same fields in different order
I sometimes need to find the largest or smallest value in a field. If I put two indexes on the same field, one in ascending order and one in descending order, is Jet smart enough to use the proper index if a give it a grouping query with the condition Max or Min, or am I just wasting resources? Pete -- This e-mail address is fake, to keep spammers and their address harvesters out of my hair. If you want to get in touch personally, I am 'pdanes' and I use yahoo mail. But please use the newsgroup when possible, so that all may benefit from the exchange of ideas. ...

Aging Report Feasibility
I would like to change RM Summary Historical Aged Trail Balance to show multicurrency. Is it possible or Is there any other report which serves the purpose or any other report I can take it as a base. If its possible can any one give me the steps that I need to do. -- Thanks Sudha That really depends what you mean by multicurrency. Have you tried un- checking the "Excluse Multicurrency Info" box in the report options? That shows a MC version of the same report, which may or may not be what you need. On Nov 20, 10:48=A0am, Sudha <Su...@discussions.microsoft.com> wrote: &...

How to exclude transfers from Account Balance History Report?
I am running a report to show my account balances from month to month. To this end, I am using the report, “Account Balance History”. How do I exclude transfers from this report? The graph has huge downward/upward spikes which I would like to eliminate. My transfers are classified using the special Money facility, i.e. it’s a Transfer as opposed to being a Withdrawal or a Deposit. The transaction is reflected in the account being transferred to, in addition to the account being transferred from. ...

reports question #2
Hi How do I generate a report that will tell me how many store credits (or Gift Certificates) have been issued/redeemed in a givin time frame? I have a report "Detailed Sales with Tax & Tender" but the only problem is it does not show more than one tender type per transaction. So if a customer combines two tender types only one is shown. Thanks throw me a bone?...anyone cheers "christian" wrote: > Hi > > How do I generate a report that will tell me how many store credits (or Gift > Certificates) have been issued/redeemed in a givin time frame? &...

WIP Detail Report
Is there a (field within a) SQL table that indicates whether or not a cost transaction posted to a Project is in WIP or has been cleared from WIP? This would enable me to reconcile nicely to WIP in the GL, and I'd like to add a marker to our Project Cost Breakdown report to distinguish which cost lines have been billed, and which have not. For example, I've found PA23203 seems to show line items tagged/billed for T&M jobs, but I haven't found one for FP ones yet. Ideally, there would be a Master cost detail table with a field that could be used as a marker. Any suggest...

New Crystal Reports
Hi all, I am fairly new to CRM and have a few of questions: 1. Are the existing reports a part of the CRM install? 2. Can new Crytal Reports be developed, and if so 3. How does one add a newly designed Crystal Report to the report view to make it available to all users? 4. Am I correct ot assume that CRM already comes with a Crystal runtime engine and users do not need to have their own copy of Cryatal installed, if so 5. We don't need Crystal licences for runtime engines correct? Many thanks in advance, Miriam Read the anwser.... "Miriam C." <MiriamC@discussions.mi...

Detailed Sales Report at Head quarters. On hand items shows total orginazation quanity not store quanitiy.
Hello, I am trying to customize the Detailed sales report to show the Onhand quantity for each store. When I select the "On Hand" Column it shows the total for the entire orginization. I want something that reflects just the Quanitiy On Hand at each location. I noticed that the Item -> Snapshot store Quanity list does this. I attempted to move the chuck of code setting the DynamicItem in the snapshot report over to the Detailed Sales Report but am recieving errors. I am not sure if the DynamicItem is support in reporttype = salesreport. I am in the process of registering...

Forms
I am currently developing a form in Publisher 2003. I need some of the responses on the form to be "required". For example, if a box is checked that a person is requesting vacation, the text field below it would be where the user would indicate his/her dates of vacation. If vacation is checked, the text field should be required. Or, even if I could just make it so the user must check a box for vacation vs. no vacation (even without dates), I would be happy with that. Any ideas, or am I not going to be able to accomplish this in Publisher? I didn't see any reference a...