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
- Copyright
- About the Author
- Credits
- Acknowledgments
- Introduction
- I. Fundamentals of Data Analysis in Access
- 1. The Case for Data Analysis in Access
- 2. Access Basics
- 3. Beyond Select Queries
- II. Basic Analysis Techniques
-
4. Transforming Your Data with Access
- 4.1. Finding and Removing Duplicate Records
- 4.2. Common Transformation Tasks
- 4.3. Summary
-
5. Working with Calculations and Dates
- 5.1. Using Calculations in Your Analysis
- 5.2. Common Calculation Errors
- 5.3. Using Dates in Your Analysis
- 5.4. The New Calculated Data Type
- 5.5. Summary
- 6. Performing Conditional Analysis
- III. Advanced Analysis Techniques
-
7. Adding Dimension with Subqueries and Domain Aggregate Functions
-
7.1. Enhancing Your Analysis with Subqueries
- 7.1.1. Why Use Subqueries?
- 7.1.2. Subquery Ground Rules
- 7.1.3. Creating Subqueries Without Typing SQL Statements
- 7.1.4. Using IN and NOT IN with Subqueries
- 7.1.5. Using Subqueries with Comparison Operators
- 7.1.6. Using Subqueries as Expressions
- 7.1.7. Using Correlated Subqueries
- 7.1.8. Using Subqueries Within Action Queries
- 7.2. Domain Aggregate Functions
- 7.3. Summary
-
7.1. Enhancing Your Analysis with Subqueries
- 8. Running Descriptive Statistics in Access
- 9. Scheduling and Running Batch Analysis
-
10. Leveraging VBA to Enhance Data Analysis
- 10.1. Creating and Using Custom Functions
- 10.2. Controlling Analytical Processes with Forms
- 10.3. Processing Data Behind the Scenes
- 10.4. Summary
- IV. Reports, Dashboards, and Visualization in Access
- 11. Presenting Data with Access Reports
-
12. Using Pivot Tables and Pivot Charts in Access
- 12.1. Pivot Tables in Access?
- 12.2. The Anatomy of a Pivot Table
- 12.3. Creating a Basic Pivot Table
- 12.4. Creating an Advanced Pivot Table with Details
- 12.5. Saving Your Pivot Table
- 12.6. Sending Your Access Pivot Table to Excel
- 12.7. Pivot Table Options
- 12.8. Working with Pivot Charts in Access
- 12.9. Summary
- 13. Enhancing Queries and Reports with Visualizations
- V. Advanced Excel and Access Integration Techniques
- 14. Getting Access Data into Excel
- 15. Using VBA to Move Data between Excel and Access
-
16. Exploring Excel and Access Automation
- 16.1. Understanding the Concept of Binding
- 16.2. Automating Excel from Access
-
16.3. Automating Access from Excel
- 16.3.1. Setting the Required References
- 16.3.2. Running an Access Query from Excel
- 16.3.3. Running Access Parameter Queries from Excel
- 16.3.4. Running an Access Macro from Excel
- 16.3.5. Opening an Access Report from Excel
- 16.3.6. Opening an Access Form from Excel
- 16.3.7. Compacting an Access Database from Excel
- 16.4. Summary
- 17. Integrating Excel and Access with XML
-
18. Integrating Excel and Other Office Applications
- 18.1. Integrating Excel with Microsoft Word
- 18.2. Integrating Excel with PowerPoint
- 18.3. Integrating Excel and Outlook
- 18.4. Summary
- VI. Appendixes
-
A. Access VBA Fundamentals
-
A.1. Covering the Basics in 10 Steps
- A.1.1. Step 1: Creating a Standard Module
- A.1.2. Step 2: Creating a Function
- A.1.3. Step 3: Giving Your Function Purpose with a Procedure
- A.1.4. Step 4: Testing Your Function
- A.1.5. Step 5: Declaring a Variable
- A.1.6. Step 6: Assigning a Value to a Variable
- A.1.7. Step 7: Compiling Your Newly Created Function
- A.1.8. Step 8: Saving Your Newly Created Function
- A.1.9. Step 9: Running Your Function in a Macro
- A.1.10. Step 10: Running Your Function from a Form
- A.2. Letting Access Teach You VBA
-
A.1. Covering the Basics in 10 Steps
-
B. Understanding and Using SQL
- B.1. Understanding Basic SQL
-
B.2. Getting Fancy with Advanced SQL Statements
- B.2.1. Expanding Your Search with the Like Operator
- B.2.2. Selecting Unique Values and Rows without Grouping
- B.2.3. Grouping and Aggregating with the GROUP BY Clause
- B.2.4. Setting Sort Order with the ORDER BY Clause
- B.2.5. Creating Aliases with the AS Clause
- B.2.6. SELECT TOP and SELECT TOP PERCENT
- B.2.7. Performing Action Queries via SQL Statements
- B.2.8. Creating Crosstabs with the TRANSFORM Statement
- B.3. Using SQL Specific Queries
- C. Query Performance, Database Corruption, and Other Thoughts
-
D. Data Analyst's Function Reference
- D.1. Abs
- D.2. Asc
- D.3. Atn
- D.4. Choose
- D.5. Chr
- D.6. Cos
- D.7. Date
- D.8. DateAdd
- D.9. DateDiff
- D.10. DatePart
- D.11. DateSerial
- D.12. DateValue
- D.13. Day
- D.14. DDB
- D.15. Domain Aggregate Functions
- D.16. Exp
- D.17. FormatCurrency
- D.18. FormatDateTime
- D.19. FormatNumber
- D.20. FormatPercent
- D.21. FV
- D.22. Hour
- D.23. IIf
- D.24. InStr
- D.25. InStrRev
- D.26. IPmt
- D.27. IRR
- D.28. IsError
- D.29. IsNull
- D.30. IsNumeric
- D.31. LCase
- D.32. Left
- D.33. Len
- D.34. Log
- D.35. Mid
- D.36. Minute
- D.37. MIRR
- D.38. Month
- D.39. MonthName
- D.40. Now
- D.41. NPer
- D.42. NPV
- D.43. NZ
- D.44. Partition
- D.45. Pmt
- D.46. PPmt
- D.47. PV
- D.48. Rate
- D.49. Replace
- D.50. Right
- D.51. Rnd
- D.52. Round
- D.53. Second
- D.54. Sgn
- D.55. Sin
- D.56. SLN
- D.57. Space
- D.58. SQL Aggregate Functions
- D.59. Sqr
- D.60. Str
- D.61. StrConv
- D.62. String
- D.63. StrReverse
- D.64. Switch
- D.65. SYD
- D.66. Tan
- D.67. Time
- D.68. TimeSerial
- D.69. TimeValue
- D.70. Trim, LTrim, RTrim
- D.71. TypeName
- D.72. UCase
- D.73. Val
- D.74. VarType
- D.75. Weekday
- D.76. WeekdayName
- D.77. Year
Product information
- Title: The Excel® Analyst's Guide to Access®
- Author(s):
- Release date: April 2010
- Publisher(s): Wiley
- ISBN: 9780470567012
You might also like
book
Professional Outlook® 2007 Programming
Written by one of the most popular and knowledgeable Microsoft Outlook MVPs, this book fills a …
book
Microsoft® Offic Excel® 2007 Visual Basic® for Applications Step by Step
Quickly teach yourself how to automate tasks and create custom spreadsheet solutions with Excel 2007 Visual …
book
Expert Access™ 2007 Programming
Expert Access 2007 Programming shows experienced developers how to create professional-level Access database applications. The authors—software …
book
Pro Access 2010 Development
Pro Access 2010 Development is a fundamental resource for developing business applications that take advantage of …