Create a "running total" field in a query in Access so I can use .

I am trying to create a running total in an Access query. An example would
be to have a table that I entered Date, Time, Buy or Sell, Price, and Shares
for stocks. I would then want to create a query that would take this
informationa and keep a running total of the value of the stock, and on the
dates with "Sell" in the transaction field, the query would determine the
historic price of all shares bought and sold before that date, and calculate
the cost of the stock sold.

Hi,



SELECT a.pk, LAST(a.f1), LAST(a.f2), SUM(b.buy). SUM(b.sell)
FROM myTable As a INNER JOIN myTable As b
ON a.dateTime = b.dateTime
GROUP BY a.pk



where pk is the primary key, f1, f2 , .... fields you wish to get too.
SUM(b.buy) would produce the SUM of every thing you buy; the exact
expression can be SUM( b.qty * b.unitPriceBuy) , or even SUM(b.sell -
b.bought), or something like that, I can tell at this moment, for lack of
details.



Hoping it may help,
Vanderghast, Access MVP




"rhingomills" wrote in message
...
I am trying to create a running total in an Access query. An example would
be to have a table that I entered Date, Time, Buy or Sell, Price, and
Shares
for stocks. I would then want to create a query that would take this
informationa and keep a running total of the value of the stock, and on
the
dates with "Sell" in the transaction field, the query would determine the
historic price of all shares bought and sold before that date, and
calculate
the cost of the stock sold.