Spaced Out ::

I was recently sent a set of records to update a database on schools in Central Bedfordshire but realised that it was only a partial match for the full set of schools in said database….. a common enough occurrence for many people maintaining records. For those that use Excel this is actually a real headache….. Excel is not a database system and whilst it has some (very) limited database functionality, one of the key things it can’t do is relationally link different tables together and then run queries on those tables. Its just one of those things Microsoft has left out to get people to use Access, although third party developers have created Add-Ins to do some aspects of this.

It still makes updating a database where you receive the data as a spreadsheet harder, but ultimately the solution is staring you in the face. Ignore Excel from the outset, load the data up in to a temporary MySQL table (or other db) and then perform the JOIN on the data to produce a new dataset that contains all the data. This can be saved and then used to manually update your main database.

JOINs are one of the key elements in relational database management so its worth being familiar with the different types (something every intro to DBs cover). In this particular instance we want a JOIN that links the two tables together based upon school ID and returns ALL the records. That’s a LEFT JOIN and worked a treat.