A recent exchange of emails with a reader in Australia left me thinking about the role MS Access should have in the average person's "toolkit". Her specific question was, "Should I use Access or Excel for this project?" My gut reaction, as always, was, "Access, of course." And, at the end of our discussions, that was exactly what I did end up recommending to her. However, we could only reach that conclusion after objectively reviewing the requirements of her project, the tasks she needed to complete with it, and how this new tool will support her business.
So it was a bit of an embarrassment to me to realize somewhere in the last couple of years I've developed the habit of viewing everything in the world as a database problem, and lately doing so without even taking time to evaluate the situation. That's a syndrome I've warned against more than once. Oh well, it doesn't hurt to get a reality check from time to time.
So, when do you want to choose Access over Excel? When would it be better to choose Excel?
I have developed a rule of thumb that I try to apply when that question comes up. I call it the “What happened last year?” criterion. The best way to talk about it may be as a hypothetical.
Let’s say your job requires you to submit quarterly reports on staffing levels and requirements in your department. You can easily put headcounts in a spreadsheet, but you can just as easily put them in a table in Access. You could even type up a Word document, for that matter. Using Excel might make it easier to do some calculations on the numbers, too.
So, which tool is the right one? Well, if someone is going to come to you and say, “Headcounts look a bit heavy this quarter. What happened during this quarter last year?” then you darn well better have that HISTORICAL data for last year safely tucked away in your Access database.
On the other hand, if it is unlikely that someone will ever ask “What happened last year?”, an Excel spreadsheet might be quicker to create and more easily distributed.
