"Xlteq provided unparalleled customer service and technical solutions beyond what we had scoped in our original requirement. I can only speak highly of xlteq who went above and beyond in assisting us to resolve various technical problems we were seeking help to address."
Christopher Buckland, Head of Research Costing and Bid Management, Cranfield University
Leading UK University, Cranfield, approached xlteq to review, upgrade and develop their Bid and Contracts Management Database.
The University encompasses several schools and sites. All of which have a variety of types of research contracts and a large number of studies at various stages as well as new proposals for work at any given time. Within one school these were managed using an Access Database, running in MS Access 10. They wanted to extend this so that the database could be used by all schools and the data input and reporting could be standardised and brought up to date with current procedures.
The first step to improving the database was updating it to run on the latest version of MS Access. This had complexity due to the VBA behind the original tool having been created by installing Excel Libraries; which then created errors when the database opened on a machine where the libraries were not installed. A “late binding” approach was used to rewrite any sections of code making use of the libraries so that the database would run on any machine without any extra installations being required.
The rest of the initial work was to improve the look and feel of the original database. The original screen sizes for which it had been designed had necessitated using a very small text size and a very “cramped” format for all the form designs. Hence the initial work was to increase the size of the forms to fill the space available and then to increase text size to make it more readable.
Finally, the forms were rearranged to move various controls onto a set of tabs so that elements could be more spread out and certain sections were only displayed if they were relevant to the data being entered. (Different schools used different sections of the same form so it made sense to separate things onto different tabs.) We also changed the colour scheme to be more representative of the University’s corporate colours.
Data Input was almost entirely through a single form, linked to one large table. The process of creating a new research contract is such that initial data needs to be entered when the research proposal goes out to tender. Subsequent updates and additions are required during the process of quoting, approvals and assignment of contract. Hence the form was required to allow for data input within a number of sub categories but also to provide easy searching for any existing files that were still work in progress. Within all of this, some data validation and reporting was required to create paper copies of various documents for the approvals process.
Some redesign of the database structure was required to concatenate comments and customer references within the various projects. Various fields were also created, discarded or renamed.
All data validation was run from the Form(s), rather than the Table Structure, so that specific printouts could only be created once all the relevant fields had been completed correctly. Extra search functions were also added to the main data input form to make it easier to search for particular records using a variety of criteria and also to identify multiple records at once.
Since this was in some ways a legacy system, many changes had been made to both the forms and the fields behind them over the years. Many of the form controls were no longer required and some had added a level of complexity that was not necessary which facilitated the tidy up process.
The database had originally been set up to generate a wide range of reports for both Contract Approval and Statistical Reporting purposes. However, due to so many changes having been made over the years (to both the database and the procedures driving it), most of the reports either failed to give all the details the team required or had stopped working altogether!
The redesign of this part of the tool has been an iterative process; first getting a report working the way that it did originally, next finding out what changes the customer required and finally adjusting those changes once all the people who are going to use that report have included their requirements.
Most of the reports were moved from being generated in Access Report Templates to being exported to Excel where further data manipulation was then possible. Additional functionality was added to enable the user to select fields to report on and save that format so that a new version of the same report could be generated each month, for example.
It was this stage of the work that required the most understanding of what each part of the data actually represented in real terms as several customised reports were required. Each report required data to be selected on a number of different criteria and functionality had to be added to the Reporting Form in order to allow for these selections.
The installation required the data from the original Back End to be imported into the new structure; this was achieved by creating a blank version of the new Back End and then importing some data and generating other data by means of a series of queries. Once the Back End was in place the Front End could be tested. Initially, it ran very slowly and this was found to be due to several different problems.
Some of the reporting that had run quite quickly in the test environment ran very slowly once more data was introduced and so some of the reporting queries had to be re-written.
There was also a “bloating” effect on the Front End database, which caused all functions to slow down. The database Front End should be small in size (typically 3-10MB depending on temporary tables used, complexity, etc). In fact, we were finding that the Front End was quickly “bloating to more like 50MB, even with almost no data in it. This problem was traced to the setting of “Track Name Autocorrect”, which stored information about every single field name in every single table. Since the original database had one table containing over 150 fields, this caused it to grow in size as soon as it linked to the Back End. Once “Autocorrect” was disabled, the size shrank back to normal and the whole database ran much more quickly and efficiently.
Once the database was running correctly a month’s worth of user testing was run to check all aspects of the functionality. This resulted in some changes to the original data validation criteria in some of the forms and the addition of some new reports. Finally, a small amount of user training was provided to familiarise the main database users with every aspect of the tool in order to allow them to trouble-shoot for others.
Firstly all aspects of Cranfield University database are now fully functional. The interface is user friendly and easy to navigate.
Importantly the speed of the tool has been significantly improved and new functionality coupled with reporting upgrades has improved the insight and efficiency of the department.
Either call us now on 020 3817 6945 or fill out the form below and one of our team will be in touch.