This week we will be working in Access again, this time to create a weighted offender score for use in making a chronic offender list. The database to follow along can be downloaded from here, or from ELearning.
First we are going to create a table that identifies how much weight we want to give to different offenses. Go ahead and open up the CrimeTables.accdb database. Then in the Create tab click Table.
You then get an empty table, and mine is autonamed Table1. Right click on the tab with the tables name, and select Design View.
It asks you to name the table, and here I name it ‘CrimeWeights’. First we need to create a primary key for our table. Every table in Access needs to have a field that is unique to every row. Here we are going to associate UCR index crimes with particular weights, and in our incident table we have UCR field with a number and label. Basically we are just going to use those UCR numbers as our unique IDs. So go ahead and name this first field UCR. Then in the Data Type column select Number in the dropdown. The key symbol indicates it should be the unique key for every row of the table (e.g. you can’t have two rows that have UCR=1 in the table).
Next fields we want to create are labels to go with the ucr numbers (CrimeLabel), and then a weight to go with the crimes (Weight). CrimeLabel treat as a string field type, and Weight treat as a number. (Access you need to specify the field type, whereas in Excel it just guesses the field type, and they can be mixed numbers/strings.) In the end here is what my table looks like (you can see I also added in variable descriptions):
Now right click on the CrimeWeights tab, and select Datasheet view. You will be prompted to save the table, and you can go ahead and say yes to that prompt.
Now we are going to put in our arbitrary weighted scores for different offenses. The idea being if someone has a murder in their history, that should person should be higher up on the priority list than a person with two or three larcenies. So in our first row, give UCR a value of 1, CrimeLabel as Murder, and Weight as 10.
Here we are only going to give weights to Part 1 crimes. So in the end the crimes and weights I want you to fill in the table are.
UCR CrimeLabel Weight
1 Murder 10
2 Rape 5
3 Robbery 5
4 Agg Assault 5
5 Burglary 2
6 Larceny 2
7 MV Theft 2
8 Arson 2And here is what my end result table looks like. Once done make sure to save the table again (right click on the table tab and select Save).
Another way to accomplish a similar process is to create a table in Excel, and then import it into Access. But I wanted you to know how to make your own table and primary keys directly in Access as well. (You can also write SQL to make a new table.)
Now onto the SQL part. To create a final weighted offender score we need to take several steps using our PersonExploded table (all the people and their involvement type in an incident), the Incidents table (the crime that took place), and our CrimeWeights table we just made.
The steps we will end up taking are:
So we will write three different queries to accomplish those tasks. The step one query will look like:
SELECT
P.incident_num,
P.inv_desc,
P.localid,
I.ucr,
I.UCR_Label,
I.DateIncident
FROM PersonExploded AS P INNER JOIN Incidents AS I
ON P.incident_num = I.incident_num;Note that an INNER JOIN like this can potentially double count crimes associated with persons, e.g. if an incident had a burglary and an assault, they both go into an offenders weight in this example. One way around that is to use a top charge per incident (see the prior weeks tutorial to show an example of that).
The second step we are going to merge in our arbitrary weights we created earlier. Here I simply named the first query Step_01. Here I also only select out persons that are arrested (I don’t give a weight to victimizations or people reporting). And I also eliminate incidents that do not have any weighted associated with them. I then name this query Step_02.
SELECT
S.incident_num,
S.DateIncident,
S.localid,
S.inv_desc,
W.Weight,
S.ucr,
W.CrimeLabel
FROM Step_01 AS S LEFT JOIN CrimeWeights AS W
ON S.ucr = W.ucr
WHERE S.inv_desc = "ARRESTED" AND W.Weight > 0;Now onto the final part – we can sum those weights up to individuals. Here I also only select out the Top 20 folks from the query. I named this query Step_03.
SELECT TOP 20
localid,
SUM(Weight) AS Score
FROM Step_02
GROUP BY localid
ORDER BY SUM(Weight) DESC;And it should look like:
Now go ahead and save all of your work and close the Access database.
Create a new Excel file. Here I am going to show you how to have a linked file from Access (it is easier to work with the Access database when it is closed). Here I just have the focus on Sheet1 and cell A1, and in the Data tab select the From Access button.
Navigate in the file explorer to select the CrimeTables access database we have been working with, and then select Open.
And then in the tables options, you see you can select your queries, as well as all of the original tables. Click on Step_03, and then select OK.
In the next step, you can just keep all of the defaults, and I am importing the Table into A1.
Once you click OK, it will take a few seconds, but will then import the table into our Excel spreadsheet.
The cool thing about this is that it is a dynamic link. So if we update the query, it will then update in our Excel spreadsheet. So we can have automated analyses that gets updated. I will show a simple example for this. Say we wanted to update our chronic offender scores to include a recency score. Go ahead and save the Excel file and close it, then open our Access database back up.
There are multiple ways to do this, but what I will do to create a recent score is identify incidents that occurred in 2015 (this is a bit older data, see last weeks to make a variable based on the current date), and make a separate weight for only those incidents. So lets go back to query Step_01, and make a variable that calculates the year of the incident, and another variable that uses an IIF statement to equal 1 if the incident occurred in 2015.
SELECT
P.incident_num,
P.inv_desc,
P.localid,
I.ucr,
I.UCR_Label,
I.DateIncident,
YEAR(I.DateIncident) AS Year
FROM PersonExploded AS P INNER JOIN Incidents AS I
ON P.incident_num = I.incident_num;And the Step_01 query results should now look like:
Now for Step_02 I am going to add in two new variables. One is an IIF statement, that equals 1 when the year of the incident is 2015 and 0 otherwise. The second new variable is going to be the multiplication of that recent dummy variable and the weighted crime score. I also sorted by the dates descending, so you can see how the variable applies to only cases in 2015.
SELECT
S.incident_num,
S.DateIncident,
S.localid,
S.inv_desc,
W.Weight,
S.ucr,
W.CrimeLabel,
IIF(S.Year=2015,1,0) AS Recent,
Recent*W.Weight AS RecentWeight
FROM Step_01 AS S LEFT JOIN CrimeWeights AS W ON S.ucr = W.ucr
WHERE S.inv_desc = "ARRESTED" AND W.Weight > 0
ORDER BY S.DateIncident DESC;And here is what this table looks like:
Now for Step_03, we can create a new column of the recent weighted scores, along with our cumulative weighted scores as well.
SELECT TOP 20
localid,
SUM(Weight) AS Score,
SUM(RecentWeight) AS RecentScore
FROM Step_02
GROUP BY localid
ORDER BY SUM(Weight) DESC;And this is what the table now looks like:
You can see that only a few of the people have recent incidents in 2015. If you wanted to select based on the ranking of the recent score instead of the cumulative score, you would change the ORDER BY statement to be ORDER BY SUM(RecentWeight) DESC;
Go ahead and save the updated SQL and Access database, and then close the Access database, and finally open back up our prior Excel file we made. You can see we have the same old table we had previously, but navigate to the Data tab in the Ribbon, and hit the Refresh button.
And boom, you will see we now also have our updated RecentScore column in the new results.
So that shows how you can make tables/graphs in Excel that auto-update based on more recent data stored in an Access database.
For your homework, create a top 10 chronic offender list based on violent crimes (UCR #’s 1-4) and a separate list for the top 10 non-violent offenders (UCR #’s 5-8). Include in those two tables names and DOB’s (from the PersonTable).
Five points extra credit, create a top 10 list for all crimes, and add into the table the component items they are based off of (i.e. have additional columns for Murder, Rape, Robbery, Agg Assault, Burglary, Larceny, MV Theft, and Arson weights).