Days between entries

[KateB]

Hi everyone,

I'm not sure if this is do-able. I have a database where patients who have
had particular tests are entered. Until recently I didn't have a unique ID
number for each patient, though each test was numbered individually. I now
need to start identifying the time between a patient's tests. If there is
more than 28 days it is considered a new episode of infection, less than 28
days and it is the same one. I need to identify 'new' episodes so therefore
need to work out how many days between the previous entries. For example:

John Smith - 1.4.08 New (0 days)
John Smith - 20.4.08 Existing (19 days)
John Smith - 11.5.08 Existing (21 days)
Mary Jones - 15.5.08 New (0 days)
John Smith - 15.6.08 New (35 days)
Mary Jones - 13.6.08 New (29 days)

So even though 6 tests have been carried out I only want to count the 4
'news'. Can anyone give me a suggestion with how to start?

Many thanks

Kate

 

[John Spencer]

Something like the following might work, although with a large number of
records it could be slow.


SELECT PatientName, TestDate
FROM YourTable as Y1
WHERE NOT EXISTS
(SELECT *
FROM YourTable as Y2
WHERE Y2.PatientName = Y1.PatientName
AND Y2.TestDate Between Y1.TestDate-28 and Y1.TestDate-1)

An alternative - Untested and I tend to get the comparision operators
wrong on these things.

SELECT Y1.PatientName, Y1.TestDate
FROM YourTable as Y1 LEFT JOIN YourTable as Y2
ON Y1.PatientName = Y2.PatientName
AND (Y1.TestDate <=Y2.PatientDate-28 and Y1.TestDate > Y2.TestDate)
WHERE Y2.PatientName is Null


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[KateB]

Hi Dustin. Sorry, I think I confused things with the example. There isn't a
"new/existing" field or "days" field at the moment. The problem is when the
results arrive on my desk they don't have the patient unique ID on them -
that arrives a month later and I have to do a manual cross-match to put it
against the right test so it isn't possible to have a separate table for the
patient information - its all in one table. With excel it would be easy -
sort ascending and do a calculation to look at the row above but that isn't
possible in Access is it? I don't particularly want to check each result to
see if there's been one before and if so count the days apart. How would I
use an update query to complete a "new/existing" field?

At the moment I count the first time a patient "appears" in the database and
report on a patient-basis rather than a "test" basis. Otherwise I could
export the results of a query to excel and calculate it there which might be
a much easier option!

Kate