When spreadsheets go bad - time for a database ...

When spreadsheets go bad - time for a database ...

I have come across two identical issues recently that clearly illustrate a key difference between spreadsheets and databases.

Both of the solutions in question concern keeping tabs on dates, especially expiry dates.  One of them is a vehicle maintenance application, so the dates are service intervals and MOTs, and that sort of thing.  The other is about staff certifications and things like fork-lift truck licences and training.

In both cases, a failure to spot an upcoming date could have serious consequences – vehicles on the road without MOT certification, or people operating plant without the relevant accreditations in place.

Both of the datasets are in spreadsheets, one row per vehicle or staff member, and there are a number of columns for the relevant dates.  If a new requirement comes up, more columns go on the end.

This is fine if you are looking at a particular item, to see what the status is of the various elements – just go to the row and look across.  It’s not so good, though, if you want to find all the items that are coming up for renewal – you need to scan all the different columns, and even if you used conditional formatting to highlight dates that fell within the next few weeks, you could miss something quite easily.

The easiest solution is to knock up  a simple database that stores the information in a series of related tables – effectively a table for the items (vehicles/staff), another for the type of activity (MOT, fork lift training renewal) and then the dates themselves in another table – you’d probably want a test date and an expiry date.  Then, all of your expiry dates are in one place, and it’s a simple matter to pull out a report of the upcoming renewals.

If you have a problem like this, send me an email via the contact form, or comment on this post, and I’ll send you a simple template built in Microsoft Access to get you started.

Leave a reply

Your email address will not be published. Required fields are marked *