Microsoft Access Advanced

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.

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

• 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

• 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

• 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

• 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

• 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