Customer management database built in Access
Local Plumbing firm came to xlteq to design and build a bespoke customer database to manage their client contact and marketing programs.
Flexiflame are a local company dealing with a variety of Gas Appliances and Plumbing Jobs, the most common of which were fitting and servicing Gas Boilers and carrying out Gas Safety Checks for landlords. They had been using a single table Access Database to keep a record of their customers, the jobs they did and the means by which each customer found out about their company, initially just to see which forms of advertising were most effective.
Since Flexiflame had a lot of repeat business there were often multiple records for each customer. There were also errors in the spellings of names and addresses which meant that it was difficult to find an existing customer. It was also necessary to enter another complete record to the database in order to enter information about a new job for an existing customer.
Flexiflame wanted to automate their database so that they could see which Appliance Services and Gas Safety Checks were due each month and contact their customers appropriately. They also wanted a quicker and easier way to search for customers and add information.
Initially, we looked at what the company did and the kind of information they needed to store about their customers. We then created a small but robust relational structure for the database using a number of tables to hold different information, including;
The design kept customers’ personal contact information in a single table, with one record per customer, then created additional tables to allow customers to have multiple “Jobs” and multiple “Appliances”, without actually linking Appliances to Jobs to keep the structure as simple as possible. There was also a table of “Job Lines” to allow for several different activities to be recorded as part of the same Job. A variety of “lookup tables” allowed us to provide managed drop-down selection menus for items such as Appliances, Parts and Activities (e.g. Service, Installation, and Report). This also allowed us to standardise the entries as multiple users had referred to the same activity in different ways; for example, an installation could have been referred to an “Install”, a “Fit”, a “Fitting”, an “Installation”, etc.
Having this structure allowed forms to be designed to enter new or recall existing customer details with sub-forms to enter job and appliance details whilst still having referential integrity for the entries.
The data from the original database contained many records for one customer but often all with slightly different details. It was therefore necessary to make adjustments to the various records in order to be able to use the original data for running queries to populate the new database. We began by sorting on a combination of name and address fields to put all records with similar details together. Unfortunately, most of the next step had to be done manually. This included correcting spellings and copying missing information from one record to another. In most cases this type of data cleansing would simply not be possible but the original batch of data was small enough to make this exercise worthwhile.
Once the data was clean we were able to use a number of queries to populate the new database structure with existing data.
This is where the bulk of the work was done as all of the control and validation for data entry were handled through the forms, rather than any constraints within the database structure. The User Interface was required to control the use of the database going forwards. Its design needed to allow for easy data entry whilst, as far as possible, preventing duplicate entries.
The main form displayed all customer information for one customer at a time with sub-forms in a multi-page control to display all job and appliance information associated with that customer. All additions and deletions to the relevant tables were handled using bespoke control buttons with VBA to enforce certain validation rules for particular fields. In order to keep the database structure as simple as possible, Jobs and Appliances were both linked to a Customer using Database Relationships but not to each other. In order to control the addition of appliances to a particular customer, more VBA was used to create a link between the Job Lines and Appliance Sub-forms and to force the creation of a new appliance of a new Job was being added where a new appliance was being fitted. This ensured clean data going forwards without over-complicating the database itself.
The forms were also designed with as many drop-down menus as possible to make the addition of job and appliance details as quick and easy as possible. This functionality also reduces the need for free field updates which in turn helps negate manual errors.
A Search form was created to allow the user to search for existing customers using Last Name, Postcode, Address or Telephone. The Search Output was then displayed using a form to allow the user to double click to be taken to that customer’s details in the main customer form.
Finally, a new Front Page was added to the database to provide a menu to access several activities such as Searching for or Adding Customers. This Front Page had the option to go to a second menu page in order to make additions and amendments to the various lookup tables controlling the options for Appliances, Parts, Employees, etc. In this way, the user never needed to see the standard Datasheet or Table Design views of the database but could still make additions and amendments to their tables quickly and easily.
Since the original database had not been updated for several months it seemed likely that there would be a need to search for Appliances and their service dates for a variety of time intervals and not just a simple output for the current month. Discussion with the customer indicated that there was a need to check for any appliance that might need servicing and any Gas Safety Report that had been carried out. The reports made use of SQL queries which took their input from the Front Page of the database where search dates could be entered. This was handled by means of VBA to pass the information to the relevant query and then create the appropriate report for printing.
A new version of the database with a better structure for holding the information and a complete user interface to allow easy data entry and prevent duplication and data errors as far as possible.
A quick reference guide to assist with using the new version of the database.
A small amount of initial assistance to demonstrate the functionality of the database and to assist with any queries.
Ongoing support as new functionality is required
Value to our client
Easy and speed of new job and client entry
Quick and functional search ability saving time with customers
Service prompting ensuring customers remain significantly improving customer retention
Additional data fields enabling more effective marketing campaigns, again resulting in improved customer retention and new customer acquisition
Larger data capacity giving a future proof solution
Reduction in human error saving valuable time