Finding the last date in a set of records 1

[MrMode]

I am struggling with this puzzle.

I have a table that is used to record individual telephone conversations. There are 3 important fields that I am interested in; ESPID, CallBackDate, CallRecordID.

I have used Max in a query to find the last dated record for each prospect which works

SELECT CallTbl.ESPID, Max(CallTbl.CallBackDate) AS MaxOfCallBackDate
FROM CallTbl
GROUP BY CallTbl.ESPID
HAVING (((Max(CallTbl.CallBackDate))<Date()));

The problem is, I need to capture the CallRecordID as well, but I cannot do that with this type of query... How do I find the last CallBackDate for each ESPID and show the associated CallRecordID?

Any assistance would be greatly appreciated.

 

[PHV]

A starting point:

SELECT A.*
FROM CallTbl A INNER JOIN (
SELECT ESPID,Max(CallBackDate) AS MaxOfCallBackDate FROM CallTbl
WHERE CallBackDate<Date() GROUP BY CallTbl.ESPID
) B ON A.ESPID=B.ESPID AND A.CallBackDate=B.MaxOfCallBackDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MrMode]

Wow, thanks very much! That has worked, much appreciated

 

[MrMode]

Hmm, I saved the query so that I could use it. Now it is throwing up an error "Not a valid file name" and will not let me open it... any thoughts?

 

[PHV]

Well, the access query GUI has always been broken, especially with inline view (replacing () with .[])
I'd create a query named, say, qryMaxOfCallBackDate with the following SQL code:
SELECT ESPID,Max(CallBackDate) AS MaxOfCallBackDate FROM CallTbl
WHERE CallBackDate<Date() GROUP BY CallTbl.ESPID

And now you may use this query:
SELECT A.*
FROM CallTbl A
INNER JOIN qryMaxOfCallBackDate B ON A.ESPID=B.ESPID AND A.CallBackDate=B.MaxOfCallBackDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MrMode]

Excellent, I will do and come back to you once I have played around with it. Thanks again.

 

[MrMode]

Brilliant, it is robust - scary change in results as all the extraneous data is removed - it has taken me a while to trust what is being displayed. Thanks very much.