The Excel analyst's guide to Access

The Excel® Analyst's Guide to Access®

About the Author

Contents at a Glance

Contents

Introduction

Part I: Fundamentals of Data Analysis in Access

Chapter 1: The Case for Data Analysis in Access

Where Data Analysis with Excel Can Go Wrong

Deciding Whether to Use Access or Excel

An Excel User's Guide to Access: Don't Panic!

Summary

Chapter 2: Access Basics

Access Tables

Understanding the Relational Database Concept

Query Basics

The Top Ten Query Errors

Summary

Chapter 3: Beyond Select Queries

Aggregate Queries

Action Queries

Crosstab Queries

Summary

Part II: Basic Analysis Techniques

Chapter 4: Transforming Your Data with Access

Finding and Removing Duplicate Records

Common Transformation Tasks

Summary

Chapter 5: Working with Calculations and Dates

Using Calculations in Your Analysis

Using Dates in Your Analysis

The New Calculated Data Type

Summary

Chapter 6: Performing Conditional Analysis

Using Parameter Queries

Using Conditional Functions

Summary

Part III: Advanced Analysis Techniques

Chapter 7: Adding Dimension with Subqueries and Domain Aggregate Functions

Enhancing Your Analysis with Subqueries

Domain Aggregate Functions

Summary

Chapter 8: Running Descriptive Statistics in Access

Basic Descriptive Statistics

Advanced Descriptive Statistics

Summary

Chapter 9: Scheduling and Running Batch Analysis

Introduction to Access Macros

Setting Up and Managing Batch Analysis

Scheduling Macros to Run Nightly

Summary

Chapter 10: Leveraging VBA to Enhance Data Analysis

Creating and Using Custom Functions

Controlling Analytical Processes with Forms

Processing Data Behind the Scenes

Summary

Part IV: Reports, Dashboards, and Visualization in Access

Chapter 11: Presenting Data with Access Reports.

Access Report Basics

Creating and Modifying Grouped Reports

Creating a Report from Scratch

Summary

Chapter 12: Using Pivot Tables and Pivot Charts in Access

Pivot Tables in Access?

The Anatomy of a Pivot Table

Creating a Basic Pivot Table

Creating an Advanced Pivot Table with Details

Saving Your Pivot Table

Sending Your Access Pivot Table to Excel

Pivot Table Options

Working with Pivot Charts in Access

Summary

Chapter 13: Enhancing Queries and Reports with Visualizations

Basic Visualization Techniques

Advanced Visualization Techniques

Summary

Part V: Advanced Excel and Access Integration Techniques

Chapter 14: Getting Access Data into Excel

The Different Methods for Importing Access Data

Summary

Chapter 15: Using VBA to Move Data between Excel and Access

Understanding ADO Fundamentals

Common Scenarios Where VBA Can Help

Summary

Chapter 16: Exploring Excel and Access Automation

Understanding the Concept of Binding

Automating Excel from Access

Automating Access from Excel

Summary

Chapter 17: Integrating Excel and Access with XML

Why XML?

Understanding XML

Creating a Simple Reporting Solution with XML

Creating a Data Entry Process Using XML

Summary

Chapter 18: Integrating Excel and Other Office Applications

Integrating Excel with Microsoft Word

Integrating Excel with PowerPoint

Integrating Excel and Outlook

Summary

Part VI: Appendixes

Appendix A: Access VBA Fundamentals

Covering the Basics in 10 Steps

Letting Access Teach You VBA

Appendix B: Understanding and Using SQL

Understanding Basic SQL

Getting Fancy with Advanced SQL Statements

Using SQL Specific Queries

Appendix C: Query Performance, Database Corruption, and Other Thoughts

Optimizing Query Performance

Handling Database Corruption.

Getting Help in Access

Appendix D: Data Analyst's Function Reference

Abs

Asc

Atn

Choose

Chr

Cos

Date

DateAdd

DateDiff

DatePart

DateSerial

DateValue

Day

DDB

Domain Aggregate Functions

Exp

FormatCurrency

FormatDateTime

FormatNumber

FormatPercent

FV

Hour

IIf

InStr

InStrRev

IPmt

IRR

IsError

IsNull

IsNumeric

LCase

Left

Len

Log

Mid

Minute

MIRR

Month

MonthName

Now

NPer

NPV

NZ

Partition

Pmt

PPmt

PV

Rate

Replace

Right

Rnd

Round

Second

Sgn

Sin

SLN

Space

SQL Aggregate Functions

Sqr

Str

StrConv

String

StrReverse

Switch

SYD

Tan

Time

TimeSerial

TimeValue

Trim, LTrim, RTrim

TypeName

UCase

Val

VarType

Weekday

WeekdayName

Year

Index.