Access 2019 Bible E-Book

Michael Alexander

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Serie: Bible
• Sprache: Englisch

Seitenzahl: 1592

Veröffentlichungsjahr: 2018

Beschreibung

Master database creation and management 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.

Leseprobe

Table of Contents

Cover

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

Relational Databases

Access Database Objects

A Five‐Step Design Method

CHAPTER 2: Getting Started with Access

The Access Welcome Screen

How to Create a Blank Database

The Access 2019 Interface

Part II: Understanding Access Tables

CHAPTER 3: Creating Access Tables

Table Types

Creating a New Table

Creating tblCustomers

Changing a Table Design

Understanding tblCustomers Field Properties

Setting the Primary Key

Indexing Access Tables

Printing a Table Design

Saving the Completed Table

Manipulating Tables

Adding Records to a Database Table

Understanding Attachment Fields

CHAPTER 4: Understanding Table Relationships

Building Bulletproof Databases

Data Normalization and Denormalization

Table Relationships

Integrity Rules

Keys

CHAPTER 5: Working with Access Tables

Understanding Datasheets

Looking at the Datasheet Window

Opening a Datasheet

Entering New Data

Navigating Records in a Datasheet

Changing Values in a Datasheet

Using the Undo Feature

Copying and Pasting Values

Replacing Values

Adding New Records

Deleting Records

Displaying Records

Sorting and Filtering Records in a Datasheet

Aggregating Data

Printing Records

Previewing Records

CHAPTER 6: Importing and Exporting Data

How Access Works with External Data

Options for Importing and Exporting

Importing External Data

Exporting to External Formats

CHAPTER 7: Linking to External Data

Linking External Data

Working with Linked Tables

Splitting a Database

Part III: Working with Access Queries

CHAPTER 8: Selecting Data with Queries

Introducing Queries

Creating a Query

Working with Query Fields

Adding Criteria to Your Queries

Printing a Query's Recordset

Saving a Query

Creating Multi‐table Queries

Working with the Table Pane

Creating and Working with Query Joins

CHAPTER 9: Using Operators and Expressions in Access

Introducing Operators

Using Operators and Expressions in Queries

Entering Single‐Value Field Criteria

Using Multiple Criteria in a Query

Entering Criteria in Multiple Fields

CHAPTER 10: Going Beyond Select Queries

Aggregate Queries

Action Queries

Crosstab Queries

Optimizing Query Performance

Part IV: Analyzing Data in Microsoft Access

CHAPTER 11: Transforming Data in Access

Finding and Removing Duplicate Records

Common Transformation Tasks

CHAPTER 12: Working with Calculations and Dates

Using Calculations in Your Analyses

Using Dates in Your Analyses

CHAPTER 13: Performing Conditional Analyses

Using Parameter Queries

Using Conditional Functions

CHAPTER 14: The Fundamentals of Using SQL

Understanding Basic SQL

Getting Fancy with Advanced SQL Statements

Using SQL‐Specific Queries

CHAPTER 15: Subqueries and Domain Aggregate Functions

Enhancing Your Analyses with Subqueries

Domain Aggregate Functions

CHAPTER 16: Running Descriptive Statistics in Access

Basic Descriptive Statistics

Advanced Descriptive Statistics

Part V: Working with Access Forms and Reports

CHAPTER 17: Creating Basic Access Forms

Working with Form Views

Understanding Different Types of Forms

Working with Controls

Introducing Properties

CHAPTER 18: Working with Data on Access Forms

Using Form View

Changing Values in a Form

Printing a Form

Working with Form Properties

Adding a Form Header or Footer

Working with Section Properties

Changing the Layout

Converting a Form to a Report

CHAPTER 19: Working with Form Controls

Setting Control Properties

Creating a Calculated Control

Working with Subforms

Form Design Tips

Tackling Advanced Forms Techniques

Using the Tab Control

Using Dialog Boxes to Collect Information

Designing a Form from Scratch

CHAPTER 20: Presenting Data with Access Reports

Introducing Reports

Creating a Report, from Beginning to End

Banded Report Design Concepts

Creating a Report from Scratch

Improving the Report's Appearance

CHAPTER 21: Advanced Access Report Techniques

Grouping and Sorting Data

Formatting Data

Adding Data

Adding Even More Flexibility

Part VI: Microsoft Access Programming Fundamentals

CHAPTER 22: Using Access Macros

An Introduction to Macros

Understanding Macro Security

Multi‐action Macros

Submacros

Conditions

Temporary Variables

Error Handling and Macro Debugging

Embedded Macros

Macros versus VBA Statements

CHAPTER 23: Using Access Data Macros

Introducing Data Macros

Understanding Table Events

Using the Macro Builder for Data Macros

Understanding the Action Catalog

Creating Your First Data Macro

Managing Macro Objects

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

Understanding VBA Branching Constructs

Working with Objects and Collections

Exploring the Visual Basic Editor

CHAPTER 25: Mastering VBA Data Types and Procedures

Using Variables

Working with Data Types

Understanding Subs and Functions

Creating Functions

Simplifying Code with Named Arguments

CHAPTER 26: Understanding the Access Event Model

Programming Events

Identifying Common Events

Paying Attention to Event Sequence

CHAPTER 27: Debugging Your Access Applications

Organizing VBA Code

Testing Your Applications

Traditional Debugging Techniques

Using the Access Debugging Tools

Trapping Errors in Your Code

Part VII: Advanced Access Programming Techniques

CHAPTER 28: Accessing Data with VBA

Working with Data

Understanding DAO Objects

Understanding ADO Objects

Writing VBA Code to Update a Table

CHAPTER 29: Advanced Data Access with VBA

Adding an Unbound Combo Box to a Form to Find Data

Filtering a Form

CHAPTER 30: Customizing the Ribbon

The Ribbon Hierarchy

Editing the Default Ribbon

Working with the Quick Access Toolbar

Developing Custom Ribbons

Creating a Custom Ribbon

The Basic Ribbon XML

Adding Ribbon Controls

Attaching Ribbons to Forms and Reports

Removing the Ribbon Completely

CHAPTER 31: Preparing Your Access Application for Distribution

Defining the Current Database Options

Developing the Application

Polishing Your Application

Bulletproofing an Application

Securing the Environment

CHAPTER 32: Integrating Access with SharePoint

Introducing SharePoint

Understanding SharePoint Sites

Sharing Data between Access and SharePoint

Using SharePoint Templates

Index

End User License Agreement

List of Tables

Chapter 1

TABLE 1.1 Customer‐Related Data Items Found in the Reports

TABLE 1.2 Sales Data Items Found in the Reports

TABLE 1.3 Comparing the Data Items

TABLE 1.4 Tables with Keys

Chapter 3

TABLE 3.1 The Collectible Mini Cars Customers Table

TABLE 3.2 Data Types Available in Microsoft Access

TABLE 3.3 Numeric Field Settings

TABLE 3.4 Numeric Format Examples

TABLE 3.5 Format Examples

TABLE 3.6 Required and AllowZeroLength Property Combinations

Chapter 4

TABLE 4.1 Deriving the Primary Key

Chapter 5

TABLE 5.1 Navigating in a Datasheet

TABLE 5.2 Wildcard Characters

TABLE 5.3 Editing Techniques

Chapter 8

TABLE 8.1 Rules for Updating Queries

Chapter 9

TABLE 9.1 And Operator Results

TABLE 9.2 Or Expression Results

TABLE 9.3 Not Operator Results

TABLE 9.4 Common Operators Used in Select Queries

TABLE 9.5 Using Wildcards with the Like Operator

Chapter 13

TABLE 13.1 Customer Classifications

Chapter 14

TABLE 14.1 Selection Methods Using the Like Operator

Chapter 17

TABLE 17.1 Controls in Access Forms

TABLE 17.2 Form/Report Control Naming Conventions

Chapter 18

TABLE 18.1 Navigating in a Form

TABLE 18.2 Editing Techniques

TABLE 18.3 Form Format Properties

