Show the balance after each record

[krester]

Hi all,
In a table – tblOrders, there is field "expense ".
I would like to show expenses accountancy, for example:​

order_id date expense balance (expenses accountancy)

1 01/01/2008 32 32​

2 05/01/2008 75 107

3 15/01/2008 100 207

4 18/01/2008 12 219​

Usually I will show the records between chosen dates, while the balance consider the records above,for example:​

the records between 04/01/2008 and 17/01/2008


order_id date expense balance (expenses accountancy)
2 05/01/2008 75 107

3 15/01/2008 100 207​

(I think that in such circumstances I cant use the running sum in a report).
Thanks in advance,
krester ​

 

[jal]

Hi all,

In a table – tblOrders, there is field "expense ".
I would like to show expenses accountancy, for example:​

order_id date expense balance (expenses accountancy)

1 01/01/2008 32 32​

2 05/01/2008 75 107

3 15/01/2008 100 207​

4 18/01/2008 12 219​

Usually I will show the records between chosen dates, while the balance consider the records above,for example:​

the records between 04/01/2008 and 17/01/2008


order_id date expense balance (expenses accountancy)
2 05/01/2008 75 107

3 15/01/2008 100 207​

(I think that in such circumstances I cant use the running sum in a report).
Thanks in advance,

krester ​

Try this:

SELECT t1.*, (SELECT sum(t2.expense) FROM tblOrders as t2 WHERE t2.OrderID <= t1.OrderId)
as Running_Total
FROM tblOrders as t1
ORDER BY t1.OrderID

 

[krester]

Thank you Jal – it's working! ​

 

[krester]

Hi Jal,
Thank you again for your code, I like the way you solved it.

Usually it shows for each record the balance, except for the records from the same date. In this situation – it's summed all the expenses from the same day, and show the same balance for each record for the same day. For example:
1 01/05/2008 50 50
2 04/05/2008 30 80
3 06/05/2008 20 130
4 06/05/2008 30 130​

What's wrong ? ​