PDA Integrating with Access

The Problem

Although many customers have regular weekly orders, the day of delivery as well as quantities are likely to change from time to time. Other customers order on an ad hoc basis. Orders are likely to change further on delivery, when additional items are frequently requested. These changes have to be recorded and later fed back into the central system. Some customers pay on delivery, while others pay on account.

Separate spreadsheets were used for each week day, with data being manually copied when delivery dates were altered, or overwritten when quantities changed. Managing these processes, and producing the round information for each driver, was very time-consuming and complicated. With no history of previous orders, product and customer analysis was not possible.

The Solution

To begin with, we focused on developing a core Microsoft Access database for storing customer information, round lists and estimated orders. Compiling future orders is much easier with centrally managed round lists.

Each customer has an order screen showing previous orders which can be copied forward, then added to and adjusted as required. All information is readily to hand when communicating with customers so they can be prompted with suggestions for less frequently ordered items. Totals are automatically calculated with relevant discounts applied.

The completed rounds list is downloaded to custom Android application on the user's mobile phone, together with necessary customer information such as current order, money owed from previous orders, telephone number, and address including postcode (for Sat Nav). Addresses are sequenced for efficiency.

A Packing List is printed, listing the totals required for all products. This automatically calculates the correct number of boxes, taking into account the differing box quantities according to each product. Additional loose products are also itemised on the list. A summary round sheet is printed as a paper list of customers to visit on day.

For each delivery, the driver checks the order with the customer and updates the PDA with any additions or changes. The customer then signs the box on the phone app to confirm receipt. The driver enters the money received by the customer and an invoice/receipt is automatically printed.

On returning to the depot all information is downloaded to the central system. This includes any deliveries not completed, changes to orders, and money received from each customer.

Administrative staff have a range of Microsoft Access reports for post sales analysis. They can easily identify the most popular products as well as the most profitable ones. For greater flexibility data is automatically exported to Microsoft Excel. Staff can monitor peaks, troughs and trends, which helps in ordering supplies, as well as strategic and marketing planning. Price changes are easily managed, and tracking debtors can be done simply and effectively.

The custom Android application can be installed on any suitable Android phone. Receipt printing is via bluetooth to an Intermec PB50 which can be easily carried by the driver using an over shoulder strap.

With a computerised rounds system that is flexible and easy to use, our client is able to operate more efficiently, enabling the business to move to the next level. They are currently expanding their product range, and have already taken over additional rounds.