Query with Inner Join

[Lisad]

I am trying to create a query which shows only the newest note for every prospect. I have the code below, but I get a 'Syntax error in JOIN Operation' when I try to run the query and TBLNotes is highlighted.


SELECT TBLProspect.CompanyName,TBLNotes.Note, TBLNotes.NoteDate
FROM TBLNotes AS a
INNER JOIN TBLProspect ON TBLProspect.LeadID=TBLNotes.LeadID
WHERE ((((Select Count(*) from tblNotes where LeadID=a.LeadID and NoteDate>=a.NoteDate)) In (1)))
ORDER BY a.LeadID, a.NoteDate DESC;

Where am I going wrong?

 

[FoFa]

Try This:
SELECT b.CompanyName, a.Note, a.NoteDate
FROM TBLNotes AS a
INNER JOIN TBLProspect AS b ON b.LeadID = a.LeadID
WHERE a.LeadID IN (Select LeadID from tblNotes where TBLNoteDate>=a.NoteDate )
ORDER BY a.LeadID, a.NoteDate DESC;