TABLE 18.4 Form Data Properties

TABLE 18.5 Form “Other” Properties

Chapter 19

TABLE 19.1 Important Tab Control Properties

TABLE 19.2 Property Settings for Dialog Forms

TABLE 19.3 Row Source Properties

Chapter 20

TABLE 20.1 Text Box Control Values for Can Grow and Can Shrink

Chapter 21

TABLE 21.1 Properties for the “Blank Line” Example

TABLE 21.2 Micro‐Adjustment Keystroke Combinations

TABLE 21.3 Access Object Types and Values

Chapter 22

TABLE 22.1 mcrBackupContactsAndProducts

TABLE 22.2 mcrMainMenu

TABLE 22.3 mcrReportMenu

TABLE 22.4 mcrHelloWorldEnhanced

TABLE 22.5 mcrReportMenuEnhanced

TABLE 22.6 mcrDivisionErrorHandling

Chapter 25

TABLE 25.1 VBA Data Types

TABLE 25.2 Access and VBA Data Types

TABLE 25.3 A Sample Naming Convention

Chapter 26

TABLE 26.1 Events Common to Multiple Object Types

TABLE 26.2 Essential Form Events

TABLE 26.3 Form Mouse and Keyboard Events

TABLE 26.4 Form Data Events

TABLE 26.5 Control Events

TABLE 26.6 Report Events

TABLE 26.7 Report Section Events

Chapter 28

TABLE 28.1 Recordset Types

TABLE 28.2 Deletion Status Values

Chapter 30

TABLE 30.1 The USysRibbons Table Design

Chapter 31

TABLE 31.1 Form Properties for a Progress Meter

TABLE 31.2 Startup Option Properties of the Application Object

Chapter 32

TABLE 32.1 SharePoint Data Type Conversion

List of Illustrations

Chapter 2

FIGURE 2.1 The Access welcome screen provides a number of ways to start working with Access.

FIGURE 2.2 Enter the name of the new database in the File Name box.

FIGURE 2.3 Your new database is created.

FIGURE 2.4 The Access interface starts with the Ribbon at the top and the Navigation pane at the left.

FIGURE 2.5 Choosing an alternate display for the Navigation pane.

FIGURE 2.6 Contextual tabs contain commands that are specific to whichever object is active.

FIGURE 2.7 The Quick Access toolbar is located above the Ribbon.

FIGURE 2.8 Commands you can add to the Quick Access toolbar.

FIGURE 2.9 Adding more commands to the Quick Access toolbar.

Chapter 3

FIGURE 3.1 The Create tab contains tools necessary for adding new objects to your Access database

FIGURE 3.2 The new table in Datasheet view.

FIGURE 3.3 Field design tools are located on the Fields tab of the Ribbon.

FIGURE 3.4 A new table added in Design view

FIGURE 3.5 The Design tab of the Ribbon.

FIGURE 3.6 The Property Sheet.

FIGURE 3.7 The Data Type drop‐down list.

FIGURE 3.8 The Input Mask Wizard for creating input masks for Text field types.

FIGURE 3.9 A data‐validation warning box appears when the user enters a value in the field that does not match the rule specified in the design of the table.

FIGURE 3.10 The Table Design View area on the Options dialog box contains a box for setting the AutoIndex on Import/Create options.

FIGURE 3.11 The property area for the Currency field named CreditLimit.

FIGURE 3.12 The Lookup tab for a Yes/No field.

FIGURE 3.13 Setting up a combo box as the display control for Credit Status.

FIGURE 3.14 Using a combo box as a lookup control to restrict user input on a field.

FIGURE 3.15 Creating a composite primary key.

FIGURE 3.16 frmIndexTest provides a quick and easy way to verify the importance of indexes.

FIGURE 3.17 Multiple‐field (composite) indexes can enhance performance.

FIGURE 3.18 It's easy to set the properties of an index.

FIGURE 3.19 The Documenter dialog box.

FIGURE 3.20 Printing options in the Print Table Definition dialog box.

FIGURE 3.21 Pasting a table opens the Paste Table As dialog box.

FIGURE 3.22 Using Datasheet view to add records to a table.

FIGURE 3.23 Managing attachments in an Attachment field.

Chapter 4

FIGURE 4.1 An Access table containing customer, contact,

and

orders data.

FIGURE 4.2 An unnormalized tblBookOrders table.

FIGURE 4.3 Only a slight improvement over the previous design.

FIGURE 4.4 First normal form at last!

FIGURE 4.5 Moving customer data to its own table.

FIGURE 4.6 The first step in making our table 2NF.

FIGURE 4.7 We have achieved second normal form.

FIGURE 4.8 An Excel worksheet used as a flat‐file database.

FIGURE 4.9 The relationship between the Employees and Payroll tables is an example of a typical one‐to‐many relationship.

FIGURE 4.10 Most of the Collectible Mini Cars table relationships.

FIGURE 4.11 A one‐to‐one relationship.

FIGURE 4.12 The Northwind Traders database contains many examples of one‐to‐many relationships.

FIGURE 4.13 A database of students and the clubs they belong to is an example of a many‐to‐many relationship.

FIGURE 4.14 A typical database relationship.

FIGURE 4.15 Double‐click a table name to add it to the Relationships window.

FIGURE 4.16 You enforce referential integrity in the Edit Relationships dialog box.

FIGURE 4.17 A one‐to‐many relationship between tblBookOrders5 and tblBookOrderDetails.

FIGURE 4.18 The Join Properties dialog box, used to set up the join properties between tblCustomers and tblSales. Notice that it specifies all records from the Customers table.

FIGURE 4.19 A dialog box warning that referential integrity can't be enforced because of integrity violations.

FIGURE 4.20 A simple validation rule goes a long way toward preserving the database's integrity.

Chapter 5

FIGURE 5.1 A typical Datasheet view. Each row represents a single record in the table; each column represents a single field (like Description or ModelYear) in the table.

FIGURE 5.2 The Navigation buttons of a datasheet.

FIGURE 5.3 The Datasheet Ribbon's Home tab.

FIGURE 5.4 An empty datasheet. Notice that the first record is blank and has an asterisk in the record selector.

FIGURE 5.5 Entering a new record into the Datasheet view of tblProducts.

FIGURE 5.6 The error message Access displays when attempting to save a record with a duplicate primary key value entered into the new record. Use an AutoNumber field as your primary key to avoid this error.

FIGURE 5.7 The warning Access displays when entering data that doesn't match the field's data type. Access gives you a few choices to correct the problem.

FIGURE 5.8 The Zoom window. Notice that you can see all of the field's data event at a larger font size.

FIGURE 5.9 The Find and Replace dialog box. The fastest way to activate it is to simply press Ctrl+F.

FIGURE 5.10 The Find and Replace dialog box with the Replace tab showing. In this case, you want to replace Mini Vans with Minivans.

FIGURE 5.11 The Delete Record dialog box warns you that you're about to delete a specific number of records. The default response is Yes (okay to delete), so be careful when deleting records.

FIGURE 5.12 Select and drag a column to change the field order.

FIGURE 5.13 The Column Width dialog box.

FIGURE 5.14 Changing a row's height. Position the mouse as shown and drag to the desired height.

FIGURE 5.15 Changing the datasheet's font directly from the Ribbon. Choose font type style, size, and other font attributes for the entire datasheet.

FIGURE 5.16 Use the Datasheet Formatting dialog box to customize the look of the datasheet.

FIGURE 5.17 Different line styles and row colors for the datasheet.

FIGURE 5.18 Hide and unhide columns using the Unhide Columns dialog box.

FIGURE 5.19 The Sort & Filter group lets you change the record order and reduce the number of visible rows.

FIGURE 5.20 Using Filter by Selection. In this case, you see all trucks that are not Mack models.

FIGURE 5.21 Filtering the Make field. Use the column filter menu to select criteria for a field.

FIGURE 5.22 Using Filter by Form lets you set multiple conditions for filtering at one time. Notice the Or tab at the bottom of the window.

