Microsoft Access 2007 data analysis (with source code)

550 Pages·2007·5.44 MB·english

by  Michael Alexander

01_104859 ffirs.qxp 2/20/07 3:02 PM Page iii
Microsoft® Access™ 2007
Data Analysis
Michael Alexander
01_104859 ffirs.qxp 2/20/07 3:02 PM Page iv
Microsoft®Access™ 2007 Data Analysis
Published by
Wiley Publishing, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256
www.wiley.com
Copyright © 2007 by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
ISBN: 978-0-470-10485-9
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
No part of this publication may be reproduced, stored in a retrieval system or transmitted
in any form or by any means, electronic, mechanical, photocopying, recording, scanning or
otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copy-
right Act, without either the prior written permission of the Publisher, or authorization
through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222
Rosewood Drive, Danvers, MA01923, [phone], fax [phone]. Requests to the
Publisher for permission should be addressed to the Legal Department, Wiley Publishing,
Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, [phone], fax [phone], or
online at http://www.wiley.com/go/permissions.
Limit of Liability/Disclaimer of Warranty:The publisher and the author make no repre-
sentations or warranties with respect to the accuracy or completeness of the contents of this
work and specifically disclaim all warranties, including without limitation warranties of fit-
ness for a particular purpose. No warranty may be created or extended by sales or promo-
tional materials. The advice and strategies contained herein may not be suitable for every
situation. This work is sold with the understanding that the publisher is not engaged in ren-
dering legal, accounting, or other professional services. If professional assistance is
required, the services of a competent professional person should be sought. Neither the
publisher nor the author shall be liable for damages arising herefrom. The fact that an orga-
nization or Website is referred to in this work as a citation and/or a potential source of fur-
ther information does not mean that the author or the publisher endorses the information
the organization or Website may provide or recommendations it may make. Further, read-
ers should be aware that Internet Websites listed in this work may have changed or disap-
peared between when this work was written and when it is read.
For general information on our other products and services or to obtain technical support,
please contact our Customer Care Department within the U.S. at [phone], outside the
U.S. at [phone] or fax [phone].
Library of Congress Cataloging-in-Publication Data Available from Publisher
Trademarks: Wiley, the Wiley logo, and related trade dress are trademarks or registered
trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other
countries, and may not be used without written permission. Microsoft and Access are trade-
marks or registered trademarks of Microsoft Corporation in the United States and/or other
countries. All other trademarks are the property of their respective owners. Wiley Publish-
ing, Inc., is not associated with any product or vendor mentioned in this book.
Wiley also publishes its books in a variety of electronic formats. Some content that appears
in print may not be available in electronic books.
01_104859 ffirs.qxp 2/20/07 3:02 PM Page vii
About the Author
Michael Alexander is a Microsoft Certified Application Developer
(MCAD) with more than 14 years experience consulting and developing
office solutions. He currently lives in Plano, TX where he serves as a Senior
Program Manager for a top technology firm. In his spare time he runs a free
tutorial site, www.datapigtechnologies.com, where he shares basic
Access and Excel tips to the Office community.
vii
01_104859 ffirs.qxp 2/20/07 3:02 PM Page ix
Credits
Acquisitions Editor Project Coordinator
Katie Mohr Patrick Redmond
Development Editor Graphics and Production
Kelly Talbot Specialists
Jennifer Mayberry, Barbara Moore,
Technical Editor
Rashelle Smith
Todd Meister
Quality Control Technician
Production Editor
John Greenough
Angela Smith
Proofreading
Copy Editor
Lisa Stiers
Travis Henderson
Indexing
Editorial Manager
Aptara
Mary Beth Wakefield
Anniversary Logo Design
Production Manager
Richard J. Pacifico
Tim Tate
Vice President and Executive
Group Publisher
Richard Swadley
Vice President and Executive
Publisher
Joseph B. Wikert
ix
02_104859 ftoc.qxp 2/17/07 12:49 AM Page xi
Contents
About the Author ix
Acknowledgments xix
Introduction xxi
Part I Fundamentals of Data Analysis in Access 1
Chapter1 The Case for Data Analysis in Access 3
Where Data Analysis with Excel Can Go Wrong 3
Scalability 4
Transparency of Analytical Processes 6
Separation of Data and Presentation 7
Deciding Whether to Use Access or Excel 8
Size of Data 9
Data Structure 9
Data Evolution 9
Functional Complexity 10
Shared Processing 10
An Excel User’s Guide to Access: Don’t Panic! 10
Tables 11
Queries 11
Reports 12
Macros and VBA 12
Summary 13
Chapter 2 Access Basics 15
Access Tables 15
Table Basics 16
Opening a Table in the Datasheet View 16
Identifying Important Table Elements 17
xi
02_104859 ftoc.qxp 2/17/07 12:49 AM Page xii
xii Contents
Opening a Table in the Design View 17
Exploring Data Types 19
Before Creating a Table 20
Creating a Table with Design View 21
Field Properties 24
Primary Key 26
Getting Data into Access 28
Importing 28
Linking 28
Things to Remember About Importing Data 28
Importing Data from an Excel Spreadsheet 29
Importing Data from a Text File 30
Understanding the Relational Database Concept 30
Why Is This Concept Important? 30
Excel and the Flat-File Format 31
Splitting Data into Separate Tables 33
Foreign Keys 34
Relationship Types 34
Referential Integrity 36
Cascading Updates and Deletes 37
Query Basics 37
What Is a Query? 37
Creating Your First Select Query 37
Sorting Query Results 40
Filtering Query Results 42
Querying Multiple Tables 43
Refining the Query 45
Using Operators in Queries 46
Exporting Query Results 49
Summary 49
Chapter3 Beyond Select Queries 51
Aggregate Queries 51
Creating an Aggregate Query 52
About Aggregate Functions 56
Group By 56
Sum, Avg, Count, StDev, Var 57
Min, Max, First, Last 58
Expression, Where 58
Action Queries 61
Why Use Action Queries? 62
Make-Table Queries 62
Why Use a Make-Table Query? 62
What Are the Hazards of Make-Table Queries? 62
Creating a Make-Table Query 63
Delete Queries 65
Why Use a Delete Query? 65
What Are the Hazards of Delete Queries? 65
Creating a Delete Query 66
02_104859 ftoc.qxp 2/17/07 12:49 AM Page xiii
Contents xiii
Append Queries 68
Why Use an Append Query? 68
What Are the Hazards of Append Queries? 69
Creating an Append Query 71
Update Queries 74
Why Use an Update Query? 75
What Are the Hazards of Update Queries? 75
Creating an Update Query 75
AWord on Updatable Datasets 78
Crosstab Queries 78
Using the Crosstab Query Wizard 79
Creating a Crosstab Query Manually 84
Using the Query Design Grid to Create Your Crosstab
Query 85
Customizing Your Crosstab Queries 88
Summary 90
Part II Basic Analysis Techniques 93
Chapter4 Transforming Your Data with Access 95
Finding and Removing Duplicate Records 96
Defining Duplicate Records 96
Finding Duplicate Records 97
Removing Duplicate Records 100
Common Transformation Tasks 102
Filling in Blank Fields 102
Concatenating 104
Concatenating Fields 104
Augmenting Field Values with Your Own Text 105
Changing Case 107
Removing Leading and Trailing Spaces from a String 109
Finding and Replacing Specific Text 110
Adding Your Own Text in Key Positions Within a String 112
Parsing Strings Using Character Markers 116
Query 1 118
Query 2 119
Summary 120
Chapter5 Working with Calculations and Dates 121
Using Calculations in Your Analysis 121
Common Calculation Scenarios 122
Using Constants in Calculations 122
Using Fields in Calculations 123
Using the Results of Aggregation in Calculations 124
Using the Results of One Calculation as an Expression
in Another 124
Using a Calculation as an Argument in a Function 125
Using the Expression Builder to Construct Calculations 126
02_104859 ftoc.qxp 2/17/07 12:49 AM Page xiv
xiv Contents
Common Calculation Errors 130
Understanding the Order of Operator Precedence 130
Watching Out for Null Values 131
Watching the Syntax in Your Expressions 133
Using Dates in Your Analysis 134
Simple Date Calculations 134
Advanced Analysis Using Functions 135
The Date Function 135
The Year, Month, Day, and Weekday Functions 139
The DateAdd function 141
Grouping Dates into Quarters 143
The DateSerial Function 145
Summary 146
Chapter6 Performing Conditional Analysis 149
Using Parameter Queries 149
How Parameter Queries Work 151
Ground Rules of Parameter Queries 151
Working with Parameter Queries 152
Working with Multiple Parameter Conditions 152
Combining Parameters with Operators 153
Combining Parameters with Wildcards 154
Using Parameters as Calculation Variables 155
Using Parameters as Function Arguments 156
Using Conditional Functions 159
The IIf Function 159
Using IIf to Avoid Mathematical Errors 159
Using IIf to Save Time 161
Nesting IIf Functions for Multiple Conditions 163
Using IIf Functions to Create Crosstab Analyses 164
The Switch Function 166
Comparing the IIf and Switch Functions 167
Summary 169
Part III Advanced Analysis Techniques 171
Chapter 7 Understanding and Using SQL 173
Understanding Basic SQL 173
The SELECT Statement 175
Selecting Specific Columns 175
Selecting All Columns 176
The WHERE Clause 176
Making Sense of Joins 177
Inner Joins 177
Outer Joins 178
Getting Fancy with Advanced SQLStatements 179
Expanding Your Search with the Like Operator 180
Selecting Unique Values and Rows without Grouping 181
02_104859 ftoc.qxp 2/17/07 12:49 AM Page xv
Contents xv
Grouping and Aggregating with the GROUPBYClause 182
The HAVING Clause 183
Setting Sort Order with the ORDER BYClause 183
Creating Aliases with the AS Clause 183
Creating a Column Alias 184
Creating a Table Alias 184
SELECT TOPand SELECT TOPPERCENT 184
Top Values Queries Explained 184
SELECT TOP 186
SELECT TOPPERCENT 187
Performing Action Queries via SQLStatements 187
Make-Table Queries Translated 187
Append Queries Translated 188
Update Queries Translated 188
Delete Queries Translated 188
Creating Crosstabs with the TRANSFORM Statement 188
Using SQLSpecific Queries 189
Merging Datasets with the UNION Operator 189
Creating a Table with the CREATE TABLE Statement 191
Manipulating Columns with the ALTER TABLE Statement 192
Adding a Column with the ADD Clause 192
Altering a Column with the ALTER COLUMN Clause 193
Deleting a Column with the DROPCOLUMN Clause 193
Summary 193
Chapter8 Subqueries and Domain Aggregate Functions 195
Enhancing Your Analysis with Subqueries 196
Why Use Subqueries? 197
Subquery Ground Rules 197
Creating Subqueries without Typing SQLStatements 198
Using IN and NOT IN with Subqueries 201
Using Subqueries with Comparison Operators 201
Using Subqueries as Expressions 202
Using Correlated Subqueries 203
Uncorrelated Subqueries 203
Correlated Subqueries 203
Using a Correlated Subquery as an Expression 205
Using Subqueries within Action Queries 205
ASubquery in a Make-Table Query 205
ASubquery in an Append Query 205
ASubquery in an Update Query 206
ASubquery in a Delete Query 206
Domain Aggregate Functions 208
Understanding the Different Domain Aggregate Functions 210
DSum 210
DAvg 210
DCount 211
DLookup 211
02_104859 ftoc.qxp 2/17/07 12:49 AM Page xvi
xvi Contents
DMin and DMax 211
DFirst and DLast 211
DStDev, DStDevP, DVar, and DvarP 211
Examining the Syntax of Domain Aggregate Functions 212
Using No Criteria 212
Using Text Criteria 212
Using Number Criteria 213
Using Date Criteria 213
Using Domain Aggregate Functions 214
Calculating the Percent of Total 214
Creating a Running Count 215
Using a Value from the Previous Record 217
Summary 219
Chapter9 Running Descriptive Statistics in Access 221
Basic Descriptive Statistics 222
Running Descriptive Statistics with Aggregate Queries 222
Determining Rank, Mode, and Median 223
Ranking the Records in Your Dataset 224
Getting the Mode of a Dataset 225
Getting the Median of a Dataset 227
Pulling a Random Sampling from Your Dataset 229
Advanced Descriptive Statistics 231
Calculating Percentile Ranking 231
Determining the Quartile Standing of a Record 233
Creating a Frequency Distribution 235
Summary 240
Chapter10 Analyzing Data with Pivot Tables and Pivot Charts 241
Pivot Tables in Access? 242
The Anatomy of a Pivot Table 243
The Totals and Detail Area 243
The Row Area 244
The Column Area 245
The Filter Area 245
Creating a Basic Pivot Table 246
Creating an Advanced Pivot Table with Details 250
Saving Your Pivot Table 252
Sending Your Access Pivot Table to Excel 253
Pivot Table Options 254
Expanding and Collapsing Fields 255
Changing Field Captions 255
Sorting Data 256
Grouping Data 256
Using Date Groupings 259
Filtering for Top and Bottom Records 260
Adding a Calculated Total 261