Route activity tracking and management using available technology

Article history: Received 1 August 2011 Available online 10 August 2011 Small organizations that maintain their own fleet and make their own deliveries are responsible for ensuring their drivers are utilizing the most efficient routes while delivering products to their customers. Furthermore, efficient delivery requires that drivers spend as little time as possible dropping off and picking up products, since these activities are referred to as “non-value added activities,” although they are necessary tasks in the order cycle process. To aid in reducing order cycle times, large organizations that can afford it have employed transportation management systems. Unfortunately, small organizations with limited resources are less likely to adopt transportation management systems, despite the need for such automation. One solution is to use available productivity software to track and manage driver route activity in an effort to improve and maintain driver productivity by reducing nonvalue time and identifying optimal routes. This paper will outline how office productivity software such as Microsoft® Access can meet the needs of small organizations with limited resources by describing the development and use of a route activity database that employs an easy-to-use multi-user interface. This paper also includes the details of the underlying infrastructure and the user interface.


Introduction
Route management and delivery can be a complex process that requires complex statistical analysis.Many researchers such as Campbell and Savelsbergh (2005), Chen and Wu (2006), Nowak et al. (2008), Ohlmann et al. (2008), Rand (2009), Ropke and Pisinger (2006), and Tang and Wang (2008) have thoroughly studied the process.The literature on route management and delivery is diverse, but most of this research points to a small number of universal goals for route management and delivery.Moura and Oliveira (2009, p.776) summarized these goals as "(1) to minimize the number of vehicles; (2) to minimize the total travel distance; (3) to minimize the total time; and (4) to minimize the vehicles total waiting time at clients".These goals are paramount to route optimization and management, since their achievement leads to reduced costs and improved efficiencies.Of the four goals discussed by Moura and Oliveira, the goal of minimizing the total travel distance has been widely studied for nearly a century through the application and analysis of the Traveling Salesman Problem (TSP).The TSP presents a dilemma for a salesman who must visit every city within a sales territory and then return to the home city while traveling the least distance necessary (Michalewicz & Fogel, 2003;Turner et al., 1993).
Over the years, numerous methods have been devised to solve the TSP.While some have been simple methods where all of the distances between the points are compared using all of the possible permutations to determine the shortest path to take, others have used complex mathematical calculations.Regardless of the approach taken to solve the TSP, it becomes difficult to solve as the number of stops grows, since the number of permutations of the possible routes can quickly grow making the calculations even more difficult.For example, while "a 10-city TSP has about 181,000 possible solutions, a 20-city TSP has about 10,000,000,000,000,000 possible solutions" (Michalewicz & Fogel, 2003, p.13).
The available literature points out that solving the TSP does not solve all of the problems a salesman would encounter while traveling through the most optimal path, since the shortest route might not be the best route to take.Other factors such as traffic congestion, detours, different speed limits, and a variety of other factors need to be considered (Michalewicz & Fogel, 2003;Turner et al., 1993).Therefore, despite all the attempts to develop sophisticated models to solve the TSP, no models exist that can identify routes free of all delays, accidents, and other unforeseen obstacles to an optimal route.While the use of technology such graphical positioning satellites and software applications have led to improvements in route optimization, more sophisticated systems and models are yet to be developed that truly address all routing issues.
The TSP also applies to local deliveries as much as it does to a salesman traveling throughout a large region.Like traveling salesmen, local delivery drivers must make multiple stops in a given day, before they return to their distribution facility.If their routes are not optimized, they could be on the road longer than is required and wasting fuel in the process.Determining the shortest and most efficient route for the delivery process should take into consideration all of the factors that may impact a given route.Therefore, strictly looking at the distance travelled from one city to the next is not always the best solution for the TSP, nor should the distance between two stops be for the local delivery driver, since traffic congestion, road construction, and traffic patterns can differ substantially in various areas of a given city.
An application of the VRP that helps further establish the theoretical basis for this research is that of Ceselli et al. (2009).Ceselli et al.'s research addressed the tenants of the VRP by developing an algorithm for companies that develop software-based solutions to address vehicle routing problems.Since their research addressed common vehicle routing problems through an algorithm for software solutions, their research shows that software applications designed around advanced algorithms derived from theoretical foundations can help solve routing problems organizations face, although they are still premature in their ability to address the TSP and the VRP with great precision.
It is important to note that TSP focused research, like VRP focused research, attempts to solve the constant and prevalent problem of determining optimal routes.Furthermore, while various approaches and models address the common problem of finding the most optimal routes exist today, no single approach or model has been developed or found that makes the routing problem simple to manage in all situations.In fact, a great deal of the literature points to the difficulty of solving the routing problem through these various approaches and models.
It is apparent that large organizations with large volumes of delivery have more complex routing problems.Although the routing problem is not as extensive for small organizations, small organizations that maintain their own fleet of trucks and make their own deliveries still undergo similar routing problems.Like large organizations, small organizations are responsible for ensuring the products they sell reach their customers as quickly as possible, while at the same time they need to ensure the cost of making those deliveries is minimized as much as possible (Ohlmann et al., 2008).Accomplishing such a task requires tracking and management of the delivery process.Without tracking and management, the delivery process could be operating at a less-than-optimal level and wasting valuable resources without management realizing it.Small organizations, such as Kuna FoodService (2011), realized the need for tracking and management of delivery routes and have adopted systems that resulted in reduced delivery times and a reduction in delivery costs.For example, Kuna FoodService was able to save $4,000 a month by adopting such a system.
Small organizations, such as local electronic distributors, that support businesses within a small geographical area may have three or four trucks that make daily or weekly runs to the usual customers.An organization of this size may not have the revenues to purchase transportation management systems that can cost hundreds of thousands of dollars or more.Therefore, they are forced to rely on drivers to use the best route, spend as little time as possible making deliveries, and share that knowledge with management and the other drivers.This non-automated approach leads to several obvious problems.One of these problems is that drivers may not feel comfortable sharing all of their knowledge with others.Perhaps they feel it gives them more job security if they keep the information to themselves.Another problem is that drivers are not likely to remember how long they took at each location while they dropped off a delivery.Furthermore, they may not remember how long their entire run took.Transferring knowledge becomes more difficult when drivers cannot recall essential details of their runs.
Another problem with a non-automated approach to delivery management is that deliveries could be taking longer than they should be, since there is no historical data to use as a frame of reference.Delivery times could be taking longer than they used to without management realizing it until customers begin to complain.Also, a lack of automation makes it extremely difficult to identify the most efficient routes that utilize the least amount of resources, since there is virtually no visibility of the delivery process.
A solution to the problem is to use available productivity tools to develop a simple database that allows small organizations to track and manage delivery routes in an effort to identify route inefficiencies, improve customer service, reduce fuel costs, and to reduce the time it takes to deliver products to customers.One of these available productivity tools is Microsoft ® Access.Access is a powerful productivity tool bundled in the Microsoft ® Office Professional version and also available separately.The cost of Access as a standalone version is under $300, which is well below the cost of transportation management systems that may be out of reach for small organizations with limited resources.Although Access is readily available as a productivity tool, few small businesses fully utilize it since it is more complex than the other tools available in Microsoft ® Office.Regardless, Office power users can master it in a relatively short time.Furthermore, it is bundled with a variety of sample databases and wizards that make it easy to develop databases from scratch or use one of the many pre-packaged database templates.
Advanced users of Access should be able to develop databases that are user friendly by modifying existing database templates or by developing their own databases from scratch.Access provides many advanced wizards that allow users to develop data tables, input forms, queries, and advanced reports.Databases can be developed that hide all of the complexities of a database and allow users to interact with a graphical user interface that is menu driven.Furthermore, Access is capable of allowing more than one user to access, edit, and retrieve data from the same database at the same time.This multi-user capability enables centralized storage of data.Therefore, all delivery personnel and managers will have access to the same set of data, which should lead to less confusion and more accurate decision making.
A search of available literature on transportation management systems resulted in many articles, such as Ng et al. (2008) and Kant et al. (2008) that describe large scale systems employed by organizations with revenues and sales volumes that justify such large expenditures.Unfortunately, no articles were located that described small scale transportation management systems that were designed and developed within the small organizations that utilize them.Although a need exists for such systems, the research community has paid little attention to it.This article will attempt to address this need in the literature by describing the design, development, and the pilot study of an Access database developed within a small electronic distributor that employs three drivers and is part of a chain of 12 other locations.

