V. Advanced Excel and Access Integration Techniques
by Michael Alexander
The Excel® Analyst's Guide to Access®
- 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
In This Part
Chapter 14: Getting Access Data into Excel
Chapter 15: Using VBA to Move Data between Excel and Access
Chapter 16: Exploring Excel and Access Automation
Chapter 17: Integrating Excel and Access with XML
Chapter 18: Integrating Excel and Other Office Applications
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.