To many people, there is some confusion as to what the difference is between Microsoft Access and Microsoft Excel. To try and clear some of the confusion up, I’m starting to write a series on Access Vs. Excel. In this part, I will be examining example cases where it’s better to use Access rather than Excel.

Here are the three example cases:

• You need to store complex dynamic data and report on it - In this case, you have data that is going to change over time and it’s not straightforward (think many interrelationships). Not only that, but you will need to be able to produce reports on this data even after it changes. While this can be done in Excel, it is usually easier to do this in Access. One reason for this is because it is generally easier to dynamically alter/add/subtract data in Access once you have the database setup. Consequently, because your reports in Access will be based off of database queries, you shouldn’t even have to update the reports to get the new data, you should just have to run the queries. In Excel, you will spend hours deleting rows, adding rows, reformatting your report, and all the other fun tasks that follow, and you’ll have to do this each time you change the data.

• You will need to be able to create custom reports on the fly - This is another huge advantage for Access. Once again, you can design new queries easily in order to create a custom report. All you then have to do is run the queries to get your new report. In Excel, this would be a much more arduous process, as you would probably have to create a new spreadsheet, link the cells, transfer the data, format it, etc.

• You need to store your data for a period of time - If you need to store your data for the long run, Access is the hands down choice. It is much easier to take this data later and put it into other databases or manipulate it in your own database rather than digging through an excel workbook trying to cut and paste what you need, or change it.

Even though Access is the better choice in the example cases above, there are times when Excel is the better option. The next article in this series will deal with these example cases.