Design Decisions: Access vs Excel (a Case Study)

My experience with the corporate world had been fairly average -- typical bureaucracy, chain-of-command for approvals, slow-moving business processes. Although it can be stifling, I can deal with it. But, the thing I have the biggest issue with that I've come across is the over-use of Excel.

I love Excel, it's a fairly powerful GUI-based analysis tool. But, we must always remember that it's exactly that: an analysis tool.

One of the recent projects I've been assigned to at my job is to improve the process for scheduling. The design for the current solution is both impressive and atrocious.

The guy who created it handles all the finances for my employer and guess what he loves to use! Now, I am not trying to knock him, in fact, he's a really great guy and definitely knows his way around Excel. However, I think he was trying to hammer a nail with a wrench (It's possible, but perhaps not the right tool).

His design was that a master file with one sheet would hold all information about permanent schedules. Then he created 4 separate files -- one for each department -- that referenced the master file. Then, for each of the 4 departments (plus one because one department has separate schedules for the first and second shift), he created a roster for each of the 12 periods. Let's count that, shall we? 1 + 4 + (5 * 12) = 65. 65 Excel files!

The design gets the job done, but it makes maintaining the schedules a time-consuming task. It takes me about 5-6 hours a week just to cover vacations and make all the necessary adjustments.

When somebody has a vacation or temporary schedule change, that needs to be done in the middle tier. If one person is on vacation, that has to be manually entered in one file. When someone is covering, that has to be manually addressed. If that associate is not in the department they're covering in, you have to go find the one he is in, edit his schedule for that week to say that he's in another department, and then manually move him over to the correct department's file. There is a lot of repetition and a lot of room for human error.

Now, there is a lot of resistance to change in the company, so I cannot completely scrap everything, but I can get rid of the two top tiers.

The type of data that's being entered in these two top tiers is data that needs to be highly available to the supervisors and needs to retain its integrity. Access, in this instance, is a lot better suited to this sort of task. The database I designed, before the forms and query definitions, was about 2mb where just one of the middle tier files was 6mb in size.

Now, I know what you're thinking.

"Megabytes? That's tiny! Databases are used to store terabytes of data!"

Yes, that's true. The amount of data is fairly small; however, using Access, I was able to cut the size of the top two tiers by 93%! Remember, there are 5 files in the top tiers.

On top of that, whenever there is a schedule change, I just have to make one adjustment and write a query that automatically makes the change for me. So, at worst, that's a 50% time-savings. Plus, vacations can automatically be entered because there's already an existing database with all of them!

And, on top of that, multiple people can view and work with that data at the same time, now.

So what are you supposed to take away from this?

  1. Excel is overused in the business world.
  2. It's possible to treat Excel as a database (especially with the data model feature in Excel 2016) however, it will be bloated and can be slow and even crash with medium or large amounts of data.
  3. If you have data that needs to keep it's integrity and needs high availability, it's better to use Access than Excel.

If you've gotten this far, I'd like to thank you for taking the time to read my post. If you feel you've learned something from this, I ask you to share this with your coworkers and friends on any social media platform you choose.

Leave a Reply

Your email address will not be published. Required fields are marked *