PC Review


Reply
 
 
RD
Guest
Posts: n/a
 
      22nd Nov 2010
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
 
Reply With Quote
 
 
 
 
John Spencer
Guest
Posts: n/a
 
      23rd Nov 2010
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

 
Reply With Quote
 
 
 
 
RD
Guest
Posts: n/a
 
      29th Nov 2010
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


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off



Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 01:26 AM.