Access Data Analysis Cookbook by Bluttman, Ken -- Read -- Imperial Library of Trantor

Index

Access Data Analysis Cookbook

SPECIAL OFFER: Upgrade this ebook with O’Reilly A Note Regarding Supplemental Files Preface

Who Should Read This Book What's in This Book Conventions Used in This Book Using Code Examples We'd Like Your Feedback! Acknowledgments

From Ken Bluttman From Wayne S. Freeze

1. Query Construction

1.1. Finding Unmatched Records

1.1.1. Problem 1.1.2. Solution 1.1.3. Discussion 1.1.4. See Also

1.2. Making AND and OR Do What You Expect

1.2.1. Problem 1.2.2. Solution 1.2.3. Discussion

1.3. Working with Criteria Using the IN Operator

1.3.1. Problem 1.3.2. Solution 1.3.3. Discussion 1.3.4. See Also

1.4. Excluding Records with the NOT Operator

1.4.1. Problem 1.4.2. Solution 1.4.3. Discussion 1.4.4. See Also

1.5. Parameterizing a Query

1.5.1. Problem 1.5.2. Solution 1.5.3. Discussion

1.5.3.1. Specifying a data type for the parameter

1.6. Returning a Top or Bottom Number of Records

1.6.1. Problem 1.6.2. Solution 1.6.3. Discussion

1.7. Returning Distinct Records

1.7.1. Problem 1.7.2. Solution 1.7.3. Discussion

1.7.3.1. Using DistinctRow

1.8. Returning Random Records

1.8.1. Problem 1.8.2. Solution 1.8.3. Discussion

1.9. Fine-Tuning Data Filtering with Subqueries

1.9.1. Problem 1.9.2. Solution 1.9.3. Discussion

1.9.3.1. Removing excessive queries

1.10. Combining Data with Union Queries

1.10.1. Problem 1.10.2. Solution 1.10.3. Discussion

1.11. Inserting On-the-Fly Fields in Select Queries

1.11.1. Problem 1.11.2. Solution 1.11.3. Discussion

1.12. Using Aliases to Simplify Your SQL Statements

1.12.1. Problem 1.12.2. Solution 1.12.3. Discussion

1.13. Creating a Left Join

1.13.1. Problem 1.13.2. Solution 1.13.3. Discussion

1.14. Creating a Right Join

1.14.1. Problem 1.14.2. Solution 1.14.3. Discussion

1.15. Creating an Outer Join

1.15.1. Problem 1.15.2. Solution 1.15.3. Discussion 1.15.4. See Also

2. Calculating with Queries

2.1. Finding the Sum or Average in a Set of Data

2.1.1. Problem 2.1.2. Solution 2.1.3. Discussion

2.2. Finding the Number of Items per Group

2.2.1. Problem 2.2.2. Solution 2.2.3. Discussion

2.3. Using Expressions in Queries

2.3.1. Problem 2.3.2. Solution 2.3.3. Discussion

2.4. Using Custom Functions in Queries

2.4.1. Problem 2.4.2. Solution 2.4.3. Discussion

2.5. Using Regular Expressions in Queries

2.5.1. Problem 2.5.2. Solution 2.5.3. Discussion 2.5.4. See Also

2.6. Using a Cartesian Product to Return All Combinations of Data

2.6.1. Problem 2.6.2. Solution 2.6.3. Discussion

2.7. Creating a Crosstab Query to View Complex Information

2.7.1. Problem 2.7.2. Solution 2.7.3. Discussion

2.7.3.1. Sophisticated crosstabs

3. Action Queries

3.1. Running an Update Query

3.1.1. Problem 3.1.2. Solution 3.1.3. Discussion

3.2. Appending Data

3.2.1. Problem 3.2.2. Solution 3.2.3. Discussion

3.2.3.1. Appending from a recordset

3.3. Deleting Data

3.3.1. Problem 3.3.2. Solution 3.3.3. Discussion

