Microsoft Access 2007 Data Analysis P1

Chia sẻ: Vong Phat | Ngày: | Loại File: PDF | Số trang:30

Thêm vào BST

Báo xấu

141
lượt xem 27
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

If you were to ask a random sampling of people what data analysis is, most would say that it is the process of calculating and summarizing data to get an answer to a question. In one sense, they are correct. However, the actions they are describing represent only a small part of the process known as data analysis.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Microsoft Access 2007 Data Analysis P1

Microsoft® Access™ 2007 Data Analysis Michael Alexander
Microsoft® Access™ 2007 Data Analysis
Microsoft® Access™ 2007 Data Analysis Michael Alexander
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, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4355, 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 (800) 762-2974, outside the U.S. at (317) 572-3993 or fax (317) 572-4002. 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.
For Mary, Ethan, and Emma
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
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
Contents About the Author ix Acknowledgments xix Introduction xxi Part I Fundamentals of Data Analysis in Access 1 Chapter 1 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
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 Chapter 3 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
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 A Word 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 Chapter 4 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 Chapter 5 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
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 Chapter 6 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 SQL Statements 179 Expanding Your Search with the Like Operator 180 Selecting Unique Values and Rows without Grouping 181
Contents xv Grouping and Aggregating with the GROUP BY Clause 182 The HAVING Clause 183 Setting Sort Order with the ORDER BY Clause 183 Creating Aliases with the AS Clause 183 Creating a Column Alias 184 Creating a Table Alias 184 SELECT TOP and SELECT TOP PERCENT 184 Top Values Queries Explained 184 SELECT TOP 186 SELECT TOP PERCENT 187 Performing Action Queries via SQL Statements 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 SQL Specific 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 DROP COLUMN Clause 193 Summary 193 Chapter 8 Subqueries and Domain Aggregate Functions 195 Enhancing Your Analysis with Subqueries 196 Why Use Subqueries? 197 Subquery Ground Rules 197 Creating Subqueries without Typing SQL Statements 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 A Subquery in a Make-Table Query 205 A Subquery in an Append Query 205 A Subquery in an Update Query 206 A Subquery in a Delete Query 206 Domain Aggregate Functions 208 Understanding the Different Domain Aggregate Functions 210 DSum 210 DAvg 210 DCount 211 DLookup 211
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 Chapter 9 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 Chapter 10 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
Contents xvii Working with Pivot Charts in Access 265 The Data Area 265 The Series Area 265 The Category Area 266 The Filter Area 267 Creating a Basic Pivot Chart 268 Formatting Your Pivot Chart 269 Summary 272 Part IV Automating Data Analysis 353 Chapter 11 Scheduling and Running Batch Analysis 275 Introduction to Access Macros 276 Dealing with Access 2007 Security Features 277 The Quick Fix 278 The Long-Term Fix 279 Creating Your First Macro 280 Essential Macro Actions 282 Manipulating Forms, Queries, Reports, and Tables 283 The Access Environment 283 Executing Processes 284 Outputting Data 284 Setting Up and Managing Batch Analysis 285 Getting Organized 285 Using a Logical Naming Convention 285 Using the Description Property 288 Setting Up a Basic Batch Analysis 289 Building Smarter Macros 291 Simulating If...Then 292 Simulating If...Then...Else 294 Looping with Macros 296 Scheduling Macros to Run Nightly 301 Using an AutoExec Macro to Schedule Tasks 301 Using the Windows Task Scheduler 302 Using Command Lines to Schedule Tasks 307 When to Use Command Lines to Schedule Tasks Instead of AutoExec 308 Scheduling a Macro to Run Using a Command Line 308 Summary 310 Chapter 12 Leveraging VBA to Enhance Data Analysis 311 Creating and Using Custom Functions 312 Creating Your First Custom Function 313 Creating a Custom Function that Accepts Arguments 315 Controlling Analytical Processes with Forms 319 The Basics of Passing Data from a Form to a Query 320 Enhancing Automation with Forms 324 Enumerating Through a Combo Box 326