Michael Alexander is a Microsoft Certified Application Developer. For more than 15 years, he has been consulting and developing Microsoft Office solutions. Michael is a Microsoft MVP and runs a free tutorial website at www.datapigtechnologies.com. Dick Kusleika has been named a Microsoft MVP for 12 consecutive years. He develops Access- and Excel-based solutions for clients, and has worked with Microsoft Office for more than 20 years. Dick writes a popular Excel-related blog at www.dailydoseofexcel.com.

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 10

Chapter 2: Getting Started with Access 19

The Access Welcome Screen 19

How to Create a Blank Desktop Database 21

The Access 2016 Interface 23

The Ribbon 26

The Quick Access toolbar 28

Part II: Understanding Access Tables 31

Chapter 3: Creating Access Tables 33

Table Types 33

Creating a New Table 34

Creating tblCustomers 51

Changing a Table Design 52

Understanding tblCustomers Field Properties 70

Setting the Primary Key 73

Indexing Access Tables 75

Printing a Table Design 81

Saving the Completed Table 83

Manipulating Tables 83

Adding Records to a Database Table 85

Understanding Attachment Fields 86

Chapter 4: Understanding Table Relationships 89

Building Bulletproof Databases 90

Data Normalization and Denormalization 92

Table Relationships 104

Integrity Rules 113

Keys 116

Chapter 5: Working with Access Tables 131

Understanding Datasheets 132

Looking at the Datasheet Window 134

Opening a Datasheet 138

Entering New Data 138

Navigating Records in a Datasheet 145

Changing Values in a Datasheet 148

Using the Undo Feature 150

Copying and Pasting Values 151

Replacing Values 152

Adding New Records 153

Deleting Records 153

Displaying Records 154

Sorting and Filtering Records in a Datasheet 163

Aggregating Data 168

Printing Records 169

Previewing Records 170

Chapter 6: Importing and Exporting Data 171

How Access Works with External Data 171

Options for Importing and Exporting 175

Importing External Data 177

Exporting to External Formats 200

Chapter 7: Linking to External Data 209

Linking External Data 210

Working with Linked Tables 224

Splitting a Database 228

Part III: Working with Access Queries 235

Chapter 8: Selecting Data with Queries 237

Introducing Queries 238

Creating a Query 240

Working with Query Fields 246

Adding Criteria to Your Queries 250

Printing a Query’s Recordset 254

Saving a Query 254

Creating Multi-Table Queries 254

Working with the Table Pane 259

Creating and Working with Query Joins 261

Chapter 9: Using Operators and Expressions in Access 267

Introducing Operators 267

Using Operators and Expressions in Queries 282

Entering Single-Value Field Criteria 288

Using Multiple Criteria in a Query 296

Entering Criteria in Multiple Fields 302

Chapter 10: Going Beyond Select Queries 309

Aggregate Queries 309

Action Queries 318

Crosstab Queries 333

Optimizing Query Performance 345

Part IV: Analyzing Data in Access 349

Chapter 11: Transforming Data in Access 351

Finding and Removing Duplicate Records 351

Common Transformation Tasks 359

Chapter 12: Working with Calculations and Dates 375

Using Calculations in Your Analyses 375

Using Dates in Your Analyses 388

Chapter 13: Performing Conditional Analyses 401

Using Parameter Queries 401

Using Conditional Functions 410

Chapter 14: The Fundamentals of Using SQL 421

Understanding Basic SQL 421

Getting Fancy with Advanced SQL Statements 426

Using SQL-Specific Queries 433

Chapter 15: Subqueries and Domain Aggregate Functions 441

Enhancing Your Analyses with Subqueries 442

Domain Aggregate Functions 455

Chapter 16: Running Descriptive Statistics in Access 467

Basic Descriptive Statistics 468

Advanced Descriptive Statistics 476

Part V: Working with Access Forms and Reports 483

Chapter 17: Creating Basic Access Forms 485

Formulating Forms 486

Working with Controls 498

Introducing Properties 517

