total repeating subreport

Hi, 

thanks in advance for any help...


I have main / sub report setup to produce and invoice.

the main form provides the "project detail" for an invoice

the subreport gathers, presents and then totals all of the line item charges,
the total is recored in the filed sumTask.

the subreport repeast for as many task as there are associated for a
particular project, so a project can have 1...n tasks, and each task can have
1...n line items (the actualy charge for the material labor, etc)

this all works perfectly.

Now I need to calculate the grand total from the repeating subforms and
present it in the footer of the main form, but I don't know how to do this.

Thanks!

-- 
Message posted via http://www.accessmonster.com

0
granola911
12/26/2009 4:01:04 PM
access.reports 4434 articles. 0 followers. Follow

5 Replies
850 Views

Similar Articles

[PageSpeed] 4

granola911,
    By "footer", I take you mean Report Footer, and the report consists
of only one invoice.
    And since you gave no names, I'll use my own example names.

    Given...
rptInvoice = The ONE of the invoice (InvoiceID, CustName, Address, etc)
rptDetail = The MANY subreport of transactions against that InvoiceID
sumTask = Total from the subreport transactions

    From rptInvoice, Report Footer, you should be able to refer to the
subreport sum with...
        Reports!rptInvoice!rptDetail.Report!sumTask
    I did not have time to test, but that should do the trick...

    Note: Given proper table design, and report design, an Invoice report 
usually
does not require a subreport.  I know your Invoice data entry form works 
that
way, but a report has special functions that allow you to combine the two 
tables
in the report query, and then Group by Invoice... and show associated 
transactions
in the Detail Section.   Then... the whole adding/displaying process is 
simplified.
    The setup you have now will work... so keep on with what you have, but
consider my suggestion on  your next One to Many type report.
-- 
    hth
    Al Campagna
    Microsoft Access MVP
    http://home.comcast.net/~cccsolutions/index.html

    "Find a job that you love... and you'll never work a day in your life."

"granola911 via AccessMonster.com" <u56971@uwe> wrote in message 
news:a12bed2ce0373@uwe...
> Hi,
>
> thanks in advance for any help...
>
>
> I have main / sub report setup to produce and invoice.
>
> the main form provides the "project detail" for an invoice
>
> the subreport gathers, presents and then totals all of the line item 
> charges,
> the total is recored in the filed sumTask.
>
> the subreport repeast for as many task as there are associated for a
> particular project, so a project can have 1...n tasks, and each task can 
> have
> 1...n line items (the actualy charge for the material labor, etc)
>
> this all works perfectly.
>
> Now I need to calculate the grand total from the repeating subforms and
> present it in the footer of the main form, but I don't know how to do 
> this.
>
> Thanks!
>
> -- 
> Message posted via http://www.accessmonster.com
> 


0
Al
12/26/2009 9:22:51 PM
In addition  to Al's suggestion, you can generally create totals queries with 
your group totals to add to the record source of you report's record source 
query. 

-- 
Duane Hookom
Microsoft Access MVP


"Al Campagna" wrote:

> granola911,
>     By "footer", I take you mean Report Footer, and the report consists
> of only one invoice.
>     And since you gave no names, I'll use my own example names.
> 
>     Given...
> rptInvoice = The ONE of the invoice (InvoiceID, CustName, Address, etc)
> rptDetail = The MANY subreport of transactions against that InvoiceID
> sumTask = Total from the subreport transactions
> 
>     From rptInvoice, Report Footer, you should be able to refer to the
> subreport sum with...
>         Reports!rptInvoice!rptDetail.Report!sumTask
>     I did not have time to test, but that should do the trick...
> 
>     Note: Given proper table design, and report design, an Invoice report 
> usually
> does not require a subreport.  I know your Invoice data entry form works 
> that
> way, but a report has special functions that allow you to combine the two 
> tables
> in the report query, and then Group by Invoice... and show associated 
> transactions
> in the Detail Section.   Then... the whole adding/displaying process is 
> simplified.
>     The setup you have now will work... so keep on with what you have, but
> consider my suggestion on  your next One to Many type report.
> -- 
>     hth
>     Al Campagna
>     Microsoft Access MVP
>     http://home.comcast.net/~cccsolutions/index.html
> 
>     "Find a job that you love... and you'll never work a day in your life."
> 
> "granola911 via AccessMonster.com" <u56971@uwe> wrote in message 
> news:a12bed2ce0373@uwe...
> > Hi,
> >
> > thanks in advance for any help...
> >
> >
> > I have main / sub report setup to produce and invoice.
> >
> > the main form provides the "project detail" for an invoice
> >
> > the subreport gathers, presents and then totals all of the line item 
> > charges,
> > the total is recored in the filed sumTask.
> >
> > the subreport repeast for as many task as there are associated for a
> > particular project, so a project can have 1...n tasks, and each task can 
> > have
> > 1...n line items (the actualy charge for the material labor, etc)
> >
> > this all works perfectly.
> >
> > Now I need to calculate the grand total from the repeating subforms and
> > present it in the footer of the main form, but I don't know how to do 
> > this.
> >
> > Thanks!
> >
> > -- 
> > Message posted via http://www.accessmonster.com
> > 
> 
> 
> .
> 
0
Utf
12/27/2009 4:47:01 AM
Hi 
thanks for the input... there are some special underlying reasons why in this
cast the subreport works best.

