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
1219 Views

Similar Articles

[PageSpeed] 11

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:

how to sum up or count based on dates
Let say I have this file will multiple sheets: Bob, Adam, Shiela This example in Bob sheet: Date Activities Venue 3/19/2006 Parents Meeting Branch 3/25/2006 Area Meeting A HQ 4/3/2006 Clients Orientation Branch 5/11/2006 Team Meeting HQ Adam: Date Activities Venue 2/8/2006 Clients Oreintation Branch 3/25/2006 Area Meeting B HQ 4/17/2006 Team Meeting HQ...

Take Ownership
What does it mean to Take Ownership of a file or folder? <PeoplesChoice@Chicago.net> wrote in message news:gjqqv5ddgj7uesie7vdej1tshbv49unr1u@4ax.com... > What does it mean to Take Ownership of a file or folder? It means to transfer ownership of an object from the original owner to you, (your account). Google ' Take Ownership ' for more information. To take ownership of a file or a folder How to take ownership of a file You must have ownership of a protected file in order to access it. If another user has restricted access and you are the computer administ...

Sum of numbers with Prefix?
Hi, We've got a sheet with a list of employees (Row) and dates (Columns) - in each cell there can be a particular code relating to the attendance of that employee. for example: 1st 2nd 3rd 4th Joe Bloggs L#30 / H L#22 L#30 means that Joe was 30 minutes late ! / = on time H= Holiday etc etc (there are about 20 codes) at the end of these rows are a few summary cells which show total number of holidays in the month and total number of lates (both using countIf) I need to add a 3rd summary column which shows how many minutes ...

dynamic adaptive / variable report field format
In Office 2007 doing an Access report I have some fields that are to display various quantites and amounts the range of the fields could be small (ie. 0.6575466667, $13.33) or large (ie. 500,000, $128,790) because of the limited real-estate these fields occupy in the report - I'd like to suppress decimal places when the values are large but include decimals (and limit to 2 places) when the values are small - and avoiding ######### when the value violates/exceeds explicit field format layout how do I accomplish this? between currency, standard and general number or auto, 0 ...

MSVC 6 and Reported Memory Leaks
Hi, I have always depended on MSVCs Memory Leak report to help ensure that I deallocate all memory that I allocate in my code. If I run under the debugger and memory leaks are reported, I know that the error is in the recently modified code. However, I recently upgraded the version of ACE/TAO that I use and now MSVC always reports a whole bunch of apparently spurious leaks. These leaks occur in two contiguous (by allocation number) bunches. I can't catch these in MSVC indicating that the memory is already allocated by the time that I step into my program (F11). Furthermore, Purify, Bo...

