Generate Begin_Balance based on previous Ending_Balance

J

jsccorps

I have the daily sales. I need to create a report based on a range of dates.
Similar to the following

Date Begin_Balance Sales EndBalance
9/1/04 100 20 120
9/2/04 120 40 160
9/3/04 160 -50 110
etc.

criteria: Between [Start Date] and [End Date]

Is there a standard query method or algorithm for doing this?
 
M

Michel Walsh

Hi,



SELECT a.Date, SUM(a.Sales) As Sales,
(SELECT SUM(b.Sales)
FROM myTable As b
WHERE a.Date >= b.Date
AND b.Date >= StartingDate) AS EndBalance

FROM myTable As a

WHERE a.Date BETWEEN StartingDate AND EndingDate

GROUP BY a.Date




Remove the AND b.Date >= StartingDate in the sub-query, if the
Begin_Balance is not zero.




Hoping it may help,
Vanderghast, Access MVP
 
Top