Strange Cumulative in Query Results

[Jackson via AccessMonster.com]

Hi,

I'm trying to use a subquery to do a running sum of Qty for a query. Here is
sample data:

OrderID FillID TradeDate Account Security BS Qty Price
2 132 23-Mar-09 A ERM9 S 17 102
2 133 23-Mar-09 A ERM9 S 36 105
2 134 23-Mar-09 A ERM9 S 12 108
2 135 23-Mar-09 B ERM9 S 10 104
2 136 23-Mar-09 B ERM9 S 23 103
2 137 23-Mar-09 B ERM9 S 7 106

My SQL to try and get a running sum is:

SELECT qryAllocationsRolledUp.OrderID, qryAllocationsRolledUp.FillID,
qryAllocationsRolledUp.TradeDate, qryAllocationsRolledUp.Account,
qryAllocationsRolledUp.Ticker, qryAllocationsRolledUp.BS,
qryAllocationsRolledUp.Qty, qryAllocationsRolledUp.Price,
(SELECT SUM(qryAllocationsRolledUp.Qty) FROM qryAllocationsRolledUp Q1 WHERE
Q1.Account = qryAllocationsRolledUp.Account AND Q1.Ticker =
qryAllocationsRolledUp.Ticker AND Q1.FillID<=qryAllocationsRolledUp.FillID)
AS CumQty
FROM qryAllocationsRolledUp
ORDER BY qryAllocationsRolledUp.OrderID, qryAllocationsRolledUp.Account,
qryAllocationsRolledUp.FillID;

However that gives strange results. What I'm trying to do is get a running
sum of by account and Ticker (ticker's all the same above anyway) of the Qty
column. Any ideas where I'm going wrong?

Thanks.

 

[Lord Kelvan]

well you are missing an as in your query i have no idea what ticker is

i am not sure what you are looking for anyways

are you trying to find a running total for a certian account or a
certian account in a certian order

this should give you a running total for a certian account

SELECT qryAllocationsRolledUp.OrderID, qryAllocationsRolledUp.FillID,
qryAllocationsRolledUp.TradeDate, qryAllocationsRolledUp.Account,
qryAllocationsRolledUp.Ticker, qryAllocationsRolledUp.BS,
qryAllocationsRolledUp.Qty, qryAllocationsRolledUp.Price,
(SELECT SUM(qryAllocationsRolledUp.Qty) FROM qryAllocationsRolledUp AS
Q1 WHERE
Q1.Account = qryAllocationsRolledUp.Account AND Q1.FillID <
qryAllocationsRolledUp.FillID)
AS CumQty
FROM qryAllocationsRolledUp
ORDER BY qryAllocationsRolledUp.OrderID,
qryAllocationsRolledUp.Account,
qryAllocationsRolledUp.FillID;

this should give you the running total based on a certian account for
a certian order

SELECT qryAllocationsRolledUp.OrderID, qryAllocationsRolledUp.FillID,
qryAllocationsRolledUp.TradeDate, qryAllocationsRolledUp.Account,
qryAllocationsRolledUp.Ticker, qryAllocationsRolledUp.BS,
qryAllocationsRolledUp.Qty, qryAllocationsRolledUp.Price,
(SELECT SUM(qryAllocationsRolledUp.Qty) FROM qryAllocationsRolledUp AS
Q1 WHERE
Q1.Account = qryAllocationsRolledUp.Account and q1.orderid =
qryAllocationsRolledUp.orderid AND Q1.FillID <
qryAllocationsRolledUp.FillID)
AS CumQty
FROM qryAllocationsRolledUp
ORDER BY qryAllocationsRolledUp.OrderID,
qryAllocationsRolledUp.Account,
qryAllocationsRolledUp.FillID;

regards
Kelvan

 

[Jackson via AccessMonster.com]

Thanks Kelvan,

The query was being based on a prior query which was group and summing totals
already which I think was responsible for the bad results. When I went back
and added the subquery to the original query which was based on a table I got
it working.

Regards,
Jackson