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?
- Promote consistency in detention decisions across the state of Alaska.
- Improve detention assessment accuracy.
- Reduce the number of beds needed in secure facilities.
- Provide consistent and detailed documentation of detention decisions.
- Make detention decision information available state-wide, online, immediately, to authorized Juvenile Justice staff.
Why Automate the DAI?
- Manually finding and scoring the case history is time consuming and error prone.
- Math mistakes happen.
- Learning all of the rules for scoring is difficult for infrequent users.
- Data on paper forms cannot easily be shared between offices.
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.
- The seven numbered tabs have a toltal of ten sub-forms with details about the juvenile's record and how it is scored.
- Three of the tabbed pages provide the staff member with background information that is not scored.
- The final Save tabbed page is used to save the score and details, after manually entering some information about the specific detention assessment.
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