In my "project header" group section - I am able to "extract" the totalField
from each instance of the repeating subreport, however, I get an error when I
try to sum that field.

I have a SUM on my subreport - tasksTotal, I pull that out into the proejct
header section into a field called subTotal. Then I try to sum that field in
the Page footer: =Sum(([subTotal])), but that give me an error.

Thanks.


Duane Hookom wrote:
>In addition  to Al's suggestion, you can generally create totals queries with 
>your group totals to add to the record source of you report's record source 
>query. 
>
>> granola911,
>>     By "footer", I take you mean Report Footer, and the report consists
>[quoted text clipped - 49 lines]
>> 
>> .

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200912/1

0
granola911
12/27/2009 2:23:20 PM
You can't use Sum() to total a control as it only works on fields or 
expressions from the report's record source. Also, you can't use Sum() of 
anything in a Page Footer (only works in Detail, Report, and Group sections). 
You might be able to try a running sum on the control in the section 
containing the subreport.

I still feel it is generally easier to create a totals query and add it to 
the report's record source.
 
-- 
Duane Hookom
Microsoft Access MVP


"granola911 via AccessMonster.com" wrote:

> Hi 
> thanks for the input... there are some special underlying reasons why in this
> cast the subreport works best.
> 
> In my "project header" group section - I am able to "extract" the totalField
> from each instance of the repeating subreport, however, I get an error when I
> try to sum that field.
> 
> I have a SUM on my subreport - tasksTotal, I pull that out into the proejct
> header section into a field called subTotal. Then I try to sum that field in
> the Page footer: =Sum(([subTotal])), but that give me an error.
> 
> Thanks.
> 
> 
> Duane Hookom wrote:
> >In addition  to Al's suggestion, you can generally create totals queries with 
> >your group totals to add to the record source of you report's record source 
> >query. 
> >
> >> granola911,
> >>     By "footer", I take you mean Report Footer, and the report consists
> >[quoted text clipped - 49 lines]
> >> 
> >> .
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200912/1
> 
> .
> 
0
Utf
12/28/2009 12:04:01 AM
Hi, 

thanks, that is exactly what I'm going to do.

Duane Hookom wrote:
>You can't use Sum() to total a control as it only works on fields or 
>expressions from the report's record source. Also, you can't use Sum() of 
>anything in a Page Footer (only works in Detail, Report, and Group sections). 
>You might be able to try a running sum on the control in the section 
>containing the subreport.
>
>I still feel it is generally easier to create a totals query and add it to 
>the report's record source.
> 
>> Hi 
>> thanks for the input... there are some special underlying reasons why in this
>[quoted text clipped - 19 lines]
>> >> 
>> >> .

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200912/1

0
granola911
12/28/2009 9:42:09 PM
Reply:

Similar Artilces:

need macro :repeat rows at top
I am a pure novice with Excel 2007. I only have one work sheet I work with. I need a macro to place rows 3,4,5,23,and 24 at the top of every succeeding page following page one. They show up only in Print. Page setup won't work as the rows must be contiguous.The data in each of these rows must remain as each successive page header is to be the exact duplicate.Coulde you please write the macro exactly as I should copy to VBE. -- J Macho ...

