Course Description

This is an ideal course for users who already know how to develop simple databases and want to further enhance the development with charts, pivot tables, advanced forms and reports as well as automation.

Prerequisites

Participants should have some basic knowledge in using Access.

Course Outline

Day 1

Lesson 1: Aggregate Queries

• Create an Aggregate Query
• About Aggregate Functions
  • Group By
  • Sum, Avg, Count, StDev, Var
  • Min, Max, First, Last
  • Expression, Where

Lesson 2: Action Queries

• Why Use Action Queries?
• Make-Table Queries
• Delete Queries
• Append Queries
• Update Queries
• A Word on Updatable Datasets

Lesson 3: Crosstab Queries

• Using the Crosstab Query Wizard
• Create Crosstab Query Manually
• Customizing Your Crosstab Query

Lesson 4: Finding Duplicates & Unmatched Records Query

• Defining Duplicate Records
• Find Duplicate Records
• Removing Duplicate Records
• Find Unmatched Query
• Common Transformation Tasks
  • Filling in Blank Fields
  • Concatenating
  • Changing Case
  • Removing Leading & Trailing Spaces from a String
  • Finding and Replacing Specific Text
  • Adding Own Text in Key Position within a String
  • Parsing Strings Using Character Markers

Lesson 5: Conditional Analysis in Queries

• Using Parameter Queries
  • Ground Rules of Parameter Queries
  • Working with Parameter Queries
  • Working with Multiple Parameter Conditions
  • Combining Parameter with Operators
  • Combining Parameter with Wildcards
  • Using Parameters as Calculation Variables
  • Using Parameters as Function Arguments
  • Creating a Parameter Prompt that Accepts Multiple Entries
• Using Conditional Functions
  • The IIf Function
  • Using IIf to Avoid Mathematical Errors
  • Using IIf to Save Time
  • Nesting IIf Functions for Multiple Conditions
  • Using IIf Functions to Create Crosstab Analysis

Lesson 6: PivotTable and PivotChart

• The Anatomy of a PivotTable
• Creating a Basic PivotTable
• Creating an Advanced PivotTable with Details
• Saving Your PivotTable
• PivotTable Options
• Working with Pivot Charts in Access
• Formatting Your Pivot Chart

Day 2

Lesson 7: Macros

• Introduction to Macros
• Dealing with Access 2016 Security Features
• Understand Macros
• Triggering Macros
• Create the Macro
• Modify the Forms
• Run Macros

Lesson 8: Switchboard

• Create a Switchboard
• Add Items to a Switchboard
• Access Switchboard
• Edit Items in a Switchboard
• Delete a Switchboard or Switchboard Item
• Display Main Switchboard on Startup
• Access 2016 Navigation Form

Lesson 9: Managing Database

• Analyzing Performance
• Analyzing Tables
• Splitting a Database
• Encrypting a Database with Password
• Remove Encryption