Recidivism

[RD]

Hi all,

Trying to wrap my brain around how to query for recidivism.

I'm working with a complex database not of my design in a system that
has been in use for 5 years, or so.

I have one table that logs both program entries and exits with a
"status" of either Entry or Exit as well as the date of the
Entry/Exit. This table also has the client ID and a Program ID.

The request is to track the clients who transition out of an Emergency
Program into a Transitional Program or even out of Program completely
and then re-enter the Emergency Program.

I was hoping to be able to do it with a query, or nested queries, but
it looks like I might need to just grab a recordset and loop through
each record in code, holding a first Entry/Exit set in memory while
looking for subsequent Entry/Exit sets by Client ID.

Any thoughts?

Many thanks,
RD

 

[John Spencer]

This might give you an idea of how to get started. It basically looks for
records that have an exit date before an entry date. You will have to refine
it further based on your table structure and fields.


SELECT SomeTable.*
FROM SomeTable
WHERE Exists
(SELECT *
FROM SomeTable as Temp
WHERE Temp.DateField < SomeTable.DateField
AND Temp.Status = "Exit"
AND Temp.ClientID = SomeTable.ClientID
AND Temp.ProgramID = SomeTable.ProgramID)
AND ProgramID = <<SOME VALUE>>
AND Status = "Entry"

Another idea would be to use something like the following to get the matching
Entry and Exit dates.
SELECT A.ClientID, A.ProgramID, A.DateField
, Min(B.DateField)
FROM SomeTable as A INNER JOIN SomeTable as B
ON A.ClientID = B.ClientID
AND A.ProgramID = B.ProgramID
AND A.DateField < B.DateField
WHERE B.Status = "Exit"
AND A.Status = "Entry"


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

 

[RD]

Thanks a lot, John. This gives me a start.

RD

This might give you an idea of how to get started. It basically looks for
records that have an exit date before an entry date. You will have to refine
it further based on your table structure and fields.


SELECT SomeTable.*
FROM SomeTable
WHERE Exists
(SELECT *
FROM SomeTable as Temp
WHERE Temp.DateField < SomeTable.DateField
AND Temp.Status = "Exit"
AND Temp.ClientID = SomeTable.ClientID
AND Temp.ProgramID = SomeTable.ProgramID)
AND ProgramID = <<SOME VALUE>>
AND Status = "Entry"

Another idea would be to use something like the following to get the matching
Entry and Exit dates.
SELECT A.ClientID, A.ProgramID, A.DateField
, Min(B.DateField)
FROM SomeTable as A INNER JOIN SomeTable as B
ON A.ClientID = B.ClientID
AND A.ProgramID = B.ProgramID
AND A.DateField < B.DateField
WHERE B.Status = "Exit"
AND A.Status = "Entry"


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