Access Advanced

Design, build, and optimize production-ready Microsoft Access databases. This hands-on course takes you beyond the basics, covering advanced queries, relational data integrity, form and report design, and automation through macros and VBA, so you can deliver efficient, well-structured database solutions with confidence.

What You'll Learn

By the end of this course, you will be able to:

• Optimize database performance using indexes, validation rules, and efficient table design
• Write advanced queries using UNION, crosstab, and complex functions to extract meaningful data
• Build sophisticated forms with subforms, calculated totals, and dynamic control behavior
• Design polished, professional reports with grouping, numbering, multi-column layouts, and templates
• Visualize data by creating and embedding charts in forms and reports
• Automate repetitive tasks and user workflows using macros and event-driven logic
• Import, export, and link data across databases, spreadsheets, and text files
• Enforce referential integrity through foreign keys, cascade rules, and join strategies
• Maintain and document databases using built-in tools for compacting, backup, and dependency tracking

Requirements

• Working knowledge of Microsoft Access or a comparable relational database application
• Familiarity with basic database concepts such as tables, queries, and forms
• General comfort using Windows

Audience

• Database Administrators and Developers
• Business Analysts and Reporting Specialists
• Power Users and Office Professionals
• Anyone looking to move beyond basic Access usage and build robust database solutions

Topics

Advanced Table and Field Design

• Configuring Indexes to Improve Query and Sort Performance
• Enforcing Data Quality with Table-Level Validation Rules
• Working with Lookup Fields and Value Lists
• Storing and Managing Binary Data with the OLE Object Data Type

Advanced Queries

• Combining Result Sets with UNION Queries
• Summarizing Data Across Categories with Crosstab Queries
• Applying Logical, Date, Text, Conversion, and Aggregate Functions
• Identifying Duplicate and Unmatched Records

Form Design and Behavior

• Building and Configuring Subforms for Related Data
• Displaying Calculated Totals on Forms
• Opening Subforms in Separate Windows
• Setting Default Values and Controlling Form Behavior
• Switching and Customizing Form Views

Reports and Templates

• Numbering Records Within Groups
• Creating Multi-Column Report Layouts
• Building Reusable Report Templates
• Designing Custom Label Reports

Charts and Data Visualization

• Creating Charts from Table and Query Data
• Embedding Charts in Forms and Reports
• Editing Chart Appearance and Data Bindings

Macros and Automation

• Creating and Editing Basic Macros
• Using the Macro Wizard for Guided Automation
• Adding Conditional Logic to Macros
• Assigning Macros to Control and Form Events
• Running Macros in Multiple Ways
• Organizing Related Macros into Groups
• Automating Startup Behavior with AutoExec and AutoKeys
• Testing and Stepping Through Macros with Single Step Mode

Navigation and Application Design

• Building a Switchboard for User-Friendly Application Navigation
• Configuring Startup Options to Control Application Behavior

Data Import and Export

• Exporting Tables and Queries to External Formats
• Saving Reports as Snapshots or Exporting to Microsoft Word
• Importing Data from Other Access Databases
• Importing CSV Text Files and Excel Spreadsheets
• Linking to Tables in External Databases

Relational Model and Data Integrity

• Defining Foreign Keys and Enforcing Referential Integrity
• Applying Cascade Delete and Cascade Update Rules
• Understanding Inner, Left Outer, and Right Outer Joins
• Joining Tables Without Explicit Join Clauses
• Testing and Validating Integrity Constraints

Database Maintenance and Administration

• Compacting and Repairing Databases
• Backing Up and Restoring Database Files
• Documenting Database Objects with the Database Documenter
• Replicating Databases and Synchronizing Replicas
• Viewing and Analyzing Object Dependencies