Previous Date in a Query

[ko5500]

I have a table containing data regarding patients' visits to medica
offices. Here is a sample:

Patientnumber patientname dateofvisit
001 Joe 1/15/04
001 Joe 2/21/05
001 Joe 2/11/06
001 Joe 3/1/07
002 Jane 1/2/04
002 Jane 1/13/05
002 Jane 1/16/06
002 Jane 1/3/07

I want to create a query that will add a field showing the date of th
last visit for each record. It should look like this:

Patientnumber patientname dateofvisit lastvisitdate
001 Joe 1/15/04
001 Joe 2/21/05 1/15/04
001 Joe 2/11/06 2/21/05
001 Joe 3/1/07 2/11/06
002 Jane 1/2/04
002 Jane 1/13/05 1/2/04
002 Jane 1/16/06 1/13/05
002 Jane 1/3/07 1/16/06

Any help would be greatly appreciated. Thanks in advance

 

[John Spencer]

You will need a subquery for this in your select clause

SELECT PatientNumber, PatientName, DateOfVisit
, (SELECT Max(DateOfVisit)
FROM YourTable as T2
WHERE T2.PatientNumber = T1.PatientNumber
AND T2.DateOfVisit < T1.DateOfVisit) as PriorVisit
FROM YourTable as T1

Or optionally, you can use DMax to do the same thing.

If you are doing this in the grid then you would need to do the following
for the prior date
Field: PriorVisit: (SELECT Max(DateOfVisit) FROM YourTable as T2 WHERE
T2.PatientNumber = T1.PatientNumber AND T2.DateOfVisit < T1.DateOfVisit)

T1 and T2 are just "aliases" for your table. They allow you to refer to the
same table two times.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..