Microsoft Excel Microsoft Access Microsoft Access versus Microsoft Excel for Data Analysis and Reporting

Provided by Luke Chung, President of FMS Inc.

Choosing Between Spreadsheets and Databases

We are often asked by Microsoft Office power users whether, why, and when they should use Microsoft Access versus Microsoft Excel. Especially when they are very comfortable using MS Excel and don't understand the reasons why anyone would use MS Access or databases. Here's our perspective.

We do not believe it's an either/or situation. Our experience with Access and Excel is that they both have strengths and naturally complement each other. Individuals who understand the differences, learn the products, and apply the appropriate tool for the appropriate situation give themselves and their organizations a competitive advantage.

Microsoft Office products empower individuals (Microsoft calls you Information Workers) to get things done on your own. This lets you leverage your knowledge of the work that actually needs to be done to your solution. This is far more efficient than forcing you to escalate problems to "IT professionals" who need help understanding your technical requirements with limited understanding of your business requirements. It becomes nearly impossible to do when one doesn't know the solution yet and is trying and modifying many things.

The learning curve for Excel is very short, so it's easy to use Excel and be productive right away. Rare are the situations where IT staff create spreadsheets Information Workers can do for themselves.

Excel makes it easy to store data, perform numerical calculations, format cells, and adjust layouts to generate the output and reports to share with others. Advanced features such as subtotals, power pivot tables and pivot charts, analysis toolkit, and many templates make it easy to accomplish a wide range of tasks. It can even integrate with the Analytic Services (Business Intelligence) from SQL Server. Tweaking the results is also very easy to get the exact layout, fonts, colors, etc. that you want.

Unfortunately, there's a price for the flexibility of spreadsheets. While it's easy to create formulas, reference cells, copy and paste data, and link worksheets and spreadsheets together, as the work gets more complex, spreadsheets become more difficult to change and manage. While spreadsheets are ideal for creating one time analysis, they become problematic as the data grows and evolves over time. As new rows and columns get added, summary ranges and formulas may need to be modified or new ones created, data and formulas aren't consistently updated, and these mistakes lead to bad results and decisions.

The challenges of spreadsheets are due to the difficulty maintaining them accurately over time and scaling the volume.

For spreadsheet experts, it's often difficult to understand what databases offer that spreadsheets don't already have. There are several advantages of databases:

  • Data structure and normalization through multiple tables
  • Scalability: adding more records is free
  • Data and Referential Integrity
  • Queries and Reports
  • Automation through Macros and VBA Modules

Table Structures and Validation

Databases make it easy to store information in one place and reference it in multiple places. For instance, customer information may be kept in a customer table where the name, address, phone number, email, etc. are kept. It is then referenced in other places such as queries, forms, and reports. If the customer's information changes, the new information is automatically updated in all the referenced places. The table designs also impose structure that lends itself to data types, validation and consistency for higher quality than what normally exists in spreadsheets. The basics of storing numeric, date, and text fields are just the beginning.

Records are Free in Databases

The biggest difference with spreadsheets is that in a database, records are free. If it's well designed, over time, new records are continually added without needing to add new fields. All the queries, forms, and reports continue to work without any changes. Different filters may be applied but the results are always consistent. There's no need to retest the formulas (cells) when new data is added. This allows accurate reports to be generated every year, quarter, month, week, day, etc.

Data and Referential integrity

With the fear of garbage in - garbage out, Access databases provide many tools to maintain data quality. Lookup lists and validation rules for individual fields and records can be easily implement in Access at the table level. Forms can add additional rules during data entry to respond to user selection and events. Access also offers referential integrity between tables to ensure data is consistently defined across tables.

Queries and Reports

Microsoft Access queries and reports let you slice and dice your data and present it in detail or summary form regardless of how the data is stored or sorted in the underlying tables. It offers a great deal of power and flexibility to analyze and present results. Using groupings, it's easy to show aggregations as the data changes and groups get added or removed.

Automation through Macros and VBA Modules

From a macro or VBA module, you can use the DoCmd.TransferSpreadsheet command to export data from an Access table or query to an Excel spreadsheet (use the acExport option). Your spreadsheet can then reference that data or import it into itself.

The TransferSpreadsheet command with the acImport option can also be used to import a spreadsheet into an Access table.

With Office/Excel automation, you can actually open an existing Excel spreadsheet from Microsoft Access, and place data in specific cells, thereby automating the updates of the data there.

Most Advanced Product in Office

Microsoft Access is the most advanced and complicated product in Microsoft Office. Database design and development require a higher level of experience and training than using a spreadsheet or Word document.

Need to Learn How to Create Databases

