- 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
Executive Editor
Carol Long
Project Editor
Maureen Spears
Technical Editor
Dick Kusleika
Production Editor
Kathleen Wisor
Copy Editor
C.M. Jones
Editorial Director
Robyn B. Siesky
Editorial Manager
Mary Beth Wakefield
Marketing Manager
Ashley Zurcher
Production Manager
Tim Tate
Vice President and Executive Group Publisher
Richard Swadley
Vice President and Executive Publisher
Barry Pruett
Associate Publisher
Jim Minatel
Project Coordinator, Cover
Lynsey Stanford
Proofreader
Sheilah Ledwidge, Word One
Indexer
Robert Swanson
Cover Designer
Ryan Sneed
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.