Introduction xxxix
Part I: Access Building Blocks 1
Chapter 1: An Introduction to Database Development 3
The Database Terminology of Access 3
Relational Databases 6
Access Database Objects 7
A Five-Step Design Method 9
Chapter 2: Getting Started with Access 17
The Access Welcome Screen 17
How to Create a Blank Database 18
The Access 2019 Interface 20
The Ribbon 24
The Quick Access toolbar 25
Part II: Understanding Access Tables 29
Chapter 3: Creating Access Tables 31
Table Types 31
Creating a New Table 32
Creating tblCustomers 49
Changing a Table Design 50
Understanding tblCustomers Field Properties 68
Setting the Primary Key 71
Indexing Access Tables 73
Printing a Table Design 79
Saving the Completed Table 81
Manipulating Tables 81
Adding Records to a Database Table 84
Understanding Attachment Fields 84
Chapter 4: Understanding Table Relationships 87
Building Bulletproof Databases 88
Data Normalization and Denormalization 90
Table Relationships 102
Integrity Rules 110
Keys 113
Creating relationships and enforcing referential integrity 119
Viewing all relationships 125
Deleting relationships 126
Following application-speci?c integrity rules 126
Chapter 5: Working with Access Tables 129
Understanding Datasheets 130
Looking at the Datasheet Window 131
Opening a Datasheet 135
Entering New Data 135
Navigating Records in a Datasheet 142
Changing Values in a Datasheet 146
Using the Undo Feature 148
Copying and Pasting Values 148
Replacing Values 149
Adding New Records 150
Deleting Records 150
Displaying Records 152
Sorting and Filtering Records in a Datasheet 160
Aggregating Data 165
Printing Records 166
Previewing Records 167
Chapter 6: Importing and Exporting Data 169
How Access Works with External Data 169
Options for Importing and Exporting 173
Exporting to External Formats 202
Chapter 7: Linking to External Data 209
Linking External Data 210
Working with Linked Tables 225
Splitting a Database 229
Part III: Working with Access Queries 237
Chapter 8: Selecting Data with Queries 239
Introducing Queries 240
Creating a Query 241
Working with Query Fields 247
Adding Criteria to Your Queries 252
Printing a Query’s Recordset 255
Saving a Query 256
Creating Multi-table Queries 256
Working with the Table Pane 261
Creating and Working with Query Joins 262
Chapter 9: Using Operators and Expressions in Access 269
Introducing Operators 269
Using Operators and Expressions in Queries 283
Entering Single-Value Field Criteria 289
Using Multiple Criteria in a Query 297
Entering Criteria in Multiple Fields 304
Chapter 10: Going Beyond Select Queries 311
Aggregate Queries 311
Action Queries 320
Crosstab Queries 336
Optimizing Query Performance 348
Part IV: Analyzing Data in Microsoft Access 353
Chapter 11: Transforming Data in Access 355
Finding and Removing Duplicate Records 355
Common Transformation Tasks 363
Chapter 12: Working with Calculations and Dates 379
Using Calculations in Your Analyses 379
Using Dates in Your Analyses 392
Chapter 13: Performing Conditional Analyses 405
Using Parameter Queries 405
Using Conditional Functions 414
The Switch function 420
Comparing the IIf and Switch functions 421
Chapter 14: The Fundamentals of Using SQL 425
Understanding Basic SQL 425
Getting Fancy with Advanced SQL Statements 430
Showing only the SELECT TOP or SELECT TOP PERCENT 433
Performing action queries via SQL statements 436
Using SQL-Speci?c Queries 438
Chapter 15: Subqueries and Domain Aggregate Functions 445
Enhancing Your Analyses with Subqueries 446
Domain Aggregate Functions 459
Examining the syntax of domain aggregate functions 461
Using domain aggregate functions 463
Chapter 16: Running Descriptive Statistics in Access 469
Basic Descriptive Statistics 470
Advanced Descriptive Statistics 479
Part V: Working with Access Forms and Reports 487
Chapter 17: Creating Basic Access Forms 489
Working with Form Views 489
Understanding Different Types of Forms 490
Working with Controls 503
Introducing Properties 524
Chapter 18: Working with Data on Access Forms 531
Using Form View 532
Changing Values in a Form 537
Printing a Form 544
Working with Form Properties 545
Adding a Form Header or Footer 556
Working with Section Properties 556
Changing the Layout 558
Converting a Form to a Report 562
Chapter 19: Working with Form Controls 563
Setting Control Properties 563
Creating a Calculated Control 571
Working with Subforms 572
Form Design Tips 573
Tackling Advanced Forms Techniques 576
Using the Tab Control 583
Using Dialog Boxes to Collect Information 586
Designing a Form from Scratch 589
Chapter 20: Presenting Data with Access Reports 599
Introducing Reports 599
Creating a Report, from Beginning to End 604
Creating a report with the Report Wizard 605
Banded Report Design Concepts 622
Creating a Report from Scratch 628
Improving the Report’s Appearance 651
Chapter 21: Advanced Access Report Techniques 657
Grouping and Sorting Data 658
Formatting Data 670
Adding Data 688
Adding Even More Flexibility 690
Part VI: Microsoft Access Programming Fundamentals 703
Chapter 22: Using Access Macros 705
An Introduction to Macros 705
Understanding Macro Security 709
Multi-action Macros 713
Submacros 716
Conditions 720
Temporary Variables 723
Error Handling and Macro Debugging 728
Embedded Macros 734
Macros versus VBA Statements 736
Chapter 23: Using Access Data Macros 741
Introducing Data Macros 742
Understanding Table Events 742
Using the Macro Builder for Data Macros 745
Understanding the Action Catalog 747
Creating Your First Data Macro 751
Managing Macro Objects 755
Recognizing the Limitations of Data Macros 757
Chapter 24: Getting Started with Access VBA 759
Introducing Visual Basic for Applications 760
Understanding VBA Terminology 761
Starting with VBA Code Basics 762
Creating VBA Programs 763
Modules and procedures 763
Working in the code window 770
Understanding VBA Branching Constructs 777
Working with Objects and Collections 786
Exploring the Visual Basic Editor 792
Chapter 25: Mastering VBA Data Types and Procedures 801
Using Variables 802
Working with Data Types 809
Understanding Subs and Functions 829
Creating Functions 835
Simplifying Code with Named Arguments 840
Chapter 26: Understanding the Access Event Model 843
Programming Events 844
Identifying Common Events 847
Paying Attention to Event Sequence 856
Chapter 27: Debugging Your Access Applications 865
Organizing VBA Code 866
Testing Your Applications 867
Traditional Debugging Techniques 873
Using the Access Debugging Tools 879
Trapping Errors in Your Code 893
Part VII: Advanced Access Programming Techniques 899
Chapter 28: Accessing Data with VBA 901
Working with Data 901
Understanding DAO Objects 904
Understanding ADO Objects 917
Writing VBA Code to Update a Table 923
Chapter 29: Advanced Data Access with VBA 937
Adding an Unbound Combo Box to a Form to Find Data 937
Filtering a Form 945
Chapter 30: Customizing the Ribbon 955
The Ribbon Hierarchy 955
Controls for Access Ribbons 956
Editing the Default Ribbon 961
Working with the Quick Access Toolbar 964
Developing Custom Ribbons 966
Creating a Custom Ribbon 969
The Basic Ribbon XML 977
Adding Ribbon Controls 979
Attaching Ribbons to Forms and Reports 989
Removing the Ribbon Completely 990
Chapter 31: Preparing Your Access Application for Distribution 993
De?ning the Current Database Options 994
Polishing Your Application 1009
Bulletproo?ng an Application 1022
Chapter 32: Integrating Access with SharePoint 1039
Introducing SharePoint 1039
Understanding SharePoint Sites 1040
Sharing Data between Access and SharePoint 1043
Using SharePoint Templates 1052
Index 1055