FIGURE 5.23 The datasheet Total row.

FIGURE 5.24 The Microsoft Access Print menu.

Chapter 6

FIGURE 6.1 The External Data tab of the Ribbon hints at the variety of external data sources available to Access.

FIGURE 6.2 Many types of Access database objects can be imported from one Access database into another.

FIGURE 6.3 The Save Import Steps feature lets you save frequently executed import processes for future use.

FIGURE 6.4 The Saved Imports feature lets you rerun previous saved import processes.

FIGURE 6.5 Access can import data from an Excel spreadsheet, but there are some restrictions.

FIGURE 6.6 Excel worksheet columns should contain consistent data.

FIGURE 6.7 The Import Spreadsheet Wizard.

FIGURE 6.8 Does the first row contain column headings?

FIGURE 6.9 You can override any of the default settings Access has chosen.

FIGURE 6.10 Specify a primary key for the new table.

FIGURE 6.11 The first screen of the Import Text Wizard.

FIGURE 6.12 The second Import Text Wizard screen.

FIGURE 6.13 A typical fixed‐width text file.

FIGURE 6.14 The Import Text Wizard screen for fixed‐width text files.

FIGURE 6.15 The Import Specification dialog box for importing a fixed‐width text file.

FIGURE 6.16 The Export XML dialog box.

FIGURE 6.17 Advanced XML export options.

FIGURE 6.18 Exporting XML schema information.

FIGURE 6.19 XML presentation options.

FIGURE 6.20 An exported XML file in plain text.

FIGURE 6.21 Access understands XML data.

FIGURE 6.22 The Export – HTML Document dialog box.

FIGURE 6.23 The same wizard imports objects and links to external Access tables.

FIGURE 6.24 Importing Access objects.

FIGURE 6.25 The initial Outlook import options.

FIGURE 6.26 Importing Outlook objects into Access.

FIGURE 6.27 Naming the table in an ODBC destination.

FIGURE 6.28 Selecting the ODBC driver.

FIGURE 6.29 The Employees table has successfully exported to a SQLite3 database.

FIGURE 6.30 The Microsoft Word Mail Merge Wizard allows you to export data to existing or new documents.

FIGURE 6.31 A completed Word Merge.

FIGURE 6.32 Select PDF or XPS as a file format.

FIGURE 6.33 A table published in the PDF file format.

Chapter 7

FIGURE 7.1 Linked tables in an Access database. Notice that each linked table has an arrow icon indicating its status as a linked table.

FIGURE 7.2 A query using externally linked tables.

FIGURE 7.3 The Datasheet view of externally linked data.

FIGURE 7.4 Use the Get External Data dialog box to select the type of operation you want to perform on the external data sources.

FIGURE 7.5 Use the Link Tables dialog box to select the Access table(s) for linking.

FIGURE 7.6 The Navigation pane with tblSalesPayments added. Hovering over the linked table icon reveals its source.

FIGURE 7.7 The first screen of the Get External Data – Excel Spreadsheet dialog box.

FIGURE 7.8 The main Link Spreadsheet Wizard screen.

FIGURE 7.9 The Link HTML Wizard dialog box showing the data in the HTML file.

FIGURE 7.10 The Link Specification dialog box that is used to name the column headings (field names) for the linked table.

FIGURE 7.11 A typical fixed‐width text file.

FIGURE 7.12 CSV data is more compact than fixed‐width text, but it's more difficult to read.

FIGURE 7.13 Opening linked tables in Design view comes with a warning.

FIGURE 7.14 The Linked Table Manager lets you relocate external tables that have been moved.

FIGURE 7.15 A new row in the text file will sync with Access automatically.

FIGURE 7.16 A database kept on a file server can generate a large amount of traffic on the network.

FIGURE 7.17 A database kept on a file server can generate a large amount of traffic on the network.

FIGURE 7.18 The Database Splitter is a simple wizard.

FIGURE 7.19 Specify the permanent location of the back‐end database in the Create Back‐end Database dialog box.

FIGURE 7.20 The Database Splitter creates links for all tables in the database.

Chapter 8

FIGURE 8.1 The Show Table dialog box and the query design window.

FIGURE 8.2 The query design window with tblProducts added.

FIGURE 8.3 The Query Tools Design Ribbon.

FIGURE 8.4 To add fields from your table to the QBD grid, simply double‐click or drag the field.

FIGURE 8.5 Selecting multiple fields to add to the QBD grid.

FIGURE 8.6 Adding the asterisk to the QBD grid selects all fields in the table.

FIGURE 8.7 Click the Run button to display the results of your query.

FIGURE 8.8 Right‐click the query's tab header and select Design View to return to the QBD grid.

FIGURE 8.9 Selecting a column in the QBD grid. The pointer changes to a downward‐pointing arrow when you move over the field selector.

FIGURE 8.10 Moving the Category field to between ProductID and Description. Notice the QBD field icon below the arrow near the Description column.

FIGURE 8.11 Resizing columns in the QBD grid.

FIGURE 8.12 The Show check box is unchecked for the State field so that field will not show in the results.

FIGURE 8.13 An ascending sort has been specified for the LastName and FirstName fields.

FIGURE 8.14 The order of the fields in the QBD grid is critical when sorting on multiple fields.

FIGURE 8.15 Specifying Cars as the query's criteria.

FIGURE 8.16 Specifying text and date criteria in the same query.

FIGURE 8.17 The query design window with four tables added. Notice that the join lines are already present.

FIGURE 8.18 The QBD grid with table names displayed. Notice that it shows all four table names.

FIGURE 8.19 An inner join operation will select only the records that have matching values in both tables. The arrows point to the records that will be included in the results.

FIGURE 8.20 A left outer join operation will select all records from the first table and only those records from the second table that have matching values in both tables. The arrows point to the records that will be included in the results.

FIGURE 8.21 A right outer join operation will select all records from the second table and only those records from the first table that have matching values in both tables. The arrows point to the records that will be included in the results.

FIGURE 8.22 An ad hoc join between tblProducts and tblCategories.

FIGURE 8.23 Selecting an outer join for the query.

FIGURE 8.24 A right outer join corrects the “missing products” problem in Figure 8.22.

Chapter 9

FIGURE 9.1 The QBD pane shows a simple criterion asking for all models where the category is Cars.

FIGURE 9.2 Using

And

and

Or

criteria in a query

FIGURE 9.3 Creating complex criteria without using the

And/Or

operators.

FIGURE 9.4 The SQL view for the query in Figure 9.3. Notice that it contains a single

OR

and two

AND

operators (in the

WHERE

clause).

FIGURE 9.5 The query design window showing tblCustomers open.

FIGURE 9.6 Using the

Like

operator in a select query.

FIGURE 9.7 The results of using the

Like

operator with a select query in a Memo field. The query looks for the word

red

in the Notes field.

FIGURE 9.8 Using the

Not

operator in criteria.

FIGURE 9.9 Criteria set for products with low inventory.

FIGURE 9.10 Using the

Or

operator. Notice the two

Or

operators under the State field—AZ Or CA Or NY.

FIGURE 9.11 Using the

Or

cell of the QBD pane. You can place criteria vertically in the QBD grid.

FIGURE 9.12 Using the

In

operator to find all records for buyer state being either AZ, CA, or NY.

FIGURE 9.13 Using an

And

operator to specify complex query criteria.

FIGURE 9.14 Using the

Between…And

operator. The results are the same as the query in Figure 9.13.

FIGURE 9.15 Use

Is Null

to select rows containing fields that contain no data.

FIGURE 9.16 The QBD pane with

And/Or

criteria between fields using the Criteria and Or rows.

FIGURE 9.17 Using the

Or

operator between fields.

FIGURE 9.18 A simple rearrangement in the QBD grid results in a very different query.

FIGURE 9.19 Using

And

s and

Or

s in a select query.

FIGURE 9.20 Using multiple

And

s and

Or

s across fields. This is a rather complex select query.

Chapter 10

