Cover

Title Page

Copyright

About the Authors

About the Technical Editor

Credits

Acknowledgments

Contents

Introduction

Is This Book for You?

Conventions Used in This Book

How This Book Is Organized

How to Use This Book

What’s on the Website

Part I: Access Building Blocks

Chapter 1: An Introduction to Database Development

The Database Terminology of Access

Databases

Tables

Records and fields

Values

Relational Databases

Access Database Objects

Tables

Queries

Data-entry and display forms

Reports

Macros and VBA

Planning for database objects

A Five-Step Design Method

Step 1: The overall design—from concept to reality

Step 2: Report design

Step 3: Data design

Step 4: Table design

Step 5: Form design

Chapter 2: Getting Started with Access

The Access Welcome Screen

How to Create a Blank Database

The Access 2019 Interface

The Navigation pane

Custom

Object Type

Tables and Related Views

Created Date

Modified Date

The Ribbon

The Quick Access toolbar

Part II: Understanding Access Tables

Chapter 3: Creating Access Tables

Table Types

Object tables

Transaction tables

Join tables

Creating a New Table

Designing tables

Using the Design tab

Primary Key

Insert Rows

Delete Rows

Property Sheet

Indexes

Working with fields

Naming a field

Specifying a data type

Entering a field description

Specifying data validation rules

Creating tblCustomers

Using AutoNumber fields

Completing tblCustomers

Changing a Table Design

Inserting a new field

Deleting a field

Changing a field location

Changing a field name

Changing a field size

Handling data conversion issues

Assigning field properties

Common properties

Format

Input Mask

Caption

Validation Rule and Validation Text

Required

AllowZeroLength

Indexed

Understanding tblCustomers Field Properties

Setting the Primary Key

Choosing a primary key

Creating the primary key

Creating composite primary keys

Indexing Access Tables

The importance of indexes

Multiple-field indexes

When to index tables

Printing a Table Design

Saving the Completed Table

Manipulating Tables

Renaming tables

Deleting tables

Copying tables in a database

Copying a table to another database

Adding Records to a Database Table

Understanding Attachment Fields

Chapter 4: Understanding Table Relationships

Building Bulletproof Databases

Data Normalization and Denormalization

First normal form

Second normal form

Identifying entities

Less obvious entities

Breaking the rules

Third normal form

Denormalization

Table Relationships

Connecting the data

One-to-one

One-to-many

Many-to-many

Integrity Rules

No primary key can contain a null value

All foreign key values must be matched by corresponding primary keys

Keys

Deciding on a primary key

Looking at the benefits of a primary key

Designating a primary key

Single-field versus composite primary keys

Natural versus surrogate primary keys

Creating primary keys

Creating relationships and enforcing referential integrity

Specifying the join type between tables

Enforcing referential integrity

Viewing all relationships

Deleting relationships

Following application-specific integrity rules

Chapter 5: Working with Access Tables

Understanding Datasheets

Looking at the Datasheet Window

Moving within a datasheet

Using the Navigation buttons

Examining the Datasheet Ribbon

Views

Clipboard

Sort & Filter

Records

Find

Window

Text Formatting

Opening a Datasheet

Entering New Data

Saving the record

Understanding automatic data-type validation

Knowing how properties affect data entry

Standard text data entry

Date/Time data entry

Number/Currency data entry with data validation

OLE object data entry

Long Text field data entry

Navigating Records in a Datasheet

Moving between records

Finding a specific value

Changing Values in a Datasheet

Manually replacing an existing value

Changing an existing value

Using the Undo Feature

Copying and Pasting Values

Replacing Values

Adding New Records

Deleting Records

Displaying Records

Changing the field order

Changing the field display width

Changing the record display height

Changing display fonts

Displaying cell gridlines and alternate row colors

Aligning data in columns

Hiding and unhiding columns

Freezing columns

Saving the changed layout

Saving a record

Sorting and Filtering Records in a Datasheet

Sorting records

Filtering a selection

Filtering by form

Aggregating Data

Printing Records

Previewing Records

Chapter 6: Importing and Exporting Data

How Access Works with External Data

