From: granola911 via AccessMonster.com on
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
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
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
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
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
>
> .
>