Access 2019 Bible

Access 2019 Bible is your, comprehensive reference to the world's most popular database management tool. With clear guidance toward everything from the basics to the advanced, this go-to reference helps you take advantage of everything Access 2019 has to offer. Whether you're new to Access or getting started with Access 2019, you'll find everything you need to know to create the database solution perfectly tailored to your needs, with expert guidance every step of the way. The companion website features all examples and databases used in the book, plus trial software and a special offer from Database Creations. Start from the beginning for a complete tutorial, or dip in and grab what you need when you need it.

Access enables database novices and programmers to store, organize, view, analyze, and share data, as well as build powerful, integrable, custom database solutions — but databases can be complex, and difficult to navigate. This book helps you harness the power of the database with a solid understanding of their purpose, construction, and application.

• Understand database objects and design systems objects
• Build forms, create tables, manipulate datasheets, and add data validation
• Use Visual Basic automation and XML Data Access Page design
• Exchange data with other Office applications, including Word, Excel, and more

From database fundamentals and terminology to XML and Web services, this book has everything you need to maximize Access 2019 and build the database you need.

Michael Alexander is a Microsoft Certified Application Developer (MCAD) and a Microsoft MVP with over 15 years of experience developing reporting solutions for a variety of industries. He is also the principal contributor at datapigtechnologies.com, where he shares video tutorials with the Microsoft Access and Excel communities. Mike has authored several books on advanced business analysis. Dick Kusleika is a 12-time Microsoft Excel MVP, and the principal contributor at the Daily Dose of Excel blog.

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-specific 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-Specific 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

Defining the Current Database Options 994

Polishing Your Application 1009

Bulletproofing 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

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.