Problems with a query

[rafal444]

The query below is droping many statements, It only shows statements for 6/30/2006, which is the latest statement out of all movies.

I want to show the movie and the latest statement for that particular movie. This is the query I have:

SELECT A.*
FROM [QryReportBase_CalculatedAll Step 4] AS A
WHERE (((A.StatementDate)=(Select Max([StatementDate]) from [QryReportBase_CalculatedAll Step 4] as B Where B.FilmID=A.FilmID)))
ORDER BY A.Film;

 

[PHV]

Something like this ?
SELECT A.*
FROM [QryReportBase_CalculatedAll Step 4] AS A INNER JOIN (
SELECT FilmID, Max(StatementDate) AS LastStatement FROM [QryReportBase_CalculatedAll Step 4] GROUP BY FilmID
) AS B ON A.FilmID = B.FilmID AND A.StatementDate = B.LastStatement
ORDER BY A.Film

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886