3.4. Creating Tables with Make-Table Queries

3.4.1. Problem 3.4.2. Solution 3.4.3. Discussion

4. Managing Tables, Fields, Indexes, and Queries

4.1. Creating Tables Programmatically

4.1.1. Problem 4.1.2. Solution

4.1.2.1. Using DAO to create a table 4.1.2.2. Using ADOX to create a table 4.1.2.3. Using SQL to create a table 4.1.2.4. Using an XSD schema definition to create a table

4.1.3. Discussion

4.1.3.1. Testing for the table's existence 4.1.3.2. Which method should you use?

4.2. Altering the Structure of a Table

4.2.1. Problem 4.2.2. Solution

4.2.2.1. Programmatically adding and deleting a field 4.2.2.2. Changing a field's data type

4.2.3. Discussion

4.3. Creating and Using an Index

4.3.1. Problem 4.3.2. Solution 4.3.3. Discussion

4.4. Programmatically Removing a Table

4.4.1. Problem 4.4.2. Solution 4.4.3. Discussion

4.5. Programmatically Creating a Query

4.5.1. Problem 4.5.2. Solution 4.5.3. Discussion

4.5.3.1. Creating action queries

5. Working with String Data

5.1. Returning Characters from the Left or Right Side of a String

5.1.1. Problem 5.1.2. Solution 5.1.3. Discussion

5.2. Returning Characters from the Middle of a String When the Start Position and Length Are Known

5.2.1. Problem 5.2.2. Solution 5.2.3. Discussion

5.3. Returning the Start Position of a Substring When the Characters Are Known

5.3.1. problem 5.3.2. Solution 5.3.3. Discussion

5.4. Stripping Spaces from the Ends of a String

5.4.1. Problem 5.4.2. Solution 5.4.3. Discussion

5.5. Stripping Spaces from the Middle of a String

5.5.1. Problem 5.5.2. Solution 5.5.3. Discussion

5.6. Replacing One String with Another String

5.6.1. Problem 5.6.2. Solution 5.6.3. Discussion

5.7. Concatenating Data

5.7.1. Problem 5.7.2. Solution 5.7.3. Discussion

5.7.3.1. Using the Join function

5.8. Sorting Numbers That Are Stored as Text

5.8.1. Problem 5.8.2. Solution 5.8.3. Discussion

5.9. Categorizing Characters with ASCII Codes

5.9.1. Problem 5.9.2. Solution 5.9.3. Discussion

6. Using Programming to Manipulate Data

6.1. Using Excel Functions from Access

6.1.1. Problem 6.1.2. Solution 6.1.3. Discussion

6.2. Working with In-Memory Data

6.2.1. Problem 6.2.2. Solution 6.2.3. Discussion

6.2.3.1. Using collections

6.3. Working with Multidimensional Arrays

6.3.1. Problem 6.3.2. Solution 6.3.3. Discussion

6.3.3.1. Working with three-dimensional data

6.4. Sorting an Array

6.4.1. Problem 6.4.2. Solution 6.4.3. Discussion

6.4.3.1. Sorting multidimensional arrays

6.5. Flattening Data

6.5.1. Problem 6.5.2. Solution 6.5.3. Discussion

6.6. Expanding Data

6.6.1. Problem 6.6.2. Solution 6.6.3. Discussion

6.7. Encrypting Data

6.7.1. Problem 6.7.2. Solution 6.7.3. Discussion

6.8. Applying Proximate Matching

6.8.1. Problem 6.8.2. Solution 6.8.3. Discussion

6.9. Using Transaction Processing

6.9.1. Problem 6.9.2. Solution 6.9.3. Discussion

6.10. Reading from and Writing to the Windows Registry

6.10.1. Problem 6.10.2. Solution 6.10.3. Discussion

6.11. Creating Charts

6.11.1. Problem 6.11.2. Solution 6.11.3. Discussion

6.11.3.1. Charts in Access 2007

6.12. Scraping Web HTML

