Need formula for report total

[brudderman]

I have a subreport (Access 2003) based on a subquery to list amounts (Amount) and cumulative totals (AmtYTD) given by individuals to political campaigns for specified reporting periods (usually monthly). For a given date range I will only show contributors who made a contribution in that period if their cumulative contributions to date (AmtYTD) is greater than $50. This looks and works fine so far.

But now I need to add a report footer and calculate the sum of Amount for all records returned by the query. So it will be summing only those in the specified date range whose cumulative (AmtYTD) is greater than $50.

I have added a text box in the report footer with =sum([Amount]) as the control source. It gives this error: Multi-lever GROUP BY is not allowed in a subquery.

The report runs OK without the text box. If I put the text box in the page footer, I just get #error.

Here is the SQL for the query:

SELECT b2.donor_ID, b2.TransactionDate, b2.amount, (SELECT SUM(Amount) FROM tblContributions as B1 WHERE (b1.TransactionDate<=b2.TransactionDate and b1.donor_id=b2.donor_id and b1.election=b2.election and b1.election_year=b2.election_year ) and b1.candidate=b2.candidate) AS AmtYTD, b2.election_year, b2.election
FROM tblDonors INNER JOIN tblContributions AS b2 ON tblDonors.ID = b2.donor_ID
WHERE (((b2.TransactionDate) Between #5/19/2004# And #6/30/2004#) AND (((SELECT SUM(Amount) FROM tblContributions as B1 WHERE (b1.TransactionDate<=b2.TransactionDate and b1.donor_id=b2.donor_id and b1.election=b2.election and b1.election_year=b2.election_year ) and b1.candidate=b2.candidate))>50) AND ((b2.election_year)=2004) AND ((b2.candidate) Like "*Baker"))
ORDER BY b2.TransactionDate;

What am I doing wrong???

Thanks!

Brudderman

 

[THWatson]

Brudderman
From your explanation, I gather that you are wanting to put a Sum in the main report footer, and there is a subreport involved.

If so, make a text box in the subreport footer, and then reference that in the report footer for your main report.

Hope that helps.

Tom

 

[brudderman]

Thanks, Tom!

>> I gather that you are wanting to put a Sum in the main report footer, and there is a subreport involved.

No, I want to put the Sum in the subreport footer, and that's where the error is coming in. If I can get that to work, I think I'll be OK.

Sorry I wasn't more clear.

Brudderman

 

[THWatson]

Brudderman
You should be able to add a text box to the subreport Footer that pulls out the total you want. I'm assuming that your subreport shows only the records where the [AmtYTD] > 50.

Your original post said you tried =Sum([Amount]). Is it the Amount that shows or [AmtYTD]? If it's the latter, then you will want to use =Sum([AmtYTD])


Tom

 

[brudderman]

Both Amount and AmtYTD show on the subreport. I just got it to work by using DSUM instead of SUM: =DSUM("Amount", "qryMoneyItemized").

I still don't know why the original SUM formula won't work; must have something to do with the nature of the query.

But I think I can get it from here.

Thanks!!

Brudderman