Microsoft Access versus Microsoft Excel for Data Analysis and Reporting
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.
Empowering Information Workers
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.
Advantages of Microsoft Excel Spreadsheets
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.
Disadvantages of Microsoft Excel Spreadsheets
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.
Advantages of Microsoft Access and Databases
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.
Disadvantages of Microsoft Access
The biggest disadvantage of Microsoft Access
is that being a database,
it takes more skill and training to use it well.
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. Many of
those issues are not unique to Access since they
apply to all database designs.
The learning curve of building a database, creating
queries, and designing report layouts may seem quite
daunting. It's certainly more complicated than just
putting data in cells of a spreadsheet. It can also 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. Additionally, some data
analysis features such as power pivots exist in Excel
but not Access.
Conclusion
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!
Additional Resources
|
Email Newsletter
Developer Team Blog
Facebook
Twitter