Customer Management Database Built in Access
Leading plumbing firm, Flexiflame, engaged xlteq to design and build a bespoke customer database to manage their client contact and marketing activity.
The Problem
Flexiflame are a 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.
The Solution
Database Design
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;
- Customers
- Jobs
- Job Details
- Appliances
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.
