Select most current record based on date field for mulitple records

[gottaski]

I'm trying to write a query that will select a record that has the most current date for all records in another table.

Since that probably didn't make any sense here are the tables and results i'm looking for:

Candidates table
CandidateID FirstName LastName
1 Joe Joe
2 John John

EH table
CandidateID CoEnd Title
1 3/1/00 Developer
1 3/1/99 Analyst
2 1/1/01 Planner
2 3/1/95 Assistant

The result I'm looking for:
CandidateID LastName Title LastDate
1 Joe Developer 3/1/00
2 John Planner 1/1/01

Query: SELECT Max(EH.CoEnd) AS LastDate, Candidates.CandidateID, Candidates.LastName, EH.Title
FROM EH INNER JOIN Candidates ON EH.CandidateID = Candidates.CandidateID;

i keep getting an errror saying "You tried to execute a query that doesn't include the specified expression "CandidateID" as part of an aggregate function"

Can anyone out there explain to me what I'm doing wrong? Thanks in advance!

 

[gottaski]

Guess I should have given myself a little more time to work it out as I figured it out. Needed to use Group By for CandidateID, Last for Title and Max for Date.

Hope this will help someone else out there.