Main-sub total

[rob p]

I have some main-subreport ques. Main has a variable amount of detail
records each ending with an amount in the last field. I want to list all of
these and then show the total of the amount field.

I then want a subreport to pick up where the main quits. It will have a
variable amount of detail records too. Also an amount in the last field.
Totalled for subreport.

Grand total is total of the amount field for main and subreport.

Is there a sample report that does this? I am having a problem figuring out
where to insert the sub and how to total between main and sub.

thanks.

 

[Allen Browne]

1. Open the subreport (from the database window) in design view.

2. If you do not see a Report Footer section, choose "Report Header/Footer"
from the View menu.

3. In the Report Footer section, place a text box. Right-click it and choose
Properties. Set these properties:
Name txtTotAmount
Control Source =Sum([Amount])
Format Currency

4. Save and close the subreport.

5. Open the main report in design view.

6. Below the subreport, add a text box with this Control Source:
=IIf([Sub1].[Report].[HasData], Nz([Sub1].[Report].[txtTotAmount], 0), 0)

Replace "Sub1" in that expression with the name of your subreport control.

This brings the total from the subreport back onto the main report. If there
happen to be no records in the subreport, attempting to read to total of the
non-existent records produces an error. That's why we test the HasData
property of the report in the subreport control.

You can now add in the total from the main report as well.

 

[Adriana]

Allen,
I tried this and it works well in most cases but here's a stumper. My
subreport is linked to detail items in my main report. The main report
prints many detail items. By the time I get to the total section on my
main report the subreport for the detail items has opened and closed
many times, therefore in the total section it perceives that the
subreport has no data even though above it did have data for some
detail items and displays it. I've been trying to trap the amount but
can't seem to bring it to the totals level.
An example to illustrate:
Main Detail: Contract1 $50,000.00
Subreport: Change Order1 -$ 1,000.00
Subreport: Change Order2 -$ 1,100.00
Main Detail: Contract2 $25,000.00
Main Detail: Contract3 $20,000.00
Detail Footer: Total Contracts: $95,000.00

The footer should be $92,900.00
I found that at the contract2 level the subreport still shows as
having data but once it reaches contract3 the subreport shows no data.
So at the total it has no data. You can't really
Sum(iif(subreportX.hasdata...) at the total level. Any ideas?

Thanks in advance,
Adriana

 

[Allen Browne]

Try setting the RunningSum property of the text box on the main report.

The idea is that it contiues to sum any new amounts from the subreport, so
it builds a progressive total.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Allen,
I tried this and it works well in most cases but here's a stumper. My
subreport is linked to detail items in my main report. The main report
prints many detail items. By the time I get to the total section on my
main report the subreport for the detail items has opened and closed
many times, therefore in the total section it perceives that the
subreport has no data even though above it did have data for some
detail items and displays it. I've been trying to trap the amount but
can't seem to bring it to the totals level.
An example to illustrate:
Main Detail: Contract1 $50,000.00
Subreport: Change Order1 -$ 1,000.00
Subreport: Change Order2 -$ 1,100.00
Main Detail: Contract2 $25,000.00
Main Detail: Contract3 $20,000.00
Detail Footer: Total Contracts: $95,000.00

The footer should be $92,900.00
I found that at the contract2 level the subreport still shows as
having data but once it reaches contract3 the subreport shows no data.
So at the total it has no data. You can't really
Sum(iif(subreportX.hasdata...) at the total level. Any ideas?

Thanks in advance,
Adriana


"Allen Browne" <[email protected]> wrote in message

1. Open the subreport (from the database window) in design view.

2. If you do not see a Report Footer section, choose "Report Header/Footer"
from the View menu.

3. In the Report Footer section, place a text box. Right-click it and choose
Properties. Set these properties:
Name txtTotAmount
Control Source =Sum([Amount])
Format Currency

4. Save and close the subreport.

5. Open the main report in design view.

6. Below the subreport, add a text box with this Control Source:
=IIf([Sub1].[Report].[HasData], Nz([Sub1].[Report].[txtTotAmount], 0), 0)

Replace "Sub1" in that expression with the name of your subreport control.

This brings the total from the subreport back onto the main report. If there
happen to be no records in the subreport, attempting to read to total of the
non-existent records produces an error. That's why we test the HasData
property of the report in the subreport control.

You can now add in the total from the main report as well.

 

[Adriana]

Worked like a charm. Thanks!