Using the most recent record/date in a query

[camelk]

I have a table that stores patient progress reports. The Composite key is Patient Number and Date of Progress Report. I have a query that accesses the records from the table. But....I want to access only the most recent record for any given patient number. How do I write a criteria that will let me do this.

 

[Steve101]

A couple of ways to do this, depending on exactly what it is you want to achieve:

(a) To just view a summary of last progress dates associated with a Patient, use a Total Query, where you Group By the PatientId, and display the Max of the ProgressDate. The SQL would look something like this:

SELECT PatientId, Max(LastVisit) AS MaxOfLastVisit
FROM tblPatientVisit
GROUP BY PatientId;

It would in fact be better if this was joined with the Patient table, so that you could also display Patient related data.

(b) If you wished to be able to display and change data associated with the last progress entry, then you would use a normal select query, with an appropriate constraint; for example:

SELECT PV.PatientId, PV.LastVisit
FROM tblPatientVisit AS PV
WHERE PV.LastVisit=(Select max(LastVisit)
FROM tblPatientVisit
WHERE PatientId = PV.PatientId);

This is a little more advanced than the prior example; its using whats called a correlated subquery as the criteria for selection (a query within a query).

Hope this helps,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)