How to do this query efficiently?

[Guest]

I have a very large table which gets bigger every month with new data.
(A new data extract is appended every month).
We run a series of reports off of this table. Some queries must look at all
the data and some only at certain months data.

This is the tricky bit. This table holds the life-cycle of each 'item'. Some
items are created and completed in one month and so appear only once. Some
items are created in one month and completed next month or many months later.
So each item will have one record for every month it existed. The key is the
item number plus the data extract date.

What I need to do is to create a query that gives me the most recent record
for each item. I have created a query that does this but it is very slow and
I need it to run very quickly.

Here are my current queries:

SELECT qrySSRData.[SSR_#] AS [SSR_#], qrySSRData.[Task_#] AS [Task_#],
Max(qrySSRData.REPORT_THRU_DATE) AS Max_Thru_Date
FROM qrySSRData
GROUP BY qrySSRData.[SSR_#], qrySSRData.[Task_#];

SELECT qrySSRData.*
FROM qrySSRData, qryMaxThru
WHERE (qrySSRData.[SSR_#]=qryMaxThru.[SSR_#]) And
(qrySSRData.[Task_#]=QryMaxThru.[Task_#]) And
(qryMaxThru.Max_Thru_Date=qrySSRData.REPORT_THRU_DATE);

 

[[MVP] S.Clark]

There is a way to do this with a Subquery, which may be a touch faster, but
I don't prefer them.
Have you implemented any indexes on the data?
Can you write the max dates to another table, such that the 2nd query
doesn't have to query the query?
Does an Inner Join, rather than the WHERE clause speed things up?
Can you prequery for only the records that have a certain condition, then
work from that subset?