FIGURE 10.1 Running this query will return all the records in your data set, not the summary you need.

FIGURE 10.2 Activating Totals in your design grid adds a Total row to your query grid that defaults to Group By.

FIGURE 10.3 Change the aggregate function under the LineTotal field to

Sum

.

FIGURE 10.4 After running your query, you have a summary showing your total revenue by period.

FIGURE 10.5 This query returns only one line showing total records for the 201201 period.

FIGURE 10.6 This query results in a few more records, but if you add up the counts in each group, they'll total 503.

FIGURE 10.7 The

Expression

aggregate function allows you to perform the designated custom calculation on each Period group separately.

FIGURE 10.8 Running this query will cause an error message because you have no aggregation defined for Period.

FIGURE 10.9 Adding a

Where

clause remedies the error and allows you to run the query.

FIGURE 10.10 Create this query in Design view.

FIGURE 10.11 Enter the name of your new table.

FIGURE 10.12 Click Yes to run your query.

FIGURE 10.13 This query will select all records with a Period earlier than 201206.

FIGURE 10.14 Click Yes to continue with your delete action.

FIGURE 10.15 The warning message tells you that you'll lose records during the append process.

FIGURE 10.16 This query selects all records contained in Periods 201201 through 201205.

FIGURE 10.17 Enter the name of the table to which you want to append your query results.

FIGURE 10.18 In the Append To row, select the name of the field in your destination table where you want to append the information resulting from your query.

FIGURE 10.19 Click Yes to continue with your append action.

FIGURE 10.20 This query will select all customers that are in the 33605 zip code.

FIGURE 10.21 In this query, you are updating the zip code for all customers that have a code of 33605 to 33606.

FIGURE 10.22 Click Yes to continue with your update action.

FIGURE 10.23 The basic structure of a crosstab query.

FIGURE 10.24 Select Crosstab Query Wizard from the New Query dialog box.

FIGURE 10.25 Select the data source for your crosstab query.

FIGURE 10.26 Select the ProductID field and then click the Next button.

FIGURE 10.27 Select the OrderDate field and then click the Next button.

FIGURE 10.28 Select Quarter and then click Next.

FIGURE 10.29 Select the LineTotal and Sum, and then click the Next button.

FIGURE 10.30 Click Finish to see your query results.

FIGURE 10.31 A powerful analysis in just a few clicks.

FIGURE 10.32 Create an aggregate query as shown here.

FIGURE 10.33 Set each field's role in the Crosstab row.

FIGURE 10.34 You can define a criterion to filter your crosstab queries.

FIGURE 10.35 This crosstab query will display all regions as columns in alphabetical order.

FIGURE 10.36 The Column Headings attribute is set to have the columns read in this order: West, Canada, Midwest, North, Northeast, South, Southeast, and Southwest.

Chapter 11

FIGURE 11.1 Are there duplicate records in this table? It depends on how you define one.

FIGURE 11.2 If you get this error message when trying to set a primary key, you have duplicate records in your data set.

FIGURE 11.3 Select the Find Duplicates Query Wizard and then click OK.

FIGURE 11.4 Select the data set in which you want to find duplicates, and then click Next.

FIGURE 11.5 Select the field(s) that make up a unique record in your data set.

FIGURE 11.6 Select the field(s) you want to see in your query.

FIGURE 11.7 Name your query and click Finish.

FIGURE 11.8 Your Find Duplicates query.

FIGURE 11.9 Activate the Paste Table As dialog box to copy your table's structure into a new table called LeadList_NoDups.

FIGURE 11.10 Set as a primary key the field(s) that best defines a unique record.

FIGURE 11.11 Now you can append all records excluding the duplicates.

FIGURE 11.12 This query will update the null values in the DollarPotential field to a value of 0.

FIGURE 11.13 This query will update blank values in the Segment field to a value of “Other.”

FIGURE 11.14 This query concatenates the row values of the Type field and the Code field.

FIGURE 11.15 The MyTest field now contains the concatenated values of the Type field and the Code field.

FIGURE 11.16 This query concatenates the row values of the Type field and the Code field and separates them with a colon.

FIGURE 11.17 The MyTest field now contains the concatenated values of the Type field and the Code field, separated by a colon.

FIGURE 11.18 The Address field is in all lowercase letters.

FIGURE 11.19 This query will convert addresses to proper case.

FIGURE 11.20 The leading spaces are preventing an accurate aggregation.

FIGURE 11.21 Simply pass the field name through the

Trim

function in an update query to remove the leading and trailing spaces.

FIGURE 11.22 This query finds all instances of “blvd” and replaces them with “Boulevard.”

FIGURE 11.23 The phone number has been transformed into a standard format by adding the appropriate characters to key positions with the string.

FIGURE 11.24 This query will update the MyTest field with a properly formatted phone number.

FIGURE 11.25 You need to parse the values in the ContactName field into three separate fields.

FIGURE 11.26 This query will update the Contact_LastName and Contact_FirstName fields.

FIGURE 11.27 Check your progress so far.

FIGURE 11.28 This query parses out the first name and the middle initial from the Contact_FirstName field.

FIGURE 11.29 With two queries, you've successfully parsed the ContactName field into three separate fields.

Chapter 12

FIGURE 12.1 In this query, you're using a constant to calculate a 10 percent price increase.

FIGURE 12.2 In this query, you're using two fields in a Dollar Variance calculation.

FIGURE 12.3 In this query, you're using the aggregation results for each market as expressions in your calculation.

FIGURE 12.4 This query uses the results of one calculation as an expression in another.

FIGURE 12.5 The results of this calculation will be difficult to read because they'll all be fractional numbers that have many digits trailing a decimal point. Forcing the results into round numbers will make for easier reading.

FIGURE 12.6 You can use your calculation as the argument in the

Int

function, allowing you to remove the fractional part from the resulting data.

FIGURE 12.7 Activate the Expression Builder by right‐clicking inside the Field row of the query grid and selecting Build.

FIGURE 12.8 The Expression Builder will display any database object you can use in your expression.

FIGURE 12.9 The Expression Builder displays all the functions available to you.

FIGURE 12.10 Access tells you which arguments are needed to make the function work.

FIGURE 12.11 Help files are available to explain each function in detail.

FIGURE 12.12 The function here will round the results of the calculation,

([Dim_Transactions]![Line Total])/13

.

FIGURE 12.13 Your newly created expression will give you the average revenue per period for all transactions.

FIGURE 12.14 To demonstrate how null values can cause calculation errors, build this query in Design view.

FIGURE 12.15 When any variable in your calculation is null, the resulting answer is a null value.

FIGURE 12.16 Pass the Forecast field through the

Nz

function to convert null values to zero.

FIGURE 12.17 The first record now shows a variance value.

FIGURE 12.18 You're adding 30 to each ship date, effectively creating a date that is equal to the ship date plus 30 days.

FIGURE 12.19 In this query, you're calculating the number of days between two dates.

FIGURE 12.20 This query returns the number of days between today's date and each order date.

FIGURE 12.21 No matter what day it is today, this query will return all orders older than 90 days.

FIGURE 12.22 You're calculating the difference between today's date and each manager's hire date.

FIGURE 12.23 This data set shows the number of days, not the number of years.

FIGURE 12.24 Divide your original calculation by 365.25 to convert the answer to years.

FIGURE 12.25 Your query is now returning years, but you have to strip away the fractional portion of your answer.

FIGURE 12.26 Running this query will return the number of years each employee has been with the company.

FIGURE 12.27 The

Year

,

Month

,

Day

, and

Weekday

functions enable you to parse out a part of a date.

FIGURE 12.28 This query will give you the original order date, the date you should send a warning letter, and the date on which the order will be 30 days overdue.

FIGURE 12.29 You can group dates into quarters by using the

Format

function.

FIGURE 12.30 You can also use the

Format

function in a crosstab query.

FIGURE 12.31 You've successfully grouped your dates into quarters.

Chapter 13

FIGURE 13.1 This query has a hard‐coded criterion for system period.

