Nested Join Help?

[sab]

Hello, I am having difficulties adding on a second INNER JOIN to an existing query I have.



The following query returns about 10,400 records with a Candidate's ID, the date at which they were most recently contacted, and who contacted them.

 
SELECT e.Candidate_Id, [By whom], [Date], e.Comments
FROM [Candidates: Contact Summaries] AS e INNER JOIN [SELECT Candidate_Id,
        MAX([Date]) AS LastContact
    FROM [Candidates: Contact Summaries]
    GROUP BY
        Candidate_Id]. AS lu ON e.Candidate_Id = lu.Candidate_Id AND e.Date = lu.LastContact;

I have another table (Candidates) and I need this query to pull the FirstName and LastName from the Candidates table, but I can't seem to figure out how to properly add on another JOIN. I tried this but it doesn't like it:

 SELECT e.Candidate_Id, [By whom], [Date], e.Comments
FROM [Candidates: Contact Summaries] AS e INNER JOIN [SELECT Candidate_Id,
        MAX([Date]) AS LastContact
    FROM [Candidates: Contact Summaries]
    GROUP BY
        Candidate_Id]. AS lu ON e.Candidate_Id = lu.Candidate_Id AND e.Date = lu.LastContact
    INNER JOIN Candidates ON e.Candidate_Id = Candidates.Candidate_Id

Access just tells me there's a "Syntax on FROM Clause".

Thanks for any help!

[aytee111]

The inner select should be surrounded by round brackets : FROM ..., (SELECT....)