Comparing row to next row in MS Access

[SusanStoHelit]

Hi there,

I have a table with the following fields:

Key Member ID Fill Date Drug Name
1 1000512 1/12/2006 Lipitor
2 1000512 2/13/2006 Lipitor
3 1000512 3/10/2006 Lipitor
...

And I need to find out the days between fills like this:

Key Member ID Fill Date Drug Name Gap (in Days)
1 1000512 1/12/2006 Lipitor N/A
2 1000512 2/13/2006 Lipitor 31
3 1000512 3/10/2006 Lipitor 25

Is this possible through an Access query or VB module?

 

[jrbarnett]

Try something like the following vba code:

select key,
MemberID,
FillDate,
DrugName,
(select min (filldate)
from table t2
where t2.memberid = table.memberid
and t2.drugname = table.drugname
and t2.filldate > table.filldate) as gap
from table
order by key

Not tested, but should work.

John

 

[SusanStoHelit]

John,

Won't that compare each successive Filldate with the minimum Filldate? I'd like to compare each filldate with the previous filldate.

thanks
Susan

 

[jrbarnett]

Good point.

This one will compare it by using the ID number, but I'd rather try and find a solution that doesn't use them, in case you mix different patient/drug prescriptions in the table:

select table.keyfield,
table.MemberID,
table.FillDate,
table.DrugName,
datediff("d", (select (t2.filldate)
from table t2
where t2.keyfield = (table.keyfield)-1), table.filldate) as gap
from table c1
order by c1.keyfield

John

 

[SusanStoHelit]

The patients and drugs are in no particular order. my ID is actually a random number, not sequential.

is there a way to do a query that would sort the table by member ID, drug name, and filldate and then add in a sequential number? then I could use that query as a base for this query.

 

[PHV]

And what about this ?
SELECT A.Key, A.[Member ID], A.[Fill Date], A.[Drug Name], (A.[Fill Date]-Max(B.[Fill Date])) AS Gap
FROM yourTable AS A LEFT JOIN yourTable AS B ON A.[Member ID]=B.[Member ID] AND A.[Drug Name]=B.[Drug Name]
WHERE A.[Fill Date]>B.[Fill Date]
GROUP BY A.Key, A.[Member ID], A.[Fill Date], A.[Drug Name]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886