Balance Forward

  • Thread starter Thread starter Dani2009
  • Start date Start date
D

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.
 
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
 
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"
 
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
 
That worked great!!! Thank you so much

John Spencer said:
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"
Click to expand...
.
Click to expand...
 
Back
Top