Automating Link Charts

Many Crime and Intelligence analysts spend hours or days creating expansive link charts. In this course, we’ll cover how to automate the creation of link charts using data structure common to police records management systems. These techniques are applicable to any link chart program but for the purposes of this course, we’ll use NodeXL which is a free plugin for Microsoft Excel and available at the link below. Additionally, we will make extensive use of Microsoft Access.

Students need no prior technical training or programming experience. This course starts from scratch, providing an overview of relational databases using common police department records management systems. You’ll learn how these systems organize data and how to write simple SQL queries. Students will then learn how to package a series of SQL queries in an MS Access macro to create two tools. The first is an offender search tool that will allow you to overcome dirty data and duplicate data issues common in law enforcement agencies. This tool simply allows you to type in a name, or a partial name, and search your master names list. You can then review the individuals returned and select one or more that you identify as the  target of your analysis.

We will then move into creating a second macro that takes your selection and runs a series of SQL queries to create an edge list for that individual. The edge list can then be copied and pasted into any link charting tool. A task that may have taken hours now takes only seconds! We’ll conclude with a brief overview of visualization options in NodeXL.