Select Most Recent Note for Each Employee

  • Thread starter Thread starter Michael E. Hill
  • Start date Start date
M

Michael E. Hill

I have a table listing employees with a one-to-many
relationship to a table of notes; obviously each employee
has many notes associated with him/her. I would like to
create a query that lists every employee ONCE with the
most recent note associated to that employee. Each note
does have a date field, but I can't find a way to return
only 1 note for each employee instead every note.

Thanks,
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try:

SELECT EmployeeName, N.Note
FROM Employees As E INNER JOIN Notes As N
ON E.EmployeeID = N.EmployeeID
WHERE N.NoteDate = (SELECT MAX(NoteDate) FROM Notes WHERE EmployeeID =
E.EmployeeID)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQSprb4echKqOuFEgEQIvpwCg/1P6Z2G0xL7YAsTOl0novBdEPn4AoN15
MCsSv/xsfJtmyrBs12UAs1PJ
=EG11
-----END PGP SIGNATURE-----
 
Thank you very much. I knew a compound Select statement
was the key, but I couldn't figure it out. This works
beautifully.
 
Back
Top