Additional DISTINCT TOP subquery problem

[Maxie]

Thanks to John Verhagen I've gotten the following query
working....

SELECT ticker, avg(close) as 50DMA
FROM pub_price_history
WHERE date IN (SELECT DISTINCT top 50 date FROM
pub_price_history ORDER BY date DESC)
GROUP BY ticker;

But now I've got 2 additional issues, I'm hoping someone
can help me with....

1) What if I want to calculate 2 different moving averages
in the same query....say a 50 day moving average and a 200
day moving average? How would I do that in one query?

2) This query takes about 10 minutes to run. Is there any
way to speed it up? I'm running on a P2 266Mhz. I'm
hoping when I get my primary computer back (an AMD XP2000)
that this won't be a problem....but I'm not sure....10
minutes is still a long time.

Thanks in advance.

Maxie III

 

[John Viescas]

2) Do you have an index on [date]? That should fix your performance
problem. Also, because "date" is a reserved word, you should always enclose
the name in brackets.

1) The easiest way is to use three separate queries. Save a copy of your
original query, but select top 200. Create a third query that joins the
other two queries on ticker. You could try to do it in one query in SQL by
inserting both SELECT statements in a FROM clause and join them, but because
you need to put brackets around one of the field names, Access might not
parse it correctly. It would look something like:

SELECT Avg50.ticker, Avg50.50DMA, Avg200.200DMA
FROM
(SELECT ticker, avg(close) as 50DMA
FROM pub_price_history
WHERE [date] IN (SELECT DISTINCT top 50 [date] FROM
pub_price_history ORDER BY date DESC)
GROUP BY ticker) As Avg50
INNER JOIN
(SELECT ticker, avg(close) as 200DMA
FROM pub_price_history
WHERE [date] IN (SELECT DISTINCT top 200 [date] FROM
pub_price_history ORDER BY date DESC)
GROUP BY ticker) As Avg200
ON Avg50.ticker = Avg200.ticker

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)