Solved Calculations on Previous Record

[smtazulislam]

Hello,
I want a query to calculation previous total value into current record values.

My Record Details:
Record #1
Date: 01/02/2021
Cost - 105
TotalCost - 105

Record #2
Date: 01/02/2021
Cost - 15
TotalCost - 120

Record #3
Date: 02/02/2021
Cost - 115
TotalCost - 235

Thank you. Any help will appreciate.

 

[CJ_London]

use a dsum or subquery, but your table needs either your date to contain time information as well as the date or you need a field to specify order (might be a PK field). Otherwise there is no way to determine which of the first two records is first. Provide that information and ask for more help if you don't know how to use dsum or a subquery

 

[arnelgp]

select [id], [date], [cost], (select sum(T.[cost]) from yourTable T where T.[date] <= yourTable.[date] and T.[id] <= yourTable.id) as TotalCost
from yourTable order by [date], [id];

 

[CJ_London]

@arnelgp - that wont work every time (assuming OP has an id)

id....date.......................amt
1.....1/1/2021..............10
11...31/12/2020.........15

in this scenario, id 11 was posted after id 1


what is the total for the first record?

 

[arnelgp]

then, you are too lazy to post it on same day.
see, the number of transaction on 31/12/2020,
you only have 11 and you can't finished it on same day.

 

[smtazulislam]

select [id], [date], [cost], (select sum(T.[cost]) from yourTable T where T.[date] <= yourTable.[date] and T.[id] <= yourTable.id) as TotalCost
from yourTable order by [date], [id];

Its work. Thank you so much. I apreciated for nice job.
Can I get Monthly Cost Report,
Thanks again.

 

[smtazulislam]

Its work. Thank you so much. I apreciated for nice job.
Can I get Monthly Cost Report,
Thanks again.

Your formula I get report by DAY and TOTAL project cost.
Kindly help me to create Monthly cost report.

 

[CJ_London]

then, you are too lazy to post it on same day.

I was on holiday and when I got back, started with the records on top of the pile and worked backwards because I didn't know I had to put them in order first.

 

[gemma-the-husky]

The underlying point is that by trying to store a calculated figure within records in a database, be that a calculation relating just to the current record, or a calculation relating to other records, you run the risk that editing or deleting one of the records that produces the calculation result means that the stored result becomes incorrect. In many cases it is preferable not to store the calculation result, but to calculate it each time it is needed.

Therefore if you do decide to store a calculation you need to be careful to ensure it remains accurate.