Background
A small electronics distributor site in North Carolina is a member of a small electronics distributor organization with 13 locations along the east coast.Each location operates their own fleet of three to five trucks, making deliveries to other businesses and construction sites within a small geographical area.Each location determines the best routes to use and is free to use their drivers as they see fit.Drivers also serve in other capacities such as processing incoming and outgoing stock and other warehouse functions.Each driver is responsible for logging the time it takes to make each run and the time spent at each location during the run.These times are logged in on a sheet of paper maintained by each driver.At the end of the shift, each driver will turn in the sheet to the warehouse supervisor who in turn will fax the sheets to the corporate office once a week.The data accumulated remains in paper form until it reaches the corporate office, and the corporate office uses it to determine resource needs.The process is cumbersome and provides little benefit to each location other than informing the corporate office of the lack of resources to meet delivery needs.Furthermore, a process that requires paper-based entry introduces wasted time and reduces real-time visibility (McAndrew et al., 2005).
To test the feasibility of automating the route tracking process at one of the 13 locations, an Access database system was developed and underwent a pilot study to determine its effectiveness and potential to improve delivery operations within the pilot location.Several objectives for the pilot study were established, as follows: a) Determine if the new system is user friendly, b) Identify the majority of ways the accumulated data can be used to improve delivery operations, c) Identify features that should be removed or added to the system, d) Determine if the system could be adopted by other locations, The initial intent of the system design is to meet corporate data requirements by creating a database that allows drivers to enter their data directly into a graphical user interface after they return from their runs and to give the warehouse manager the ability to email a cumulative report of route data to the corporate office with a single push of a button.Data entered by drivers will allow the warehouse manager and other personnel to determine if delivery drivers are being utilized efficiently, if scarce resources are utilized sparingly, and if delivery runs can be improved.At the end of the pilot study, the data accumulation should enable the warehouse manager to identify trends not readily identifiable using the traditional approach to corporate data reporting.Most of all, the warehouse manager should be able to improve the delivery process by quickly identifying potential problems before they lead to negative customer service trends.

