Calculate Sum in Main Report of Sub-Report Sums

[gray]

Hi

Access 2002
Windows XPPro

I have a main report containing a sub-report. The sub-report contains textboxes of invoice payment stage values found for each customer (in a set period). The sub-report totals each customers' staged payments into an invoices total by using "=Sum(Nz([Stage_Value_Ex_VAT],0))" in a textbox on the sub-report's footer section. This works correctly.

In the main report I wish to have a "grand totals" textbox in the report's footer i.e. totalling all the sum textboxes of the sub-report's footer section. Is this possible as I seem to be having problems with the syntax even if I use the wizard?

Any hints suggestions much appreciated

Thanks

 

[pbaldy]

If you post what you've tried, we can fix it. The only problem people usually have is if a subreport returns no records. Here's a fix for that:

http://www.mvps.org/access/forms/frm0022.htm

 

[gray]

Hi

Thanks for your help... here are the relevant parts of my database attached....

 

[pbaldy]

2 ways come immediately to mind. First, a DSum or subreport that sums up those values for the chosen quarter. That's probably how I'd do it. Second, you could put a hidden textbox in the detail section that got the value from that record, and set the running sum property to Over All. Then in the report footer, refer to that textbox. Because some of the subreports are empty, you'd have to use something like the link I gave earlier.

 

[gray]

Cheers Paul ... I shall dabble over the next few days and post the results.. your help much appreciated...

 

[gray]

Hi

I tried the second method thus: placed a textbox in the detail section of my sub-report. This is set to a running sum of the staged payment textbox(es) in the same detail section. This adds up all the staged payments for a single invoice very nicely. I placed a textbox in my main report footer and set its Control Source to the name of the above running sum textbox. Unfortunately, all it does is show the totals for the staged payments of the last invoice in the sequence rather than the totals for all the staged payments for all invoices. I tried also setting that to a running sum (over all) but it was the same result.

I was wondering if a Report has a recordset rather like a Form? If so I coud use those to sum up the values I need. I tried timkering with me.recordset in the code of the report but it does not seem to recognise it - I get Compile error Invalid Outside Procedure?

thanks

 

[pbaldy]

I meant for the textbox to be in the detail section of the main report, not the subreport. You already had the total of the subreport. As I mentioned, you could also use a DSum or totals query on the recordsource.

 

[gray]

Hi Paul

In the end I decided to take your advice and use a totals query on the record source. Took me a little while to figure it out but I am there now... Thanks again for your assistance.

For those of you struggling with a similar problem, here's how I got mine to work :-

--------------------------------------------------------------------
Dim SQLLine As String
Dim rsRead1 As New ADODB.Recordset
Dim rsRead2 As New ADODB.Recordset
Dim Tax_Total As Currency

SQLLine = "SELECT yourField FROM yourTable1 WHERE yourField1 = " & yourCriteria1

RsRead.Open SQLLine, CurrentProject.AccessConnection, adOpenForwardOnly, adLockOptimistic
Do Until RsRead1.EOF
RsRead2.Open "SELECT SUM(yourOtherField) AS your_Sum FROM yourTable2 WHERE " _
& yourTable2_ID = " & RsRead1!yourTable1_ID, CurrentProject.AccessConnection, adOpenForwardOnly, adLockOptimistic
If Not RsRead2.EOF Then
Tax_Total = Tax_Total + RsRead2!your_Sum
End If
RsRead2.Close
Loop
rsRead1.close

yourTextbox = Tax_Total

----------------------------------------------------------------------------------------------
Cheers