Access Proves “worth its salt” for IDOT

Today’s guest blogger is Joel Graff. Joel is a field engineer with the Illinois Department of Transportation (IDOT). He’s sharing how he utilized Access to prove the efficiency of their drivers and equipment in managing winter road conditions.

This last year salt prices skyrocketed on us (increasing threefold in some cases). As a result, upper management in IDOT insisted on using more "salt conservative" spreading policies when we apply salt to our roads during snowstorms. New policies were developed and handed out to our maintenance staff. Since IDOT maintenance is terribly understaffed, we triple our work force in the winter with temporary help, which only made abiding by the new policies more difficult.

To manage this challenge, I created a form that each plow driver I'm responsible for must fill out at the start and end of his or her shift. They record the tons of salt spread, the miles driven, and various other information.

Then, I take those forms and enter them into an Access database I wrote to help me track our salt usage. Over the course of the winter, I entered about 1,000 records into the database. I estimate that's about 1/4th of the total number of forms. At the end of the season, I did an informal statistical analysis on the data (including a histogram).

The above image is the general appearance of the database. I’ve found the split view is absolutely critical. I used the Split Form feature here, but often I still have to use my own implementation as the built-in feature is not exposed as a separate object to VBA.

In the General section of the form, the Storm, Teamsection, Shift, and Truck text boxes actually hide combo boxes. When the user clicks on the textbox, it makes the combo box visible (see below image). When the user leaves the combo box, the textbox reappears over top. To do this, I had to use the form’s timer event, otherwise I would run into control focus errors.

Data entry is partially automated, based on the truck that is selected. As a result of carrying over values from previous records, I can cut the number of keystrokes required to enter a record by one half to two-thirds or possibly more. There are 22 bound fields on the current form and can enter a record in as few as 7 fields on average.

The Manage Lists section of the form allows the user to manage the data that makes up the database’s lookup tables. Clicking on one of these options brings up a list manager  (see the Subsection Manager image below). This is a form that’s actually based on a class I’ve written in VBA. Data is updated in the list box as it is typed in the form fields below. The form invokes its own instance of the class I wrote (“clsLookup”). The clsLookup object initializes the form by examining the form’s controls and determines which controls feed the list box data and which controls are used to filter that data. It saves me from having to continually recreate forms from scratch that all function essentially identically.  It also reduces code bloat and errors.

When I proposed this to my maintenance staff, there was considerable resistance. "You'll never get them to fill out those forms!" and "Those electronic spreaders aren't reliable!" were the two most common objections. However, the results were quite valid. The forms were filled out, and with greater consistency than I'd anticipated. Further, the data indicated that our salt spreaders were accurately recording the data and our drivers really were doing what they were told! Without this database, there is no way I could possibly make that assertion with any quantifiable certainty.

In the near future, I will distribute the database to my maintenance yards so the yard supervisors can enter the information themselves and have it available for their purposes (they presently are required to track salt using antiquated software written in 1989). Any data they enter will automatically be submitted electronically, thereby saving me from having to manually enter several thousand records of data next year.

Further, I plan on incorporating a weather data feed so that I can correlate salt usage with varying winter conditions and examine how weather affects our salt spreading habits. That will probably pose the greatest challenge yet.

While this hasn't been my most complex Access project, it certainly proves to be among the most fruitful.