Overview

This course is aimed at Intermediate users of Access and covers topics such as building advanced forms, writing advanced queries, creating advanced reports and automating operations using Macros.

Training is hands-on and all classes are taught by experienced database developers.
Suitable for Access 2016, 2019 & 365 users.

Course Outline

Advanced queries

Grouping and summarizing
About grouping
Building totals queries
Totals query design
Where totals
Expression totals
Common aggregate functions
Updating, deleting, and relationships
Referential integrity
Cascading practices
About cascading deletes
Allowing deletes in key fields
Allowing updates in key fields
Indexing for performance
About indexes
How an index works
Index planning
Creating indexes in tables
Setting index properties
Maximizing performance
Specialized query types
Pass-Through Queries
Data Definition Query

Advanced forms

About subforms
Creating the main form and subform using the Form Wizard
Creating a subform using the SubForm Wizard
Using an existing form as a subform
Nested subforms
Linking forms
Main form–subform data relationships
The hyperlink control
Adding a hyperlink to a form
The Web Browser control
The attachment control
Managing attachments in a form
The chart control
Specialized form types
About navigation forms
Creating a navigation form
Modal dialog forms
Creating a database start-up form

Advanced reports

About subreports
Subreport record sources
Main and subreport with the same record source
Main and subreport with related record sources
Using an unbound main report as a container
Advanced grouping
Date ranges
Running sums
Advanced formatting
Banded reports
Formatting and design options
Showing Username name
Using a logo
Showing and hiding for printing

Macros

Creating a macro
Planning macros
Using the Macro Builder
Using Macro Builder features
Embedded macros
Assigning macros to events
Data macros
Using variables
Variable scope
Choosing variable sources
When to use variables
Creating macros with variables
Troubleshooting macros
Error handling basics
Stepping through macros
Displaying variable values
Using the OnError action
Using the MacroError Object
Designing robust macros
Logging Debug info

Importing and exporting data

Data exchange basics
Choosing which parts of Access to use
Other database applications
OCDB databases
Saving import and export procedures
Import options
Export basics
Exchanging data with other Access databases
Linking to Access tables
Importing data in Access
Exporting Access objects
Exchanging data with Excel
Importing from Excel
Linking to Excel data
Appending Excel data
Exporting data to Excel
Using Text and XML data files
Exporting to text files
Exporting to XML
Importing from text files
Importing from XML files

Database management

Preparing and running Compact & Repair
Running Compact & Repair automatically
Restoring a database
Other maintenance tasks
Encrypting a database
Creating an executable file
Documenting the database structure

Our outlines are a guide to the content covered on a typical class. We may change or alter the course topics to meet the objectives of a particular class.