Balance Forward

[Dani2009]

How can I show a previous balance on a report? I need to print quarterly
statements for customers that show the previous balance as a beginning
balance for the new quarter and the new transactions.
My table fields are as follows:

TransactionID
TransactionDate
TransactionAmount
TransactionDistrictID

I did the aggregate sum over the group, but I can't reference the ending
balance of the last quarter.

 

[John Spencer]

You could try using a calculated column such as the following.

(SELECT SUM(TransactionAmount)
FROM SomeTable as Temp
WHERE TransactionDate <= DateSerial(YEar(Date(),(Month(Date())-1)\3 * 3,0)
and Temp.TransactionDistrictID = SomeTable.TransactionDistrictID) as PriorBalance

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Dani2009]

I tried to follow the steps, I created a query with the SQL indicated, but
when I connect the query with the report, I get a message that says
"Multi-Level GROUP BY clause is not allowed in a subquery"

 

[John Spencer]

You could use DSum function.

DSUM("TransActionAmount","SomeTable","TransactionDate<=DateSerial(YEar(Date()),((Month(Date())-1)\3)
* 3,0) AND TransactionDistrictID =" & [TransactionDistrictID])

My original response had errors in the DateSerial calculation, missed some
parentheses that were necessary.

DateSerial(Year(Date()),((Month(Date())-1)\3) * 3,0)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Dani2009]

That worked great!!! Thank you so much

You could use DSum function.

DSUM("TransActionAmount","SomeTable","TransactionDate<=DateSerial(YEar(Date()),((Month(Date())-1)\3)
* 3,0) AND TransactionDistrictID =" & [TransactionDistrictID])

My original response had errors in the DateSerial calculation, missed some
parentheses that were necessary.

DateSerial(Year(Date()),((Month(Date())-1)\3) * 3,0)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I tried to follow the steps, I created a query with the SQL indicated, but
when I connect the query with the report, I get a message that says
"Multi-Level GROUP BY clause is not allowed in a subquery"

.