When To Use Access, When To Use Excel

Tea or coffee? Shoes or trainers? Access or Excel? Life is full of different options but most of the time the decision comes down to practical requirements. If you need a shot of energy to get you through the day then you'll be better off with a big mug of coffee. Likewise if you have an important business meeting scheduled it is probably best to stick to more formal footwear.

But say that you have a volume of information that needs assimilating and analysing within a functional, logical framework. Both Excel and Access can fulfil the role superficially but there are crucial differences between a spreadsheet and a database that can radically affect the processing of data.

Microsoft Excel's spreadsheets are non-relational, which means that the data that is formulated in them can only relate to itself. For example, a list that groups together employees with their particular salaries can be manipulated into various formats (alphabetical order, highest earning to lowest etc.) but it can only do that based on the data that is present in the list. As such its purpose is useful but limited.

Microsoft Access on the other hand is a relational database. This means that interconnected data can be stored in more than one table and that each of these individual table relates to each other. So as well as simply recording employees' salaries you can now cross reference that with wage increases, employment period on a particular salary and many other variations of data.

As you can see from the above example a relational database such as Access is more sophisticated and less limited in its application than Excel. When working within a large corporation with substantial numbers of data records Microsoft Access is definitely the preferred option. Complex inter-relations between data can only be handled effectively by Access, especially if they are predominantly text based rather than numeric. In addition to this many corporations use relational databases so the adoption of Access would significantly increase compatibility with other organisations' data.

Taking all of these advantages into account you could be forgiven for wondering why Excel still even exists let alone why it is such a popular choice with users. Surely a database that allows you to create so many levels of inter-relational data tables should wipe the floor with a basic, 'flat file' program that can only handle one table of data at a time.

Well, yes and no. There is no doubting the higher capabilities of Access over Excel but by the same token Access' increased sophistication requires a more complex understanding of how the system works and this can put off a lot of users who just want to create a straightforward list.

Yet a training course can easily fill in these knowledge gaps and provide the user with the wherewithal to create far more comprehensive and efficient tables and lists than would be possible in Excel. It may be more challenging in the short term but in the long term a fully functioning relational database can simplify professional life enormously.