FIGURE 13.2 To create a parameter query, replace the hard‐coded criteria with text enclosed in square brackets ([ ]).

FIGURE 13.3 The Parameter Value dialog box lets you specify the criteria each time you run your query.

FIGURE 13.4 You can employ more than one parameter in a query.

FIGURE 13.5 This query combines standard operators with parameters in order to limit the results.

FIGURE 13.6 If the parameter in this query is ignored, the query will return all records thanks to the wildcard (*).

FIGURE 13.7 You can use parameters in calculations, enabling you to change the calculation variables each time you run the query.

FIGURE 13.8 You can use parameters as arguments in functions instead of hard‐coded values.

FIGURE 13.9 This query will perform a calculation on the Actual and the Forecast fields to calculate a percent to forecast.

FIGURE 13.10 The errors shown in the results are due to the fact that some revenues are being divided by zeros.

FIGURE 13.11 This

IIf

function enables you to test for forecasts with a value of zero and bypass them when performing your calculation.

FIGURE 13.12 The

IIf

function helped you avoid the division by zero errors.

FIGURE 13.13 This query will update the MyTest field to tag all customers that have a revenue potential at or above $10,000 with the word “LARGE.”

FIGURE 13.14 This query will update the MyTest field to tag all customers that have a revenue potential less than $10,000 with the word “SMALL.”

FIGURE 13.15 You can accomplish the same task in one query using the

IIf

function.

FIGURE 13.16 This query demonstrates how to create a crosstab analysis without using a crosstab query.

FIGURE 13.17 The resulting data set gives you a clean crosstab‐style view of your data.

FIGURE 13.18 Creating crosstab‐style reports using the

IIf

function allows you to calculate more than one value.

FIGURE 13.19 This analysis would be impossible to create in a standard crosstab query, where multiple calculations are not allowed.

FIGURE 13.20 Using the

Switch

function is sometimes more practical than using nested

IIf

functions. This query will classify customers by how much they spend.

FIGURE 13.21 Each customer is conditionally tagged with a group designation based on annual revenue.

Chapter 14

FIGURE 14.1 Build this relatively simple query in Design view.

FIGURE 14.2 You can get to SQL view by selecting View ➪ SQL View.

FIGURE 14.3 A basic

SELECT

statement in SQL view.

FIGURE 14.4 Build this aggregate query in Design view. Take note that the query is sorted descending on the Sum of LineTotal.

FIGURE 14.5 Change the Top Values property to 25.

FIGURE 14.6 Running the query will give you the top 25 customers by revenue.

FIGURE 14.7 This data set shows revenue by region and market.

FIGURE 14.8 This data set shows total revenue by region.

FIGURE 14.9 The two data sets have now been combined to create a report that provides summary and detail data.

FIGURE 14.10 To create a pass‐through query, you must use the SQL window.

FIGURE 14.11 You must specify an ODBC connection string in the pass‐through query's Property Sheet.

Chapter 15

FIGURE 15.1 To use a subquery in Query Design view, simply enter the SQL statement.

FIGURE 15.2 Create a query to find the average time in service for all account managers.

FIGURE 15.3 Switch to SQL view and copy the SQL statement.

FIGURE 15.4 Create a query to count the number of account managers by time in service.

FIGURE 15.5 Paste the first SQL statement you copied into the Criteria row of the TIS_IN_MONTHS field.

FIGURE 15.6 Running this query will tell you there are 12 account managers that have a time in service greater than the company average.

FIGURE 15.7 This query uses the

IN

operator with a subquery, allowing you to run two queries in one.

FIGURE 15.8 Use comparison operators to compare the resulting data set of your outer query to the results of the subquery.

FIGURE 15.9 You're using a subquery as an expression in a calculation.

FIGURE 15.10 Your query result.

FIGURE 15.11 A correlated subquery.

FIGURE 15.12 You can use a correlated subquery as part of an expression.

FIGURE 15.13 This query shows you the difference between an aggregate query and a domain aggregate function.

FIGURE 15.14 You can clearly see the difference between an aggregate query and a domain aggregate function.

FIGURE 15.15 You want to add a column that shows the percent of total revenue for each product category.

FIGURE 15.16 Use a

DSum

function as an expression in a calculation to get percent of total.

FIGURE 15.17 You retrieved both total by group and percent of total with one query.

FIGURE 15.18 This query will return all invoice dates and the number of invoices processed on each date.

FIGURE 15.19 Use the

<=

operator in your

DCount

function to return the count of invoice dates that equals or is less than the date returned by the query.

FIGURE 15.20 You now have a running count in your analysis.

FIGURE 15.21 This query uses a

DLookup

to refer to the previous revenue value.

FIGURE 15.22 You can take this functionality a step further and perform a calculation for the previous day.

FIGURE 15.23 Enhance your analysis by adding a variance between today and yesterday.

FIGURE 15.24 Another task made possible by domain aggregate functions.

Chapter 16

FIGURE 16.1 Running this aggregate query will provide a useful set of descriptive statistics.

FIGURE 16.2 Key statistical metrics for the entire data set.

FIGURE 16.3 Add the Branch_Number field to your query to add another dimension to your analysis.

FIGURE 16.4 You have a one‐shot view of the descriptive statistics for each branch.

FIGURE 16.5 This query ranks employees by revenue.

FIGURE 16.6 You've created a Rank column for your data set.

FIGURE 16.7 This query groups by the Rev field and then counts the occurrences of each number in the Rev field. The query is sorted in descending order by Rev.

FIGURE 16.8 Almost there. Turn this into a top values query and you'll have your mode.

FIGURE 16.9 Set the Top Values property to 1.

FIGURE 16.10 This is your mode.

FIGURE 16.11 The first step in finding the median of a data set is to assign a rank to each record.

FIGURE 16.12 The Middle Value subquery counts all the records in the data set and then divides that number by 2.

FIGURE 16.13 Go to record 336 to get the median value of the data set.

FIGURE 16.14 Using the subquery as a criterion for the Rank field ensures that only the median value is returned.

FIGURE 16.15 Start by creating a Random ID field using the

Rnd

function with the CustomerID field.

FIGURE 16.16 Limit the number of records returned by setting the Top Values property of the query.

FIGURE 16.17 Running this query produces a sample of 1,000 random records.

FIGURE 16.18 Start with a query that ranks employees by revenue.

FIGURE 16.19 Add a field that returns a total data set count.

FIGURE 16.20 The final step is to create a calculated field that will give you the percentile rank for each record.

FIGURE 16.21 You've successfully calculated the percentile rank for each employee.

FIGURE 16.22 Start by creating a field named Rank that ranks each employee by revenue and a field named RCount that counts the total records in the data set.

FIGURE 16.23 Create the quartile tags using the

Switch

function.

FIGURE 16.24 Your final data set can be sorted any way without the danger of losing your quartile tags.

FIGURE 16.25 This frequency distribution was created using the

Partition

function.

FIGURE 16.26 This simple query creates the frequency distribution shown in Figure 16.25.

FIGURE 16.27 This query will create a separate frequency distribution for each branch number in your data set.

FIGURE 16.28 You've successfully created multiple frequency distributions with one query.

Chapter 17

FIGURE 17.1 Use the Forms group on the Create tab of the Ribbon to add new forms to your database.

FIGURE 17.2 Use the Form command to quickly create a new form with all the fields from a table or query.

FIGURE 17.3 Use the Form Wizard to create a form with the fields you choose.

FIGURE 17.4 Select the overall layout for the new form.

FIGURE 17.5 Saving the new form.

FIGURE 17.6 The Navigation button provides a number of tab placement options.

FIGURE 17.7 The navigation form features a large area for embedding subforms.

FIGURE 17.8 Use the Navigation Target Name property to specify the tab's subform.

FIGURE 17.9 A navigation form is a quick and easy way to provide basic navigation features.

FIGURE 17.10 Create a multiple‐items form when you want to see more than one record at a time.

