top of page

Microsoft Access & Windows
articles et conseils

Access Icon.png
Back Button

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.

Access or Excel 1.png

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 Contains Repeated Data

Access or Excel 2.png
Access or Excel 3.png

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.

Access or Excel 4.png

Figure 4 - Simple Access Switchboard

Still not sure, or need some advice?

Contact us now - we're here to help!

  • 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.

© 2013 DJH Training & Application Solutions Inc.

bottom of page