Amount in subreport

C

Christine

I have a subreport which spans several pages in a main report.

The first problem is that the amount in the first row of every page
from second page onwards are doubled from its original source report.

The second problem is that the subtotal (a footer in subreport) whic
happens to be on the first row of the pages will add the amount twic
for the last row of the previous page resulting incorrect subtotal.

I don't know what went wrong. How can I get a subreport which sho
exactly the same as its original source?

When I preview the original source report, everthing is fine. Anyon
can help
 
E

Evi

I don't quite catch your description. You can't think of a report in Pages
but in Groups.

By 'Amount' do you mean the number of records or a value in a field, or a
value produced by a calculation in a text box?

Is the subreport meant to be linked to the main report in some way?

Evi
 
M

Marshall Barton

Christine said:
I have a subreport which spans several pages in a main report.

The first problem is that the amount in the first row of every pages
from second page onwards are doubled from its original source report.

The second problem is that the subtotal (a footer in subreport) which
happens to be on the first row of the pages will add the amount twice
for the last row of the previous page resulting incorrect subtotal.

I don't know what went wrong. How can I get a subreport which show
exactly the same as its original source?
Click to expand...


It sounds like you're using code in the Detail event
procedure to accumulate the total. If so, it will not work.
For many reasons, report records are not processed in a
sequential order. You should use an aggregate function
(Count, Sum, etc) either in the report's query or in a
header/footer text box. Alternatively, you might be able to
use a RunningSum text box in the report to calculate totals.
 
C

Christine

Thanks both for your advice.

All that mentioned by you has been done before I noticed the incorrec
result in the first row of second page onwards. I really do not kno
what's wrong. Hereby I describe in more details:

Group levels = 5 group

Group By = Category; Group; Subgroup; Account Name; Date.

Fields in a record (By Query) = Date; Ref No; Category; Group
Subgroup; Account Name; Amount1; Amount2
Jan:IIf(Month([Date])=1,[Amount1]-[Amount2],0)
Feb:IIf(Month([Date])=2,[Amount1]-[Amount2],0)
Mar:IIf(Month([Date])=3,[Amount1]-[Amount2],0);...........Dec:IIf(Month([Date])=12,[Amount1]-[Amount2],0)

All the above results turns out fine in report at this stage.

Next step : Create Footer for Account Name with Running Sum of certai
fields in Detail Section (For Jan, Feb, Mar,....Dec).
Visible Property in Details Section = No
Result : OK

Next step : Create Footer for Group with Running Sum of all fileds i
Account Name Footer Section
Result : OK

Next step : Create Footer for Category with Running Sum of all field
in Group Footer Section
Result : Error Message "System Resource Exceeded", No repor
displayed.

At this stage, I deleted the last footer and the report displayed agai
with all the results displayed correctly.

Next step : I created another report (Main report) with one group leve
(Category) and insert the above report as subreport.
Result : Amount in first row of every pages (2nd page onwards) ar
diffrent from its original report resulting incorrect total. Al
others amount is correct. Group Subtotal which happens to be on th
first row of one of the page repeat adding the last row of previou
page resulting incorrect subtotal.

Really appreciate if you could help this final step in order for me t
complete the module and complete months of working on it.

Please help. Thanks
 
M

Marshall Barton

Christine said:
All that mentioned by you has been done before I noticed the incorrect
result in the first row of second page onwards. I really do not know
what's wrong. Hereby I describe in more details:

Group levels = 5 group

Group By = Category; Group; Subgroup; Account Name; Date.

Fields in a record (By Query) = Date; Ref No; Category; Group;
Subgroup; Account Name; Amount1; Amount2;
Jan:IIf(Month([Date])=1,[Amount1]-[Amount2],0);
Feb:IIf(Month([Date])=2,[Amount1]-[Amount2],0);
Mar:IIf(Month([Date])=3,[Amount1]-[Amount2],0);...........Dec:IIf(Month([Date])=12,[Amount1]-[Amount2],0)

All the above results turns out fine in report at this stage.

Next step : Create Footer for Account Name with Running Sum of certain
fields in Detail Section (For Jan, Feb, Mar,....Dec).
Visible Property in Details Section = No
Result : OK

Next step : Create Footer for Group with Running Sum of all fileds in
Account Name Footer Section
Result : OK

Next step : Create Footer for Category with Running Sum of all fields
in Group Footer Section
Result : Error Message "System Resource Exceeded", No report
displayed.
Click to expand...


I think that error could mean lots of things. On a Win9x
machine, it often meant you were running too many programs
at the same time, but it could also mean other things.

Looking at the report's record source query above where you
are already assigning the amounts to a calculated field for
each month. (I recommend that you use Null instead of 0
though.) That should make calculating the totals fairly
easy by using the Sum funtion instead of all those
RunningSum text boxes.

First make a copy of the report so you can restart if the
folowing stuff blows up on you. Then get rid of all the
RunningSum text boxes. Now, try using a text box for each
month in each group footer. Set the text boxes' control
source expression to:
=Sum(Jan)
=Sum(Feb)
etc

Each group footer may very well be just a copy/paste of the
first oner.
 
Top