Using DISTINCT and TOP

[NevilleT]

I have 2 tables. tblProject and tblDiaryNote. tblDiaryNote has 3 relevant
fields - Note, Date, ProjectNo I want to create a query that lists the most
recent note for each project (ie one record per project). I have tred to use
a combination of DISTINCT and TOP but cannot get it to work. The following
is what I am after if I could remove the duplicate project records

SELECT tblDiaryNote.ProjectNo, tblDiaryNote.Note, tblDiaryNote.Date
FROM tblDiaryNote
ORDER BY tblDiaryNote.ProjectNo DESC , tblDiaryNote.Date DESC;

Anyone have a suggestion?

 

[Van T. Dinh]

See the Microsoft Knowledge Base article:

http://support.microsoft.com/kb/210039

 

[NevilleT]

Thanks so much Van. It worked. I would never have figured that one out by
myself