Computer Training & Application Solutions made Simple
Contact us for a free consultation
  • Click to send us an email. We'd love to hear from you!   Ottawa       613-656-4229
  • Click to send us an email. We'd love to hear from you!   Montreal    514-342-9494

MS Excel or MS Access? Tips for Choosing the Best Software for the Job

By Deborah J. Sparks

Sometimes choosing between Excel and Access for 'database style' worksheets can be confusing. Let's explore some points to consider to help you decide.

 

CONSIDERATION 1 - WHAT DOES THE DATA LOOK LIKE?

  • Excel stores data differently than Access, in a 'flat file'. If you have data that is related but mostly unique, Excel may be the better choice. See Figure 1 below.

Figure 1 - Excel Flat File

 

  • Access stores data in related tables. If we take the Excel example above, but add wholesaler information, as displayed in Figure 2 below, not only is the same information repeated, it also is difficult to maintain if there are modifications.

Figure 2 - Excel Flat File - Difficult to Maintain and Repeated Data

 

  • Figure 2 above would be a good candidate for Access, splitting the wholesaler information into its own table as shown in the first table in Figure 3 below, where each wholesaler's information is input only once, making it easy to maintain and avoid repetitive data. It would then be related to a products table through the Wholesaler ID field, show in the second table in Figure 3 below.

Figure 3 - Related Access Tables

 

CONSIDERATION 2 - WHAT DO I NEED TO DO WITH THE DATA?

  • Excel is a good choice if you are going to perform things such as sorting, filtering, subtotals and create pivot table and more short-term projects and with files that are not shared.
  • Access is a good choice if you want more powerful queries, sorting, filtering, inputting data through user-friendly forms, outputting data through easily run reports and more long-term projects, in a multi-user environment.

 

CONSIDERATION 3 - WHAT IS THE SKILL LEVEL OF THE USERS?

  • Excel is a program that many users are familiar and comfortable with, so is often used even when Access would be a better choice. If Excel is the best program for the job, though, in order for users to work efficiently and with accuracy, make sure they are properly trained, or that complex applications are developed for them.
  • Access can actually be easier to use than Excel, once the database is properly designed and developed, using a simple Switchboard interface (see Figure 4 below) that allows users to simply point and click on a menu item to add/remove/update data, run reports and more.

Figure 4 - Simple Access Switchboard

 

 

Still not sure, or need some advice?

Call us now - we're here to help!



More Newsletter Articles

© 2013 DJH Training & Application Solutions Inc.

WOULD YOU LIKE TO USE THIS ARTICLE IN YOUR NEWSLETTER, EZINE OR WEB SITE? You can, as long as you include this complete blurb with it: An entrepreneur at heart, Deborah J. Sparks established DJH Training & Application Solutions Inc. over 20 years ago, realizing her dream of creating a company known for providing high quality computer software training, application development and support and exercising her passion for teaching. To learn more and to register for DJH's free newsletter, Innovations, please visit www.djhsolutions.ca.