Moving Average

[carl]

Hi. I posted a similar question earlier in the week. I may not have explained
my problem well. So I am trying again...

My data table looks like this...

Sym adate Sum
AACC 2/1/2008 18
AATI 2/1/2008 164
AAUK 2/1/2008 726
AAV 2/1/2008 1010
AB 2/1/2008 602
ABD 2/1/2008 4
ABFS 2/1/2008 254
ABH 2/1/2008 8166

This is a small portion of the table. I would like to be able to query so
that if I input a date (eg 2/20/2008) the result will be for each Sym, the
average of Sum for the previous 20 days if that average of Sum is greater
than 200.

Thank you in advance.

 

[Michel Walsh]

SELECT a.sym, a.aDate, AVG(b.[sum])

FROM yourTable AS a INNER JOIN yourTable AS b
ON a.sym=b.sym
AND b.aDate <= a.aDate
AND b.aDate >= a.aDate - 20

GROUP BY a.sym, a.aDate





and, oh, by the way, I used yourTable name as name for your table. Feel free
to change it for your real table name. It occurs at two place in the query.


You type that code in the SQL view, in the query editor.

I have not idea what you mean by " if that average of Sum is greater than
200." You don't want see the result for that given { sym, aDate } ? If
so, try:


SELECT a.sym, a.aDate, AVG(b.[sum])

FROM yourTable AS a INNER JOIN yourTable AS b
ON a.sym=b.sym
AND b.aDate <= a.aDate
AND b.aDate >= a.aDate - 20

GROUP BY a.sym, a.aDate

HAVING AVG(b.[sum]) >= 200







Vanderghast, Access MVP

 

[KARL DEWEY]

Try this --
SELECT carl.Sym, Avg(carl.Sum) AS AvgOfSum
FROM carl
WHERE (((carl.adate) Between CVDate([Enter date]) And CVDate([Enter
date])-19))
GROUP BY carl.Sym
HAVING (((Avg(carl.Sum))>200));