quiry adding another field calculated from the existing fields

[aa]

if I have a table of transactions on a bank account with two fileds:
Field_1 holds the date of the transaction
Field_2 holds the amount of the transaction

What would be the quiry which would add the third filed Field_3 containing
the balance on the account which is calculated as the value from the
previous line of Field_3 less Filed_2 from the current line?

 

[Ken Sheridan]

You've no need to store the balances in a column in the base table, nor
should you do so as it introduces redundancy and the risk of inconsistent
data.

The balances can be computed on the fly in query like so:

SELECT Field_1, Field_2, (SELECT SUM(Field_2)
FROM Field_2s As T2
WHERE T2.Field_1 <= T1.Field_1) AS Balance
FROM Transactions AS T1
ORDER BY Field_1;

This will return the end-of-day balance if there are multiple transactions
per day and Field_1 records the date only, not the unique date and exact time
of day per transaction. If the former, and the table contains a primary key
column, TransactionID say, then the balances per transaction can be returned
like so:

SELECT Field_1, Field_2, (SELECT SUM(Field_2)
FROM Field_2s As T2
WHERE T2.Field_1 <= T1.Field_1
AND ( T2.TransactionID <= T1.TransactionID
OR T2.Field_1 <> T1.Field_1)) AS Balance
FROM Transactions AS T1
ORDER BY Field_1, TransactionID;

Ken Sheridan
Stafford, England

 

[aa]

Thank you very much.