Access 2003 - Query To Retrieve the Latest Record

[Ignorant]

Hi,
Iam trying to retrieve the last record, based on date field (empanelDate) for each counselID from the table EmpanelmentTran.

SELECT EmpanelmentTran.counselID, Last(EmpanelmentTran.empanelDate) AS LastOfempanelDate
FROM EmpanelmentTran
GROUP BY EmpanelmentTran.counselID, EmpanelmentTran.empanelmentPurposeID;

The above code retrieves all the records not just the last record for each counselID

PS: I tried using MAX instead of LAST but with same result...

[Allan Murphy]

You will display all records as you are grouping on counselID AND empanelmentPurposeID. As each counsel has one id but many PurposeIDs you will display the LAST date for EACH PurposeID. If you remove the PurposeID from the Group statement then you will display only one record for each counselID.

[Krishnan]

Mr. Allan,
Pls advise me where this above code will set up?

Awaiting yr reply.

Thanks,
Kishan

[Allan Murphy]

Try this

SELECT EmpanelmentTran.counselID, Last(EmpanelmentTran.empanelDate) AS LastOfempanelDate
FROM EmpanelmentTran
GROUP BY EmpanelmentTran.counselID;