sub-total discount should not round
In RMS Point of sale, if you use the discount option to set the sub-total amount to a certain amount, it will round the price per unit causing the extended price (sub-total) amount to be incorrect. For example: If you are selling an item with quantity 32,000 and extended price of $.10 and then use the discount feature to 'discount all items' and 'subtotal amount' of $1700, it will cause the extended price to be listed as $1699.20 rather than $1700. This is causing issues for our customer. Is RMS intended to work this way or is this a bug? ---------------- This post is a...

Repeat key (F4, CTRL+Y) strange behaviour
Hello, I am having a strange problem with a worksheet my colleague sent me. When I use CTRL-+ to insert a row it works fine. But if I try to repeat the action with the F4 key (same behaviour with CTRL-Y) Excel does the "Goto Special" action. I'm going crazy about this, there seems not to be any macro associated to the function key too, please help! The F4 key works fine in a blank worksheet. Thanks almost forgetting..it's Excel 2003 with SP1 Can you do one more test? Start excel in safe mode close excel windows start button|Run button excel /safe file|open your workboo...

Formula not calculating total
Hi I'm doing a report in excel and when I try to use autosum to add up my columns, it doesn't add up the columns requested. Instead, it just types the formula in the cell destination. Hope someone can help, otherwise I've got to get my calculator out! Thanks Debbie "Debsy" <anonymous@discussions.microsoft.com> wrote in message news:FC90F5F1-CC00-4710-A9A7-B29B4C763030@microsoft.com... > Hi > > I'm doing a report in excel and when I try to use autosum to add up my columns, it doesn't add up the columns requested. Instead, it just types the formu...

SubReport Field / Report Field = %
Hi! I have a report [rptProjectedCostSummaryFinal] with a subreport [rsubCauseCodeAllocation]. The report is like so.... Details details details $$$$$$ Details details details $$$$$$ Total Budget Total Committed Total Uncommitted Total Projected Total Variance The subreport is like so... CauseCodeDesc Amount 1. Base Scope $50,000 2. Stakeholder $500 3. Error & Omission $2,000 4. Job Field $675 Total $53,175 What I want to happen is for each of the four cause codes to show what percentage each is of the to...

How do I automatically total and flag charges when they reach a s.
I have to pay writers per article. I cannot pay them until their total reaches a set amount. Then I send accounting a request to pay. Right now I just keep typing their info into excel, which means that I retype their name, new article name, words, price, when each issue comes out, then I put them in abc order, then I total them up on my own if they look like they might be up to the set amount. But I keep thinking there has to be a better way to do this. Any ideas? There are definitely better ways of doing this. I would need more detail on the info you use, to really help you though. ...

totals query
I'm trying to export our email list and I'm having a problem. I'm working with 5 fields first name, last name, email, date, ip I tried to group by email and max of date, but I still saw duplicates. So I also tried to max of IP and then I did not get duplicates. that's nice but not what I was expecting. When I decided to max of date I was assuming that I would get the IP for the most recent order, but now that I have to max of IP also I don't know what I'm gonna get. On a side note and somewhat unrelated. sometimes our customers are not very tech s...

determine which values in a range of cells make up a given total
I wish to find which cells in a range of data add up to a predetermined value. hi, Sheri ! > I wish to find which cells in a range of data add up to a predetermined value. following is in spanish, but I guess you might want to give a try (?) http://www.teladearana.es/seccion/excel/137-localizar-sumas.html two versions (each with downloadable workbooks): - a vba procedure using solver - an UDF (based on Jimmy L. Day algorithm: -> http://tinyurl.com/3qglnn) hth, hector. See: http://www.tushar-mehta.com/excel/templates/match_values/index.html -- Gary'...

Carry Forward Totals
I have 12 input worksheets - One for every month I have another 13 worksheets on these, the values from the 1st 12 is broken down to give totals for our 15 locations and the final is ytd totals for all. The problem occurs on the YTD sheet. For example, all the information was input in the January worksheet and carried forward to the Branch sheet and the YTD sheet as entered. The balancing of the spreadsheets did not occur until February 5 and by that time, data had been entered on the Feb sheet and carried forward to the YTD Sheet. I tried this on the YTD worksheet but doesn't wor...