6.12.1. Problem 6.12.2. Solution 6.12.3. Discussion

6.13. Creating Custom Report Formatting

6.13.1. Problem 6.13.2. Solution 6.13.3. Discussion

6.14. Rounding Values

6.14.1. Problem 6.14.2. Solution 6.14.3. Discussion

6.15. Running Word Mail Merges

6.15.1. Problem 6.15.2. Solution 6.15.3. Discussion

6.16. Building a Multifaceted Query Selection Screen

6.16.1. Problem 6.16.2. Solution 6.16.3. Discussion

7. Importing and Exporting Data

7.1. Creating an Import/Export Specification

7.1.1. Problem 7.1.2. Solution 7.1.3. Discussion

7.2. Automating Imports and Exports

7.2.1. Problem 7.2.2. Solution 7.2.3. Discussion

7.3. Exporting Data with the FileSystemObject

7.3.1. Problem 7.3.2. Solution 7.3.3. Discussion

7.4. Importing Data with the FileSystemObject

7.4.1. Problem 7.4.2. Solution 7.4.3. Discussion

7.5. Importing and Exporting Using XML

7.5.1. Problem 7.5.2. Solution 7.5.3. Discussion

7.6. Generating XML Schemas

7.6.1. Problem 7.6.2. Solution 7.6.3. Discussion

7.7. Using XSLT on Import or Export

7.7.1. Problem 7.7.2. Solution 7.7.3. Discussion

7.8. Working with XML via the MSXML Parser

7.8.1. Problem 7.8.2. Solution 7.8.3. Discussion

7.9. Reading and Writing XML Attributes

7.9.1. Problem 7.9.2. Solution 7.9.3. Discussion

7.10. Creating an RSS Feed

7.10.1. Problem 7.10.2. Solution 7.10.3. Discussion

7.11. Passing Parameters to SQL Server

7.11.1. Problem 7.11.2. Solution 7.11.3. Discussion

7.12. Handling Returned Values from SQL Server Stored Procedures

7.12.1. Problem 7.12.2. Solution 7.12.3. Discussion

7.13. Working with SQL Server Data Types

7.13.1. Problem 7.13.2. Solution 7.13.3. Discussion

7.14. Handling Embedded Quotation Marks

7.14.1. Problem 7.14.2. Solution 7.14.3. Discussion

7.15. Importing Appointments from the Outlook Calendar

7.15.1. Problem 7.15.2. Solution 7.15.3. Discussion

7.16. Importing Emails from Outlook

7.16.1. Problem 7.16.2. Solution 7.16.3. Discussion

7.17. Working with Outlook Contacts

7.17.1. Problem 7.17.2. Solution 7.17.3. Discussion

7.18. Importing Data from Excel

7.18.1. Problem 7.18.2. Solution 7.18.3. Discussion

7.19. Exporting Data to Excel

7.19.1. Problem 7.19.2. Solution 7.19.3. Discussion

7.20. Talking to PowerPoint

7.20.1. Problem 7.20.2. Solution 7.20.3. Discussion

7.21. Selecting Random Data

7.21.1. Problem 7.21.2. Solution 7.21.3. Discussion

8. Date and Time Calculations

8.1. Counting Elapsed Time

8.1.1. Problem 8.1.2. Solution 8.1.3. Discussion

8.2. Counting Elapsed Time with Exceptions

8.2.1. Problem 8.2.2. Solution 8.2.3. Discussion

8.3. Working with Time Zones

8.3.1. Problem 8.3.2. Solution 8.3.3. Discussion

8.4. Working Around Leap Years

8.4.1. Problem 8.4.2. Solution 8.4.3. Discussion

8.5. Isolating the Day, Month, or Year

8.5.1. Problem 8.5.2. Solution 8.5.3. Discussion

8.6. Isolating the Hour, Minute, or Second

8.6.1. Problem 8.6.2. Solution 8.6.3. Discussion

8.7. Adding Time

