Following variables over time.

[Guest]

I database that follows people (who have unique identifiers) for blood
pressure at different dates. Each date that they are measured is another
record in the database. What I would like to do is write a query that would
find people whose blood pressure levels are greater that a certain number (I
can already do this), and then find a way to check what number is recorded at
their next recorded measurement. However, there is no specified amount of
time until they come back. In essence, my question is how do I look for the
next recorded record for a unique identifier as specified by the next point
in time.

 

[Dale Fye]

Tony,

I usually create a query that looks something like:

qry_PatientVisitNextVisit:

SELECT T.PatientID, T.VisitDate,
(SELECT MIN(VisitDate)
FROM tbl_PatientVisits T2
WHERE T2.PatientID = T.PatientID
AND T2.VisitDate > T.VisitDate) AS NextVisit
FROM tbl_PatientVisits AS T;

You can then use this as an intermediate table between two copies of the
tbl_PatientVisits table to allow you to compare values of BP or Pulse or
whatever was recorded for that visit. Something like:

SELECT Connector.PatientID, Connector.VisitDate, Connector.NextVisit, V1.BP,
V2.BP
FROM (tbl_PatientVisits AS V1
INNER JOIN qry_PatientVisitNextVisit AS Connector
ON (V1.VisitDate = Connector.VisitDate)
AND (V1.PatientID = Connector.PatientID))
INNER JOIN tbl_PatientVisits AS V2
ON (Connector.NextVisit = V2.VisitDate)
AND (Connector.PatientID = V2.PatientID)
WHERE Val(Left([V1].[BP],3))>110

*Note: For my test I only entered a single value for BP, in a text column.
I would assume that you would have separate values for both portions of the
BP and would write your WHERE clause appropriately.

HTH
Dale