Types of external data

Ways of working with external data

When to link to external data

When to import external data

When to export data

Options for Importing and Exporting

Importing External Data

Importing from another Access database

Importing from an Excel spreadsheet

Importing a SharePoint list

Importing data from text files

Delimited text files

Fixed-width text files

Importing and exporting XML documents

Importing and exporting HTML documents

Importing Access objects other than tables

Importing an Outlook folder

Exporting to External Formats

Exporting objects to other Access databases

Exporting through ODBC drivers

Exporting to Word

Merging data into Word

Publishing to PDF or XPS

Chapter 7: Linking to External Data

Linking External Data

Identifying linked tables

Limitations of linked data

Linking to other Access database tables

Linking to ODBC data sources

Linking to non-database data

Linking to Excel

Linking to HTML files

Linking to text files

Working with Linked Tables

Setting view properties

Setting relationships

Optimizing linked tables

Deleting a linked table reference

Viewing or changing information for linked tables

Refreshing linked tables

Splitting a Database

The benefits of splitting a database

Knowing where to put which objects

Using the Database Splitter add-in

Part III: Working with Access Queries

Chapter 8: Selecting Data with Queries

Introducing Queries

What queries can do

What queries return

Creating a Query

Adding fields to your queries

Adding a single field

Adding multiple fields

Running your query

Working with Query Fields

Selecting a field in the QBD grid

Changing field order

Resizing columns in the QBD grid

Removing a field

Inserting a field

Hiding a field

Changing the sort order of a field

Adding Criteria to Your Queries

Understanding selection criteria

Entering simple string criteria

Entering other simple criteria

Printing a Query’s Recordset

Saving a Query

Creating Multi-table Queries

Viewing table names

Adding multiple fields

Recognizing the limitations of multi-table queries

Overcoming query limitations

Updating a unique index (primary key)

Replacing existing data in a query with a one-to-many relationship

Updating fields in queries

Working with the Table Pane

Looking at the join line

Moving a table

Removing a table

Adding more tables

Creating and Working with Query Joins

Understanding joins

Leveraging ad hoc table joins

Specifying the type of join

Deleting joins

Chapter 9: Using Operators and Expressions in Access

Introducing Operators

Types of operators

Mathematical operators

Comparison operators

String operators

Boolean (logical) operators

Miscellaneous operators

Operator precedence

The mathematical precedence

The comparison precedence

The Boolean precedence

Using Operators and Expressions in Queries

Using query comparison operators

Understanding complex criteria

Using functions in select queries

Referencing fields in select queries

Entering Single-Value Field Criteria

Entering character (Text or Memo) criteria

The Like operator and wildcards

Specifying non-matching values

Entering numeric criteria

Entering true or false criteria

Entering OLE object criteria

Using Multiple Criteria in a Query

Understanding an Or operation

Specifying multiple values with the Or operator

Using the Or cell of the QBD pane

Using a list of values with the In operator

Using And to specify a range

Using the Between...And operator

Searching for null data

Entering Criteria in Multiple Fields

Using And and Or across fields in a query

Specifying Or criteria across fields of a query

Using And and Or together in different fields

A complex query on different lines

Chapter 10: Going Beyond Select Queries

Aggregate Queries

Creating an aggregate query

About aggregate functions

Group By

Sum, Avg, Count, StDev, Var

Min, Max, First, Last

Expression, Where

Action Queries

Make-table queries

Delete queries

Append queries

Update queries

Crosstab Queries

Creating a crosstab query using the Crosstab Query Wizard

Creating a crosstab query manually

Using the query design grid to create your crosstab query

Customizing your crosstab queries

Optimizing Query Performance

Normalizing your database design

Using indexes on appropriate fields

Optimizing by improving query design

Compacting and repairing your database regularly

Part IV: Analyzing Data in Microsoft Access

Chapter 11: Transforming Data in Access

Finding and Removing Duplicate Records

Defining duplicate records

Finding duplicate records

Removing duplicate records

Common Transformation Tasks

Filling in blank fields

Concatenating

Concatenating fields

Augmenting field values with your own text

Changing case

