If you have large quantities of data in a Microsoft Access database, and need to study that data in depth, this book is a data cruncher's dream. Access Data Analysis Cookbook offers practical recipes to solve a variety of common problems that users have with extracting Access data and performing calculations on it. Each recipe includes a discussion on how and why the solution works. Whether you use Access 2007 or an earlier version, this book will teach you new methods to query data, different ways to move data in and out of Access, how to calculate answers to financial and investment issues, and more. Learn how to apply statistics to summarize business information, how to jump beyond SQL by manipulating data with VBA, how to process dates and times, and even how to reach into the Excel data analysis toolkit. Recipes demonstrate ways to:
- Develop basic and sophisticated queries
- Apply aggregate functions, custom functions, regular expressions, and crosstabs
- Apply queries to perform non-passive activities such as inserting, updating, and deleting data
- Create and manipulate tables and queries programmatically
- Manage text-based data, including methods to isolate parts of a string and ways to work with numbers that are stored as text
- Use arrays, read and write to the Windows registry, encrypt data, and use transaction processing
- Use the FileSystemObject, use XML with XSLT, communicate with SQL Server, and exchange data with other Office products
- Find answers from time-based data, such as how to add time, count elapsed time, work with leap years, and how to manage time zones in your calculations
- Deal with business and finance problems, including methods for calculating depreciation, loan paybacks, and Return on Investment (ROI)
- Explore statistical techniques, such as frequency, variance, kurtosis, linear regression, combinations and permutations
Access Data Analysis Cookbook is a one-stop-shop for extracting nuggets of valuable information from your database, and anyone with Access experience will benefit from these tips and techniques, including seasoned developers. If you want to use your data, and not just store it, you'll find this guide indispensable.
Access Data Analysis Cookbook......Page 1
Table of Contents......Page 6
Who Should Read This Book......Page 10
What’s in This Book......Page 11
Conventions Used in This Book......Page 12
We’d Like Your Feedback!......Page 13
From Wayne S. Freeze......Page 14
Solution......Page 16
Solution......Page 19
Discussion......Page 20
Solution......Page 22
Discussion......Page 23
Solution......Page 25
Solution......Page 26
Discussion......Page 27
Specifying a data type for the parameter......Page 29
Solution......Page 31
Discussion......Page 32
Solution......Page 34
Using DistinctRow......Page 36
Discussion......Page 39
Solution......Page 41
Discussion......Page 43
Removing excessive queries......Page 44
Solution......Page 46
Discussion......Page 48
Solution......Page 50
Discussion......Page 51
Solution......Page 52
Discussion......Page 53
Solution......Page 54
Discussion......Page 55
Solution......Page 56
Discussion......Page 57
Solution......Page 58
Discussion......Page 59
See Also......Page 60
Solution......Page 61
Discussion......Page 63
Solution......Page 65
Discussion......Page 66
Solution......Page 67
Discussion......Page 68
Solution......Page 69
Problem......Page 72
Solution......Page 73
Discussion......Page 75
Problem......Page 76
Solution......Page 77
Discussion......Page 78
Problem......Page 80
Solution......Page 81
Sophisticated crosstabs......Page 84
Solution......Page 88
Discussion......Page 93
Solution......Page 94
Discussion......Page 96
Appending from a recordset......Page 97
Problem......Page 98
Solution......Page 99
Discussion......Page 100
Solution......Page 103
Discussion......Page 106
Solution......Page 107
Using DAO to create a table......Page 108
Using ADOX to create a table......Page 109
Using an XSD schema definition to create a table......Page 110
Discussion......Page 112
Testing for the table’s existence......Page 113
Problem......Page 114
Programmatically adding and deleting a field......Page 115
Changing a field’s data type......Page 116
Solution......Page 117
Solution......Page 119
Discussion......Page 120
Solution......Page 121
Creating action queries......Page 122
Solution......Page 124
Discussion......Page 125
Discussion......Page 126
Solution......Page 127
Discussion......Page 128
Solution......Page 130
Problem......Page 131
Solution......Page 132
Discussion......Page 133
Solution......Page 134
Discussion......Page 135
Discussion......Page 136
Using the Join function......Page 138
Solution......Page 139
Discussion......Page 140
Solution......Page 142
Discussion......Page 144
Solution......Page 146
Discussion......Page 149
Solution......Page 151
Discussion......Page 153
Using collections......Page 154
Solution......Page 155
Discussion......Page 156
Working with three-dimensional data......Page 157
Solution......Page 159
Discussion......Page 160
Sorting multidimensional arrays......Page 161
Problem......Page 162
Solution......Page 163
Discussion......Page 164
Solution......Page 166
Discussion......Page 167
Solution......Page 168
Discussion......Page 170
Discussion......Page 172
Solution......Page 175
Solution......Page 177
Discussion......Page 178
Solution......Page 180
Discussion......Page 181
Charts in Access 2007......Page 184
Solution......Page 185
Discussion......Page 187
Solution......Page 188
Discussion......Page 189
Solution......Page 192
Discussion......Page 193
Solution......Page 195
Solution......Page 198
Discussion......Page 199
Solution......Page 203
Discussion......Page 206
Solution......Page 209
Discussion......Page 211
Solution......Page 212
Problem......Page 214
Solution......Page 215
Discussion......Page 218
Solution......Page 219
Discussion......Page 220
Discussion......Page 222
Solution......Page 224
Discussion......Page 226
Solution......Page 227
Discussion......Page 229
Solution......Page 231
Solution......Page 233
Problem......Page 236
Discussion......Page 237
Solution......Page 238
Problem......Page 239
Discussion......Page 240
Discussion......Page 241
Solution......Page 242
Solution......Page 245
Problem......Page 247
Solution......Page 248
Discussion......Page 249
Solution......Page 250
Discussion......Page 251
Problem......Page 253
Discussion......Page 254
Solution......Page 255
Solution......Page 259
Discussion......Page 260
Solution......Page 261
Discussion......Page 263
Problem......Page 264
Solution......Page 265
Solution......Page 267
Discussion......Page 269
Discussion......Page 270
Solution......Page 271
Discussion......Page 272
Problem......Page 273
Discussion......Page 274
Solution......Page 275
Discussion......Page 276
Discussion......Page 278
See Also......Page 280
Solution......Page 281
Discussion......Page 282
Solution......Page 283
Solution......Page 284
Solution......Page 287
Discussion......Page 288
Discussion......Page 289
Solution......Page 291
Discussion......Page 293
Discussion......Page 296
Solution......Page 298
Solution......Page 302
Discussion......Page 310
Discussion......Page 314
Solution......Page 316
Discussion......Page 319
Solution......Page 322
Discussion......Page 324
Solution......Page 325
Discussion......Page 326
Discussion......Page 329
Discussion......Page 331
Solution......Page 332
Solution......Page 333
Discussion......Page 335
Solution......Page 336
Discussion......Page 337
Discussion......Page 338
Solution......Page 339
Discussion......Page 340
Discussion......Page 342
Solution......Page 345
Discussion......Page 347
Solution......Page 348
Discussion......Page 349
Solution......Page 350
Discussion......Page 351
Discussion......Page 352
Problem......Page 353
Solution......Page 354
Solution......Page 355
Discussion......Page 359
Index......Page 360
