Full Description of the TTA
Request Essentials
What
The St Charles (MO) Police Department would like to request free Technology/Computer Applications assistance.
Where
Specifically, our crime analyst and several senior officers would like to receive On-Site Services to help us take advantage of Open Database Connectivity (ODBC), along with Microsoft Access 2010 and Structured Query Language (SQL), to directly access the data in our Records Management System (RMS) and Computer-Aided Dispatch (CAD) system – both of which are SunGard Public Sector ONESolution products.
When
The default timing of at least 60 days in the future would be fine for the delivery of the requested assistance.
Who
In August 2015, our Department sent four representatives to the “Building Analytical Capacity” workshop that BJA co-sponsored at the St Louis Police Academy. It was the instructor, Christopher W. Bruce, who made us aware of the BJA NTTAC. We would be delighted if he, or someone with similar IT and crime-analysis skills, could spend up to one work week in St Charles to help us learn how to use Access queries and reports on a regular basis.
Needs
In January, two staff members completed introductory Access (2 days) and SQL (1 day) training. In February, the city’s IT department gave our crime analyst ODBC access to our RMS and CAD data. Since then, the analyst has begun crafting SQL queries using Access 2010.
Below are just a few examples of the types of data extraction and reporting needs that either were met using ODBC, Access 2010, and SQL, or could possibly be met using ODBC, Access 2010, and SQL, if staff members received on-site guidance and training.
Example One
One of our lieutenants needed some traffic crash data to apply for Missouri Department of Transportation grant money. Specifically, he needed to know, for the period 1/1/2013 – 12/31/2015:
1. Total number of crashes resulting in serious injury
2. Total number of speed-related crashes
3. Total number of speed-related crashes resulting in serious injury
4. Total number of alcohol-related crashes
5. Total number of alcohol-related crashes resulting in serious injury
6. Total number of unbuckled serious injuries
As the lieutenant stated, “I attempted to gather this information by searching the accident module in RMS, however, I am unable to figure out how to gather the specific information I need because the boxes I need to search are not available or I can’t seem to find them.”
Using our ODBC connection, we were able to answer the questions by working with three Accident Module tables – tamain, taunits, and tanames – and setting the appropriate selection criteria.
Example Two
One of our captains wanted to know the number of arrests in 2015, by the hometowns of the arrestees, for certain NIBRS codes.
This search cannot be conducted using the pre-built, front-end queries available in our SunGard RMS. However, we were able to answer this question by linking the main Names table – nmmain – with two Arrest Module tables – armain and archrg – and then setting the appropriate selection criteria.
Example Three
One of our traffic-enforcement patrolmen wanted a list of all his DWI arrests in 2015, so he could be nominated for the Law Enforcement Traffic Safety Advisory Council (LETSAC) “Officer of the Year” award. He also needed a list of all his traffic citations in 2015 for the following four categories:
• Speeding citations
• Safety-belt violations
• All other hazardous moving violations
• All other non-hazardous moving violations
We were able to answer the first question by linking two Arrest Module tables – armain and archrg – and then setting the appropriate criteria. And we were able to answer the second set of questions by linking two Citation Module tables – tcmain and tcchrg – and then setting the appropriate criteria. Because the various traffic-related charges vary so dramatically, the final steps of analysis to answer these questions were undertaken by copying the query results from Access and pasting them into an Excel workbook for final manipulation.
Example Four
Once every four weeks, in sync with our department’s operational schedule, the crime analyst prepares two reports.
One report tracks (i) criminal activity and contacts/enforcement for the recently ended four-week period and compares it to (ii) the previous four-week period and to (iii) the same four-week period a year ago. It also tracks (iv) criminal activity and contacts/enforcement for the year-to-date and compares it to (v) the same year-to-date activity a year ago. Twenty different crimes are tracked and five different measures of contacts/enforcement are tracked.
This report, which is now prepared manually, requires 25 (rows or categories) x 5 (columns or time periods) = 125 queries, using the front-end, pre-built queries available in SunGard. The query results are entered into an Excel spreadsheet, which calculates differences and percentage changes and illustrates the year-to-date, four-week trend with sparklines. A copy of the most recent report will be attached.
We believe that this report could be automated using Access, not only for the 125 queries but also for creation of the composite report. However, our analyst needs guidance to accomplish this automation.
The second report tracks patrol officer performance by platoon, squad, and specialty unit. The bulk of the data is extracted as CSV files from the Employee Administration Module of our RMS. Calls for service and traffic stop data, from our CAD Officer Activity Report, is then added to the mix. Finally, days worked is extracted from our NOVAtime time and attendance system.
All of the data is entered into an Excel workbook, which calculates total activity, average daily activity, group totals, group averages, and illustrates the year-to-date average daily activity with sparklines. A copy of the most recent report will be attached.
We believe that portions of this report could be automated using Access, not only for the data extraction but also the creation of the composite report. But, once again, our analyst needs guidance to accomplish this automation.
Example Five
At least once a year, the City of St Charles’ Public Works Department requests traffic-crash data, so it can apply for grants to fund the construction of street and intersection improvements. Currently these requests are being fulfilled by providing Public Works with the basic accident data, including location coordinates. Public Works then uses Geographic Information Systems (GIS) software to select the data for the accidents that occurred in a certain area of interest, e.g. an intersection or a specific stretch of roadway.
ODBC, Access, and SQL might also be useful for handling these requests.
Example Six
Twice a year, we extract crime data for Lindenwood University, which they use to prepare their annual, federally-mandated Clery Act security report. Currently these data requests are fulfilled by using GIS to select the crimes that “intersect” with the Lindenwood University campus, dormitories, off-campus properties, and related buffer zones.
ODBC, Access, and SQL might also be useful for handling these requests.
Benefits
If our technical assistance request is approved, the Department expects that our crime analyst and Command Staff will be able to glean much more useful information from our RMS and CAD systems and accomplish this much more quickly than we can at present.
Understaffed crime analysis department needs automation assistance
Please check the box next to the following questions if the answer is 'yes'.
Please enter the applicable Event Date if there is an Event associated with this TTA.
When entering an Event Date, the Time is also required.
If the TTA is targeted to a particular audience or location, please complete the questions below.
Milestones are an element, activity, work product, or key task associated with completing the TTA (e.g. kick-off meeting, collect data from stake holders, deliver initial data analysis).
Please complete the fields below, if applicable, to create a milestone for this TTA.
Please respond to the Performance Metrics below. The Performance Metrics questions are based on the TTA Type indicated in the General Information section of the TTA.
Below are just a few examples of the types of data extraction and reporting needs that either were met using ODBC, Access 2010, and SQL, or could possibly be met using ODBC, Access 2010, and SQL, if staff members received on-site guidance and training.
Example One
One of our lieutenants needed some traffic crash data to apply for Missouri Department of Transportation grant money. Specifically, he needed to know, for the period 1/1/2013 – 12/31/2015:
1. Total number of crashes resulting in serious injury
2. Total number of speed-related crashes
3. Total number of speed-related crashes resulting in serious injury
4. Total number of alcohol-related crashes
5. Total number of alcohol-related crashes resulting in serious injury
6. Total number of unbuckled serious injuries
As the lieutenant stated, “I attempted to gather this information by searching the accident module in RMS, however, I am unable to figure out how to gather the specific information I need because the boxes I need to search are not available or I can’t seem to find them.”
Using our ODBC connection, we were able to answer the questions by working with three Accident Module tables – tamain, taunits, and tanames – and setting the appropriate selection criteria.
Example Two
One of our captains wanted to know the number of arrests in 2015, by the hometowns of the arrestees, for certain NIBRS codes.
This search cannot be conducted using the pre-built, front-end queries available in our SunGard RMS. However, we were able to answer this question by linking the main Names table – nmmain – with two Arrest Module tables – armain and archrg – and then setting the appropriate selection criteria.
Example Three
One of our traffic-enforcement patrolmen wanted a list of all his DWI arrests in 2015, so he could be nominated for the Law Enforcement Traffic Safety Advisory Council (LETSAC) “Officer of the Year” award. He also needed a list of all his traffic citations in 2015 for the following four categories:
• Speeding citations
• Safety-belt violations
• All other hazardous moving violations
• All other non-hazardous moving violations
We were able to answer the first question by linking two Arrest Module tables – armain and archrg – and then setting the appropriate criteria. And we were able to answer the second set of questions by linking two Citation Module tables – tcmain and tcchrg – and then setting the appropriate criteria. Because the various traffic-related charges vary so dramatically, the final steps of analysis to answer these questions were undertaken by copying the query results from Access and pasting them into an Excel workbook for final manipulation.
Example Four
Once every four weeks, in sync with our department’s operational schedule, the crime analyst prepares two reports.
One report tracks (i) criminal activity and contacts/enforcement for the recently ended four-week period and compares it to (ii) the previous four-week period and to (iii) the same four-week period a year ago. It also tracks (iv) criminal activity and contacts/enforcement for the year-to-date and compares it to (v) the same year-to-date activity a year ago. Twenty different crimes are tracked and five different measures of contacts/enforcement are tracked.
This report, which is now prepared manually, requires 25 (rows or categories) x 5 (columns or time periods) = 125 queries, using the front-end, pre-built queries available in SunGard. The query results are entered into an Excel spreadsheet, which calculates differences and percentage changes and illustrates the year-to-date, four-week trend with sparklines. A copy of the most recent report will be attached.
We believe that this report could be automated using Access, not only for the 125 queries but also for creation of the composite report. However, our analyst needs guidance to accomplish this automation.
The second report tracks patrol officer performance by platoon, squad, and specialty unit. The bulk of the data is extracted as CSV files from the Employee Administration Module of our RMS. Calls for service and traffic stop data, from our CAD Officer Activity Report, is then added to the mix. Finally, days worked is extracted from our NOVAtime time and attendance system.
All of the data is entered into an Excel workbook, which calculates total activity, average daily activity, group totals, group averages, and illustrates the year-to-date average daily activity with sparklines. A copy of the most recent report will be attached.
We believe that portions of this report could be automated using Access, not only for the data extraction but also the creation of the composite report. But, once again, our analyst needs guidance to accomplish this automation.
Example Five
At least once a year, the City of St Charles’ Public Works Department requests traffic-crash data, so it can apply for grants to fund the construction of street and intersection improvements. Currently these requests are being fulfilled by providing Public Works with the basic accident data, including location coordinates. Public Works then uses Geographic Information Systems (GIS) software to select the data for the accidents that occurred in a certain area of interest, e.g. an intersection or a specific stretch of roadway.
ODBC, Access, and SQL might also be useful for handling these requests.
Example Six
Twice a year, we extract crime data for Lindenwood University, which they use to prepare their annual, federally-mandated Clery Act security report. Currently these data requests are fulfilled by using GIS to select the crimes that “intersect” with the Lindenwood University campus, dormitories, off-campus properties, and related buffer zones.
ODBC, Access, and SQL might also be useful for handling these requests.
Benefits
If our technical assistance request is approved, the Department expects that our crime analyst and Command Staff will be able to glean much more useful information from our RMS and CAD systems and accomplish this much more quickly than we can at present.
Please submit a signed letter of support from your agency’s executive or other senior staff member. The letter can be emailed to or uploaded with this request. The letter should be submitted on official letterhead and include the following information:
- General information regarding the request for TTA services, i.e., the who, what, where, when, and why.
- The organizational and/or community needs specific to the request for TTA services.
- The benefits or anticipated outcomes from the receipt of TTA services.
By submitting this application to BJA NTTAC, I understand that upon approval of this application for TTA, the requestor agrees to keep BJA NTTAC informed of any circumstances that may impact the delivery of the TTA, including changes in the date of the event, event cancellation, or difficulties communicating with the assigned TTA provider.
Please call [site:phone] if you need further assistance completing this application.