CODEWITHSUNDEEP
sqlms-access
jameslagan
jameslagan

Reputation: 55

Find the next record by date for a person and compare it to their initial record

I have a moderate sized table (tbl) with:

id, 
person_id, 
date, 
resultA, 
resultB, 
resultC, 
resultD

with about 20000 results.

Each id of the table corresponds to a person's result in 4 different tests and their person_id.

e.g.

id 1 
person_id 2 (Joe Bloggs (made up name)) 
result on date (01/01/2012) 
A positive, 
B negative, 
C negative, 
D negative

I need to perform a query where it selects the records where one of the results was positive and the remainder negative. EASY!

SELECT id, person_id, date, resultA, resultB, resultC, resultD 
FROM tbl 
WHERE resultA = "P" AND resultB = "N" AND resultC = "N" AND resultD = "N"

The tricky bit is that I want to find out what the NEXT result for that person, by date, is for each of the records that meet the criteria above. I then want to compare the two results and see if the results have changed. i.e. resultB might change from "N" to "P" in the say, 1 month, between Joe Bloggs (made up name) results and I need to capture that.

I am sure I need to use subqueries in access 2010 but I can't quite work out how to do it.

Upvotes: 1

Views: 2186

Answers (1)

Fionnuala
Fionnuala

Reputation: 91356

I have changed date to a date, because date is a reserved word. This only returns an extra column for ResultA, but the subquery can be repeated for each result.

SELECT a.id,
       a.person_id,
       a.adate,
       a.resulta,
       a.resultb,
       a.resultc,
       a.resultd,
       (SELECT TOP 1 b.resulta
        FROM   tbl b
        WHERE  b.person_id = a.person_id
               AND b.adate > a.adate
        ORDER  BY b.adate, b.id) AS res
FROM   tbl AS a
WHERE  a.resulta = "P"
       AND a.resultb = "N" 
       AND a.resultc = "N" 
       AND a.resultd = "N"

I have edited ORDER BY b.adate to include b.id, as per comments. Access returns matched records and if a person has more than one record on the same date, more than one record will be returned by Top 1.

Upvotes: 1

Related Questions