Total in subreport... 1

[ponderdj]

Hello all,

I have created a report that uses 6 subreports. Each of the subreports contains 2 columns that I total at the end of the subreport. I used this code to find the totals:

Option Compare Database
Option Explicit
Public PageSum As Double
Public PageSum2 As Double

Private Sub Detail_Print(Cancel As Integer, FormatCount As Integer)
   PageSum = PageSum + Reports![rptSubIPR]![MRC]
   PageSum2 = PageSum2 + Reports![rptSubIPR]![CountOfPDC]
End Sub


Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
   ' reset the counter for each new page
   PageSum = 0
   PageSum2 = 0
End Sub

If I open the subreport by itself, everything works fine, but once I open the big report, I get the error:
"Run-time error '2451':

The Report Name 'rptSubIPR' you enetered is misspelled or refers to a report that isn't open or doesn't exist."

-I am positive, however, that the report exists.

Any ideas?

 

[dhookom]

I don't understand why you are using code to create sums in your report (subreport) footers. I would have expected to see code in the On Page Header event but there seems to be none.

Your code would need to be changed to something like:

   PageSum = PageSum + Reports![rptMainReport]![rptSubIPR].Report![MRC]
   PageSum2 = PageSum2 + Reports![rptMainReport]![rptSubIPR].Report![CountOfPDC]

Again, I don't know why you are using code to sum values.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ponderdj]

I am finding the total in the REPORT(subreport) footer because Access does not show subreport PAGE headers or footers, unless I am mistaken. There may be an easier way to do this, and if so please let me know - I just found this solution and went with it.

 

[dhookom]

You are correct about Page sections not showing in subreports. I would think you could just add controls to the subreport's Report Footer section with control sources of:
=Sum(MRC)
and
=Sum(CountOfPDC)
This assumes MRC and CountOfPDC are fields in the Record Source of the subreport.

If you want to get these totals in your main report, use:
=rptSubIPR.Report.txtSumOfMRC (or whatever your control name is)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ponderdj]

-I'm not sure if there would be an easier work around, but changing my code by using your suggestion is now working.

Thank You!

If you don't mind, I have another question:
I orginally tried to add =Sum(MRC)and =Sum(CountOfPDC) but received the error:
"Multi-Level GROUP BY clause is not allowed in a subquery."
Any idea if there would be an easier solution than the one I came up with?

 

[dhookom]

I would need to see the SQL view of your subreport's record source since this would be the cause of your error "Multi-Level...".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ponderdj]

I wanted to return records where [selling program]= TRNB
and count the number of transactions between each buyer with CountOfPDC. I'm pretty sure there is a more efficient way of doing this, but once again..it worked.

Well, here it is:

SELECT BaseLine1.[Buying Program], BaseLine1.[Selling Program], (SELECT Count([CSA]) FROM tblISCCSAsbaseline WHERE [Buying Program]=BaseLine1.[Buying Program] And [Selling Program]="TRNB") AS CountOfPDC, (SELECT SUM([Total MRC (Baseline)]) FROM tblISCCSAsbaseline WHERE  [Selling Program]="TRNB" AND BaseLine1.[Buying Program]=[Buying Program]) AS MRC, (SELECT SUM([Total MRC (Baseline)]) FROM tblISCCSAsbaseline WHERE  [Selling Program]="TRNB") AS Total, (SELECT SUM([October MRC]) FROM tblISCCSAsbaseline WHERE Baseline1.[Buying Program] =tblISCCSAsbaseline.[Buying Program] AND [Selling Program]="TRNB") AS [October 04], (SELECT SUM([November MRC]) FROM tblISCCSAsbaseline WHERE Baseline1.[Buying Program] =tblISCCSAsbaseline.[Buying Program] AND [Selling Program]="TRNB") AS [November 04], (SELECT SUM([December MRC]) FROM tblISCCSAsbaseline WHERE Baseline1.[Buying Program] =tblISCCSAsbaseline.[Buying Program] AND [Selling Program]="TRNB") AS [December 04], (SELECT SUM([January MRC]) FROM tblISCCSAsbaseline WHERE Baseline1.[Buying Program] =tblISCCSAsbaseline.[Buying Program] AND [Selling Program]="TRNB") AS [January 05], (SELECT SUM([February MRC]) FROM tblISCCSAsbaseline WHERE Baseline1.[Buying Program] =tblISCCSAsbaseline.[Buying Program] AND [Selling Program]="TRNB") AS [February 05], (SELECT SUM([March MRC]) FROM tblISCCSAsbaseline WHERE Baseline1.[Buying Program] =tblISCCSAsbaseline.[Buying Program] AND [Selling Program]="TRNB") AS [March 05], (SELECT SUM([April MRC]) FROM tblISCCSAsbaseline WHERE Baseline1.[Buying Program] =tblISCCSAsbaseline.[Buying Program] AND [Selling Program]="TRNB") AS [April 05], (SELECT SUM([May MRC]) FROM tblISCCSAsbaseline WHERE Baseline1.[Buying Program] =tblISCCSAsbaseline.[Buying Program] AND [Selling Program]="TRNB") AS [May 05], (SELECT SUM([June MRC]) FROM tblISCCSAsbaseline WHERE Baseline1.[Buying Program] =tblISCCSAsbaseline.[Buying Program] AND [Selling Program]="TRNB") AS [June 05], (SELECT SUM([July MRC]) FROM tblISCCSAsbaseline WHERE Baseline1.[Buying Program] =tblISCCSAsbaseline.[Buying Program] AND [Selling Program]="TRNB") AS [July 05], (SELECT SUM([August MRC]) FROM tblISCCSAsbaseline WHERE Baseline1.[Buying Program] =tblISCCSAsbaseline.[Buying Program] AND [Selling Program]="TRNB") AS [August 05], (SELECT SUM([September MRC]) FROM tblISCCSAsbaseline WHERE Baseline1.[Buying Program] =tblISCCSAsbaseline.[Buying Program] AND [Selling Program]="TRNB") AS [September 05]
FROM tblISCCSAsbaseline AS BaseLine1
GROUP BY BaseLine1.[Buying Program], BaseLine1.[Selling Program]
HAVING ((BaseLine1.[Selling Program])="TRNB");

 

[ponderdj]

dhookom,

It seems my solution was not as much of a solution as I once thought. For some reason, it works fine after I design and test it, but if I close the database and re-open it I receive the "Multi-level Group By..." error again, even though I am using code to find the sum. Any ideas on what to do?

 

[dhookom]

I would attempt to remove the subqueries in favor of multiple totals queries that get right-joined to your main table.

Fields with names like "December MRC" suggests un-normalized data. You might have a good reason but it isn't evident.

One thing you can do is to create a Make Table query based on your above query. Then use the "made" table as your record source.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ponderdj]

Yep, normalization was the problem. I kind of knew it would be, but wanted to avoid restructuring the whole DB that I inherited. Almost got away with it. Thanks.