Access: Calculate a patient's weight loss between the last 2 visits in Access 2003/XP/2000/97

Question: In Access 2003/XP/2000/97, I'm working on a query to determine a patient's weight loss between the last 2 visits. I have a weight table (tblWeight) which has the following columns (in this order):

Patid, FirstName, LastName, Weight, Date

I've been using the "Group by" feature to get the lastest visit date and the associated weight (which works great). But I am having trouble setting up my query to get the weight from the previous visit.

I'd like the query to return the following results:

Patid, FirstName, LastName, LastVisitDate, LastVisitWeight, PrevVisitDate, PrevVisitWeight, WeightDifference

How can I do this?

Answer: Normally, we might use GROUP BY queries to do this. But due to the complexity of the query results, it's a lot easier to use a combination of the DLookup, DMax, IsNull, and Iif functions. We'll demonstrate how to do this with the example below.

If we had the tblWeight table populated with the following records:

In this example, we want to return the following query results:

You could do this by using the DLookup, DMax, IsNull, and Iif functions in a query as follows:

The last 5 fields are defined as follows:

LastVisitDate: DMax("Date","tblWeight","Patid=" & [Patid])

LastVisitWeight: DLookUp("Weight","tblWeight","Patid=" & [Patid] & " and Date = #" & [LastVisitDate] & "#")

PrevVisitDate: DMax("Date","tblWeight","Patid=" & [Patid] & " and Date < #" & [LastVisitDate] & "#")

PrevVisitWeight: IIf(IsNull([PrevVisitDate]),Null,DLookUp("Weight","tblWeight","Patid=" & [Patid] & " and Date = #" & [PrevVisitDate] & "#"))

WeightDifference: ([LastVisitWeight]-[PrevVisitWeight])/[PrevVisitWeight]

As you can see each of these calculated fields builds off of the previous ones.

If you took a look at the SQL for the query it would look as follows:

SELECT DISTINCT tblWeight.Patid, tblWeight.FirstName, tblWeight.LastName,
DMax("Date","tblWeight","Patid=" & [Patid]) AS LastVisitDate,
DLookUp("Weight","tblWeight","Patid=" & [Patid] & " and Date = #" & [LastVisitDate] & "#") AS LastVisitWeight, DMax("Date","tblWeight","Patid=" & [Patid] & " and Date < #" & [LastVisitDate] & "#") AS PrevVisitDate, IIf(IsNull([PrevVisitDate]),Null,DLookUp("Weight","tblWeight","Patid=" & [Patid] & " and Date = #" & [PrevVisitDate] & "#")) AS PrevVisitWeight,
([LastVisitWeight]-[PrevVisitWeight])/[PrevVisitWeight] AS WeightDifference
FROM tblWeight
ORDER BY tblWeight.Patid;