| |
| |
| Acknowledgments | |
| |
| |
| Introduction | |
| |
| |
| Defining Data Analysis | |
| |
| |
| Why Use Microsoft Access for Data Analysis? | |
| |
| |
| Where data analysis with Excel can go wrong | |
| |
| |
| Deciding whether to use Access or Excel | |
| |
| |
| A Gentle Introduction to Access | |
| |
| |
| What You Will Learn from This Book | |
| |
| |
| What this book does and does not cover | |
| |
| |
| How this book is organized | |
| |
| |
| About the companion database | |
| |
| |
| |
| Fundamentals of Data Analysis in Access | |
| |
| |
| |
| Access Basics | |
| |
| |
| Access Table Basics | |
| |
| |
| What is a table? | |
| |
| |
| Opening a table in Datasheet view | |
| |
| |
| Identifying important table elements | |
| |
| |
| Opening a table in Design view | |
| |
| |
| Exploring data types | |
| |
| |
| Preparing to create a table | |
| |
| |
| Different methods for creating a table | |
| |
| |
| Creating a table with Design view | |
| |
| |
| Advanced Table Concepts | |
| |
| |
| Field properties | |
| |
| |
| Primary key | |
| |
| |
| Getting Data into Access | |
| |
| |
| Importing | |
| |
| |
| Linking | |
| |
| |
| Things to remember about importing data | |
| |
| |
| The Relational Database Concept | |
| |
| |
| Excel and the flat-file format | |
| |
| |
| Splitting data into separate tables | |
| |
| |
| Foreign key | |
| |
| |
| Relationship types | |
| |
| |
| Query Basics | |
| |
| |
| What is a query? | |
| |
| |
| Creating a select query | |
| |
| |
| |
| Beyond Select Queries | |
| |
| |
| Aggregate Queries | |
| |
| |
| Creating an aggregate query | |
| |
| |
| Exploring aggregate functions | |
| |
| |
| Action Queries | |
| |
| |
| Why use action queries? | |
| |
| |
| Make-table queries | |
| |
| |
| Delete queries | |
| |
| |
| Append queries | |
| |
| |
| Update queries | |
| |
| |
| Crosstab Queries | |
| |
| |
| Using the Crosstab Query Wizard | |
| |
| |
| Creating a crosstab query manually | |
| |
| |
| Customizing your crosstab queries | |
| |
| |
| |
| Basic Analysis Techniques | |
| |
| |
| |
| Transforming Your Data with Access | |
| |
| |
| Finding and Removing Duplicate Records | |
| |
| |
| Defining duplicate records | |
| |
| |
| Finding duplicate records | |
| |
| |
| Removing duplicate records | |
| |
| |
| Common Transformation Tasks | |
| |
| |
| Filling in blank fields | |
| |
| |
| Concatenating | |
| |
| |
| Changing case | |
| |
| |
| Removing leading and trailing spaces from a string | |
| |
| |
| Finding and replacing specific text | |
| |
| |
| Adding your own text in key positions within a string | |
| |
| |
| Parsing strings using character markers | |
| |
| |
| |
| Working with Calculations and Dates | |
| |
| |
| Using Calculations in Your Analysis | |
| |
| |
| Common calculation scenarios | |
| |
| |
| Using the Expression Builder to construct calculations | |
| |
| |
| Common calculation errors | |
| |
| |
| Using Dates in Your Analysis | |
| |
| |
| Simple date calculations | |
| |
| |
| Advanced analysis using functions | |
| |
| |
| |
| Performing Conditional Analysis | |
| |
| |
| Using Parameter Queries | |
| |
| |
| How parameter queries work | |
| |
| |
| Ground rules of parameter queries | |
| |
| |
| Working with parameter queries | |
| |
| |
| Using Conditional Functions | |
| |
| |
| The IIf function | |
| |
| |
| The Switch function | |
| |
| |
| |
| Advanced Analysis Techniques | |
| |
| |
| |
| Working with Subqueries and Domain Aggregate Functions | |
| |
| |
| Understanding SQL | |
| |
| |
| The SELECT statement | |
| |
| |
| The ORDER BY clause | |
| |
| |
| The WHERE clause | |
| |
| |
| The GROUP BY clause | |
| |
| |
| The HAVING clause | |
| |
| |
| The AS clause | |
| |
| |
| Making sense of joins | |
| |
| |
| Union queries in SQL | |
| |
| |
| The SELECT TOP and SELECT TOP PERCENT statements | |
| |
| |
| Action queries in SQL | |
| |
| |
| Enhancing Your Analysis with Subqueries | |
| |
| |
| Why use subqueries? | |
| |
| |
| Subquery ground rules | |
| |
| |
| Using IN and NOT IN with subqueries | |
| |
| |
| Using subqueries with comparison operators | |
| |
| |
| Using subqueries as expressions | |
| |
| |
| Using correlated subqueries | |
| |
| |
| Using subqueries within action queries | |
| |
| |
| Domain Aggregate Functions | |
| |
| |
| The anatomy of domain aggregate functions | |
| |
| |
| Understanding the different domain aggregate functions | |
| |
| |
| Examining the syntax of domain aggregate functions | |
| |
| |
| Using domain aggregate functions | |
| |
| |
| |
| Running Descriptive Statistics in Access | |
| |
| |
| Basic Descriptive Statistics | |
| |
| |
| Running descriptive statistics with aggregate queries | |
| |
| |
| Determining rank, mode, and median | |
| |
| |
| Pulling a random sampling from your dataset | |
| |
| |
| Advanced Descriptive Statistics | |
| |
| |
| Calculating percentile ranking | |
| |
| |
| Determining the quartile standing of a record | |
| |
| |
| Creating a frequency distribution | |
| |
| |
| |
| Analyzing Data with Pivot Tables and Pivot Charts | |
| |
| |
| Working with Pivot Tables in Access | |
| |
| |
| The anatomy of a pivot table | |
| |
| |
| Creating a basic pivot table | |
| |
| |
| Creating an advanced pivot table with details | |
| |
| |
| Setting the PivotTable view as the default view | |
| |
| |
| Pivot table options | |
| |
| |
| Working with Pivot Charts in Access | |
| |
| |
| The anatomy of a pivot chart | |
| |
| |
| Creating a basic pivot chart | |
| |
| |
| Formatting your pivot chart | |
| |
| |
| |
| Automating Data Analysis | |
| |
| |
| |
| Scheduling and Running Batch Analysis | |
| |
| |
| Introduction to Access Macros | |
| |
| |
| Creating your first macro | |
| |
| |
| Essential macro actions | |
| |
| |
| Setting Up and Managing Batch Analysis | |
| |
| |
| Getting organized | |
| |
| |
| Setting up a basic batch analysis | |
| |
| |
| Building smarter macros | |
| |
| |
| Looping with macros | |
| |
| |
| Scheduling Macros to Run Nightly | |
| |
| |
| Using an AutoExec macro to schedule tasks | |
| |
| |
| Using command lines to schedule tasks | |
| |
| |
| |
| Leveraging VBA to Enhance Data Analysis | |
| |
| |
| Creating and Using Custom Functions | |
| |
| |
| Creating your first custom function | |
| |
| |
| Creating a custom function that accepts arguments | |
| |
| |
| Controlling Analytical Processes with Forms | |
| |
| |
| The basics of passing data from a form to a query | |
| |
| |
| Enhancing automation with forms | |
| |
| |
| Enumerating through a combo box | |
| |
| |
| Processing Data Behind the Scenes | |
| |
| |
| Processing data with RunSQL statements | |
| |
| |
| Advanced techniques using RunSQL statements | |
| |
| |
| |
| Reporting Your Results Using Excel Automation | |
| |
| |
| The Basics of Excel Automation | |
| |
| |
| A word on binding Access to Excel | |
| |
| |
| Creating your first Excel automation procedure | |
| |
| |
| Sending Access data to Excel | |
| |
| |
| Advanced Automation Techniques | |
| |
| |
| Stealing code from Excel | |
| |
| |
| Optimizing macro-generated code | |
| |
| |
| |
| Query Performance, Database Corruption, and Other Final Thoughts | |
| |
| |
| Optimizing Query Performance | |
| |
| |
| Understanding Microsoft Jet's query optimizer | |
| |
| |
| Steps you can take to optimize query performance | |
| |
| |
| Handling Database Corruption | |
| |
| |
| The signs and symptoms of a corrupted database | |
| |
| |
| Recovering a corrupted database | |
| |
| |
| Preventing database corruption | |
| |
| |
| Getting Help in Access | |
| |
| |
| Location matters when asking for help | |
| |
| |
| Use the table of contents instead of a keyword search | |
| |
| |
| Online help is better than offline help | |
| |
| |
| Diversify your knowledgebase with online resources | |
| |
| |
| |
| Data Analyst's Function Reference | |
| |
| |
| |
| Access VBA Fundamentals | |
| |
| |
| |
| Data Analyst's Error Reference | |
| |
| |
| Index | |