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 2019 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 13
What's New in Access 2019? 16
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? 23
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 31
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 37
Working with ScreenTips 38
Mousing Around 41
Navigating Access with the Alt Key 41
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 45
Field Types and Uses 46
Choosing Between Flat and Relational Databases 51
Isolationist tables 51
Tables that mix and mingle 52
Building a Database 53
Adding and Removing Tables 57
One more, please 57
Oops, I didn't mean to do that 59
Part 2: Getting it All on the Table 61
Chapter 4: Sounds Like a Plan 63
Planning Your Database Tables 63
Reviewing fields 64
Determining data types 64
The new normal 66
Understanding normal forms 66
Normalizing your tables 67
Building Tables in Design View 70
Creating fields 70
Setting data types 72
Chapter 5: Table Tune Ups 75
The Primary Key to Success 76
The lowdown on primary keys 76
Creating a primary key 77
Making Tables Get Along 79
Rules of relationships 79
Relationship types 80
Building Table Relationships 82
The Relationships window 82
Table relationships 83
Indexing for Faster Queries 87
Creating your own index 88
Adding and removing indexes 91
Chapter 6: Remodeling Your Data 93
Opening a Table for Editing 94
Inserting Records and Fields 97
Adding a record 97
Inserting a field 98
Deleting a field 101
Modifying Field Content 102
Name-Calling 103
Renaming fields 104
Renaming a table 106
Turn Uh-Oh! into Yee-Hah! 108
Chapter 7: Types, Masks, and Triggers 109
Access Table Settings 110
Field Data Formats 112
Text fields 113
Number and currency fields 115
Date/time fields 118
Yes/No fields 119
Gaining Control of Data Entry 121
You really need to put a mask on those fields 121
To require or not to require 128
Making your data toe the line with validation 129
Give Your Fingers a Mini Vacation by Default 131
Part 3: Data Management Mania 133
Chapter 8: A Form for All Reasons 135
Generating Forms 135
Keeping it simple: Form tools 137
Granting most wishes: The Form Wizard 139
Customizing Form Parts 143
Taking the Layout view 143
The theme's the thing 145
Managing form controls 145
Managing Data in Form View 150
Navigating and finding records 150
Saving, clearing, and deleting 151
Chapter 9: Importing and Exporting Data 153
Retrieving Data from Other Sources 154
Translating data formats 154
Importing and linking 157
Hit the Road, Data 162
Export formats 162
Exporting table or query data 163
Exporting a report to PDF 165
Chapter 10: Automatically Editing Data 167
Please Read This First! 167
Creating Consistent Corrections 170
Using Queries to Automate the Editing Process 173
Looking for duplicate records 173
Running the Find Duplicates Query Wizard 174
Chapter 11: Access and the Web 179
How Access Works with the Web 179
Understanding Office 365 180
Connect Office 365 to Access 2019 181
Your data and the cloud 182
Upload your Access desktop data to the cloud 183
Click! Using Hyperlinks in your desktop Access database 186
Adding a Hyperlink field to your desktop database table 187
Fine-tuning your hyperlinks 188
Testing links 190
Part 4: The Power of Questions 191
Chapter 12: Finding, Filtering, and Sorting Your Data - Fast 193
Using the Find Command 194
Finding anything fast 194
Shifting Find into high gear 196
Sorting Alphabetically and Numerically 199
Sorting by a single field 199
Sorting on more than one field 200
Fast and Furious Filtering 201
Filtering by a field's content 202
Filter by selection 203
Filter by Form 204
Unfiltering Filter by Form 207
Filter by excluding selection 207
Chapter 13: I Was Just Asking for Answers 209
Simple (Yet Potent) Filter and Sort Tools 210
Filter things first 210
Fact-finding with fun, fast filtering 213
Here's the "advanced" part 214
Select Queries 220
Solid relationships are the key to getting it all (from your tables) 221
Running the Query Wizard 221
Getting Your Feet Wet with Ad Hoc Queries 227
Adding the finishing touches 230
Saving the query 231
Running your query 233
Chapter 14: I Want These AND Those OR Them 235
Working with AND and/or OR 236
Data from here to there 237
Using multiple levels of AND 238
Establishing criteria with OR 239
Combining AND with OR and OR with AND 240
Chapter 15: Number Crunching with the Total Row 243
Say Hello to the Total Row 243
Adding the Total Row to Your Queries 245
Working Out the Total Row 246
Putting it together with Group By 247
Well, that certainly sums it up 248
Counting, the easy way 250
Narrowing the results with Where 251
Creating Your Own Top-Ten List 252
Choosing the Right Field for the Summary Instruction 254
Chapter 16: Express Yourself with Formulas 255
A Simple Calculation 256
Complex Calculations 258
Calculate 'til you drop! 259
Using one calculation in another 259
Using parameter queries to ask for help 261
"Adding" words with text formulas 262
Hooray for Expression Builder 264
Chapter 17: Take Charge with Action Queries 269
Easy Update 270
Add Records in a Flash 274
Quick Cleanup 277
Part 5: Simple and Snazzy Reporting 281
Chapter 18: Fast and Furious Automatic Reporting 283
Quick and Not-S0-Dirty Automatic Reporting 284
Creating a quick, one-table report 285
Starting the Report Wizard 289
Previewing Your Report 294
Zooming in and out and all around 295
Pop goes the menu 297
Beauty Is Only Skin (Report) Deep 298
The Print Options tab 298
The Page tab 300
The Columns tab 301
Chapter 19: Professionally Designed Reports Made Easy 303
Report Repairs 304
Report Organization 305
Structural devices 306
Page breaks 308
Formatting Stuff 310
Adding color 313
Relocation, relocation, relocation 314
One size does not fit all 316
Spaced-out controls 317
Borderline beauty 318
Tweaking your text 321
Sneaking a Peek 323
Getting a Themes Makeover 324
Adding More Design Elements 325
Drawing lines 325
Pretty as a picture 326
Adding a logo 327
Chapter 20: Groups and Page Breaks, Headers and Footers 329
A Place for Everything and Everything in Its Place 330
Layout basics 330
Sections 332
Grouping your records 335
So you want more? 338
Customizing Properties 338
Controlling report and page headings 341
Adjusting individual sections 343
Itemized adjustments 344
Chapter 21: Mailings to the Masses 351
Massive Mailings with the Label Wizard 351
Part 6: The Parts of Tens 359
Chapter 22: Ten Common Problems 361
That's Just Not Normal 362
You Type 73.725, but it Changes to 74 363
The Words They Are A-Changing 363
Was There and Now It's Gone 364
Undo 365
Search for the missing record 365
Backup recovery 365
You Run a Query, but the Results Aren't What You Expect 366
The Dreaded Parameter Dialog Box 367
The Slowest Database in Town 368
Your Database File Is as Big as a House 369
You Get a Mess When Importing Your Spreadsheet 371
We're Sorry; Your Database File Is Corrupt 371
Chapter 23: Ten Uncommon Tips 373
Document Everything as Though One Day You'll Be Questioned by the FBI 374
Keep Your Fields as Small as Possible 375
Use Number Fields for Real Numbers 376
Validate Your Data 376
Use Understandable Names to Keep Things Simple 377
Delete with Great Caution 377
Backup, Backup, Backup 377
Think, Think, and Think Again 378
Get Organized and Stay Organized 378
There's No Shame in Asking for Help 379
Appendix A: Getting Help 381
Index 383
