Create column using previous value

[elenasbeatrice]

Hello.

I want to create a "Balance" column, exactly like in excel, in which if TYPE = WITHDRAW, BALANCE = PREVIOUS BALANCE (from the same "BALANC" column) - VALUE FROM CASH, if TYPE = SUPPLY; PREVIOUS BALANCE (from the same "BALANCE" column) + CASH VALUE.

[Bob Fitz]

You will need a field (column) in the query by which the query can be sorted. Perhaps one that records the date and time of each transaction. Do you have such a field.

BTW "Type" is a reserved work in Access and should not be used as field name.

[CJ_London]

There is no concept in databases for Previous or Next without an order, and not possible reference a previous calculated value

So you need to provide an order - might be datetime (date on it's own won't work unless you only ever have one record per day), might be a PK (but risk of issues if entries not entered in the correct order).

but the principle would be to use a subquery - so your query might look like this if using datetime.

SELECT A.DateTime, A.TranType, A.TranValue, (SELECT sum(TranValue*iif(Trantype='SUPPLY',1, -1)) as BALANCE FROM tblTrans B WHERE B.DateTime<=A.DateTime) AS BALANCE
FROM tblTrans A
ORDER BY A.DateTime

The subquery is this part

(SELECT sum(TranValue*iif(Trantype='SUPPLY',1, -1)) as BALANCE FROM tblTrans B WHERE B.DateTime<=A.DateTime) AS BALANCE

[elenasbeatrice]

What about an index column? It is possible with this column? Because i have a lot of transactions in one day.

[elenasbeatrice]

Yes, i have an index column because i have a date and time column but i have more than one transaction per day.

[CJ_London]

If the index column provides the right order, use that

[elenasbeatrice]

Can you write me the correct code with the index column? I m begginer in Access and SQL

[CJ_London]

Can you write me the correct code with the index column?

see post #3, just change names to suit