Cover image for The Excel® Analyst's Guide to Access®

The Excel® Analyst's Guide to Access®

Author Michael Alexander
Release Date: 2010/04/01
ISBN: 9780470567012
Topic:
0%
33
Chapters
0-1
Hours read
0k
Total Words
    Start Reading Now    
       Add to Wishlist       
View table of contents

Book Description

The ultimate handbook for Excel analysts who need reporting solutions using Access

Excel and Access are intended to work together. This book offers a comprehensive review of the extensive analytical and reporting functionality that Access provides and how it enhances Excel reporting functions.

Sales managers, operations analysts, administrative assistants, office managers, and many others who rely heavily on data can benefit from learning to integrate Excel and Access, and this book shows you how.

Coverage includes:

  • Data Analysis in Access & the Basics of Access

  • Beyond Select Queries

  • Transforming Your Data with Access

  • Working with Calculations and Dates

  • Performing Conditional Analysis

  • Adding Dimension with Subqueries and Domain Aggregate Functions

  • Running Descriptive Statistics in Access

  • Scheduling and Running Batch Analysis

  • Leveraging VBA to Enhance Data Analysis

  • Reports, Dashboards, and Visualization in Access

  • Presenting Data with Access Reports

  • Using Pivot Tables and Pivot Charts in Access

  • Enhancing Queries and Reports with Visualizations

  • Advanced Excel and Access Integration Techniques

  • Getting Access Data into Excel

  • Using VBA to Move Data between Excel and Access

  • Exploring Excel and Access Automation

  • Integrating Excel and Access with XML

  • Integrating Excel and Other Of?ce Applications

  • Access VBA Fundamentals

  • Understanding and Using SQL

  • and more!

