Correlated Subquery Returns Date, ID number and call name

[Jim S]

Hello,

I have a table with the following fields, ID, DATE and Call Type.
I am trying to get the latest call for each ID. I tried using the grouping
function but i couldnt bring the call type through as well.

I read something about a correlated query and have some syntax that looks
like this, but it only returns the most recent record called.

SELECT *
FROM LC AS P1
WHERE (((P1.CommDate)=(SELECT MAX([CommDate])
FROM LC AS P2
WHERE P2.[ID_number] = P1.[id_number])));

I want it to return the most recent call for each ID number and also disply
the type of call, and time,
it should be like

IDa 4/25/08 Email
IDb 4/25/08 CALL
IDc 4/25/08 CALL

Thank you,

 

[Ken Sheridan]

The query looks OK to me, and should give the results you want. I'd normally
use a subquery myself, but another way of doing the same thing would be to
first create a query which returns the ID_number and latest CommDate per
ID_number:

SELECT ID_Number,
MAX(CommDate) AS LatestDate
FROM LC
GROUP BY ID_Number;

Save this as qryLatestDates say

Then join the LC table to this query on the ID_Number and date columns:

SELECT LC.*
FROM LC INNER JOIN qryLatestDates
ON LC.ID_number = qryLatestDates.ID_Number
AND LC.CommDate = qryLatestDates.LatestDate;

The above can be created completely in query design view if you wish rather
than writing the SQL of course. However you do it, though, your original
query should return exactly the same rows as the two-query approach, so why
you are only getting one row returned is unclear, to me at least.

Ken Sheridan
Stafford, England