---

MS Access 2003: Calculate a patient's weight loss between the last 2 visits

This MSAccess tutorial explains how to create a query to calculate a patient's weight loss between the last two visits in Access 2003 (with screenshots and step-by-step instructions).

Question: In Microsoft 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 function, DMax function, IsNull function, and Iif function. 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;