total monthly count of received and sent emails
I would like to know if there is a way from exchange server 2003 or outlook 2003. to run a report or something to that affect to tell me how many emails I received/sent last month? I think you will need to look at 3rd party reporting tools. http://www.msexchange.org/software/Reporting/ Nue "Tim65" <Tim65@discussions.microsoft.com> wrote in message news:D80FF886-299B-48E2-AD21-6E71AD47C688@microsoft.com... >I would like to know if there is a way from exchange server 2003 or outlook > 2003. to run a report or something to that affect to tell me how many > emails &...

Punch time query to calculate total work time
I have a table containing two fields; Employee ID and Time stamp, respectively. Let's say the table contains the following values: Emp ID Time Stamp ------- ------------ 1001 1/1/2010 09:00 1001 1/1/2010 12:00 1001 1/1/2010 13:30 1001 1/1/2010 17:00 The data reflects that employee #1001 has clocked-in at 09:00 am and clocked-out at 12:00pm. The employee has clocked-in again at 1:30 pm and finally clocked out at 5:00 pm. Since the data was generated using Employee ID card, there is no separate time-in and time out field. How can I qu...

Repeating numbers amd text ( content of the text box)
Hello, Using crosstab query, my report shows like this: Position Seq No. Position Year 2007 1. Elder Jason 1. Edler Mathew 2. Deacon Mark 2 Deacon John. How can I make it to show up like this: 1. Elder Jason Mathew 2. Deacon Mark John In other word, we do not want it to repeat the same number. With many thanks, -- H. Frank Situmorang Set the Hide Duplicates property of the Position text box. Where you also want to...

Subreport on Crytal Report
Hi Everyone I am creating crytal report for store ops manager. Does RMS cryta report support crytal report with subreport? I am receiving erro "cannot open sql server" when I run crytal report with subreport i store ops mngr Please advise Thanks Franci ...

Formula for Grossing up a number or total of numbers
What formula would I use to gross up a number or total of numbers by 30%? Assuming the numbers are not the results of formulas... Enter 1.3 in an empty cell (any empty cell will do) Copy that cell: Edit>Copy Select the range of cells that you want to increase by 30% Then, Edit>Paste Special>Multiply>OK Delete the 1.3 -- Biff Microsoft Excel MVP "Duncan" <Duncan@discussions.microsoft.com> wrote in message news:DE25C420-3525-470E-AE1D-5C5A3BA4CFCB@microsoft.com... > What formula would I use to gross up a number or total of numbers by 30%? ...

