When to use Access versus Excel
A typical question I run into is 'Why should I use Access when I can do everything I want in Excel?'. The answer is never black and white, but each of these programs have been created to address specific types of problems. I find many cases where people have used Excel for something where they really should have used Access. This usually happens because people are familiar with Excel, but not quite so comfortable with using Access. This can get you into trouble, and you really should ask yourself some questions before you get started. Here are some of the items that go through my mind when I'm trying to help someone decide which of these application should be used:
- What are you trying to accomplish? Excel is better at math, advanced calculations, and graphs (although there are very few things you can do in Excel that you can't do in Access, it can just be a little more difficult). Access is better at large data sets, keeping and organizing lists, and sharing data with other applications. Try to look at what you're doing before you decide which one to use. If you want to compare how different interest rate would affect your mortgage or do a complex what-if analysis then you should use Excel. If you want to create a list of all of your company's sales reps, their addresses and phone numbers, and what software they have on your laptops then you should use Access.
- What's your comfort level with each, and how soon do you need to get this done? If you've got a pressing deadline, now might not be the time to absorb the learning curve of a new software package. If you haven't used Access before don't try to pick it up in an hour. You may want to try working on a project in your spare time, and ease into it.
- Is the data going to be needed by more than one person at the same time? While you can set up a spreadsheet for sharing, it can be very difficult for several people to be editing the same sheet at the same time. Access is much better when you need to have multiple users hitting the data all at once.
- Do you need to provide people with some of the information, but you don't want them to be able to see all of it? Or do you want to make it so that some people can make changes to some items, but make it so that others can not? If so, you will definitely want to use Access.
- Is it possible that you will continue to add to the information over a period of time, so that it will eventually become a very large data set? If so, Access is much better at handling large amounts of data.
- Are you going to need to share the information with other software programs? It is much easier to import into and export out of Access than it is out of Excel.
Answering these questions in advance can save you time in the long run. Stick with Excel when it makes sense, and don't be afraid to get into Access and check it out. I'll be writing a little tutorial on creating a simple table in Access next, and we'll enter some typical user information into it.