8.7.1. Problem 8.7.2. Solution 8.7.3. Discussion

9. Business and Finance Problems

9.1. Calculating Weighted Averages

9.1.1. Problem 9.1.2. Solution 9.1.3. Discussion

9.2. Calculating a Moving Average

9.2.1. Problem 9.2.2. Solution 9.2.3. Discussion

9.3. Calculating Payback Period

9.3.1. Problem 9.3.2. Solution 9.3.3. Discussion

9.4. Calculating Return on Investment

9.4.1. Problem 9.4.2. Solution 9.4.3. Discussion

9.5. Calculating Straight-Line Depreciation

9.5.1. Problem 9.5.2. Solution 9.5.3. Discussion

9.6. Creating a Loan Payment Schedule

9.6.1. Problem 9.6.2. Solution 9.6.3. Discussion

9.7. Using PivotTables and PivotCharts

9.7.1. Problem 9.7.2. Solution 9.7.3. Discussion 9.7.4. See Also

9.8. Creating PivotTables

9.8.1. Problem 9.8.2. Solution 9.8.3. Discussion

9.9. Charting Data

9.9.1. Problem 9.9.2. Solution 9.9.3. Discussion

9.10. Finding Trends

9.10.1. Problem 9.10.2. Solution 9.10.3. Discussion

9.11. Finding Head and Shoulders Patterns

9.11.1. Problem 9.11.2. Solution 9.11.3. Discussion

9.12. Working with Bollinger Bands

9.12.1. Problem 9.12.2. Solution 9.12.3. Discussion

9.13. Calculating Distance Between Zip Codes

9.13.1. Problem 9.13.2. Solution 9.13.3. Discussion

10. Statistics

10.1. Creating a Histogram

10.1.1. Problem 10.1.2. Solution 10.1.3. Discussion

10.2. Finding and Comparing the Mean, Mode, and Median

10.2.1. Problem 10.2.2. Solution 10.2.3. Discussion

10.3. Calculating the Variance in a Set of Data

10.3.1. Problem 10.3.2. Solution 10.3.3. Discussion

10.4. Finding the Covariance of Two Data Sets

10.4.1. Problem 10.4.2. Solution 10.4.3. Discussion

10.5. Finding the Correlation of Two Sets of Data

10.5.1. Problem 10.5.2. Solution 10.5.3. Discussion

10.6. Returning All Permutations in a Set of Data

10.6.1. Problem 10.6.2. Solution 10.6.3. Discussion

10.7. Returning All Combinations in a Set of Data

10.7.1. Problem 10.7.2. Solution 10.7.3. Discussion

10.8. Calculating the Frequency of a Value in a Set of Data

10.8.1. Problem 10.8.2. Solution 10.8.3. Discussion

10.9. Generating Growth Rates

10.9.1. Problem 10.9.2. Solution 10.9.3. Discussion

10.10. Determining the Probability Mass Function for a Set of Data

10.10.1. Problem 10.10.2. Solution 10.10.3. Discussion

10.11. Computing the Kurtosis to Understand the Peakedness or Flatness of a Probability Mass Distribution

10.11.1. Problem 10.11.2. Solution 10.11.3. Discussion

10.12. Determining the Skew of a Set of Data

10.12.1. Problem 10.12.2. Solution 10.12.3. Discussion

10.13. Returning a Range of Data by Percentile

10.13.1. Problem 10.13.2. Solution 10.13.3. Discussion

10.14. Determining the Rank of a Data Item

10.14.1. Problem 10.14.2. Solution 10.14.3. Discussion

10.15. Determining the Slope and the Intercept of a Linear Regression

10.15.1. Problem 10.15.2. Solution 10.15.3. Discussion

10.16. Measuring Volatility

10.16.1. Problem 10.16.2. Solution 10.16.3. Discussion

Index About the Authors Colophon SPECIAL OFFER: Upgrade this ebook with O’Reilly

← Prev
Back
Next →

← Prev
Back
Next →