total repeating subreport

[granola911 via AccessMonster.com]

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!

 

[Al Campagna]

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."

 

[Duane Hookom]

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 via AccessMonster.com]

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.

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]

.

 

[Duane Hookom]

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

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.

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]

.

--



.

 

[granola911 via AccessMonster.com]

Hi,

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

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]