Alexander Michael, Kusleika Richard. Access 2019 Bible

Wiley, 2018. — 1245 p. — (Bible). — ISBN: 9781119514756, ASIN 1119514754.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.: Access Building Blocks
: An Introduction to Database Development
The Database Terminology of Access
Databases
Tables
Records and felds
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: 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
Modifed Date
The Ribbon
The Quick Access toolbar: Understanding Access Tables
: 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 felds
Naming a feld
Specifying a data type
Entering a feld description
Specifying data validation rules
Creating tblCustomers
Using AutoNumber felds
Completing tblCustomers
Changing a Table Design
Inserting a new feld
Deleting a feld
Changing a feld location
Changing a feld name
Changing a feld size
Handling data conversion issues
Assigning feld 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-feld 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: 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 benefts of a primary key
Designating a primary key
Single-feld 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-specifc integrity rules: 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 feld data entry
Navigating Records in a Datasheet
Moving between records
Finding a specifc 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 feld order
Changing the feld 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: 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 fles
Delimited text fles
Fixed-width text fles
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: 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 fles
Linking to text fles
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 benefts of splitting a database
Knowing where to put which objects
Using the Database Splitter add-in: Working with Access Queries
: Selecting Data with Queries
Introducing Queries
What queries can do
What queries return
Creating a Query
Adding felds to your queries
Adding a single feld
Adding multiple felds
Running your query
Working with Query Fields
Selecting a feld in the QBD grid
Changing feld order
Resizing columns in the QBD grid
Removing a feld
Inserting a feld
Hiding a feld
Changing the sort order of a feld
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 felds
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 felds 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: 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 felds 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 felds in a query
Specifying Or criteria across felds of a query
Using And and Or together in different felds
A complex query on different lines: 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 felds
Optimizing by improving query design
Compacting and repairing your database regularly: Analyzing Data in Microsoft Access
: Transforming Data in Access
Finding and Removing Duplicate Records
Defning duplicate records
Finding duplicate records
Removing duplicate records
Common Transformation Tasks
Filling in blank felds
Concatenating
Concatenating felds
Augmenting feld values with your own text
Changing case
Removing leading and trailing spaces from a string
Finding and replacing specifc text
Adding your own text in key positions within a string
Parsing strings using character markers
Query
Query: Working with Calculations and Dates
Using Calculations in Your Analyses
Common calculation scenarios
Using constants in calculations
Using felds 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: 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: The Fundamentals of Using SQL
Understanding Basic SQL
The SELECT statement
Selecting specifc 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-Specifc 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: 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: 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: Working with Access Forms and Reports
: 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 feld 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: 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 felds
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 feld
Entering data in the Date feld
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: 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: 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
Defning the report layout
Assembling the data
Creating a report with the Report Wizard
Creating a new report
Selecting the grouping levels
Defning 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
Defning 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: 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: Microsoft Access Programming Fundamentals
: 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: 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: 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: 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: 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 confrm record deletion: 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
On Error GoTo Label
The Resume keyword
The Err object
Including error handling in your procedures: Advanced Access Programming Techniques
: 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 felds 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: 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 flter dialog box
Linking the dialog box to another form: 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: Preparing Your Access Application for Distribution
Defning 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 specifcation
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
Bulletproofng 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: 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