DataBaseZone.com
Conrad Muller
Juneau, Alaska

Email: conrad at
databasezone
dot com

An Application to Score Juveniles for Secure Detention
when they are Charged with Crimes


My major project for the State of Alaska was adding a detention assessment module (DAI) to the Alaska Division of Juvenile Justice case management system. The module was an MS Access client. The backend was a SQL Server 2000 database server. The module contained about 1,200 lines of T-SQL in 21 stored procedures and about 2,000 lines of Visual Basic code in the client. The DAI application was reached by about 400 staff through a Windows 2000 terminal server.

After I left, other developers eventually turned the DAI into a Web application, integrating it into the main case management system.

Detention assessment is an evaluation of the need to hold a juvenile a juvenile in secure detention, based on current criminal charges plus previous interactions with the justice system. Detention is ordinarily only used for youth who are a danger to themselves or the community.

Why a formal detention assessment instrument?
Why Automate the DAI?

The DAI

After logging in and finding the juvenile through a search screen, current charges can be scored and entered into the computation. This step can be skipped if the current charges are already entered in the JOMIS client tracking system.

The Continue button opens the main form with the score calculated and the juvenile's history and scoring details available on tabbed pages. This form takes about one or two seconds to open.

This form has eleven tabbed pages.

It is possible to store multiple detention assessments for a juvenile, so the next screen displays a list of saved assessments. After choosing an assessment, the following report is displayed.

On a report with real juvenile information, the lower part of the report would be filled with notes and details about this particular assessment.

This bit of VBA code connects to the database and retrieves a list of supervisors through a stored procedure.

' -------------------------------------------------------
' Create a variable to hold the SQL string as source for the recordset.

Dim sp_sql_supervisor As String

' Use a stored procedure as a Record Source for the (ADO) recordset.
sp_sql_supervisor = "Execute djj_supervisor"

' Create an instance of the ADO recordset class based on the above connection.
Dim rs_supervisor As ADODB.Recordset
Set rs_supervisor = New ADODB.Recordset

' Set the properties of the recordset and open it.
With rs_supervisor
    Set .ActiveConnection = ado_conn
    .Source = sp_sql_supervisor
    .CursorType = adOpenStatic
    .LockType = adLockReadOnly
    .Open
End With

' Generate a list of supervisors' names from the rs_supervisor recordset.
rs_supervisor.MoveFirst
str_supervisor_list = RTrim(rs_supervisor("STAFF_FNAME")) + " " _
    + IIf(Len(RTrim(rs_supervisor("STAFF_MNAME"))) > 0, RTrim(rs_supervisor("STAFF_MNAME")) _
    + " ", "") + RTrim(rs_supervisor("STAFF_LNAME"))

rs_supervisor.MoveNext
    Do While Not rs_supervisor.EOF
    str_supervisor_list = str_supervisor_list + "; " + RTrim(rs_supervisor("STAFF_FNAME")) _
    + " " + IIf(Len(RTrim(rs_supervisor("STAFF_MNAME"))) > 0, RTrim(rs_supervisor("STAFF_MNAME")) _
    + " ", "") + RTrim(rs_supervisor("STAFF_LNAME"))
    rs_supervisor.MoveNext
Loop

' Fill the "Supervisor" drop-down-list with names from the list generated above.
Me.lst_approving_supervisor.RowSource = str_supervisor_list

' Close the recordset, and free the memory it used.
rs_supervisor.Close
Set rs_supervisor = Nothing

' -------------------------------------------------------

Stored Procedure: djj_aftercare

CREATE PROCEDURE [dbo].[djj_aftercare]

/*
This stored procedure finds the most recent secure detention. If the detention has ended,
and the detention end date is less than 90 days before the dates of the current referral charges,
aftercare is reported and points are given.
*/

@juvenile_key int,
@incident_first_date datetime,
@incident_last_date datetime

AS

SELECT Convert ( char ( 10 ) ,DT_ADMIT_ACTIVITY_LOG.ADMITTED,101 ) AS admitted,
    Convert ( char ( 10 ) ,DT_ADMIT_ACTIVITY_LOG.RELEASED,101 ) AS released,
    DT_COMMITMENT_REASON.COMMITMENT_TYPE,
    DT_UNITS.UNIT_NAME

FROM DT_ADMIT_ACTIVITY_LOG INNER JOIN DT_UNITS ON DT_ADMIT_ACTIVITY_LOG.UNIT_KEY = DT_UNITS.UNIT_KEY
    INNER JOIN DT_COMMITMENT_REASON ON DT_ADMIT_ACTIVITY_LOG.COMMITMENT_KEY = DT_COMMITMENT_REASON.COMMITMENT_KEY

WHERE DT_ADMIT_ACTIVITY_LOG.JUVENILE_KEY = @juvenile_key
    AND DT_ADMIT_ACTIVITY_LOG.ADMITTED= ( SELECT Max (DT_ADMIT_ACTIVITY_LOG.ADMITTED ) AS MaxOfADMITTED
        FROM DT_ADMIT_ACTIVITY_LOG INNER JOIN DT_UNITS ON DT_ADMIT_ACTIVITY_LOG.UNIT_KEY=DT_UNITS.UNIT_KEY
        GROUP BY DT_ADMIT_ACTIVITY_LOG.JUVENILE_KEY, DT_UNITS.UNIT_TYPE_KEY
        HAVING DT_ADMIT_ACTIVITY_LOG.JUVENILE_KEY=@juvenile_key AND DT_UNITS.UNIT_TYPE_KEY=2 )
                AND DT_ADMIT_ACTIVITY_LOG.RELEASED Is Not Null
                AND ( DateDiff (dd,DT_ADMIT_ACTIVITY_LOG.RELEASED, @incident_last_date ) Between 1 and 90
                OR DateDiff (dd,DT_ADMIT_ACTIVITY_LOG.RELEASED, @incident_first_date ) Between 1 and 90 )

GO

Home | Resume | Project Portfolio | Writings | Developer Resources | Contact Form