Chapter 18: Working with Data on Access Forms 525

Using Form View 526

Changing Values in a Form 531

Printing a Form 538

Working with Form Properties 539

Adding a Form Header or Footer 550

Working with Section Properties 550

Changing the Layout 552

Converting a Form to a Report 556

Chapter 19: Working with Form Controls 557

Setting Control Properties 558

Creating a Calculated Control 565

Working with Subforms 565

Form Design Tips 567

Tackling Advanced Forms Techniques 570

Using the Tab Control 577

Using Dialog Boxes to Collect Information579

Designing a Form from Scratch 582

Chapter 20: Presenting Data with Access Reports 591

Introducing Reports 592

Creating a Report, from Beginning to End 596

Banded Report Design Concepts 614

Creating a Report from Scratch 620

Improving the Report’s Appearance 642

Chapter 21: Advanced Access Report Techniques 649

Grouping and Sorting Data 650

Formatting Data 662

Adding Data 680

Adding Even More Flexibility 682

Part VI: Access Programming Fundamentals 695

Chapter 22: Using Access Macros 697

An Introduction to Macros 698

Understanding Macro Security 702

Multi-Action Macros 706

Submacros 709

Conditions 713

Temporary Variables 716

Error Handling and Macro Debugging 721

Embedded Macros 727

Macros versus VBA Statements 729

Chapter 23: Using Access Data Macros 733

Introducing Data Macros 734

Understanding Table Events 735

Using the Macro Designer for Data Macros 738

Understanding the Action Catalog 740

Creating Your First Data Macro 743

Managing Macro Objects 747

Recognizing the Limitations of Data Macros 749

Chapter 24: Getting Started with Access VBA 751

Introducing Visual Basic for Applications 752

Understanding VBA Terminology 754

Starting with VBA Code Basics 755

Creating VBA Programs 755

Understanding VBA Branching Constructs 770

Working with Objects and Collections 780

Exploring the Visual Basic Editor 785

Chapter 25: Mastering VBA Data Types and Procedures 795

Using Variables 796

Working with Data Types 803

Understanding Subs and Functions824

Creating Functions 830

Simplifying Code with Named Arguments 836

Chapter 26: Understanding the Access Event Model 839

Programming Events 840

Identifying Common Events 843

Paying Attention to Event Sequence 852

Chapter 27: Debugging Your Access Applications 861

Organizing VBA Code 862

Testing Your Applications 863

Traditional Debugging Techniques 870

Using the Access Debugging Tools 876

Trapping Errors in Your Code 889

Part VII: Advanced Access Programming Techniques 895

Chapter 28: Accessing Data with VBA Code 897

Working with Data 897

Understanding ADO Objects 901

Understanding DAO Objects 914

Writing VBA Code to Update a Table 924

Chapter 29: Advanced Data Access with VBA 941

Adding an Unbound Combo Box to a Form to Find Data 941

Filtering a Form 949

Chapter 30: Customizing the Ribbon 959

The Ribbon Hierarchy 959

Editing the Default Ribbon 965

Working with the Quick Access Toolbar 968

Developing Custom Ribbons 971

Creating a Custom Ribbon 974

The Basic Ribbon XML 983

Adding Ribbon Controls 985

Attaching Ribbons to Forms and Reports 994

Removing the Ribbon Completely 996

Chapter 31: Preparing Your Access Application for Distribution 999

Defining the Current Database Options 1000

Developing the Application 1009

Polishing Your Application 1015

Bulletproofing an Application 1029

Securing the Environment 1040

Part VIII: Access and Windows SharePoint Services 1045

Chapter 32: Integrating Access with SharePoint 1047

Introducing SharePoint 1047

Understanding SharePoint Sites 1048

Sharing Data between Access and SharePoint 1051

Using SharePoint Templates 1060

Chapter 33: Deploying Access Applications to SharePoint 1063

Understanding Web Publishing with Access 1064

Examining the Limitations of Access Web Applications 1067

Publishing a Custom Access Application to SharePoint 1070

Index 1091

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.