Max( ) Function

[Jacinto Muchine]

I have 2 tables with a 1-to-many relationship,
respectively tblSales, tblContactNotes. Each Sale
may have many contact notes over a period of time.

I'd like to extract only one record from the
tblContactNotes which would have the greatest date (the
most recent date). I.e, the last contact note for a
particular sale. Also, Is it possible to retrieve only the
last 3 contact notes for a particular sale?

I have written the query below but it returns all
contactnotes for a sale.

SELECT tblContactNotes.ContactNotesID, Max
(tblContactNotes.NoteDate) AS MaxOfNoteDate,
tblContactNotes.SalesId, tblContactNotes.Note
FROM tblContactNotes INNER JOIN tblSales ON
tblContactNotes.SalesId = tblSales.SalesId
GROUP BY tblContactNotes.ContactNotesID,
tblContactNotes.SalesID, tblContactNotes.Note;

Any help would be very appreciated!

Jacinto

 

[Jeff Boyce]

Jacinto

Take a look at the "TOP" property of the query.

Good luck

Jeff Boyce
<Access MVP>