Solved Calculations on Previous Record (1 Viewer)

smtazulislam

Member
Local time
Today, 08:29
Joined
Mar 27, 2020
Messages
806
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

Super Moderator
Staff member
Local time
Today, 05:29
Joined
Feb 19, 2013
Messages
16,553
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

..forever waiting... waiting for jellybean!
Local time
Today, 13:29
Joined
May 7, 2009
Messages
19,169
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

Super Moderator
Staff member
Local time
Today, 05:29
Joined
Feb 19, 2013
Messages
16,553
@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

..forever waiting... waiting for jellybean!
Local time
Today, 13:29
Joined
May 7, 2009
Messages
19,169
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

Member
Local time
Today, 08:29
Joined
Mar 27, 2020
Messages
806
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.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 05:29
Joined
Feb 19, 2013
Messages
16,553
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:29
Joined
Feb 19, 2002
Messages
42,981
1. This is a linier process and doesn't belong in a query.
2. For it to be done in a query, you must identify a set
3. The set in this case is the number of records < something.
4. arne assumed the data would be input in order and so the autonumber would work. If that is not the case, then you need some other UNIQUE field that will always be < the current record.
5. If you don't have one, you cannot do this in a query. Period.

WHRE do you want to see this? If it is in the report, do it there. The process is trivial and works on the sort order of the detail items; which again will NOT work unless you have a unique identifier that will sort the records in the order in which you want them accumulated.

Relational tables are not flat. They do not have a fixed order. When you want to do something that requires a fixed order, such as this, you need a unique identifier which can be relied on to enforce the order you need.

If the output is a report, it is far more efficient to do this in the report using the Running Sum property on the Data tab.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 05:29
Joined
Sep 12, 2006
Messages
15,614
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.
 

Users who are viewing this thread

Top Bottom