Insurance Company Client Management

Golf Player Insurance Client Management Access Database Rebuild

Insurance providers for individual Golfers, Golf Clubs and Anglers, Golfguard came to Xlteq to rebuild their client management database. Part of their current system is stored over two Access databases (which are no longer supported by the developers), Excel spreadsheets and recorded manually in files. Additionally one database was reaching the limit of the amount of data it could store and was not able to work properly until it had been cleaned up. They understood that this was not a method to run their business and therefore required a new database to be built.

An initial consultation was completed with the client to understand how the business operates, what they wanted to achieve, what expectations they had and to answer any other concerns or questions. From this consultation it was decided that the client required the following:

  • Spreadsheet and paper-based data should be stored in a database so there was no need to go to several different sources to get customer information
  • Easy access to historical customer information (which is especially important for auditing purposes)
  • Data checking and error prevention by moving separate processes into one
  • Automatic schedule and letter production which is not currently possible
  • Automatic email creation for various activities such as late payment, renewal due, etc
  • A better front end in the new database compared to the old one. This should provide error detection, suggested entries, etc
  • Enhanced functionality compared the previous database, such as the ability to renew an insurance premium
  • Additional reports

As this was a complete re-write of two databases, it was key for Xlteq to understand how the current databases were set up and how data was currently being stored before we could suggest a solution. After an in-depth analysis of the database and the current process it was discovered that the two databases actually contained very similar data fields and structures. Therefore the first major change to the current process was to suggest that we create one database rather than two. This would provide the benefits of not only consolidating the data into one place and making it easier for a user to find any customer record and would also reduce development time and therefore cost.

With the requirement to provide an Insurance Schedule to the customers, including the ability to reprint historic documents, it was realised that we needed the facility to store this historic information. As there was the potential for thousands of documents to be recorded, it was unfeasible to simply store all the documents into a folder. Instead a solution was proposed where we store a template of each document type and against each policy we store the information required to populate the template. This greatly reduces the disk space needed as we store the data for the document rather than the whole document itself. It also allows the user to create any document for any policy whenever they need to by simply creating the document from the database rather than trying to find the document in a maze of folders.

The project was agreed by the client and work began on building the new database for which we used a simple menu design giving quick access to the three key areas – Reporting, Data Entry and Administrative functions

Administrator Functions

As with almost all databases, it is sometimes required that the user, when entering data, be presented with a list of options for data entry. This list will need to be updated from time-to-time so we provide a simple screen which gives access to these lists. This puts the client in control of their data without having to rely on any development costs for minor data changes.

Advanced Admin Functions

As we wanted to automate as much of the clients processes as possible, we were able to provide administrative tables which allowed highly complex actions to be performed but were not complicated to use.

For example one data entry field for “Property Damage” required the drop down values to change depending on the values in 2 other fields. Not only was it was possible that the drop down values could change, but the values we had to look up could also change. The solution was to create a lookup table, giving the client a simple data entry screen to populate and update and creating the complicated checks and lookup functionality in code, where the user does not have to deal with.

Data Entry

To Identify any current record, a simple but powerful search function has been provided which returns records, checking the search term in multiple data fields.

Selecting a contact presents the user with the policy information and additional details.

The main policy page provides the key information about the policy with other tabs storing more detailed information on the customers “Equipment” and any monetary “Transactions” they have had with Golfguard.

The “Schedule” screen is a special screen which, as previously mentioned, stores information created to produce the customer’s schedule. When a schedule (or other report, letter, etc) is required to be created, data from here is used. This means data storage is kept to a minimum and if any of the current default values (eg premium values, damage limits, etc) change, the original values which were used are retained.

In order to populate reports and letters, each template has the relevant keyword we want to populate replaced with a code word eg “{PLPoluLimit}”. When we populate the template, we simply replace this code word with the value stored in the schedule screen. This method also allows us to perform additional checks and queries on what data should be populated by intercepting the data we are going to use and doing these checks and changes at this point.

Letters and Emails

The ability to produce a letter and email is a required functionality of the client and this is achieved by providing the user with a simple but comprehensive interface. The user simply chooses the letter to create, selects who to output the letter for (which they can then filter down to specific policies if they want) before finally choosing whether to output the file as a word document, email or even just sent to print.

Reports

Finally a few reports were required and built. Again a very simple interface allows the user to enter a date range between two dates and filter by a specific policy type. The resulting report is displayed for the user using the built-in reporting functionality in Access. This provides a quick and cost-effective method for running the reports.

Contact us to find out more about our Excel and Access Database development and support.


Contact