Totalling in subreport

[Kevin Witty]

I have a subreport of a main report which shows invoice payments made and
their allocations to different orders. It should look like this:

Customer
Group1 Header
Rec# Check# CheckDate Amount Allocated
1 1 5/1/04 100,00 100.00
Group 2 Header
1 25.00
Order 1 Detail
2 75.00
Order 9 Detail
3 2 5/3/04 75.00 75.00 Order 5
Group 2 Header
3 75.00
Order 2 Detail
4 3 5/6/04 200.00 150.00
Group 2 Header
4 100.00
Order 92 Detail
5
50.00 Order 177 Detail
6 4 5/6/04 200.00 200.00
Group 2 Header
6 100.00
Order 94 Detail
7 100.00
Order 123 Detail
Total 575.00 525.00
Group 1 Footer

Each record contains the check#, check date, check amount, total allocated
amount, and order allocation. I'm simply supressing the display of the data
in the detail records. I obviously can't use sum(Amount) to get the total
amount, since that would produce the sum of Amount in each record. I can set
report total fields to 0 at the Group 1 Header, but I can't add Group 2
numbers to them at Group 2 Header time because the Group Header might repeat
on a second page. So I thought I could do this at detail format time by
adding them only on the first record of each group 2 set of records.
HOWEVER, this doesn't work.

In the case I'm looking at, record 1 is the last record to appear on a page.
By using debug, I find that Access fires the Detail Format event as follows:

Total Amount
Record 1 25
Record 2 100
Record 1 (again) 125
Record 2 (again) 200
Record 3 275
Record 4 375
Record 5 425
Record 6 525
Record 7 625
Record 2-7 (again) 1125

Can anyone make ANY sense out of this, and tell me how I can get around
it????? Obviously the totals are completely out of whack. I suppose I could
put the details into a sub-report of the sub-report, but that seems
ridiculous. The main report already has two separate subreports, So I'd then
have

Main
Sub
Sub
Sub


Kevin (Hoping these columns don't get completely out of whack in the
formatting here)

 

[Sophie Guo [MSFT]]

Hello,

You can refer to the following article demonstrates how you can use a total
from a subreport on a main report, and how you can display a total for all
the subreports.

208835 ACC2000: How to Display and Total Subtotals from Subreports
http://support.microsoft.com/?id=208835

I hope the informaiton is helpful.

Sophie Guo
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Kevin Witty]

Hi, Sophie -

I'm afraid I didn't make myself clear. It's the subtotal FOR the subreport
I'm having trouble calculating. I know how to get a total FROM a subreport
onto a main report.

 

[Sophie Guo [MSFT]]

Hello,

To clarify the issue, you can send me a simple Access database and the
detail steps to reproduce the issue. The Access database just includes the
necessary table and report which are needed to reproduce the issue. I will
check the issue on my side and let you know the results.

Sophie Guo
(e-mail address removed)

Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Kevin Witty]

Sophie -

I replied to this a couple of days ago asking how to send you the file, but
apparently my reply got lost.

I finally gave up and did a sub-sub-report, but I'm still having trouble
understanding why (and getting around) the fact that Access will reprocess
subform records at Detail Format time. So if I use the oft-used routine of
accumulating a total at Detail Format time, I'll get the wrong total.

???

Thanks,

Kevin

 

[Sophie Guo [MSFT]]

Hello Kevin,

I am not entirely clear about the current situation. You can send me the
file and the detailed steps to reproduce the issue at (e-mail address removed).

I look forward to hearing from you.

Sophie Guo

Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.