Latest record selection

[Chuck]

I'm stumped on this one. I have a group of employees in a table based upon
an employee number. This table just contains personal data. I have another
table that contains notes about the employee that it date-stamped. The two
tables are joined together so that I can see all the notes on a particular
employee. However, the boss now just wants to see the latest entry in the
form. So, for example, I can have 12 note records per employee, and on the
form, show the personal data, along with a text box with the latest note.
He wants to double-click on the record selector to open a form and show all
the note history then. I'm having a hard time figuring out how to select
the latest note entry for each employee. Thanks.

 

[MGFoster]

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

"Latest" usually means most recent date/time. Do you have a date time
column in the Notes table? If so use that date to get the latest entry.
Hint: Max(date_column).

You have another question that seems to be how to open a form to show
all the note history. Here's one way:

In the Forms Double Click event do this:

Private Sub Form_DblClick(Cancel As Integer)

DoCmd.OpenForm "FormName", WhereCondition:="PersonID=" & Me!PersonID

End Sub

This assumes that you have a PersonID on the personnal form. Change it
to whatever name you are using as the Primay Key (the linking column
between the Personnel table and the Notes table).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBRAi8oIechKqOuFEgEQKq4ACdEFeRNUiuXaujMm1we/2snaO8ZckAnAnj
z9ms+47p1ljzMrt8BPfjRZSB
=vZJk
-----END PGP SIGNATURE-----

 

[John Spencer]

SELECT EmployeeID, InputDate, Note
FROM YourTable
WHERE InputDate =
(SELECT Max(InputDate)
FROM YourTable as T
WHERE T.EmployeeID = YourTable.EmployeeID)

Two query solution.
SELECT EmployeeID, Max(InputDate) as LastNoteDate
FROM YourTable

Save that as qLastNote
SELECT EmployeeID, InputDate, Note
FROM YourTable INNER JOIN qLastNote
ON YourTable.EmployeeID = qLastNote.EmployeeID
AND YourTable.InputDate = qLastNote.InputDate