total repeating subreport
in [Reports]
|
From: granola911 via AccessMonster.com on 26 Dec 2009 11:01 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
From: Al Campagna on 26 Dec 2009 16:22 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(a)uwe> wrote in message news:a12bed2ce0373(a)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 >
From: Duane Hookom on 26 Dec 2009 23:47 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(a)uwe> wrote in message > news:a12bed2ce0373(a)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 > > > > > . >
From: granola911 via AccessMonster.com on 27 Dec 2009 09:23 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
From: Duane Hookom on 27 Dec 2009 19:04 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 > > . >
|
Next
|
Last
Pages: 1 2 Prev: Background picture for the whole page (incl. margins) Next: IIf - Sum Problem |