Total number of Jan 2007 entries in a list of date formatted field
I have a report that captures work done by sales reps. For each entry there exists a date. This report is exported in Excel format and the date is captured as mm/dd/yyyy hh:mm. I would like to have a column that lists the number of work entries by Jan 2007, Feb 2007, Mar 2007, etc. I am at a loss as to how I can conditionally count the occurrence based on the month and the year in this date field. Thanks in advance. =SUMPRODUCT(--(YEAR(B2:B200)=2007),--(MONTH(B2:B200)=1)) where B2:B200 holds the dates you can expand on that if you want to count for an individual =SUMPRODUCT(--(A...

Running Totals for Pivot Tables
Hello I have a pivot table with months across the top and version as the row:- Data Version Jul Aug Sept Oct 1 1 4 7 10 2 2 5 8 11 3 3 6 9 12 Just wondered if it is possible to add cumulative totals to a pivot table? ie. Data Version Jul Aug Sept Oct 1 1 4 7 10 2 2 5 8 11 3 3 6 9 12 Cumulative 1 1 5 12 22 Thanks Lesley Make your pivot table and then use menu Insert/Calculated Field. You can n...

How can I total the top 9 scores of 12 weeks in a sporting contest
I need to keep track of the scores in a quiz for 12 weeks running, then total each contestant's best 9 scores. Is there any way to sum a column, automaticaly ignoring the 3 worst scores? =SUM(LARGE(A1:A12,ROW(1:9)) it is an array formula, so commit it with Ctrl-Shift-Enter. -- HTH Bob Phillips "Vance Burton" <Vance Burton@discussions.microsoft.com> wrote in message news:914AED28-4FC3-42EF-AB5A-D8943147F795@microsoft.com... > I need to keep track of the scores in a quiz for 12 weeks running, then total > each contestant's best 9 scores. > > Is ther...

Running Totals Algorithm
I'm not new to this but am looking for ideas on how best to handle this. I'd like to write an application that tracks account information for over 200 people. There will be charges and payments. But I need to be able to instantly report the running total, kind of like Microsoft Money and Intuit's Quicken do. My preference is not to use a database for this. I guess I can just store the running total so it does not need to be computed on the fly and just update each person's total when a new charge or payment is made. I just wondered if anyone else has done something like...

Showing Totals in a Data Table on a 100% Stacked Chart?
Does anyone know how to show totals in the data table for a 100% stacked chart? EggHeadCafe - Software Developer Portal of Choice Windows Forms .NET DataGrid ComboBox Columns And Custom Row Coloring http://www.eggheadcafe.com/tutorials/aspnet/65fa582e-e15c-48d1-a641-dfdad4ea0b4a/windows-forms-net-datagr.aspx Simulate the data table with a custom table Custom Chart Table http://www.tushar-mehta.com/excel/newsgroups/data_table/index.htm On Mon, 16 Nov 2009 12:11:11 -0800, Lauren McElaney wrote: >Does anyone know how to show totals in the data table for a 100% stacked chart? ...

how can I stop sub total rows appearing in pivot tables in excel
I am working in excel and I need to prevent sub total rows appearing in my pivot tables. Does any know if this is possible and if so how? Also, I am working in Access and I need to be able to have multiple columns headers (grouped) similar to what you can do in Excel, any ideas? Thanks for your help. Lee If you rightclick on the button like header in your pivottable, you can select "field settings". there's a "none" option for subtotals. If you don't get an answer for your access question, you may want to try an Access newsgroup. Lee wrote: > > I am wor...

Grouping Totals
I have a report that displays the data from a query I'm running. The report is grouped as follows Bin Number Skip Number Quantity Date Cost Now what I want to do is to display the total quantity and cost for each bin just before a new bin number is displayed i.e. something like Bin Number 12 Skip Number 23 Quantity Date Cost 132 15/06/07 =A35.23 12 15/06/07 =A38.23 TOTAL QUANTITY =3D 144 TOTAL COST =3D =A313.46 ------------------------------------------------ Bin Number 13 Skip Number 24 Quantity Date Cost 24...

Outlook 2007 keeps repeating/adding send tasks while sending/receiving
Hi all, I have Outlook 2007 installed on a XP pro PC. I have 1 account for a IMAP mailserver and a couple accounts for POP3 mailservers configured. Auto send/receive is disabled at startup (so I have to start in manually). Everything worked fine till some weeks ago. When I hit the send/receive button, all the configured accounts are added to the task list in the send/receive window. After all account are processed, all accounts are added to the task list again for sending, over and over again. I have a screenshot of that on http://www.raadselweb.net/temp/sendreceive.jpg Also, sometimes...

Total of page in report writer
How can i get the total of page to do for example Page 1 of 47 Hi Denis, Report writer cannot get the no of pages as Page 1 of 47. This is what i found in Knowledge Base Article. TechKnowledge Content Issue : Can I modify a report in Report Writer that will give Page Numbers like the following example: Page 1 of 3, Page 2 of 3, Page 3 of 3? Resolution No. Report Writer is a one pass reporting tool so this functionality is not available. Report Writer cannot tell what page the report will end on, therefore it is unable to print Page Numbers in this format. This article was TechKnow...

formula to add a figure to a total if conditions are.....
Hi, OK, obviously by the subject excel is not my strong point! ;-) What I would like please is the formula to do the following. If the total in cell A1 is under 750, add 80 to the figure and total it. Does that make sense? Thanks Tony hi, put this in B1.... =IF(A1<750,A1+80,A1) Regards FSt1 "Mr Offle" wrote: > Hi, > OK, obviously by the subject excel is not my strong point! ;-) > What I would like please is the formula to do the following. > > If the total in cell A1 is under 750, add 80 to the figure and total it. > > Does that make sense? > >...

having trouble creating chart to show monthly totals
Hi I'm using office XP. I keep a single spreadsheet were I record orders received. Its a very simple sheet with one order per row. In col B I place the date the order was received (in the format DD/MM/YY) I'd like to make a chart (pie or column) which displays the total number of orders per month so that I can see at a glance when were busy and quieter periods. I've tried using the wizard but all I ever get is a mess and not what I expect to see at all. I suspect I may need to separate my data by month? But is there a way for Excel to work that out for itself from a sin...