Removing leading and trailing spaces from a string

Finding and replacing specific text

Adding your own text in key positions within a string

Parsing strings using character markers

Query 1

Query 2

Chapter 12: Working with Calculations and Dates

Using Calculations in Your Analyses

Common calculation scenarios

Using constants in calculations

Using fields in calculations

Using the results of aggregation in calculations

Using the results of one calculation as an expression in another

Using a calculation as an argument in a function

Constructing calculations with the Expression Builder

Common calculation errors

Understanding the order of operator precedence

Watching out for null values

Watching the syntax in your expressions

Using Dates in Your Analyses

Simple date calculations

Advanced analysis using functions

The Date function

The Year, Month, Day, and Weekday functions

The DateAdd function

Grouping dates into quarters

The DateSerial function

Chapter 13: Performing Conditional Analyses

Using Parameter Queries

How parameter queries work

Ground rules of parameter queries

Working with parameter queries

Working with multiple parameter conditions

Combining parameters with operators

Combining parameters with wildcards

Using parameters as calculation variables

Using parameters as function arguments

Using Conditional Functions

The IIf function

Using IIf to avoid mathematical errors

Saving time with IIf

Nesting IIf functions for multiple conditions

Using IIf functions to create crosstab analyses

The Switch function

Comparing the IIf and Switch functions

Chapter 14: The Fundamentals of Using SQL

Understanding Basic SQL

The SELECT statement

Selecting specific columns

Selecting all columns

The WHERE clause

Making sense of joins

Inner joins

Outer joins

Getting Fancy with Advanced SQL Statements

Expanding your search with the Like operator

Selecting unique values and rows without grouping

Grouping and aggregating with the GROUP BY clause

Setting the sort order with the ORDER BY clause

Creating aliases with the AS clause

Creating a column alias

Creating a table alias

Showing only the SELECT TOP or SELECT TOP PERCENT

Top values queries explained

The SELECT TOP statement

The SELECT TOP PERCENT statement

Performing action queries via SQL statements

Make-table queries translated

Append queries translated

Update queries translated

Delete queries translated

Creating crosstabs with the TRANSFORM statement

Using SQL-Specific Queries

Merging data sets with the UNION operator

Creating a table with the CREATE TABLE statement

Manipulating columns with the ALTER TABLE statement

Adding a column with the ADD clause

Altering a column with the ALTER COLUMN clause

Deleting a column with the DROP COLUMN clause

Dynamically adding primary keys with the ADD CONSTRAINT clause

Creating pass-through queries

Chapter 15: Subqueries and Domain Aggregate Functions

Enhancing Your Analyses with Subqueries

Why use subqueries?

Subquery ground rules

Creating subqueries without typing SQL statements

Using IN and NOT IN with subqueries

Using subqueries with comparison operators

Using subqueries as expressions

Using correlated subqueries

Uncorrelated subqueries

Correlated subqueries

Using a correlated subquery as an expression

Using subqueries within action queries

A subquery in a make-table query

A subquery in an append query

A subquery in an update query

A subquery in a delete query

Domain Aggregate Functions

Understanding the different domain aggregate functions

DSum

DAvg

DCount

DLookup

DMin and DMax

DFirst and DLast

DStDev, DStDevP, DVar, and DvarP

Examining the syntax of domain aggregate functions

Using no criteria

Using text criteria

Using number criteria

Using date criteria

Using domain aggregate functions

Calculating the percent of total

Creating a running count

Using a value from the previous record

Chapter 16: Running Descriptive Statistics in Access

Basic Descriptive Statistics

Running descriptive statistics with aggregate queries

Determining rank, mode, and median

Ranking the records in your data set

Getting the mode of a data set

Getting the median of a data set

Pulling a random sampling from your data set

Advanced Descriptive Statistics

Calculating percentile ranking

Determining the quartile standing of a record

Creating a frequency distribution

Part V: Working with Access Forms and Reports

Chapter 17: Creating Basic Access Forms

Working with Form Views

Understanding Different Types of Forms

Creating a new form

Using the Form command

Using the Form Wizard

Looking at special types of forms

Navigation forms

Multiple-items forms

