03_104859 flast.qxp 2/17/07 12:49 AM Page xxviii
PART
I
Fundamentals of Data
Analysis in Access
04_104859 pt01.qxp 2/17/07 12:50 AM Page 1
04_104859 pt01.qxp 2/17/07 12:50 AM Page 2
3
When you ask most people which software tool they use for their daily
data analysis, the answer you most often get is Excel. Indeed, if you were
to enter the key words data analysis in an Amazon.com search, you would
get a plethora of books on how to analyze your data with Excel. Well if so
many people seem to agree that using Excel to analyze data is the way to
go, why bother using Access for data analysis? The honest answer: to avoid
the limitations and issues that plague Excel.
This is not meant to disparage Excel or its wonderful functionalities.
Many people have used Excel for years and continue to use it every day. It
is considered to be the premier platform for performing and presenting data
analysis. Anyone who does not understand Excel in today’s business world
is undoubtedly hiding that shameful fact. The interactive, impromptu
analysis that Excel can perform makes it truly unique in the industry.
However, it is not without its limitations, as you will see in the following
section.
Where Data Analysis with Excel Can Go Wrong
Years of consulting experience have brought me face to face with man-
agers, accountants, and analysts who all have had to accept one simple
The Case for Data Analysis
in Access
CHAPTER
1
05_104859 ch01.qxp 2/17/07 12:50 AM Page 3

distribute easily. You may even consider breaking down the workbook into
smaller workbooks (possibly one for each region). This causes you to
duplicate your work.
In time, you may eventually reach the 1,048,576-row limit of your work-
sheet. What happens then? Do you start a new worksheet? How do you
analyze two datasets on two different worksheets as one entity? Are your
formulas still good? Will you have to write new macros?
These are all issues that need to be dealt with.
4Part I
■
Fundamentals of Data Analysis in Access
05_104859 ch01.qxp 2/17/07 12:50 AM Page 4
Of course, you will have the Excel power-users, who will find various
clever ways to work around these limitations. In the end, however, they
will always be just workarounds. Eventually even these power-users will
begin to think less about the most effective way to perform and present
analysis of their data and more about how to make something fit into Excel
without breaking their formulas and functions. Excel is flexible enough
that a proficient user can make most things fit into Excel just fine. How-
ever, when users think only in terms of Excel, they are undoubtedly limit-
ing themselves, albeit in an incredibly functional way!
In addition, these capacity limitations often force Excel users to have the
data prepared for them. That is, someone else extracts large chunks of data
from a large database and then aggregates and shapes the data for use in
Excel. Should the serious analyst always be dependant on someone else for
his or her data needs? What if an analyst could be given the tools to access
vast quantities of data without being reliant on others to provide data?
Could that analyst be more valuable to the organization? Could that ana-
lyst focus on the accuracy of the analysis and the quality of the presenta-
tion instead of routing Excel data maintenance?

parency of analytical processes. Meaning it is extremely difficult to deter-
mine what is actually going on in a spreadsheet. Anyone who has had to
work with a spreadsheet created by someone else knows all too well the
frustration that comes with deciphering the various gyrations of calcula-
tions and links being used to perform some analysis. Small spreadsheets
that are performing modest analysis are painful to decipher, whereas large,
elaborate, multi-worksheet workbooks are virtually impossible to decode,
often leaving you to start from scratch.
Even auditing tools that are available with most Excel add-in packages
provide little relief. Figure 1-1 shows the results of a formula auditing tool
run on an actual workbook used by a real company. This is a list of all the
formulas in this workbook. The idea is to use this list to find and make
sense of existing formulas. Notice that line 2 shows that there are 156 for-
mulas. Yeah, this list helps a lot; good luck.
Figure 1-1: Formula auditing tools don’t help much in deciphering spreadsheets.
6Part I
■
Fundamentals of Data Analysis in Access
05_104859 ch01.qxp 2/17/07 12:50 AM Page 6
Compared to Excel, Access might seem rigid, strict, and unwavering in
its rules. No, you can’t put formulas directly into data fields. No, you can’t
link a data field to another table. To many users, Excel is the cool gym
teacher who enables you to do anything, whereas Access is the cantanker-
ous librarian who has nothing but error messages for you. However, all
this rigidity comes with a benefit.
Since only certain actions are allowable, you can more easily come to
understand what is being done with a set of data in Access. If a dataset is
being edited, a number is being calculated, or any portion of the dataset is
being affected as a part of an analytical process, you will readily see that
action. This is not to say that users can’t do foolish and confusing things in