Not part of aggregate function 1

[simon551]

Hi! I'm trying to get the Key ID field of a table in a query that takes the last date listed for each Foreign Key ID. I can group by the foreign key but if I try to get the key as well then it craps out. Can you help?

SELECT MemberID, Max(ProcessDate) AS MaxDate, MembershipProcessID
FROM tblMembershipsProcessed
GROUP BY MemberID;

That MembershipProcessID field is what I'm after...

Thanks!

 

[randy700]

What does this give you?

GROUP BY MemberID, MembershipProcessID

Randy

 

[PHV]

SELECT A.MemberID, A.ProcessDate, A.MembershipProcessID
FROM tblMembershipsProcessed A INNER JOIN (
SELECT MemberID, Max(ProcessDate) AS MaxDate FROM tblMembershipsProcessed GROUP BY MemberID
) B ON A.MemberID = B.MemberID AND A.ProcessDate = B.MaxDate;

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

 

[simon551]

Thank you PHV!