Running Sum - Error

[Jackson via AccessMonster.com]

Hi,

I had a running sum calculated field working in a query recently but tried to
reconstruct for the following type of table (have a query first to arrange
the data called qryCumulative, query where I'm trying to add the cumulative
field is qryCumulative2)

ID TradeDate Security B/S Qty
1 01/03/07 ABC B 10
2 02/03/07 ABC S -5

Thus I want for each security to create a running sum field, in the above the
values would be 10 and then 5...

Here is my SQL:
SELECT qryCumulative.ID, qryCumulative.TradeDate, qryCumulative.Security,
qryCumulative.[B/S], qryCumulative.Qty, (SELECT Sum(qryCumulative.Qty) From
qryCumulative As X Where qryCumulative.Security = X.Security and
qryCumulative.TradeDate<=X.TradeDate and qryCumulative.ID <= X.ID) AS
Cumulative
FROM qryCumulative
ORDER BY qryCumulative.TradeDate, qryCumulative.ID;

However the Cumulative field is not calculating correctly...any ideas? IDs
may be bigger on earlier trade date so trade date is the first factor...

Thanks.

 

[omocaig via AccessMonster.com]

seems like you don't need the qryCumulative.ID <= X.ID in your sub-query
because the running sum is independent of the ID, as you said "IDs may be
bigger on earlier trade date..."

Date alone should produce the correct result, only issue being that if you
have trades on the same date, if you do I would drop the ID from the sort and
add qty in to both the inner and outer query. Like this (air code):

SELECT
ID
, TradeDate
, Security
, [B/S]
, Qty
, (
SELECT Sum(x.Qty)
From qryCumulative As X
Where qryCumulative.Security = X.Security
and qryCumulative.TradeDate<=X.TradeDate
and qryCumulative.Qty <= X.Qty
) AS Cumulative
FROM qryCumulative
ORDER BY TradeDate, Security, Qty;

hth,
Giacomo