The McKinstry Tool Tracker App: Using Access 2013 and Office 365 to manage critical assets

BarcodeIt’s a common scenario: There is a collection of things that people borrow and return. And any collection has common problems: How do you track the items? Are they available? If not, who has them? Where are they? When will they be returned? What is their condition?

For McKinstry, a builder and manager of energy-efficient commercial buildings, the problem centered on a collection of tools, from meters and sensors to hammers. McKinstry employees and subcontractors use the tools to complete energy audits and small-scale construction projects. Their solution for tracking the tools was simple: a clipboard hung in the tool room. If someone needed a tool, they would go find it in the tool room and, hopefully, mark the tool as checked-out on the clipboard. 

The drawbacks are obvious: People who needed a tool had to go to the tool room to see if one was available, and sometimes they’d forget to mark the tools when they checked one out. Tools were not where they were supposed to be, often getting left on job sites or forgotten about and not returned. Over time, the problem got worse and ended up costing the company thousands of dollars to replace lost and damaged tools.

Enter Access 2013 and Office 365. Microsoft worked with McKinstry to create a tool tracker app using Access 2013 (and hosted on Office 365) for the company to try in their Colorado office. The app features a table for tools, with views that show which are available or checked out, displayed by their storage location. There are also tables for people, jobs, and storage locations, which make it easy to associate this data with each tool. A checkout history shows the usage history of each tool displayed within the new Related Items control. The checkout history displays the last time the tool was checked out and who checked out the tool.

The Access web app UI provides a clear, easy-to-use layout for navigating the data in an Access app, a built-in Related Items control that shows the items associated with the record being viewed (think customers and orders), and a fast text-based search for data. Initial feedback was very positive, but some tweaks were requested, such as a home page with basic directions and buttons for the most common tasks (like checking out a tool).

With the early feedback addressed, daily use began at McKinstry. People could now find out whether a tool was available online from their office before going down to the tool room. They could see where the tool was stored, the condition of the tool, and its usage history. They could see who had the tools and when they were expected back.

While the UI and ease-of-use was appreciated, searching for a tool by typing in the tool name proved to be too much overhead for the people in a hurry to get out the door to their job site. They asked if a faster solution was available, such as a barcode scanner like those used for checkout in retail stores. The feature appears complicated but was actually quite simple to implement in Access. The scanner simply sends keystrokes through a USB connection, so Access uses built-in search to instantly identify the tool based on the code in the record for that tool. A data macro launched from a custom Action Bar button checks out the tool and adds a record to the checkout history with a simple button click.

McKinstry uses the Access app in their Colorado office using an Office 365 account, and plans wider adoption into other offices using either Office 365 or an on-premise app once they deploy SharePoint 2013. They know where their tools are and their current condition–improving productivity and providing a significant cost savings. Watch the video to see the tool tracking Access app in action.