Split forms

Datasheet forms

Resizing the form area

Saving your form

Working with Controls

Categorizing controls

Adding a control

Using the Controls group

Using the field list

Selecting and deselecting controls

Selecting a single control

Selecting multiple controls

Deselecting controls

Manipulating controls

Resizing a control

Sizing controls automatically

Moving a control

Aligning controls

Modifying the appearance of a control

Grouping controls

Changing a control’s type

Copying a control

Deleting a control

Reattaching a label to a control

Introducing Properties

Displaying the Property Sheet

Getting acquainted with the Property Sheet

Changing a control’s property setting

Naming control labels and their captions

Chapter 18: Working with Data on Access Forms

Using Form View

Looking at the Home tab of the Ribbon

The Views group

The Clipboard group

The Sort & Filter group

The Records group

The Find group

The Window group

The Text Formatting group

Navigating among fields

Moving among records in a form

Changing Values in a Form

Knowing which controls you can’t edit

Working with pictures and OLE objects

Entering data in the Long Text field

Entering data in the Date field

Using option groups

Using combo boxes and list boxes

Switching to Datasheet view

Saving a record

Printing a Form

Working with Form Properties

Changing the title bar text with the Caption property

Creating a bound form

Specifying how to view the form

Removing the Record Selector

Looking at other form properties

Adding a Form Header or Footer

Working with Section Properties

The Visible property

The Height property

The Back Color property

The Special Effect property

The Display When property

The printing properties

Changing the Layout

Changing a control’s properties

Setting the tab order

Modifying the format of text in a control

Using the Field List to add controls

Converting a Form to a Report

Chapter 19: Working with Form Controls

Setting Control Properties

Customizing default properties

Looking at common controls and properties

The Text Box control

The Command Button control

The Combo Box and List Box controls

The Check Box and Toggle Button controls

The Option Group control

The Web Browser control

Creating a Calculated Control

Working with Subforms

Form Design Tips

Using the Tab Stop property

Tallying check boxes

Setting up combo boxes and list boxes

Tackling Advanced Forms Techniques

Using the Page Number and Date/Time controls

Using the Image control

Morphing a control

Using the Format Painter

Offering more end-user help

Adding background pictures

Limiting the records shown on a form

Using the Tab Control

Using Dialog Boxes to Collect Information

Designing the query

Setting up the command buttons

Adding a default button

Setting a Cancel button

Removing the control menu

Designing a Form from Scratch

Creating the basic form

Creating a subform

Adding the subform

Changing the form’s behavior

Setting the form properties

Looking up values during data entry

Saving the record

Changing the form’s appearance

Chapter 20: Presenting Data with Access Reports

Introducing Reports

Identifying the different types of reports

Tabular reports

Columnar reports

Mailing label reports

Distinguishing between reports and forms

Creating a Report, from Beginning to End

Defining the report layout

Assembling the data

Creating a report with the Report Wizard

Creating a new report

Selecting the grouping levels

Defining the group data

Selecting the sort order

Selecting summary options

Selecting the layout

Opening the report design

Adjusting the report’s layout

Choosing a theme

Creating new theme color schemes

Using the Print Preview window

Publishing in alternate formats

Viewing the report in Design view

Printing or viewing the report

Printing the report

Viewing the report

Saving the report

Banded Report Design Concepts

The Report Header section

The Page Header section

The Group Header section

The Detail section

The Group Footer section

The Page Footer section

The Report Footer section

Creating a Report from Scratch

Creating a new report and binding it to a table

Defining the report page size and layout

Placing controls on the report

Resizing a section

Modifying the appearance of text in a control

Working with Text Box controls

Adding and using Text Box controls

Entering an expression in a Text Box control

Sizing a Text Box control or Label control

Deleting and cutting attached labels from Text Box controls

Pasting labels into a report section

Moving Label and Text Box controls

Modifying the appearance of multiple controls

Changing Label and Text Box control properties

Growing and shrinking Text Box controls

Sorting and grouping data

Creating a group header or footer

Sorting data within groups

Removing a group

Hiding a section

Sizing a section

Moving controls between sections

