Updating Table's Running Sum?

[(PeteCresswell)]

I've got a table of transactions in which a running sum is kept for performance
reasons.

i.e.
tblTrade.SharesNetBalance = Net Of All Trades (buys/sells) for a given tranche.


Users want to be able to delete trades (instead of just entering an opposing
trade of the same amount).

This means that, when a trade is deleted, I need to recompute all those net
balances.


Started writing a lot of VBA code to determine the last trade before the deleted
one, iterate through the table computing running sums on the fly and so-forth.

Then I recalled some SQL that somebody gave me that computed a relative line
number in the result set. Sounds like a running sum to me....

So, can I concoct an Update query that will iterate though a recordset and
compute a running sum? Seems like that might be faster - given the nature of
SQL - even though we'd be doing all records for a given tranche instead of a
subset based on the deleted trade.

 

[Guest]

Recording a calculated value in the table is a bad idea from the get-go.
Just use a totals query to sum the transactions.

As for as your users do not they need a history of the transactions? If
they delete records the history is lost. Are all negative transaction
identical to the positive ones? Sems unlikely.

 

[(PeteCresswell)]

Per KARL DEWEY:

Recording a calculated value in the table is a bad idea from the get-go.
Just use a totals query to sum the transactions.

Understood.... but in this case it's a performance issue. Re-calculating
on-the-fly brings the screen to it's knees.

 

[(PeteCresswell)]

Per (PeteCresswell):

Understood.... but in this case it's a performance issue. Re-calculating
on-the-fly brings the screen to it's knees.

OTOH, with the running sum built into a query, maybe the performance issue would
go away... It's starting to sound like my initial decision to denormalize the
sums was based more on lack of imagination/expertise than anything else.

SO:... can anybody offer up some sample SQL?

On occasion, I've implemented something like what's below to create row numbers,
but I don't fully understand how it works and in come cases it falls apart: