Microsoft Access vs Excel

Excel is intuitive.  Anyone can open it up and, with just a few minutes of poking around, get the idea behind Excel.  If you need to jumble up some numbers Excel will not disappoint.  It’s unlikely that you’ll find much that you can’t do with your data here.

But what happens when you outgrow Excel?  Usually the problem isn’t that we as users outgrow Excel, but that our data does.  I’ve seen users outgrow Excel in at least three different ways:

  1. Tons of data.  Have you ever seen a spreadsheet that takes over a minute to open?  Sure, Excel can handle these massive files, but it gets frustrating as your file gets bigger and bigger.
  2. Tons of users.  By tons of users, I mean more than one.  If your data is in Excel and your coworker needs to get in and make changes, you have to save the file, put it somewhere that she can get to it, and wait for her to finish.  She might be updating just one entry, but you’re locked out until she’s done.
  3. The 10,000 monkeys.  Have you ever been working in Excel and realized that you sorted only *part* of your data about 20 edits back?  Oh No!  Hope you’ve got a saved backup.  If you’ve got several people playing with the file, the chances of this increase exponentially.  Yes, you can lock the file but Excel is designed to be flexible and now you’re removing that flexibility.

If you’ve ever encountered any of these situations, you should give Access a try.  It can handle more data, more users, and is generally harder to fat finger your data.

Access has a steeper learning curve.  I’ve seen users that’ve just dumped their Excel files into it and thought they were using Access…  They weren’t.

One of the things that allows Access to be more powerful is it’s ability to define data.  Have you ever had an Excel spreadsheet of addresses where someone accidentally put a zip code into the state field, “Missouri” instead of “MO”, or “Independence, MO” instead of just “Independence”?  In Access you can define your fields upfront to prevent this sort of thing.  Yes, you can do this in Excel too, but in Access it’s built into the process and more powerful.

Let’s say you’ve got a list of names and you’ve added a contact type next to each such as, friend, neighbor, relative, business contact, etc.  In Excel each of these is stored in a cell and that cell contains whatever text you type (even if you misspell “Neighbor”).  If you have 20,000 addresses, you’re loading all of this text into the memory of the computer each time you open the file.  This is why your Excel files take longer and longer to load as they grow.

In Access, you can add a contact type and just store a very small number (1, 2, 3, etc) next to each of your contacts.  The screen will still show “Neighbor,” or “Friend,” but internally the computer is only storing a very small number, and best of all, this doesn’t need to be loaded into the computer’s memory until you need it.  A properly designed Access file will load into memory fast regardless of how much data you’ve got.

You can also store your Access file on a network drive and allow other people to use it at the same time.  Most users only edit one record (row) at a time.  The only time you’ll run into problems is when you and your coworker are trying to edit the exact same record, and Access will let you know when the record is in use.

In Access it’s impossible to sort only half your columns.  Because Access is able to handle so much more data, it’s set up to prevent some of the most common mistakes made in Excel.

But, Access isn’t intuitive the way Excel is.  You’ll need to learn a little bit before you’re ready to transfer your data over.  The payoff can be considerable.  Drop a note if you’re interested in learning more.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s