Planning and design approach
The design and development of the system was modeled after a paper-based form used by drivers to annotate the time it takes to make their runs and the time spent at each location.The same fields on the paper-based form were carried over to the new system.The entire system was built using Access 2003, since all computers at the pilot location were equipped with this version and the potential for version conflicts can be minimized by using the same version of Access.A faculty member at a large public university in North Carolina volunteered to develop the system and to train drivers and the warehouse manager on its use during the pilot study.No compensation was obtained for the design, development, or for the management of the pilot study.The faculty member used personal resources for the design and development of the system.
The system was designed and developed over a two week period in the summer of 2010 and deployed during the month of August 2010.After several meetings with the warehouse manager and a driver with over 10 years of experience in the company, a draft of the underlying table structure was developed.See Table 1 for a list of the fields chosen for the two underlying tables.The Microsoft ® Access Relationship window was used to establish a one-to-many relationship between the two tables.Since the Route Activity ID was a common field between both tables, it was used to form the relationship between the two tables.Therefore, Route Activity ID was chosen as the primary key in the Routes Table and as the foreign key in the Route Details Table .This arrangement creates the one-to-many relationship between the two tables, where one record in the Routes Table may have one or more related records in the Route Details Table .Database design standards such minimum field sizes, referential integrity, and data integrity constraints were integrated into the system.Furthermore, the system was designed using a two-database design approach, where the tables reside in a backend database and the remaining objects (queries, forms, and reports) reside in a frontend database.Links were created using the Link Tables Wizard within Access to link the data tables to the other database objects.This design approach allows multiple users to enter and edit data simultaneously, while at the same time reduces the chances of record locking errors as more than one user attempts to edit data through the frontend systems.For this pilot study, two separate copies of the frontend were installed on users' computers.Both frontend databases contained links to the same backend database where the tables resided.Since individual users execute separate frontend databases and automatically link to the same set of data, the entire organization is able to centralize their data and produce reports based on the most current data possible.Fig. 1 to Fig. 3 provide snapshots of the frontend databases used in this pilot study.
The pilot study was conducted over a 30 day period.During this period, drivers utilized their existing route logging forms to annotate the times associated with their routes and upon returning to their warehouse entered the collected data through one of the two frontend databases using the graphical user interface displayed in Figure 1.At the end of each week, the warehouse manager generated cumulative reports that were automatically emailed to the corporate office from the frontend database user interface with a push of one button.Figures 2-3 display part of the process.This feature of the database system automated a task that used to take at least three hours a week to complete and reduced processing time to about one minute, about the time it takes for the warehouse manager to execute the command and for the email to arrive at the corporate office.The warehouse manager was no longer required to accumulate weekly route data from all drivers and then fax the route sheets to the corporate office within the pilot study period.

