Recidivism

Discussion in 'Microsoft Access VBA Modules' started by RD, Nov 22, 2010.

  1. RD

    RD Guest

    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
     
    RD, Nov 22, 2010
    #1
    1. Advertisements

  2. RD

    John Spencer Guest

    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
     
    John Spencer, Nov 23, 2010
    #2
    1. Advertisements

  3. RD

    RD Guest

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

    RD


    On Tue, 23 Nov 2010 08:36:35 -0500, John Spencer
    <> 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
     
    RD, Nov 29, 2010
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.

Share This Page