The Excel Analyst's Guide to Access helps you get more from both applications.

Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Table of Contents

  1. Copyright
  2. About the Author
  3. Credits
  4. Acknowledgments
  5. Introduction
    1. What to Expect from This Book
    2. What Not to Expect from This Book
    3. Skills Required for This Book
    4. How This Book is Organized
      1. Part I: Fundamentals of Data Analysis in Access
      2. Part II: Basic Analysis Techniques
      3. Part III: Advanced Analysis Techniques
      4. Part IV: Reports, Dashboards and Visualizations in Access
      5. Part V: Advanced Excel and Access Integration Techniques
      6. Part VI: Appendixes
    5. Conventions
    6. Companion Database
  6. I. Fundamentals of Data Analysis in Access
  7. 1. The Case for Data Analysis in Access
    1. 1.1. Where Data Analysis with Excel Can Go Wrong
      1. 1.1.1. Scalability
      2. 1.1.2. Transparency of Analytical Processes
      3. 1.1.3. Separation of Data and Presentation
    2. 1.2. Deciding Whether to Use Access or Excel
      1. 1.2.1. Size of Data
      2. 1.2.2. Data Structure
      3. 1.2.3. Data Evolution
      4. 1.2.4. Functional Complexity
      5. 1.2.5. Shared Processing
    3. 1.3. An Excel User's Guide to Access: Don't Panic!
      1. 1.3.1. Tables
      2. 1.3.2. Queries
      3. 1.3.3. Reports
      4. 1.3.4. Macros and VBA
    4. 1.4. Summary
  8. 2. Access Basics
    1. 2.1. Access Tables
      1. 2.1.1. Table Basics
        1. 2.1.1.1. Opening a Table in the Datasheet View
        2. 2.1.1.2. Identifying Important Table Elements
        3. 2.1.1.3. Opening a Table in the Design View
        4. 2.1.1.4. Exploring Data Types
        5. 2.1.1.5. Creating a Table from Scratch
        6. 2.1.1.6. Working with Field Properties
        7. 2.1.1.7. Setting Primary Keys
      2. 2.1.2. Importing Data into Access
        1. 2.1.2.1. Importing Data from Text File
      3. 2.1.3. Linking an Excel Worksheet to Access
    2. 2.2. Understanding the Relational Database Concept
      1. 2.2.1. Why Is This Concept Important?
      2. 2.2.2. Excel and the Flat-File Format
      3. 2.2.3. Splitting Data into Separate Tables
      4. 2.2.4. Relationship Types
    3. 2.3. Query Basics
      1. 2.3.1. What Is a Query?
      2. 2.3.2. Creating Your First Select Query
        1. 2.3.2.1. Sorting Query Results
        2. 2.3.2.2. Filtering Query Results
        3. 2.3.2.3. Querying Multiple Tables
        4. 2.3.2.4. Refining the Query
        5. 2.3.2.5. Using Operators in Queries
    4. 2.4. The Top Ten Query Errors
    5. 2.5. Summary
  9. 3. Beyond Select Queries
    1. 3.1. Aggregate Queries
      1. 3.1.1. Creating an Aggregate Query
      2. 3.1.2. About Aggregate Functions
        1. 3.1.2.1. Group By
        2. 3.1.2.2. Sum, Avg, Count, StDev, Var
        3. 3.1.2.3. Min, Max, First, Last
        4. 3.1.2.4. Expression, Where
          1. 3.1.2.4.1. The Expression Aggregate Function
          2. 3.1.2.4.2. The Where Clause
    2. 3.2. Action Queries
      1. 3.2.1. Why Use Action Queries?
      2. 3.2.2. Make-Table Queries
        1. 3.2.2.1. Why Use a Make-Table Query?
        2. 3.2.2.2. What Are the Hazards of Make-Table Queries?
        3. 3.2.2.3. Creating a Make-Table Query
      3. 3.2.3. Delete Queries
        1. 3.2.3.1. Why Use a Delete Query?
        2. 3.2.3.2. What Are the Hazards of Delete Queries?
        3. 3.2.3.3. Creating a Delete Query
      4. 3.2.4. Append Queries
        1. 3.2.4.1. Why Use an Append Query?
        2. 3.2.4.2. What Are the Hazards of Append Queries?
        3. 3.2.4.3. Creating an Append Query
      5. 3.2.5. Update Queries
        1. 3.2.5.1. Why Use an Update Query?
        2. 3.2.5.2. What Are the Hazards of Update Queries?
        3. 3.2.5.3. Creating an Update Query
        4. 3.2.5.4. A Word on Updatable Datasets
    3. 3.3. Crosstab Queries
      1. 3.3.1. Using the Crosstab Query Wizard
      2. 3.3.2. Creating a Crosstab Query Manually
        1. 3.3.2.1. Customizing Your Crosstab Queries
    4. 3.4. Summary
  10. II. Basic Analysis Techniques
  11. 4. Transforming Your Data with Access
    1. 4.1. Finding and Removing Duplicate Records
      1. 4.1.1. Defining Duplicate Records
      2. 4.1.2. Finding Duplicate Records
      3. 4.1.3. Removing Duplicate Records
    2. 4.2. Common Transformation Tasks
      1. 4.2.1. Filling in Blank Fields
      2. 4.2.2. Concatenating
        1. 4.2.2.1. Concatenating Fields
        2. 4.2.2.2. Augmenting Field Values with Your Own Text
      3. 4.2.3. Changing Case
      4. 4.2.4. Removing Leading and Trailing Spaces from a String
      5. 4.2.5. Finding and Replacing Specific Text
      6. 4.2.6. Adding Your Own Text in Key Positions Within a String
      7. 4.2.7. Parsing Strings Using Character Markers
        1. 4.2.7.1. Query 1
        2. 4.2.7.2. Query 2
    3. 4.3. Summary
  12. 5. Working with Calculations and Dates
    1. 5.1. Using Calculations in Your Analysis
      1. 5.1.1. Common Calculation Scenarios
        1. 5.1.1.1. Using Constants in Calculations
        2. 5.1.1.2. Using Fields in Calculations
        3. 5.1.1.3. Using the Results of Aggregation in Calculations
        4. 5.1.1.4. Using the Results of One Calculation as an Expression in Another
        5. 5.1.1.5. Using a Calculation as an Argument in a Function
      2. 5.1.2. Using the Expression Builder to Construct Calculations
    2. 5.2. Common Calculation Errors
      1. 5.2.1.
        1. 5.2.1.1. Understanding the Order of Operator Precedence
        2. 5.2.1.2. Watching Out for Null Values
        3. 5.2.1.3. Watching the Syntax in Your Expressions
    3. 5.3. Using Dates in Your Analysis
      1. 5.3.1. Simple Date Calculations
      2. 5.3.2. Advanced Analysis Using Functions
        1. 5.3.2.1. The Date Function
          1. 5.3.2.1.1. Finding the Number of Days Between Today and a Past Date
          2. 5.3.2.1.2. Using the Date Function in a Criteria Expression
          3. 5.3.2.1.3. Calculating an Age in Years Using the Date Function
          4. 5.3.2.1.4. The Year, Month, Day, and Weekday Functions
        2. 5.3.2.2. The DateAdd Function
        3. 5.3.2.3. Grouping Dates into Quarters
        4. 5.3.2.4. The DateSerial Function
    4. 5.4. The New Calculated Data Type
    5. 5.5. Summary
  13. 6. Performing Conditional Analysis
    1. 6.1. Using Parameter Queries
      1. 6.1.1. How Parameter Queries Work
      2. 6.1.2. Ground Rules of Parameter Queries
      3. 6.1.3. Working with Parameter Queries
        1. 6.1.3.1. Working with Multiple Parameter Conditions
        2. 6.1.3.2. Combining Parameters with Operators
        3. 6.1.3.3. Combining Parameters with Wildcards
        4. 6.1.3.4. Using Parameters as Calculation Variables
        5. 6.1.3.5. Using Parameters as Function Arguments
    2. 6.2. Using Conditional Functions
      1. 6.2.1. The IIf Function
        1. 6.2.1.1. Using IIf to Avoid Mathematical Errors
        2. 6.2.1.2. Using IIf to Save Time
        3. 6.2.1.3. Nesting IIf Functions for Multiple Conditions
        4. 6.2.1.4. Using IIf Functions to Create Crosstab Analyses
      2. 6.2.2. The Switch Function
      3. 6.2.3. Comparing the IIf and Switch Functions
    3. 6.3. Summary
  14. III. Advanced Analysis Techniques
  15. 7. Adding Dimension with Subqueries and Domain Aggregate Functions
    1. 7.1. Enhancing Your Analysis with Subqueries
      1. 7.1.1. Why Use Subqueries?
      2. 7.1.2. Subquery Ground Rules
      3. 7.1.3. Creating Subqueries Without Typing SQL Statements
      4. 7.1.4. Using IN and NOT IN with Subqueries
      5. 7.1.5. Using Subqueries with Comparison Operators
      6. 7.1.6. Using Subqueries as Expressions
      7. 7.1.7. Using Correlated Subqueries
        1. 7.1.7.1. Uncorrelated Subqueries
        2. 7.1.7.2. Correlated Subqueries
        3. 7.1.7.3. Using a Correlated Subquery as an Expression
      8. 7.1.8. Using Subqueries Within Action Queries
        1. 7.1.8.1. A Subquery in a Make-Table Query
        2. 7.1.8.2. A Subquery in an Append Query
        3. 7.1.8.3. A Subquery in an Update Query
        4. 7.1.8.4. A Subquery in a Delete Query
    2. 7.2. Domain Aggregate Functions
      1. 7.2.1. Understanding the Different Domain Aggregate Functions
      2. 7.2.2. Examining the Syntax of Domain Aggregate Functions
        1. 7.2.2.1. Using No Criteria
        2. 7.2.2.2. Using Text Criteria
        3. 7.2.2.3. Using Number Criteria
        4. 7.2.2.4. Using Date Criteria
      3. 7.2.3. Using Domain Aggregate Functions
        1. 7.2.3.1. Calculating the Percent of Total
        2. 7.2.3.2. Creating a Running Count
        3. 7.2.3.3. Using a Value from the Previous Record
    3. 7.3. Summary
  16. 8. Running Descriptive Statistics in Access
    1. 8.1. Basic Descriptive Statistics
      1. 8.1.1. Running Descriptive Statistics with Aggregate Queries
      2. 8.1.2. Determining Rank, Mode, and Median
        1. 8.1.2.1. Ranking the Records in Your Dataset
        2. 8.1.2.2. Getting the Mode of a Dataset
        3. 8.1.2.3. Getting the Median of a Dataset
      3. 8.1.3. Pulling a Random Sampling from Your Dataset
    2. 8.2. Advanced Descriptive Statistics
      1. 8.2.1. Calculating Percentile Ranking
      2. 8.2.2. Determining the Quartile Standing of a Record
      3. 8.2.3. Creating a Frequency Distribution
    3. 8.3. Summary
  17. 9. Scheduling and Running Batch Analysis
    1. 9.1. Introduction to Access Macros
      1. 9.1.1. Dealing with Access Macro Security
        1. 9.1.1.1. The Quick Fix
        2. 9.1.1.2. The Long-Term Fix
      2. 9.1.2. Creating Your First Macro
      3. 9.1.3. Essential Macro Actions
        1. 9.1.3.1. Manipulating Forms, Queries, Reports, and Tables
        2. 9.1.3.2. The Access Environment
        3. 9.1.3.3. Executing Processes
        4. 9.1.3.4. Outputting Data
    2. 9.2. Setting Up and Managing Batch Analysis
      1. 9.2.1. Getting Organized
        1. 9.2.1.1. Using a Logical Naming Convention
        2. 9.2.1.2. Using the Description Property
      2. 9.2.2. Setting Up a Basic Batch Analysis
      3. 9.2.3. Building Smarter Macros
        1. 9.2.3.1. If...Then in Access 2010 Macros
        2. 9.2.3.2. If...Then in Access 2007 Macros
        3. 9.2.3.3. If...Then...Else in Access 2010 Macros
        4. 9.2.3.4. If...Then...Else in Access 2007 Macros
      4. 9.2.4. Looping with Macros
    3. 9.3. Scheduling Macros to Run Nightly
      1. 9.3.1. Using an AutoExec Macro to Schedule Tasks
        1. 9.3.1.1. Using the Windows Task Scheduler
      2. 9.3.2. Using Command Lines to Schedule Tasks
        1. 9.3.2.1. When to Use Command Lines to Schedule Tasks Instead of AutoExec
        2. 9.3.2.2. Scheduling a Macro to Run Using a Command Line
    4. 9.4. Summary
  18. 10. Leveraging VBA to Enhance Data Analysis
    1. 10.1. Creating and Using Custom Functions
      1. 10.1.1. Creating Your First Custom Function
      2. 10.1.2. Creating a Custom Function that Accepts Arguments
    2. 10.2. Controlling Analytical Processes with Forms
      1. 10.2.1. The Basics of Passing Data from a Form to a Query
      2. 10.2.2. Enhancing Automation with Forms
        1. 10.2.2.1. Enumerating Through a Combo Box
    3. 10.3. Processing Data Behind the Scenes
      1. 10.3.1. Processing Data with RunSQL Statements
        1. 10.3.1.1. The Basics of the RunSQL Method
        2. 10.3.1.2. Using RunSQL Statements
      2. 10.3.2. Advanced Techniques Using RunSQL Statements
        1. 10.3.2.1. Suppressing Warning Messages
        2. 10.3.2.2. Passing a SQL Statement as a Variable
        3. 10.3.2.3. Passing User-Defined Parameters from a Form to Your SQL Statement
          1. 10.3.2.3.1. Passing Textual Parameters from a Form
          2. 10.3.2.3.2. Passing Numeric Parameters from a Form
          3. 10.3.2.3.3. Passing Date Parameters from a Form
    4. 10.4. Summary
  19. IV. Reports, Dashboards, and Visualization in Access
  20. 11. Presenting Data with Access Reports
    1. 11.1. Access Report Basics
      1. 11.1.1. Creating Your First Report
      2. 11.1.2. Viewing Your Report
        1. 11.1.2.1. Report View
        2. 11.1.2.2. Layout View
        3. 11.1.2.3. Design View
        4. 11.1.2.4. Page Footer
        5. 11.1.2.5. Report Footer
    2. 11.2. Creating and Modifying Grouped Reports
      1. 11.2.1. Grouping
      2. 11.2.2. Sorting and Totaling
    3. 11.3. Creating a Report from Scratch
      1. 11.3.1. Creating Your Source Data
      2. 11.3.2. Building the Report in Design View
    4. 11.4. Summary
  21. 12. Using Pivot Tables and Pivot Charts in Access
    1. 12.1. Pivot Tables in Access?
    2. 12.2. The Anatomy of a Pivot Table
      1. 12.2.1. The Totals and Detail Area
      2. 12.2.2. The Row Area
      3. 12.2.3. The Column Area
      4. 12.2.4. The Filter Area
    3. 12.3. Creating a Basic Pivot Table
    4. 12.4. Creating an Advanced Pivot Table with Details
    5. 12.5. Saving Your Pivot Table
    6. 12.6. Sending Your Access Pivot Table to Excel
    7. 12.7. Pivot Table Options
      1. 12.7.1. Expanding and Collapsing Fields
      2. 12.7.2. Changing Field Captions
      3. 12.7.3. Sorting Data
      4. 12.7.4. Grouping Data
      5. 12.7.5. Using Date Groupings
      6. 12.7.6. Filter for Top and Bottom Records
      7. 12.7.7. Adding a Calculated Total
    8. 12.8. Working with Pivot Charts in Access
      1. 12.8.1. Pivot Chart Fundamentals
        1. 12.8.1.1. Data Area
        2. 12.8.1.2. Series Area
        3. 12.8.1.3. Category Area
        4. 12.8.1.4. Filter Area
      2. 12.8.2. Creating a Basic Pivot Chart
      3. 12.8.3. Formatting Your Pivot Chart
    9. 12.9. Summary
  22. 13. Enhancing Queries and Reports with Visualizations
    1. 13.1. Basic Visualization Techniques
      1. 13.1.1. Using Number Formatting Tricks to Enhance Reporting
      2. 13.1.2. Using Conditional Formatting in Access
        1. 13.1.2.1. Apply Conditional Formatting to a Field Based on its Own Value
        2. 13.1.2.2. Apply Conditional Formatting Based on another Control's Value
        3. 13.1.2.3. Clearing Conditional Formatting
    2. 13.2. Advanced Visualization Techniques
      1. 13.2.1. Enhancing Queries and Reports with Data Bars
        1. 13.2.1.1. Introducing the STRING Function
        2. 13.2.1.2. Extending Data Bars to Reports
      2. 13.2.2. Sprucing up Queries and Reports with Symbols
      3. 13.2.3. Using Your Own Dashboard Graphics in Access
        1. 13.2.3.1. Storing External Graphics in an Access Table
        2. 13.2.3.2. Using the Graphics Table
        3. 13.2.3.3. Using Multiple Sets of Graphics
    3. 13.3. Summary
  23. V. Advanced Excel and Access Integration Techniques
  24. 14. Getting Access Data into Excel
    1. 14.1. The Different Methods for Importing Access Data
      1. 14.1.1. The Drag and Drop Method
      2. 14.1.2. Using the Export Wizard from Access
      3. 14.1.3. Using Get External Data from Excel
      4. 14.1.4. Using MS Query
        1. 14.1.4.1. Starting MS Query
        2. 14.1.4.2. Setting up Your Data Source
        3. 14.1.4.3. Building Your Custom Data Pull
    2. 14.2. Summary
  25. 15. Using VBA to Move Data between Excel and Access
    1. 15.1. Understanding ADO Fundamentals
      1. 15.1.1. The Connection String
      2. 15.1.2. Declaring a Recordset
        1. 15.1.2.1. Return Read Only Data from a Table or Query
        2. 15.1.2.2. Return Updateable Data from a Table or Query
      3. 15.1.3. Writing Your First ADO Procedure
        1. 15.1.3.1. Referencing the ADO Object Library
        2. 15.1.3.2. Writing the Code
        3. 15.1.3.3. Using the Code
      4. 15.1.4. Writing your First ADO/SQL Data Extract
      5. 15.1.5. Using Criteria in your SQL Statements
        1. 15.1.5.1. Set Numeric Criteria
        2. 15.1.5.2. Set Textual Criteria
        3. 15.1.5.3. Set Date Criteria
        4. 15.1.5.4. Set Multiple Criteria
        5. 15.1.5.5. Using the LIKE Operator with ADO
    2. 15.2. Common Scenarios Where VBA Can Help
      1. 15.2.1. Query Data from an Excel Workbook
      2. 15.2.2. Append Records to an Existing Excel Table
      3. 15.2.3. Append Excel Records to an Existing Access Table
      4. 15.2.4. Querying Text Files
    3. 15.3. Summary
  26. 16. Exploring Excel and Access Automation
    1. 16.1. Understanding the Concept of Binding
      1. 16.1.1. Early Binding
      2. 16.1.2. Late Binding
    2. 16.2. Automating Excel from Access
      1. 16.2.1. Creating your First Excel Automation Procedure
      2. 16.2.2. Automating Data Export to Excel
        1. 16.2.2.1. Sending one Recordset to Excel
        2. 16.2.2.2. Sending Two Datasets to Two Different Tabs in the Same Workbook
      3. 16.2.3. Automating Excel Reports: Without Programming Excel
      4. 16.2.4. Using Find and Replace to Adjust Macro-Generated Code
      5. 16.2.5. Running an Excel Macro from Access
      6. 16.2.6. Optimizing Macro-Generated Code
        1. 16.2.6.1. Removing Navigation Actions
        2. 16.2.6.2. Deleting Code that Specifies Default Settings
        3. 16.2.6.3. Cleaning Up Double Takes and Mistakes
        4. 16.2.6.4. Temporarily Disabling Screen Updating
    3. 16.3. Automating Access from Excel
      1. 16.3.1. Setting the Required References
      2. 16.3.2. Running an Access Query from Excel
      3. 16.3.3. Running Access Parameter Queries from Excel
      4. 16.3.4. Running an Access Macro from Excel
      5. 16.3.5. Opening an Access Report from Excel
      6. 16.3.6. Opening an Access Form from Excel
      7. 16.3.7. Compacting an Access Database from Excel
    4. 16.4. Summary
  27. 17. Integrating Excel and Access with XML
    1. 17.1. Why XML?
    2. 17.2. Understanding XML
      1. 17.2.1. The XML Declaration
      2. 17.2.2. Processing Instructions
      3. 17.2.3. Comments
      4. 17.2.4. Elements
      5. 17.2.5. The Root Element
      6. 17.2.6. Attributes
      7. 17.2.7. Namespaces
    3. 17.3. Creating a Simple Reporting Solution with XML
      1. 17.3.1. Exporting XML Data from Access
      2. 17.3.2. Utilize XML Data in Excel
    4. 17.4. Creating a Data Entry Process Using XML
      1. 17.4.1. Creating the Data Entry Schema in Access
      2. 17.4.2. Setting up the Data Entry Form in Excel
      3. 17.4.3. Exporting Results from Excel to XML
      4. 17.4.4. Getting the Results Back into Access
    5. 17.5. Summary
  28. 18. Integrating Excel and Other Office Applications
    1. 18.1. Integrating Excel with Microsoft Word
      1. 18.1.1. Creating a Dynamic Link to an Excel Table
        1. 18.1.1.1. Linking an Excel Table to Word
        2. 18.1.1.2. Preventing the Link from Automatically Updating
      2. 18.1.2. Getting Excel Data to a Word Document Using Automation
      3. 18.1.3. Creating a Word Mail Merge Document
      4. 18.1.4. Simulating the Word Mail Merge Function from Excel
    2. 18.2. Integrating Excel with PowerPoint
      1. 18.2.1. Creating a PowerPoint Slide with a Title
      2. 18.2.2. Copying a Range of Cells to a Presentation
      3. 18.2.3. Sending All Excel Charts to the Presentation
      4. 18.2.4. Converting a Workbook into a PowerPoint Presentation
    3. 18.3. Integrating Excel and Outlook
      1. 18.3.1. Mailing the Active Workbook
      2. 18.3.2. Mailing a Specific Range
      3. 18.3.3. Mailing to All Email Addresses in Your Contact List
      4. 18.3.4. Saving All Attachments in a Folder
      5. 18.3.5. Saving Certain Attachments to a Folder
    4. 18.4. Summary
  29. VI. Appendixes
  30. A. Access VBA Fundamentals
    1. A.1. Covering the Basics in 10 Steps
      1. A.1.1. Step 1: Creating a Standard Module
      2. A.1.2. Step 2: Creating a Function
      3. A.1.3. Step 3: Giving Your Function Purpose with a Procedure
      4. A.1.4. Step 4: Testing Your Function
      5. A.1.5. Step 5: Declaring a Variable
      6. A.1.6. Step 6: Assigning a Value to a Variable
      7. A.1.7. Step 7: Compiling Your Newly Created Function
      8. A.1.8. Step 8: Saving Your Newly Created Function
      9. A.1.9. Step 9: Running Your Function in a Macro
      10. A.1.10. Step 10: Running Your Function from a Form
    2. A.2. Letting Access Teach You VBA
  31. B. Understanding and Using SQL
    1. B.1. Understanding Basic SQL
      1. B.1.1. The SELECT Statement
        1. B.1.1.1. Selecting Specific Columns
        2. B.1.1.2. Selecting All Columns
      2. B.1.2. The WHERE Clause
      3. B.1.3. Making Sense of Joins
        1. B.1.3.1. Inner Joins
        2. B.1.3.2. Outer Joins
    2. B.2. Getting Fancy with Advanced SQL Statements
      1. B.2.1. Expanding Your Search with the Like Operator
      2. B.2.2. Selecting Unique Values and Rows without Grouping
      3. B.2.3. Grouping and Aggregating with the GROUP BY Clause
        1. B.2.3.1. The HAVING Clause
      4. B.2.4. Setting Sort Order with the ORDER BY Clause
      5. B.2.5. Creating Aliases with the AS Clause
        1. B.2.5.1. Creating a Column Alias
        2. B.2.5.2. Creating a Table Alias
      6. B.2.6. SELECT TOP and SELECT TOP PERCENT
        1. B.2.6.1. Top Values Queries Explained
        2. B.2.6.2. SELECT TOP
        3. B.2.6.3. SELECT TOP PERCENT
      7. B.2.7. Performing Action Queries via SQL Statements
        1. B.2.7.1. Make-Table Queries Translated
        2. B.2.7.2. Append Queries Translated
        3. B.2.7.3. Update Queries Translated
        4. B.2.7.4. Delete Queries Translated
      8. B.2.8. Creating Crosstabs with the TRANSFORM Statement
    3. B.3. Using SQL Specific Queries
      1. B.3.1. Merging Datasets with the UNION Operator
      2. B.3.2. Creating a Table with the CREATE TABLE Statement
      3. B.3.3. Manipulating Columns with the ALTER TABLE Statement
        1. B.3.3.1. Adding a Column with the ADD Clause
        2. B.3.3.2. Altering a Column with the ALTER COLUMN Clause
        3. B.3.3.3. Deleting a Column with the DROP COLUMN Clause
        4. B.3.3.4. Dynamically Adding Primary Keys with the ADD CONSTRAINT Clause
  32. C. Query Performance, Database Corruption, and Other Thoughts
    1. C.1. Optimizing Query Performance
      1. C.1.1. Understanding Access's Query Optimizer
      2. C.1.2. Steps You Can Take to Optimize Query Performance
        1. C.1.2.1. Normalizing Your Database Design
        2. C.1.2.2. Using Indexes on Appropriate Fields
        3. C.1.2.3. Optimizing by Improving Query Design
        4. C.1.2.4. Compacting and Repairing Your Database Regularly
    2. C.2. Handling Database Corruption
      1. C.2.1. Signs and Symptoms of a Corrupted Database
        1. C.2.1.1. Watching for Corruption in Seemingly Normal Databases
        2. C.2.1.2. Common Errors Associated with Database Corruption
      2. C.2.2. Recovering a Corrupted Database
      3. C.2.3. Steps You Can Take to Prevent Database Corruption
        1. C.2.3.1. Backing Up Your Database on a Regular Basis
        2. C.2.3.2. Compacting and Repairing Your Database on a Regular Basis
        3. C.2.3.3. Avoiding Interruption of Service While Writing to Your Database
        4. C.2.3.4. Never Working with a Database from Removable Media
    3. C.3. Getting Help in Access
      1. C.3.1. Location Matters When Asking for Help
      2. C.3.2. Online Help Is Better than Offline Help
      3. C.3.3. Diversifying your Knowledgebase with Online Resources
  33. D. Data Analyst's Function Reference
    1. D.1. Abs
    2. D.2. Asc
    3. D.3. Atn
    4. D.4. Choose
    5. D.5. Chr
    6. D.6. Cos
    7. D.7. Date
    8. D.8. DateAdd
    9. D.9. DateDiff
    10. D.10. DatePart
    11. D.11. DateSerial
    12. D.12. DateValue
    13. D.13. Day
    14. D.14. DDB
    15. D.15. Domain Aggregate Functions
    16. D.16. Exp
    17. D.17. FormatCurrency
    18. D.18. FormatDateTime
    19. D.19. FormatNumber
    20. D.20. FormatPercent
    21. D.21. FV
    22. D.22. Hour
    23. D.23. IIf
    24. D.24. InStr
    25. D.25. InStrRev
    26. D.26. IPmt
    27. D.27. IRR
    28. D.28. IsError
    29. D.29. IsNull
    30. D.30. IsNumeric
    31. D.31. LCase
    32. D.32. Left
    33. D.33. Len
    34. D.34. Log
    35. D.35. Mid
    36. D.36. Minute
    37. D.37. MIRR
    38. D.38. Month
    39. D.39. MonthName
    40. D.40. Now
    41. D.41. NPer
    42. D.42. NPV
    43. D.43. NZ
    44. D.44. Partition
    45. D.45. Pmt
    46. D.46. PPmt
    47. D.47. PV
    48. D.48. Rate
    49. D.49. Replace
    50. D.50. Right
    51. D.51. Rnd
    52. D.52. Round
    53. D.53. Second
    54. D.54. Sgn
    55. D.55. Sin
    56. D.56. SLN
    57. D.57. Space
    58. D.58. SQL Aggregate Functions
    59. D.59. Sqr
    60. D.60. Str
    61. D.61. StrConv
    62. D.62. String
    63. D.63. StrReverse
    64. D.64. Switch
    65. D.65. SYD
    66. D.66. Tan
    67. D.67. Time
    68. D.68. TimeSerial
    69. D.69. TimeValue
    70. D.70. Trim, LTrim, RTrim
    71. D.71. TypeName
    72. D.72. UCase
    73. D.73. Val
    74. D.74. VarType
    75. D.75. Weekday
    76. D.76. WeekdayName
    77. D.77. Year
3.22.217.45