Limitations
There were several limitations in this pilot study.One of these limitations was the relatively short time period chosen for the study.Since only 30 days were allowed to test the system, the long term effects and benefits of the system could not be assessed.Another limitation was the lack of technology available to allow drivers to enter data while in route.Since drivers were not equipped with computers or remote terminals, they were forced to enter their data on route tracking sheets that were later transferred to the system.This limitation required two major tasks in order to enter the data into the system.Furthermore, the system designed for this pilot study did not include all of the possible features that would be considered essential for a route tracking and management system.
Since the goal of this study was to determine if an available tool, Microsoft ® Access, could be effectively used to meet the immediate route management needs of small organizations with limited resources, the limited features of the system were considered sufficient for this study, but the additional features would have contributed to a more accurate assessment of the system's potential contributions, if they had been included.Furthermore, the design of the system did not take into consideration the differences among organizations requiring such systems.Since small distributors that make their own deliveries have different route data management needs and reporting requirements, a one-system-fits-all approach is not practical or possible without some customization.Therefore, the findings of this pilot study do not necessarily apply to all small organizations with route management needs.

Conclusion and recommendations
Although this pilot study lasted only 30 days, several conclusions were reached at the end of the study.One of these conclusions was that Microsoft ® Access can be used to meet the immediate route tracking and management needs of small organizations with limited resources in a relatively short time.Despite some of the limitations identified above, the application developed and used by a small electronics distributor resulted in a reduction in the time required to compile and submit route tracking and management data to the corporate office from approximately three hours a week to about one minute a week.Furthermore, the distributor was able to centralize route delivery data in one system that allows multi-user interaction with real-time data.The centralization and analysis of data entered by drivers lead to several changes in established routes that appeared to be inefficient.For example, one route delivery time was reduced by two hours by restructuring the scheduled deliveries of the route to reduce driver's travel time without negatively impacting customer service.
Another significant conclusion of this study was identified during interviews of users of the system.The three drivers who utilized the system indicated they found it easy to use and were happy the system was being tested.Two of the drivers did not like having to enter data on their route sheets while making their deliveries and then having to enter the data again in the system.As noted above, this limitation of the system was realized early in the study.Therefore, this user criticism came as no surprise during the user interviews.During the interviews, users did not request any other features other than the ability to enter data directly into the system while in route.In contrast, the warehouse manager requested several new reports that were not already part of the system.Although these reports can be developed and executed within Access using the available Access wizards, the manager stated that his job would be easier if the reports were developed in advance and could be accessed from the user interface.
Although drivers and the warehouse manager requested new features, neither of them requested the removal of any of the features already in the system.In fact, a review of the utilized features of the system and interviews of the drivers and the warehouse manager indicated that all of the included features were used during the pilot study.This remarkable finding could be attributed to the limited features of the systems, since the system was designed specifically for their subject organization and data management needs.Such a finding is not expected if the system was generic in nature and developed for a wider audience.
The data gathered by drivers provided the warehouse manager with a centralized repository of data not previously available, since route delivery data was maintained on separate sheets of paper that were reviewed weekly and later faxed to the corporate office.The new system allowed the warehouse manager to analyze trends in delivery times and gave the manager the ability to identify significant variances in delivery times among the same routes.One such variance was identified with one route during the pilot study.Fortunately, the significant variance in delivery times was a result of additional traffic generated by new road construction along the scheduled route.The warehouse manager identified an alternate route that increased the original delivery time by roughly 15 minutes but reduced the travel time about 50 minutes since the driver avoided delays caused by roadwork.Furthermore, the centralized data also allowed the warehouse manager to use accurate justification for an additional driver, since several of the routes were exceeding the normal workday and leading to overtime pay as drivers completed their runs.Although the manager was aware drivers had to exceed their normal workday without utilizing the new system, the manager did not have any concrete data to present to the corporate office as justification.
A final goal of the pilot study was to determine if the system could be adopted by other locations within the company.In order to determine the likelihood of success within other locations, the pilot study had to result in significant success where little or no difficulties were identified.Furthermore, users and management would have to accept the system and utilize it as designed.Fortunately, this was the case in this pilot study.There were no significant difficulties identified during this study and managers and drivers embraced the system and used it as expected.Therefore, there is a strong likelihood of success if this system is adopted in other locations within the organization.
Although the findings and conclusions reached in this pilot study are based on a relatively small population, where only one small organization was chosen as a test location, the design and implementation of this simple system proved to be successful.On the other hand, since this study was a relatively small study involving only one location, further studies are needed to determine if Microsoft ® Access can be utilized to develop similar route tracking and management systems, utilizing available resources with little or no cost to the organization.Since distributors with limited resources may not be able to afford large generic route tracking and management systems, the results of this study and future expansions of this study should determine if this option is a viable solution.Most of all, future studies of this type should include more locations and last for periods in excess of this study's duration.

Fig. 1 .
Fig. 1.Portion of the Main Data Entry Form Fig. 2. Report Type Selection Screen Fig. 3. Portion of the Cumulative Route Report

Table 1
Route Tracking and Management System Fields