Resolving Access Problem and Data Export

The Problem

This charity approached us for help solving their Access database problems. Forms did not work correctly so staff had to enter data directly into tables which was a laborious process. With no validation considerable care had to be taken to avoid errors. Information appeared to be "missing" as fields had been changed, renamed or deleted.

It was difficult to find information in the database, which was particularly frustrating as the charity receives many telephone requests for specific information about brand labels and companies. The information is also exported to produce and print leaflets every two years, however it took a long time to collate this information as over 40 separate queries had to be run. Finally, it was difficult to add new product types.

The Solution

A complete rewrite of the Access database was required in order to provide a solid foundation. With a new data model that accurately reflects our client's requirements we were able to quickly build forms that matched the way they wanted to work.

We extracted the original data from the existing database, having solved "missing data" issues, and imported this into the new database. The new forms organise the information about the brands, together with the companies who own them, exactly as they need to see this. It is now easy for them to monitor these relationships.

A key use of the database is to track the ownership of “brands”. For instance it is very common for ethical small businesses to be bought up by unethical larger companies. Members of the public are often unaware that the underlying ethics of a brand name have changed. For instance the profits made by the brand will be used according to the policies of the parent company. This is a deliberate tactic to improve the image of a company whose unethical practices have been uncovered.

Having built a bespoke database, relevant information is now linked together logically. We provided a simple search facility so staff can find a company or a brand quickly and easily by a wide range of criteria.

If a brand is sold or taken over it is very easy to switch the ownership whilst automatically retaining this history. In this way information is kept up to date. It is very easy to add new companies and brands, as well as products and categories. Validation has been incorporated to speed up data entry and minimise errors.

We automated the data export of all the information needed for their leaflet print-runs, so this can now be done with just one click of a button. All relevant data is exported to a file, categorised and ordered, ready for sending to the print company.

This project is an excellent example of why a bespoke database is beneficial, and even essential, for relatively simple databases and smaller amounts of data. An “off the shelf” contact management system cannot cater for specialist requirements such as these. Key information could certainly be entered into a catch-all “Notes” field, but it is then much harder to search and track this information.

All this was achieved for a relatively modest outlay.