Adding page breaks

Improving the Report’s Appearance

Adjusting the page header

Creating an expression in the group header

Creating a report header

Chapter 21: Advanced Access Report Techniques

Grouping and Sorting Data

Grouping alphabetically

Grouping on date intervals

Hiding repeating information

Hiding a page header

Starting a new page number for each group

Formatting Data

Creating numbered lists

Creating bulleted lists

Adding emphasis at run time

Avoiding empty reports

Inserting vertical lines between columns

Adding a blank line every n records

Even-odd page printing

Using different formats in the same text box

Centering the title

Aligning control labels

Micro-adjusting controls

Adding Data

Adding more information to a report

Adding the user’s name to a bound report

Adding Even More Flexibility

Displaying all reports in a combo box

Fast printing from queried data

Using snaking columns in a report

Exploiting two-pass report processing

Assigning unique names to controls

Part VI: Microsoft Access Programming Fundamentals

Chapter 22: Using Access Macros

An Introduction to Macros

Creating a macro

Assigning a macro to an event

Understanding Macro Security

Enabling sandbox mode

The Trust Center

Multi-action Macros

Submacros

Conditions

Opening reports using conditions

Multiple actions in conditions

Temporary Variables

Enhancing a macro you’ve already created

Using temporary variables to simplify macros

Using temporary variables in VBA

Error Handling and Macro Debugging

The OnError action

The MacroError object

Debugging macros

Embedded Macros

Macros versus VBA Statements

Choosing between macros and VBA

Converting existing macros to VBA

Chapter 23: Using Access Data Macros

Introducing Data Macros

Understanding Table Events

“Before” events

“After” events

Using the Macro Builder for Data Macros

Understanding the Action Catalog

Program flow

Data blocks

Data actions

Creating Your First Data Macro

Managing Macro Objects

Collapsing and expanding macro items

Moving macro items

Saving a macro as XML

Recognizing the Limitations of Data Macros

Chapter 24: Getting Started with Access VBA

Introducing Visual Basic for Applications

Understanding VBA Terminology

Starting with VBA Code Basics

Creating VBA Programs

Modules and procedures

Modules

Procedures and functions

Working in the code window

White space

Line continuation

Multi-statement lines

IntelliSense

Compiling procedures

Saving a module

Understanding VBA Branching Constructs

Branching

The If keyword

The Select Case...End Select statement

Looping

The Do...Loop statement

The For...Next statement

Working with Objects and Collections

An object primer

Properties and methods

Properties

Methods

The With statement

The For Each statement

Exploring the Visual Basic Editor

The Immediate window

The Project Explorer

The Object Browser

VBE options

The Editor tab of the Options dialog box

The Project Properties dialog box

Chapter 25: Mastering VBA Data Types and Procedures

Using Variables

Naming variables

Declaring variables

The Dim keyword

The Public keyword

The Private keyword

Working with Data Types

Comparing implicit and explicit variables

Forcing explicit declaration

Using a naming convention with variables

Understanding variable scope and lifetime

Examining scope

Determining a variable’s lifetime

Deciding on a variable’s scope

Using constants

Declaring constants

Using a naming convention with constants

Eliminating hard-coded values

Working with arrays

Fixed arrays

Dynamic arrays

Array functions

Understanding Subs and Functions

Understanding where to create a procedure

Calling VBA procedures

Creating subs

Creating Functions

Handling parameters

Calling a function and passing parameters

Creating a function to calculate sales tax

Simplifying Code with Named Arguments

Chapter 26: Understanding the Access Event Model

Programming Events

Understanding how events trigger VBA code

Creating event procedures

Identifying Common Events

Form event procedures

Essential form events

Form mouse and keyboard events

Form data events

Control event procedures

Report event procedures

Report section event procedures

Paying Attention to Event Sequence

Looking at common event sequences

Writing simple form and control event procedures

Opening a form with an event procedure

Running an event procedure when closing a form

Using an event procedure to confirm record deletion

Chapter 27: Debugging Your Access Applications

Organizing VBA Code

Testing Your Applications

Testing functions

Compiling VBA code

Traditional Debugging Techniques