FIGURE 17.11 Create a split form when you want to select records from a list and edit them in a form. Use the splitter bar to resize the upper and lower sections of the form.

FIGURE 17.12 Design view of a blank form. Resize the form area by dragging the bottom‐right corner.

FIGURE 17.13 The Design tab lets you add and customize controls in a form's Design view.

FIGURE 17.14 Unbound controls added from the Controls group.

FIGURE 17.15 Click Add Existing Fields in the Tools group to show the field list.

FIGURE 17.16 Drag fields from the field list to add bound controls to the form.

FIGURE 17.17 Selected controls and their moving and sizing handles.

FIGURE 17.18 The Arrange tab lets you work with moving and sizing controls, as well as manipulate the overall layout of the form.

FIGURE 17.19 Resizing a control.

FIGURE 17.20 Moving a control.

FIGURE 17.21 An example of unaligned and aligned controls on the grid.

FIGURE 17.22 Changing the appearance of multiple controls at the same time.

FIGURE 17.23 Grouping multiple controls together.

FIGURE 17.24 Turn a check box into a toggle button.

FIGURE 17.25 Associating a label with a control.

FIGURE 17.26 Change an object's properties with the Property Sheet.

FIGURE 17.27 Setting a control's Control Source property.

Chapter 18

FIGURE 18.1 A form in Form view.

FIGURE 18.2 The Home tab of the Ribbon.

FIGURE 18.3 The Navigation buttons of a form.

FIGURE 18.4 The Insert Object dialog box.

FIGURE 18.5 The Zoom dialog box.

FIGURE 18.6 Using the Date Picker control.

FIGURE 18.7 Change the Show Date Picker property to Never to disable it.

FIGURE 18.8 Using an option group to select a mutually exclusive value.

FIGURE 18.9 Using a combo box to select a value from a list.

FIGURE 18.10 Using the form selector to display the form's Property Sheet.

FIGURE 18.11 Change the Caption property in the form's Property Sheet.

FIGURE 18.12 The Continuous Forms setting of the Default view property shows multiple records at once.

FIGURE 18.13 The Arrange tab of the Ribbon for Layout view.

FIGURE 18.14 The Tab Order dialog box.

FIGURE 18.15 The Format tab of the Ribbon for Layout view.

FIGURE 18.16 Adding fields from the Field List in a form's Layout view.

Chapter 19

FIGURE 19.1 The Property Sheet for the cmdNew command button.

FIGURE 19.2 The Property Sheet for the Customers form.

FIGURE 19.3 Creating a calculated control.

FIGURE 19.4 Aggregate data from a subform can be displayed on the main form.

FIGURE 19.5 The drop‐down list for the Organization combo box.

FIGURE 19.6 These commands simplify adding the date to forms and reports.

FIGURE 19.7 Tell Access how you want the date to appear.

FIGURE 19.8 The header and footer controls provide a consistent look to your Access forms.

FIGURE 19.9 Access lets you change the type of a control without losing the properties you've already set.

FIGURE 19.10 The Format Painter makes it easy to “paint” the appearance of a control onto other controls on a form.

FIGURE 19.11 ScreenTips help make your applications easier to use.

FIGURE 19.12 A small BMP file has been added to frmCustomerSales as the Picture property.

FIGURE 19.13 Transparent Label controls allow the background picture to show through.

FIGURE 19.14 The status bar indicates that the form has a filter applied.

FIGURE 19.15 The Tab control allows a form to host a large amount of data.

FIGURE 19.16 The Tab control's shortcut menu contains relevant commands.

FIGURE 19.17 Assigning an action to a command button.

FIGURE 19.18 Placing controls on a new form.

FIGURE 19.19 A subform for invoice lines.

FIGURE 19.20 Selecting an existing form as a subform.

FIGURE 19.21 Linking a subform to a main form.

FIGURE 19.22 Controls placed on a form.

FIGURE 19.23 Setting the tab order of controls.

Chapter 20

FIGURE 20.1 A tabular report (rptProductsSummary) displayed in Print Preview.

FIGURE 20.2 A columnar report showing report controls distributed throughout the entire page.

FIGURE 20.3 An invoice report (rptInvoice).

FIGURE 20.4 rptCustomerMailingLabels, a typical mailing label report.

FIGURE 20.5 The first screen of the Report Wizard after selecting a data source and fields.

FIGURE 20.6 Specifying the report's grouping.

FIGURE 20.7 Selecting the field sorting order.

FIGURE 20.8 Selecting the summary options.

FIGURE 20.9 Selecting the page layout.

FIGURE 20.10 rptProducts_Wizard displayed in Print Preview.

FIGURE 20.11 Layout view is useful for resizing controls in a columnar report.

FIGURE 20.12 Choosing a theme for the report.

FIGURE 20.13 Setting up a custom color theme.

FIGURE 20.14 Selecting a theme element's color.

FIGURE 20.15 Applying a color theme to all matching objects in an application.

FIGURE 20.16 Displaying rptProducts_Wizard in the zoomed preview mode.

FIGURE 20.17 Displaying multiple pages of a report in Print Preview's page preview mode.

FIGURE 20.18 Access 2019 provides powerful options for publishing reports.

FIGURE 20.19 The report in Design view.

FIGURE 20.20 Layout view of a new report based on tblProducts.

FIGURE 20.21 The Ribbon while a report is open in Layout view.

FIGURE 20.22 A portion of rptProductsSummary, a grouped report containing summary data.

FIGURE 20.23 rptProductsSummary in Design view.

FIGURE 20.24 The Product Display report.

FIGURE 20.25 A blank report in Layout view.

FIGURE 20.26 Building the new report in Design view.

FIGURE 20.27 Setting a report's margins.

FIGURE 20.28 The report with several fields added.

FIGURE 20.29 Adding unbound labels to the report.

FIGURE 20.30 Adding a page‐number expression in a Text Box control.

FIGURE 20.31 A print preview of the report so far.

FIGURE 20.32 Rearranging the controls on the report.

FIGURE 20.33 The report displayed in Print Preview.

FIGURE 20.34 The Group, Sort, and Total area completed.

FIGURE 20.35 Completing the Group Header section.

FIGURE 20.36 The report is pretty plain and uninteresting at this point.

FIGURE 20.37 Adjusting controls in the page header.

FIGURE 20.38 Browsing to an image file to use as the report's logo.

FIGURE 20.39 Specifying the date and time format.

FIGURE 20.40 The completed report in Print Preview.

Chapter 21

FIGURE 21.1 Alphabetical grouping is easy.

FIGURE 21.2 Many options are available for grouping and sorting.

FIGURE 21.3 Modifying a text‐based grouping.

FIGURE 21.4 Sorting and grouping a report by company name.

FIGURE 21.5 An alphabetic heading for each customer group makes the rptSalesJanuaryAlpha2 report easier to read.

FIGURE 21.6 rptSalesJanuaryAlpha2 in Design view.

FIGURE 21.7 The January sales data grouped by each week during the month.

FIGURE 21.8 OrderDate is a Date/Time field, so the grouping options are relevant for date and time data.

FIGURE 21.9 The Design view of rptSalesJanuaryByWeek. Notice the expression in the OrderDate group header.

FIGURE 21.10 Simple tabular reports can be cluttered and boring.

FIGURE 21.11 The simple query underlying rptTabularBad.

FIGURE 21.12 The default property values sometimes lead to unsatisfactory results.

FIGURE 21.13 Hiding repeated information results in a more attractive report.

FIGURE 21.14 An interesting query that sums data and sorts the query results in descending order of the sum.

FIGURE 21.15 A straightforward report (rptUnNumberedList) produced with data from qryCustomerPurchasesJanuary.

FIGURE 21.16 The value in the unbound text box named txtRank will be incremented by 1 for each record in the report.

FIGURE 21.17 The Rank column provides a ranking for each customer in order of purchases during January.

FIGURE 21.18 The bullet character is added to the Control Source property of the txtCompanyName text box.

FIGURE 21.19 Use a Windows feature to insert the bullet in front of the CompanyName field.

