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

 

[dustinbrearton via AccessMonster.com]

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

How is your data currently organized? I would imagine you may part of the
above information in a/an table(s) (tblPatients and tblTests)? Is the new or
exisiting already a field in the tables? Same with the days? If you don't
already have New/Exisiting as a field that would likely be the easiest fix.
Add New Existing to tblTests. You could have this updated by an update query
that would say new or existing. The final query would just be a select query
that has a criteria that New/Existing = "NEW". Post more info if this
doesn't answer your question.

 

[dustinbrearton via AccessMonster.com]

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

How is your data currently organized? I would imagine you may part of the
above information in a/an table(s) (tblPatients and tblTests)? Is the new or
exisiting already a field in the tables? Same with the days? If you don't
already have New/Exisiting as a field that would likely be the easiest fix.
Add New Existing to tblTests. You could have this updated by an update query
that would say new or existing. The final query would just be a select query
that has a criteria that New/Existing = "NEW". Post more info if this
doesn't answer your question.

 

[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