Using MsgBox

Using Debug.Print

Using the Access Debugging Tools

Running code with the Immediate window

Suspending execution with breakpoints

Looking at variables with the Locals window

Setting watches with the Watches window

Using conditional watches

Using the Call Stack window

Trapping Errors in Your Code

Understanding error trapping

On Error Resume Next

On Error Goto 0

On Error GoTo Label

The Resume keyword

The Err object

Including error handling in your procedures

Part VII: Advanced Access Programming Techniques

Chapter 28: Accessing Data with VBA

Working with Data

Understanding DAO Objects

The DAO DBEngine object

The DAO Workspace object

The DAO Database object

The DAO TableDef object

The DAO QueryDef object

The DAO Recordset object

Navigating recordsets

Detecting the recordset end or beginning

Counting records

The DAO Field objects (recordsets)

Understanding ADO Objects

The ADO Connection object

The ADO Command object

The ADO Recordset object

Writing VBA Code to Update a Table

Updating fields in a record using ADO

Updating a calculated control

Recalculating a control when updating or adding a record

Checking the status of a record deletion

Eliminating repetitive code

Adding a new record

Deleting a record

Deleting related records in multiple tables

Chapter 29: Advanced Data Access with VBA

Adding an Unbound Combo Box to a Form to Find Data

Using the FindRecord method

Using a bookmark

Filtering a Form

Filtering with code

Filtering with a query

Creating a parameter query

Creating an interactive filter dialog box

Linking the dialog box to another form

Chapter 30: Customizing the Ribbon

The Ribbon Hierarchy

Controls for Access Ribbons

SplitButton

Menu

Gallery

Button

ToggleButton

ComboBox

CheckBox

Special Ribbon features

SuperTips

Collapsing the Ribbon

Editing the Default Ribbon

Working with the Quick Access Toolbar

Developing Custom Ribbons

The Ribbon creation process

Using VBA callbacks

Creating a Custom Ribbon

Step 1: Design the Ribbon and build the XML

Step 2: Write the callback routines

Step 3: Create the USysRibbons table

Step 4: Add XML to USysRibbons

Step 5: Specify the custom Ribbon property

The Basic Ribbon XML

Adding Ribbon Controls

Specifying imageMso

The Label control

The Button control

Separators

The CheckBox control

The DropDown control

The SplitButton Control

Attaching Ribbons to Forms and Reports

Removing the Ribbon Completely

Chapter 31: Preparing Your Access Application for Distribution

Defining the Current Database Options

Application options

Application Title

Application Icon

Display Form

Display Status Bar

Document Window Options

Use Access Special Keys

Compact on Close

Remove Personal Information from File Properties on Save

Use Windows-Themed Controls on Forms

Enable Layout View

Enable Design Changes for Tables in Datasheet View

Check for Truncated Number Fields

Picture Property Storage Format

Navigation options

The Display Navigation Pane check box

The Navigation Options button

Ribbon and toolbar options

Ribbon Name

Shortcut Menu Bar

Allow Full Menus

Allow Default Shortcut Menus

Name AutoCorrect Options

Developing the Application

Building to a specification

Creating documentation

Documenting the code you write

Documenting the application

Testing the application before distribution

Polishing Your Application

Giving your application a consistent look and feel

Adding common professional components

A splash screen

An application switchboard

An About box

The status bar

A progress meter

Making the application easy to start

Bulletproofing an Application

Using error trapping on all Visual Basic procedures

Maintaining usage logs

Separating tables from the rest of the application

Building bulletproof forms

Validating user input

Using the /runtime option

Encrypting or encoding a database

Removing a database password

Protecting Visual Basic code

Securing the Environment

Setting startup options in code

Disabling startup bypass

Setting property values

Getting property values

Chapter 32: Integrating Access with SharePoint

Introducing SharePoint

Understanding SharePoint Sites

SharePoint documents

SharePoint lists

Sharing Data between Access and SharePoint

Linking to SharePoint lists

Importing SharePoint lists

Exporting Access tables to SharePoint

Moving Access tables to SharePoint

Using SharePoint Templates

Index

EULA