FIGURE 21.20 Charmap is a useful tool for exploring Windows font sets.

FIGURE 21.21 Can you tell Guaraná Fantástica is on sale?

FIGURE 21.22 rptPriceList in Design view reveals how this effect is implemented.

FIGURE 21.23 Inform the user that there are no records to print.

FIGURE 21.24 Vertical lines in rptVerticalLines help segregate data.

FIGURE 21.25 Using white space to break up tabular data can make it easier to read.

FIGURE 21.26 This report trick uses hidden unbound text boxes in the Detail section.

FIGURE 21.27 txtPageNumber is the same width as the report.

FIGURE 21.28 txtPageNumber jumps from right to left.

FIGURE 21.29 rptMoreInfo demonstrates how to add more information to your reports.

FIGURE 21.30 Use an unbound text box to capture useful information.

FIGURE 21.31 frmAllReports displays the reports in

Chapter21.accdb

.

FIGURE 21.32 This query returns more detailed information than we've been using.

FIGURE 21.33 Notice how narrow the records in this report are.

FIGURE 21.34 The report makes poor use of the available page width.

FIGURE 21.35 Only a few changes are needed to produce snaking columns.

FIGURE 21.36 The wrong Column Layout setting can be confusing.

FIGURE 21.37 Snaking columns conserve page space and provide more information at a glance.

FIGURE 21.38 Headers (and footers) have properties that can be used to control actions when the grouping value changes.

FIGURE 21.39 The New Row Or Col property forces Access to start a column before or after a section.

FIGURE 21.40 All report page settings must consider the margin widths.

FIGURE 21.41 The summary information is part of the report's header.

FIGURE 21.42 rptSummary in Design view.

Chapter 22

FIGURE 22.1 Use the Macro button on the Create tab to build a new stand‐alone macro.

FIGURE 22.2 The macro builder displaying the Macro Builder and Action Catalog.

FIGURE 22.3 The Hello World! macro uses the

MessageBox

action to display a message.

FIGURE 22.4 Running the Hello World! macro displays a message box.

FIGURE 22.5 Set any object's event property to the macro to trigger that macro when that event occurs.

FIGURE 22.6 Enabling sandbox mode.

FIGURE 22.7 Digitally signing an Access project.

FIGURE 22.8 mcrBackupContactsAndProducts archives data from the live tables into the backup tables.

FIGURE 22.9 Adding a second submacro to a macro.

FIGURE 22.10 The submacro names appear after the macro object in the event property drop‐down list.

FIGURE 22.11 frmReportMenu uses a frame to select the view in which to open the Contacts, Products, and Sales reports.

FIGURE 22.12 mcrReportMenu uses an

If

action to open reports in Print or Print Preview view.

FIGURE 22.13 Multiple actions within

If

and

End If

actions execute as a group.

FIGURE 22.14 mcrHelloWorldEnhanced uses the

SetTempVar

action to get a value from the user and display it in a message box.

FIGURE 22.15 frmReportMenuEnhanced uses a combo box to select which report to open.

FIGURE 22.16 mcrReportMenuEnhanced uses temporary variables to open the report in Print or Print Preview view.

FIGURE 22.17 Errors in macros cause the macro to cease operation.

FIGURE 22.18 mcrDivision divides the numerator by the denominator and generates an error when the denominator is zero.

FIGURE 22.19 mcrDivisionErrorHandling uses the

OnError

action to display a user‐friendly error message and remove the temporary variables.

FIGURE 22.20 Use the Macro Single Step dialog box to step through a macro.

FIGURE 22.21 Use the builder button in the event property to display the Choose Builder dialog box to create an embedded macro.

FIGURE 22.22 An embedded macro doesn't have a name. The title bar displays the control and the event in which the macro is embedded.

FIGURE 22.23 The Convert Macro dialog box.

FIGURE 22.24 The newly converted module.

Chapter 23

FIGURE 23.1 Every Access table includes five data‐oriented events that can be selected when in Datasheet view.

FIGURE 23.2 Using

BeforeChange

to update a field.

FIGURE 23.3 Using

IsInsert

to determine if

BeforeChange

fired as the result of adding a new record.

FIGURE 23.4 Using

AfterInsert

to add a record to tblProductActivityLog.

FIGURE 23.5 The macro builder for the table's

AfterUpdate

event.

FIGURE 23.6 Selecting a table event when a table is in Design view.

FIGURE 23.7 Add a Group to the macro and give it a name.

FIGURE 23.8 Add a Comment describing the actions taken in this macro.

FIGURE 23.9 The

If

block conditionally executes macro actions based on logic you provide.

FIGURE 23.10 Adding the

SetField

action tells Access to change the record if the condition specified is true.

FIGURE 23.11 Click Save to finalize the macro and have it take effect.

FIGURE 23.12 Entering a positive cost will now automatically update the RetailPrice field.

FIGURE 23.13 You can collapse and expand your macro items to simplify the surface of the macro builder.

FIGURE 23.14 The order of macro items can be changed by using the up and down arrows next to the target item, by clicking and dragging items, or by copying and pasting.

FIGURE 23.15 Saving a macro as XML.

Chapter 24

FIGURE 24.1 Creating a module presents a blank code pane.

FIGURE 24.2 Running the code displays a message box.

FIGURE 24.3 Saving the database prompts you to save any unsaved modules.

FIGURE 24.4 A long statement extends beyond the code window.

FIGURE 24.5 Complete Word shows a list of keywords.

FIGURE 24.6 Access Auto List Members help in a module.

FIGURE 24.7 Access Auto Quick Info help in a module.

FIGURE 24.8 Access Auto Constants help in a module.

FIGURE 24.9 The compiler reports errors.

FIGURE 24.10 Using the

Select Case

statement.

FIGURE 24.11 Using the

Do…Loop

statement.

FIGURE 24.12 frmWithDemo is included in

Chapter24.accdb

.

FIGURE 24.13 The Immediate window.

FIGURE 24.14 The Project Explorer shows all the modules in your database.

FIGURE 24.15 Search the Object Browser to find properties and methods.

FIGURE 24.16 The Editor tab of the Options dialog box.

FIGURE 24.17 The Project Properties dialog box contains a number of interesting options.

Chapter 25

FIGURE 25.1 Variable declarations appear at the top of VBA procedures.

FIGURE 25.2 Declaring a public variable.

FIGURE 25.3 Requiring variable declaration is a good idea in most Access applications.

FIGURE 25.4 Variable scope is determined by the variable's declaration.

FIGURE 25.5 Constants are declared with the

Const

keyword.

FIGURE 25.6 A typical subroutine in an Access application.

FIGURE 25.7 The

frmSales cboCustomerID_AfterUpdate

event procedure in the VBA code window.

FIGURE 25.8 The completed

CalcExtendedPrice

function.

FIGURE 25.9 Testing the

CalcExtendedPrice

function in the Immediate window.

FIGURE 25.10 Adding a function call to the Control Source of a control.

FIGURE 25.11 The

CalcTax

function.

FIGURE 25.12 frmNamedArguments demonstrates using named arguments in VBA procedures.

FIGURE 25.13

PrepareOutput()

is able to use arguments submitted in any order as long as they're named.

Chapter 26

FIGURE 26.1 The Property Sheet for frmProducts with the Event tab open.

FIGURE 26.2 An empty event procedure template for the form's

BeforeUpdate

event.

FIGURE 26.3 Running a

NoData

event procedure when there is no data for a report.

FIGURE 26.4 Specifying an event procedure for a report's On No Data event.

FIGURE 26.5 Running an event procedure to display or hide a control on a report.

FIGURE 26.6 Using a switchboard to navigate through the forms and reports of an application.

FIGURE 26.7 Specifying an event procedure for a control event.

FIGURE 26.8 Using an event procedure to open a form.

FIGURE 26.9 Running an event procedure when a form closes.

FIGURE 26.10 Using the

MsgBox()

function to confirm a deletion.

