Resource and Maintenance Scheduling Access Database

Task and Resource Scheduling Database Built in Access

Our client provide regular and ad hoc lawn maintenance services. The business is well established and uses an existing Microsoft Access database, running on Access 2010, to maintain customer and lawn treatment records as well as generating invoices and customer mailings. The client needed to update the database. In particular, they wanted to develop a paperless approach to customer communication.

The Solution

Xlteq Access Database Consultants redesigned the legacy database to include new screens and functionality.

The Process

Developing a legacy Access database with the file and table structure already in place can add extra complexity. As well as the requirements in the proposal, there were a number of functions that were working and needed to retain their existing logic. Screens and queries were redesigned but, where possible, existing processes were not amended, this had the added advantage of making testing easier.

The existing menu system was insufficient, so the new screens needed to be branded and designed around a Switchboard Menu System. Existing screens from other functionality also needed to be updated where necessary to reflect the new look and feel of the database.

The new design included a number of new Customer Configuration Flags and the delivery mechanisms for communications was extensively overhauled.

Switchboard Menu System

The existing legacy Access database screens and control buttons were inefficient for the new requirement and had been replicated many times. The menu screens were all removed and replaced by a “Switchboard Menu”. This is concise, guiding the user through logical groupings of the database’s functionality, and removes the replication of multiple menu screens. The menu (as the other screens) became colour-coded and branded using the clients corporate colours and logo.

Forms to gather information and select groundsmen have been rewritten and reformatted to the company’s branding. 

Figure 1: The Switchboard Menu and Parameter Form

Customer Maintenance and New Flags

Customer maintenance needed to be changed to include adding new customers and maintaining existing ones.

5 new flags had to be implemented to manage communications and formatting of letters to specific customers

  • Master Customers
  • Commercial Customers
  • Email Communication
  • Text Reminders
  • Direct Debit

Each of these flags is set independently and has multiple effects

Figure 2: Customer Details Form Showing Master and Duplicate

Master Customers allow a customer to have other customers be set as surrogates. This allows for invoices for a surrogate customer to be sent to the Master Customer, while all the other communications go to the surrogate.

Commercial Customers and Direct Debit flags affect the way that invoices and other communications are formatted when published. For example - Commercial customers show VAT and Direct Debit customers do not have requests for payment, only a confirmation that the work is covered by the customers’ Direct Debit scheme.

Email Communication affects whether invoices and reminder cards are printed and sent or sent by email. These flags again affect the formatting of these documents to prepare them appropriately for printing or emailing.

Scheduling

The scheduling functionality is used extensively by the administrator, not only to ensure that the groundsmen are in the right places at the right times, but also to ensure that their schedules for a given day are efficient.

The scheduling screen has a number of filters to cut and slice the available customers and treatments that are required. Direct debit customers are highlighted and given priority as that is part of the offering from the company.

Figure 3: View of the Scheduled List

Replacing the Form Pivot

The ability to produce Pivot Forms in the Access Database was deprecated from the product following Access 2010. Therefore, any pivot functionality in an existing database is lost if Access is upgraded to a later version than 2010.

However, it is very important to the customer that they can see a list of the employees and their schedule around a chosen postal area in one place on the screen, to allow scheduling to ensure that jobs close to each other can be arranged for the same employee in the same Customer.

Figure 4: View of the Post Code Search

In the pane at the bottom of the screen, a VBA function has been created to emulate the output of the Pivot Table, showing how many jobs and value of jobs that each employee has in the area on a day. This code bypasses the inherent difficulties of replicating Pivots, which can show and name any number of columns automatically, where a normal form cannot.

Sending Letters

Letters include :

  • New client welcome
  • Invoices
  • Marketing communications

The letters are split between those known to be for email customers and those that prefer the paper postal service. The flags for email customers ensure that the correct customers are in the correct list. Invoices are formatted to show the correct messages for the commercial customers, direct debit customers and sent to the correct addresses in the event of there being a master customer.

Figure 5: View of Welcome Letters

Contact us for a free consultation

Either call us now on 020 3817 6945 or fill out the form below and one of our team will be in touch.

The contact form requires that you configure reCAPTCHA in the site configuration.

The contact form requires that you select an email template.