Display the Date of Last Record Entered by Each User 2

[djs45uk]

I have two tables with one-to-many relationship. One is called Staff and the other is Observation.

Staff enter observations into the Observation table.

I would like to display a list of the last time each member of staff entered an observation.

There is a field in the Observation table called observeDate but I need the query to pull out the most recent observeDate for each member of staff.

It's very hard to explain. I hope I've made it clear.

Many thanks
Daniel

 

[Lothario]

Try something like this:


SELECT t1.StaffName,t2.ObserveDate
FROM Staff t1, Observation t2
WHERE t1.StaffId=T2.StaffId AND t2.ObserveDate In (select top 1 t3.ObserveDate from Observation t3 where t3.StaffID = t2.StaffID ORDER BY t3.ObserveDate Desc);

-L

 

[PHV]

pull out the most recent observeDate for each member of staff
A simple aggregate query should do the trick:
SELECT Staff.PK, Max(Observation.Date) As MostRecentObserveDate
FROM Staff INNER JOIN Observation ON Staff.PK = Observation.FK

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[djs45uk]

Thank you both!

I got the 1st solution working but only if I enter the t3.staffID at runtime.

I would like to take this one step further by having a form which lists each member of staff with the last time they entered an observation into the Observation table.

Many thanks