FIGURE 26.11 A confirmation dialog box before deleting a record.

Chapter 27

FIGURE 27.1

Debug.Assert

halts code when a test fails.

FIGURE 27.2 The Debug menu in the VBA code editor window contains valuable debugging tools.

FIGURE 27.3 Viewing a compile error.

FIGURE 27.4 The

MsgBox

statement makes a satisfactory debugging tool (with some limitations).

FIGURE 27.5 Use

Debug.Print

to output messages to the Immediate window.

FIGURE 27.6 Get to know the Immediate window. You'll use it a lot in Access.

FIGURE 27.7 Running code from the Immediate window is a common practice.

FIGURE 27.8 Setting a breakpoint is easy.

FIGURE 27.9 Execution stops on the breakpoint.

FIGURE 27.10 Variables are in scope when in break mode.

FIGURE 27.11 Insert a breakpoint near the location of the code you want to step through.

FIGURE 27.12 Step Into executes one line at a time.

FIGURE 27.13 Auto Data Tips are a powerful tool for debugging.

FIGURE 27.14 Use the Locals window to examine the values of complex objects.

FIGURE 27.15 The Add Watch dialog box includes some powerful options.

FIGURE 27.16 The Watches window reveals all of a variable's details.

FIGURE 27.17 A conditional watch halts execution when the expression

rs.Fields("OrderID") .Value=10251

is

True

.

FIGURE 27.18 The Call Stack window shows you how the execution point reached its current position.

Chapter 28

FIGURE 28.1 The Object Browser provides a view into an object's properties and methods.

FIGURE 28.2 The DAO object model.

FIGURE 28.3 Demonstrating recordset navigation.

FIGURE 28.4 The ADO object model.

FIGURE 28.5 Referencing the ADO library.

FIGURE 28.6

GetString

is a convenient way to see what's in a recordset.

FIGURE 28.7 Using a form to update data in tables.

FIGURE 28.8 Using ADO to update a table.

FIGURE 28.9 Recalculating a field after a form is updated.

FIGURE 28.10

CalcTax()

uses DAO to determine sales tax.

FIGURE 28.11 Recalculating a control after a control is updated.

FIGURE 28.12 Recalculating a control after a record is deleted.

FIGURE 28.13 Examining the tables of a one‐to‐many relationship.

FIGURE 28.14 Using ADO code to delete multiple records.

Chapter 29

FIGURE 29.1 The frmProductsExample1 form with an unbound combo box.

FIGURE 29.2 The Property Sheet for the unbound combo box control.

FIGURE 29.3 The query behind the Row Source property of cboQuickSearch.

FIGURE 29.4 Using the

FindRecord

method to find a record.

FIGURE 29.5 Using a

RecordsetClone

bookmark to find a record.

FIGURE 29.6 Code for filtering and clearing a filter behind a form.

FIGURE 29.7 Creating a simple parameter query.

FIGURE 29.8 Running the parameter query.

FIGURE 29.9 The Query Parameters dialog box.

FIGURE 29.10 Creating a dialog box for selecting records.

FIGURE 29.11 Creating a query that references a form control.

FIGURE 29.12 Creating a dialog box that opens a form.

Chapter 30

FIGURE 30.1 The default Ribbon.

FIGURE 30.2 The SplitButton is a powerful Ribbon control.

FIGURE 30.3 The Menu control simplifies a user's selections.

FIGURE 30.4 The Gallery provides the user with a preview of the options.

FIGURE 30.5 A ToggleButton changes appearance to indicate state.

FIGURE 30.6 The SuperTip provides helpful information to the user.

FIGURE 30.7 The Ribbon Designer allows you to customize the Ribbon.

FIGURE 30.8 Renaming a custom group and setting the group's icon.

FIGURE 30.9 The Quick Access toolbar remains on the screen at all times.

FIGURE 30.10 You can easily add new commands to the Quick Access toolbar.

FIGURE 30.11 Set the Show Add‐in User Interface Errors property to see Ribbon errors.

FIGURE 30.12 A simple form that displays a message.

FIGURE 30.13 XML Notepad can be used to write XML.

FIGURE 30.14 The

USysRibbons

table stores the XML for custom Ribbons.

FIGURE 30.15

frmRibbons

displays the information stored in the

USysRibbons

table.

FIGURE 30.16 Specifying the new custom Ribbon in the Current Database options dialog box.

FIGURE 30.17 Changes to the Ribbon Name property require a restart of the application.

FIGURE 30.18 The XML produces new Ribbon elements that open a form.

FIGURE 30.19 Using the Ribbon Designer to obtain a Ribbon command's

imageMso

attribute.

FIGURE 30.20 You can specify a custom key tip for controls.

FIGURE 30.21 Separators provide a way to divide controls within a group.

FIGURE 30.22 CheckBox controls are a good choice when the user needs to be able to select among a number of options.

FIGURE 30.23 A DropDown control lists users’ names.

FIGURE 30.24 A SplitButton is a very useful Ribbon control.

FIGURE 30.25 Setting a form's

Ribbon Name

property.

FIGURE 30.26 Opening a form opens its Ribbon.

Chapter 31

FIGURE 31.1 The Current Database options enable you to take control of your application from the moment a user starts it.

FIGURE 31.2 A database with the Tabbed Documents option selected. The tabs let you select which Access object to work with.

FIGURE 31.3 The Navigation Options dialog box.

FIGURE 31.4 The Search Bar appears at the top of the Navigation pane.

FIGURE 31.5 Produce self‐documenting code when possible.

FIGURE 31.6 Use a consistent style in each object and throughout your application.

FIGURE 31.7 A splash screen not only increases perceived speed of your application, but it also gives your application a professional appearance.

FIGURE 31.8 The switchboard provides a handy way to navigate throughout the application.

FIGURE 31.9 An About box provides useful information to the user and protects your legal interests.

FIGURE 31.10 Use the status bar to provide feedback on a long process.

FIGURE 31.11 The progress meter after initialization.

FIGURE 31.12 The progress meter midway in its movement.

FIGURE 31.13 A homemade progress meter.

FIGURE 31.14 It's easy to get Access to automatically open a database from a shortcut icon.

FIGURE 31.15 An error message resulting from a procedure with no error‐handling routine.

FIGURE 31.16 A text file can be used to log errors.

FIGURE 31.17 The error log can be imported into a table.

FIGURE 31.18 Adding the

/runtime

switch to a shortcut.

FIGURE 31.19 Choosing to encrypt an Access database.

FIGURE 31.20 Providing a password to encrypt an Access database.

FIGURE 31.21 Choosing to remove a password from an encrypted Access database.

FIGURE 31.22 Providing a password to remove a password from an encrypted Access database.

FIGURE 31.23 Creating a project password restricts users from viewing the application's Visual Basic code.

FIGURE 31.24 Create an ACCDE file to protect your database.

Chapter 32

FIGURE 32.1 A SharePoint document library.

FIGURE 32.2 A SharePoint list allows for the storage and tracking of data in a table format.

FIGURE 32.3 Preparing to link to a SharePoint list.

FIGURE 32.4 The Get External Data – SharePoint Site dialog box.

FIGURE 32.5 Selecting a SharePoint list for linking.

FIGURE 32.6 A linked SharePoint list appears much like any other Access table.

FIGURE 32.7 Selecting a SharePoint list for importing.

FIGURE 32.8 Exporting a table to SharePoint.

FIGURE 32.9 Select the destination SharePoint site and specify a name for the exported list.

FIGURE 32.10 The Move Data group on the Ribbon contains the wizard to upsize to SharePoint.

FIGURE 32.11 The Export Tables to SharePoint Wizard dialog box specifies the destination SharePoint site.

FIGURE 32.12 SharePoint list templates available in Access.

FIGURE 32.13 The Create New List dialog box when creating a new SharePoint list from an Access template.

Guide

Cover

Table of Contents

Begin Reading

Pages

C1

iii

iv

v

vii

ix

xi

xxxix

xl

xli

xlii

1

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

29

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80