While one can create a database quickly, creating it properly so that it supports its requirements over time requires skill and training (or experience doing it incorrectly). Figuring out how to define normalized tables, link them together, and structure the information so it's easy to edit, query, view, and report can be quite challenging.

The learning curve for building a database, creating queries, and designing report layouts may seem quite daunting. Many of those issues are not unique to Access since they apply to all database platforms.

Cannot Place Fields Anywhere on a Form or Report

A database is more complicated than just putting data in cells of a spreadsheet. It can be quite frustrating to be unable to easily copy and paste blocks of data, or implement exceptions to the structure of an Access report (for instance, wanting to highlight a specific value or row with special fonts and comments). While MS Access includes VBA code that allows for customization, it requires much more effort than the simple WYSIWYG design of Excel.

Data Visualization is More Advanced in Excel

Some data analysis features such as power pivots exist in Excel but not Access. The charting features are also more advanced and modern in Excel vs. Access, so the output one generates in Excel may look better.

A database is designed to generate mass amounts of data and reports without customizing each one. Excel is great at customizing the output. One workaround is using Access to manage the data and export the data to Excel, or have Excel connect to the Access data for reporting.

Excel makes it easy to generate custom output with very flexible formatting and annotations that you can add anywhere. The payoff with Microsoft Access is how databases simplify things over time. It may be overkill for one time analysis, but if the data and reports need to be maintained over time, spreadsheets often hit a wall. Most organizations have many "similar" spreadsheets that are tweaked slightly differently and rapidly become inconsistent. A well designed Access database avoids that manageability challenge. That said, both Access and Excel have their strengths and weaknesses.

A hybrid solution where data from an Access database is exported or copied to Excel often provides the best of both worlds. The data integrity of a database with its well defined and approved output in conjunction with Excel for ad hoc analysis lets you leverage the advantages of both. Using automated processes, the sharing of data can be very smooth.

In our experience, these solutions evolve over time in very non-predictable ways regardless of technology. Having tools that allow for the flexibility to rapidly respond to such changes empowers you and your organization to meet its mission efficiently.

Good luck!

Table Design

Query Design

Form Design

Form Tips and Mistakes

Copy Command Button and Keep Picture

Module VBA to Forms and Controls

Form Navigation Caption

Resync Record in a Subform

Synchronize Two Subforms

Multiple OpenArgs Values

Late Bind Tab Subforms

Subform Reference to Control Rather than Field

Tab Page Reference

Shortcut Keys


Combo Box Top 6 Tips

Properties and Validation

Select First Item

Cascading Combo Boxes

Zip, City, State AutoFill

Report Design

Suppress Page Headers and Footers on the First Page of Your Report

Add the NoData Event

Annual Monthly Crosstab Columns

Design Environment

Add Buttons to the Quick Access Toolbar

Collapse the Office Ribbon for more space

VBA Programming

Basics: Forms and Controls

Run VBA Code from a Macro

Use Nz() to Handle Nulls

Avoid Exits in the Body of a Procedure

Shortcut Debugging Keys

Set Module Options

Math Rounding Issues

Rename a File or Folder

Avoid DoEvents in Loops

Age Calculations

Weekday Math

Send Emails with DoCmd.SendObject

Source Code Library

Microsoft Access Modules Library

Microsoft Access Modules

VBA Error Handling

Error Handling and Debugging Techniques

Error Number and Description Reference

Basic Error Handling

Pinpointing the Error Line

Performance Tips

Linked Database

Subdatasheet Name

Visual SourceSafe

Deployment

Runtime Downloads

Simulate Runtime

Prevent Close Box

Disable Design Changes

Broken References

Remote Desktop Connection Setup

Terminal Services and RemoteApp Deployment

Reboot Remote Desktop

Missing Package & Deployment Wizard

Avoid Program Files Folder

Unavailable Mapped Drives

Microsoft Access Front-End Deployment

System Admin

Disaster Recovery Plan

Compact Database

Compact on Close

Database Corruption

Remove 'Save to SharePoint Site' Prompt from an Access Database

Class Not Registered Run-time Error -2147221164

Inconsistent Compile Error

Decompile Database

Bad DLL Calling Convention

Error 3045: Could Not Use

Converting ACCDB to MDB

SQL Server Upsizing

Microsoft Access to SQL Server Upsizing Center

Microsoft Access to SQL Server Upsizing Center

When and How to Upsize Access to SQL Server

SQL Server Express Versions and Downloads

Cloud and Azure

Cloud Implications

MS Access and SQL Azure

Deploying MS Access Linked to SQL Azure

SQL Server Azure Usage and DTU Limits

Visual Studio LightSwitch

LightSwitch Introduction

Comparison Matrix

Additional Resources

Connect with Us

 

Free Product Catalog from FMS