Trouble Creating a "Moving Average" Field in Query

[Steve in Wisconsin]

Hi,

I have a table with stock prices in it. One "Close" price
per day for approximately 1000 days (hence 1000 records).
I have created a query in which I want to display each
days "Close" as well as a calculated value for the average
of the last 20 prior days from each records closing date.

It seems simple enough in concept and I have tried using
the DAVG() function but failed because I couldn't find a
way to reference only the 20 records prior to the current
record.

Any suggestions would be truly appreciated.

Thanks!

 

[Michel Walsh]

Hi,

You need to refer to the table twice.

SELECT a.TheDate, AVB(b.amount)
FROM myTable As a INNER JOIN myTable As b
ON (b.TheDate BETWEEN a.TheDate AND a.TheDate-20)
GROUP BY a.TheDate
ORDER BY a.TheDate


If the average is "by stockID", then, it is better trying:


SELECT a.TheDate, a.StockID, AVB(b.amount)
FROM myTable As a INNER JOIN myTable As b
ON (a.StockID=b.StockID)
AND
(b.TheDate BETWEEN a.TheDate-20 AND a.TheDate)
GROUP BY a.TheDate, a.StockID
ORDER BY a.TheDate, a.StockID



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


You would need a TOP and a subquery, something like:


SELECT a.theDate, a.StockID, AVG(b.Amount)

FROM myTable As a INNER JOIN myTable As b
ON a.StockID=b.StockID

WHERE b.TheDate IN(
SELECT TOP 10 c.TheDate
FROM mytable As c
WHERE c.StockID=a.StockID AND
c.TheDate <= a.TheDate
ORDER BY c.TheDate DESC )

GROUP BY a.theDate, a.StockID
ORDER BY a.theDate, a.StockID




Hoping it may help,
Vanderghast, Access MVP

I appreciate your response. I have considered using the
date field but I run into problems when some stocks will
(for numerous reasons) not trade for a few random days. So
using any date function really is something I have to be
careful to stay away from.

Do you hav eany sggestions as to how I can reference the
previous 20 records from any point in my list?

Can I create a calculated field in a query design grid
that every time the query is run it generates a
recordnumber that can be referenced in the same query?

i.e. DAVG("[ClosePrice]","myQuery", "[RecID] >= ([RecID]-
19)")

Thanks.

-----Original Message-----
Hi,

You need to refer to the table twice.

SELECT a.TheDate, AVB(b.amount)
FROM myTable As a INNER JOIN myTable As b
ON (b.TheDate BETWEEN a.TheDate AND a.TheDate- 20)
GROUP BY a.TheDate
ORDER BY a.TheDate


If the average is "by stockID", then, it is better trying:


SELECT a.TheDate, a.StockID, AVB(b.amount)
FROM myTable As a INNER JOIN myTable As b
ON (a.StockID=b.StockID)
AND
(b.TheDate BETWEEN a.TheDate-20 AND a.TheDate)
GROUP BY a.TheDate, a.StockID
ORDER BY a.TheDate, a.StockID



Hoping it may help,
Vanderghast, Access MVP



"Steve in Wisconsin"


.

 

[Michel Walsh]

Hi,


Well, it is TOP 20 in your case, not TOP 10 ... silly me.


Vanderghast, Access MVP

 

[Steve in Wisconsin]

Thanks Michel, I'll give it a try!

-----Original Message-----
Hi,


Well, it is TOP 20 in your case, not TOP 10 ... silly me.


Vanderghast, Access MVP





.