Table of Contents
Introduction 1
About This Book 1
Foolish Assumptions 2
Icons Used in This Book 2
Where to Go from Here 3
Part 1 Basic Training 5
Chapter 1 Access Basic Training 7
What is Access Good For, Anyway? 8
Building big databases 8
Creating databases with multiple tables 9
Databases with user forms 12
Databases that require special reporting 14
Reach Out with SharePoint 17
How Access Works and How You Work with It 18
Opening Access 18
Selecting a starting point 19
Now What? 24
Chapter 2 Navigating the Access Workspace 25
Diving Right In 27
Working with Onscreen Tools in Access 29
Clicking tabs 30
Using buttons 31
The File tab and Quick Access tools 32
Accessing panes, panels, and context-sensitive tools 33
Customizing the Access Workspace 34
Repositioning the Quick Access Toolbar 34
Adding buttons to the Quick Access Toolbar 35
Removing buttons from the Quick Access Toolbar 37
Minimizing the Ribbon 38
Working with Screen Tips 38
Mousing Around 41
Navigating Access with the Alt Key 42
Chapter 3 Database Basics 43
Database Lingo 43
Data, no matter how you pronounce it 44
Fields of dreams (or data) 44
Records 45
Tables 45
The database 46
Field Types and Uses 46
Choosing Between Flat and Relational Databases 52
Isolationist tables 52
Tables that mix and mingle 52
Building a Database 54
Adding and Removing Tables 58
One more, please 58
Oops, I didn't mean to do that 59
Part 2 Getting It All on the Table 63
Chapter 4 Sounds Like a Plan 65
Planning Your Database Tables 65
Reviewing fields 66
Determining data types 66
The new normal 68
Understanding normal forms 68
Normalizing your tables 69
Building Tables in Design View 73
Creating fields 73
Setting data types 75
Chapter 5 Table Tune-Ups 77
The Primary Key to Success 78
The lowdown on primary keys 78
Creating a primary key 79
Making Tables Get Along 81
Rules of relationships 81
Relationship types 82
Building Table Relationships 84
The Relationships window 84
Table relationships 85
Indexing for Faster Queries 89
Creating your own index 90
Adding and removing indexes 92
Chapter 6 Remodeling Your Data 95
Opening a Table for Editing 96
Inserting Records and Fields 98
Adding a record 99
Inserting a field 100
Deleting a field 103
Modifying Field Content 104
Name-Calling 105
Renaming fields 105
Renaming a table 108
Turn Uh-Oh! into Yee-Hah! 109
Chapter 7 Types, Masks, and Triggers 111
Access Table Settings 112
Field Data Formats 114
Text fields 115
Number and currency fields 118
Date/time fields 121
Yes/No fields 122
Gaining Control of Data Entry 123
You really need to put a mask on those fields 123
To require or not to require 130
Making your data toe the line with validation 132
Give Your Fingers a Mini Vacation by Default 133
Part 3 Data Management Mania 135
Chapter 8 A Form for All Reasons 137
Generating Forms 137
Keeping it simple: Form tools 139
Granting most wishes: The Form Wizard 141
Customizing Form Parts 146
Taking the Layout view 147
The theme's the thing 147
Managing form controls 148
Managing Data in Form View 153
Navigating and finding records 153
Saving, clearing, and deleting 154
Chapter 9 Importing and Exporting Data 157
Retrieving Data from Other Sources 158
Translating data formats 158
Importing and linking 161
Hit the Road, Data 166
Export formats 167
Exporting table or query data 167
Exporting a report to PDF 169
Chapter 10 Automatically Editing Data 171
Please Read This First! 171
Creating Consistent Corrections 174
Using Queries to Automate the Editing Process 177
Looking for duplicate records 177
Running the Find Duplicates Query Wizard 178
Chapter 11 Access and the Web 183
How Access Works with the Web 183
Understanding Microsoft 365 184
Using the Access Web Browser Control 185
Click! Using hyperlinks in your desktop Access database 188
Adding a Hyperlink field to your desktop database table 189
Fine-tuning your hyperlinks 190
Testing links 192
Publishing Access objects to the web 192
Part 4 The Power of Questions 197
Chapter 12 Finding, Filtering, and Sorting Your Data - Fast 199
Using the Find Command 200
Finding anything fast 201
Shifting Find into high gear 202
Sorting Alphabetically and Numerically 205
Sorting by a single field 205
Sorting on more than one field 206
Fast and Furious Filtering 207
Filtering by a field's content 208
Filter by selection 209
Filter by Form 211
Unfiltering Filter by Form 213
Filter by excluding selection 214
Chapter 13 I Was Just Asking … for Answers 215
Simple (Yet Potent) Filter and Sort Tools 216
Filter things first 216
Fact-finding with fun, fast filtering 219
Here's the "advanced" part 220
Select Queries 226
Solid relationships are the key to getting it all (from your tables) 227
Running the Query Wizard 227
Getting Your Feet Wet with Ad Hoc Queries 233
Adding the finishing touches 237
Saving the query 239
Running your query 239
Chapter 14 I Want These AND Those OR Them 241
Working with AND and/or OR 242
Data from here to there 243
Using multiple levels of AND 244
Establishing criteria with OR 245
Combining AND with OR and OR with AND 246
Chapter 19 Number Crunching with the Total Row 249
Say Hello to the Total Row 249
Adding the Total Row to Your Queries 251
Giving the Total Row a Workout 252
Putting it together with Group By 253
Well, that certainly sums it up 255
Counting, the easy way 256
Narrowing the results with Where 258
Creating Your Own Top-Ten List 259
Choosing the Right Field for the Summary Instruction 260
Chapter 16 Express Yourself with Formulas 263
A Simple Calculation 264
Complex Calculations 267
Calculate 'til you drop! 267
Using one calculation in another 267
Using parameter queries to ask for help 268
"Adding" words with text formulas 270
Hooray for Expression Builder 272
Chapter 17 Take Charge with Action Queries 277
Easy Update 278
Add Records in a Flash 282
Quick Cleanup 285
Part 5 Simple and Snazzy Reporting 289
Chapter 18 Fast and Furious Automatic Reporting 291
Quick and Not-SO-Dirty Automatic Reporting 292
Creating a quick, one-table report 293
Starting the Report Wizard 297
Previewing Your Report 302
Zooming in and out and all around 304
Pop goes the menu 305
Beauty Is Only Skin (Report) Deep 307
The Print Options tab 307
The Page tab 309
The Columns tab 310
Chapter 19 Professionally Designed Reports Made Easy 313
Report Repairs 314
Report Organization 315
Structural devices 316
Page breaks 318
Formatting Stuff 320
Adding color 323
Relocation, relocation, relocation 324
One size does not fit all 325
Spaced-out controls 327
Borderline beauty 328
Tweaking your text 332
Sneaking a Peek 333
Getting a Themes Makeover 335
Adding More Design Elements 336
Drawing lines 336
Pretty as a picture 337
Adding a logo 337
Chapter 20 Groups and Page Breaks, Headers and Footers 341
A Place for Everything and Everything in Its Place 342
Layout basics 342
Sections 344
Grouping your records 347
So you want more? 350
Customizing Properties 351
Controlling report and page headings 353
Adjusting individual sections 355
Itemized adjustments 357
Chapter 21 Mailings to the Masses 363
Massive Mailings with the Label Wizard 363
Part 6 The Part of Tens 371
Chapter 22 Ten Common Problems 373
That's Just Not Normal 374
You Type 73.725, but it Changes to 74 375
Where's That Word I Just Typed? 375
Was There and Now It's Gone 377
Undo 377
Search for the missing record 377
Backup recovery 378
You Run a Query, but the Results Aren't What You Expect 378
The Dreaded Parameter Dialog Box 380
The Slowest Database in Town 380
Your Database File Is Ginormous 382
You Get a Mess When Importing Your Spreadsheet 383
We're Sorry; Your Database File Is Corrupt 384
Chapter 23 Ten Uncommon Tips 385
Document Everything as if Your Life Depended on It 386
Keep Your Fields as Small as Possible 387
Use Number Fields for Real Numbers 388
Validate Your Data 388
Use Understandable Names to Keep Things Simple 389
Delete with Great Caution 389
Backup, Backup, Backup 389
Think, Think, and Think Again 390
Get Organized and Stay Organized 390
There's No Shame in Asking for Help 391
Appendix A Getting Help 393
Index 395
