The data used for this story originated from the National Highway Traffic Safety Administration’s Fatality Analysis Reporting System. The federal agency releases the FARS data a little more than a year after it’s collected. The zipped files come in a .dbf format and were massive, almost 70 megabytes; therefore, they needed to be imported into Microsoft Access to handle the large file size.
Once imported, we were faced with the reality that the entire data set was coded. We assumed this was a residual effort to save space since FARS has been documenting all traffic incidents since 1975, a time when computer memory was a modicum of what it is today. So, in response, we used census data to decode the FIPS-coded states by linking the two tables in Access’ Query Design Wizard in order to begin sorting the information.
Once decoded, we were strictly interested in accident-related trailer fatalities and deaths along with total fatal traffic accidents and deaths. We accomplished this feat using Access’ Query Wizard and, without too many issues, were able to query the data for each year (the above steps had to be repeated for each data set since FARS, naturally, only releases one year at a time).
To ease our query efforts for each data set, we created a decoded table for all fatal trailer-related accidents and deaths for the country for each year. From that table, we were simply able to query and total fatal accidents and deaths by state.
The information was then transferred into Microsoft Excel to ease the sorting, filtering, and subsequent totaling tasks (percent change, etc.) needed to put the data into perspective for the story.
— Lee Francis and Mike Waldron