Return Latest entry query 1

[DrSmyth]

Hi my query is quite simple to explain but I just can't seem to get the right answer.

I have a single table with 3 fields:
Estate Code (ID Field)
PLT PSM (Text Field)
Liaison TS (Date Field)

Each Estate Code can have several PLT PSM each with different timestamps. I want to report on the latest timestamp for each Estate Code with it's correspomding PLT PSM.

My efforts so far have been based around using a group by on the estate code and this works fine with just the date stamp, but as soon as i add the PLT PSM, i either get multiple returns (using Group by) or incorrect results (using another operator). Here is an example of my efforts so far (the problem here is the last operator on PLT PSM, but if i take that out then the query doesn't run):

SELECT [Quick Estate - Liaison PSM Data].[Estate Code], Last([Quick Estate - Liaison PSM Data].[PLT PSM]) AS [LastOfPLT PSM], Max([Quick Estate - Liaison PSM Data].[Liaison TS]) AS [MaxOfLiaison TS]
FROM [Quick Estate - Liaison PSM Data]
GROUP BY [Quick Estate - Liaison PSM Data].[Estate Code];

Thanks for any advice

 

[PHV]

SELECT A.[Estate Code], A.[PLT PSM], A.[Liaison TS]
FROM [Quick Estate - Liaison PSM Data] AS A INNER JOIN (
SELECT [Estate Code], Max([Liaison TS]) AS MaxLiaisonTS
FROM [Quick Estate - Liaison PSM Data] GROUP BY [Estate Code]
) AS B ON A.[Estate Code] = B.[Estate Code] AND A.[Liaison TS] = B.MaxLiaisonTS;

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

 

[DrSmyth]

Thanks PHV, not sure why but I'm getting a syntax error in from clause when i try and run the query. I'm using Access 97, is that a factor?

Cheers

 

[PHV]

I'm using Access 97
That's the factor.
A workaround: create a query named, say, qryMaxLiaisonTS:
SELECT [Estate Code], Max([Liaison TS]) AS MaxLiaisonTS
FROM [Quick Estate - Liaison PSM Data] GROUP BY [Estate Code];

Now your query:
SELECT A.[Estate Code], A.[PLT PSM], A.[Liaison TS]
FROM [Quick Estate - Liaison PSM Data] AS A INNER JOIN qryMaxLiaisonTS AS B ON A.[Estate Code] = B.[Estate Code] AND A.[Liaison TS] = B.MaxLiaisonTS;

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

 

[DrSmyth]

Thanks PHV, that works like a dream. Your a star, so i might as well award you with one...

Cheers