Bookmarks take me away from email
I use "Actions > Send Web Page by E-mail". Then setup the bookmark, and a link to the bookmark in Outlook. This link takes the recipient to the web page (www.webpage.com#bookmark), rather than jumping them to that part of the email. It seems like this should be possible since you can set the link to go to a bookmark. I don't even get how it thinks I would possibly want it to go to a web page. Help is much appreciated! ...

How do I remove a Calculated Field from the Pivot Table field list
I have created two fields in my Pivot Table. However, the first one had an error in it. I tried to modify it, but was unsuccessful. So, I just did it again. This means not only creating a new name, the original field stays in the Field List. So, I guess my post has two questions. How do you modify a Field? and How do you remove a Field? -- TheTraveler Hi Second part first Select the field first from the dropdown>click Modify To get rid of a field, select the field from the dropdown>Delete -- Regards Roger Govier TheTraveler wrote: > I have created tw...

TaskPad error: User cannot add/modify data in Custom Fields
Backend: Exchange 2003 Frontend: Outlook 2003 or 2000 (happens in both versions). Problem: I have two users ("A" and "B") who both have full access to their common boss's mailbox ("C"). In C's TaskPad (in the Calendar folder), they have created a couple custom fields/columns. All of a sudden, about a week ago, user B could no longer add/modify any data in the custom fields, though she could access the standard fields just fine. If she sets up the same view in the Tasks folder, she can add/modify just fine. User A can still access the custom fields....

Report Sum
I am working on a cost study form for a body shop. Each vehicle will have its own page. I have to be able to add up to 15 different list part totals as well as 15 different net part totals. I then need to add the 7% tax of the list part total to the net parts total. I would love some input on what would be the easiest way to achieve this because I then have to generate a report with by the different companies showing total parts, total labor and total profit ratio. Usually, you want to text put boxes in footer of the form. You have to pull the footer down on the bottom to have...

combining two or more fields into one field
being somewhat new to Access and Vba I need the answer to a question. I want to combine three fields into one. as FirstName+MI+LastName. I want to do this after FirstName, MI, and LastName have entered. The reason for this it to be able to check the created field for duplicates. Any suggestions On Mon, 21 May 2007 18:22:04 -0700, david d <davidd@discussions.microsoft.com> wrote: >being somewhat new to Access and Vba I need the answer to a question. I want >to combine three fields into one. as FirstName+MI+LastName. I want to do >this after FirstName, MI, ...

sum function not actually summing??????
Excel 03 OK, I'm trying to sum numbers a column. This is exact representation, cells F19 to F22: $11,087 $3,400 $2,400 $1,211 I sum into F23 and get the result of only F19 *$11,087.* F23 cell formula shows as =SUM(F19:F22) This is a sheet I downloaded. I added a row (F21) to insert the $2400. When first sum action didn't work, I thought maybe it was based on cell format, so I reformatted to label them currency (this shows in cell properties), so it's not an issue of cell format standardization. Also, I copied the numbers from F column to a new one and pasted as ...

importing the 'to' field history #2
When you have emailed someone in outlook the next time you type in the first few characters of that name it pops up for you to select. Does anybody know the extension of the log file that contains that history so that I may import that file to a new machine? ...

Disabling the BCC field
Does anyone know of a way to disable or block the bcc field? I do not want users on my netowkr to be able to send them. Thank you Andrew as far as I know the only can be desable from the client. >-----Original Message----- >Does anyone know of a way to disable or block the bcc field? I do not want >users on my netowkr to be able to send them. > > >Thank you >Andrew > > >. > ...

calculation taking too long
Currently I have a sheet where I enter my data and then another sheet within the same workbook that makes various calculations based on this data. But every time I enter data, it takes a long time for my formulas to calculate, upwards of a minute. I am pretty sure this is because my formulas contain entire columns as ranges (i.e. A:A rather than A1:A100). However I need to use A:A in my formulas as my data sheet is indefinitely long and I will continue to add to this over the years. I don't want to have to go back and change all my formulas to A1:A200 every time my data e...

Charting a Cumulative Sum
I have a column of cells with cash amounts and there are several cells per day, some positive and some negative. I want to create a chart that graphs these numbers as a Cumulative Daily Sum (as opposed to a chart simply containing each individual amount). I know how to write a Cunulative Sum formula, but I'm hoping there's a more elegant solution than filling tons of cells with formulas for each day's total and charting from that. thanks. You could use a PivotTable to summarize the data, and create a chart from the PivotTable. There are some instructions, and links to informa...

Exporting Report Data
The 2003 Access version had a very handy ability to export data from the Report to MS Excel, and nicely lay out the columns, rows and data. MS in its infinite wisdom removed that feature in the 2007 version. I tried using xml export, but it does not provide the Report data. It actually pulls all data from the table being used for a report. Anyone have a possible work-around to get the data from the reports? Thank goodness I kept one PC with Access 2003 on it. PS to Evi: I meet with DB programmer tomorrow at 4pm. ...

character limits for textbox field
If this is more appropriately asked as a "form design" forum question, my apologies up front... My problem is having an unbound form's macro to save records fail for some records and not others...on some workstations and not others...by some users and not others. The only common denominator I can come up with is the failures seem to be on records that contain lengthy comments entered into a textbox field that is bound to a memo filed in an underlying table. My research tells me that both the textbox filed and the memo field can handle waaayyyy more than the three or four...

Resolve Case, Resolution Type Field (Need more options)
I need to add more options to the Resolution Type Field (There is only the PROBLEM SOLVED option) and i can't find where , It seems to be a Pickup list but the form Resolve Case and its fields do not appear anywhere, Please let me know how can i do this if somebody knows it.... -- cB - Thanks in advance I'm having the same issue! "cB" wrote: > I need to add more options to the Resolution Type Field (There is only the > PROBLEM SOLVED option) and i can't find where , It seems to be a Pickup list > but the form Resolve Case and its fields do not appear...

Field Chooser #2
Guys-is it possible that some of the fields in Outlook 2000 (from Field Chooser) can simply disappear or could the problem be that somebody else is accessing my mailbox? It happened to me that the From field and Subject field have simply disappered twice without my action? Could it be a bug or something similar? Is it possible those fields are already in the table view? I've been spooked by that one before. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstar...

Create new field???
Could someone please tell me how I go about creating a new field? I have successfully created a new tab, and I can see a selection of fields to choose from. So I am good with that. What I need to do though is create a new field... I appreciate any help!!!!!!! Hank You need to go the CRM server and run Deployment Manager, and create the field there. -Gary "Hank" <anonymous@discussions.microsoft.com> wrote in message news:58cf01c42d43$0bf152c0$a301280a@phx.gbl... > Could someone please tell me how I go about creating a new > field? > I have successfully created a ...

Report Section Line Method Height Scaling
I discovered the Line method for report sections today. Probably old news to most of you, but it was a great discovery for me trying to duplicate a Department of Defense standard form that must look exactly like the original paper copy or the customer won't be happy. The question: How do I make vertical lines grow with the detail section? I've read through the other posts covering this topic, but I don't understand the "Step" notation offered, nor does the "B" notation I've run across in other posts seem to match up with my Access 2003 help notes or fit ...

Logging in taking too long
Why do SOME users' computers on my network take an unusual amount of time to log onto the network? I'm talking 15-20 min long! Not all of them too! This all started when we brought our server to our Austin office, due to Hurricane Rita, and brought it back to the gulf coast. When we brought it to Austin, we had to change settings (don't know what settings); then once we came back... there are some users who take forever to get on the server. Please help. Thank you, -- lfheb Have you checked event logs on both the client and the server? There has to be something in t...

How long will it take for migration?
Hi, I plan to do migration SBS 2003 to SBS 2008. I have about 500 GB data on the source server. and I have gigabyte network. How long it will take for the migration? Will the migration, will users feel the slow open files on server? Will the migration use a lot of CPU or memory? Thanks in advance! Lisa Hi Lisa (again <g>) The acutal moving over of the data files is trivial. It will take a few minutes to an hour. The real time consumer here is the moving of the exchange mail and public folders, which can take days. If you want/need a more accurate estimate of ...

Sum function changes arguments
A client's spreadsheet shows a sum function which calculates individual cells for instance =sum(B2,B4,B6,B8) then saves and closes the workbook. When she reopens it, the sum reads =sum(B2:B8). She couldn't duplicate the error whilst I was there but is adamant that nobody edits the workbook. The calculation mode is automatic and version 2007 Is this really possible??? Regards Diane Doesn't sound like it, and if she couldn't reproduce it, then it seems even less likely (how many times have I said that and been wrong :-(). HTH Bob "Dianeg&quo...

conditional sum returns not expected
Hi all. Using xl xp pro Here is conditional sum formula =SUM(IF(FAR_All_Data!$B$2:$B$1622=C$2,IF(FAR_All_Data!$B$2:$B$1622=C$3,IF(FAR_All_Data!$A$2:$A$1622=$A5,FAR_All_Data!$D$2:$D$1622,0),0),0)) Sorry about the wrapping. Where C$2 = 7001.4500 Where C$3 = 7001.4501 Where $A5 = 54608001 Where Far_All_Data! contains data, all ref's verified However, all formulas return 0.00. As I copy down the formula Col (54608001) udates ...002, 003, 004, etc... Sometimes the expected result is 0.00 other times value should b greater than 0 Not sure how to read the formula. Does it say if, or, and? ...