Thanks a lot, John. This gives me a start.
RD
On Tue, 23 Nov 2010 08:36:35 -0500, John Spencer
<(E-Mail Removed)> wrote:
>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
>
>On 11/22/2010 12:23 PM, RD wrote:
>> 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
|
