ACCESS ADVANCED 2013

Course Overview

This course is designed for people who have attended the Access Essentials course or have a comprehensive working knowledge of Access. They should be confident in designing tables, forms queries and reports.

Upon completion of the Advanced Access course students will be able to work with Select and Action queries, they will gain understanding of the impact of relationships including enforcing referential integrity, cascade and delete updates when creating and designing a relational database. They will be able to work confidently with properties and expressions in queries, calculations in forms, pivot table and pivot chart creation and macro design. This course also introduces the concept and use of VBA in database design.

Course Prerequisites

Microsoft Access Essentials Course 2010 or equivalent

Raising the Bar

1300 937 782

www.rtb.com.au

Designing Relational Databases

Designing a Relational Database
Scoping the Project
Determining the Fields
Database Normalisation
First Normal Form (1NF)
Second Normal Form (2NF)
Second Normal Form – Case Study
Third Normal Form (3NF)
Indexing a Database

Creating Relational Databases

Creating a New Database in Access
Creating Lookup Tables (Child)
Defining the Primary Key
Saving and Closing a Table
Creating Additional Tables in the Database

Defining Table Relationships

Understanding Table Relationships
Understanding Lookup Relationships
Looking Up the Employees Table
Looking Up the Expense Types Table
Viewing Table Relationships
Understanding Table Join Types
Changing a Table Join
Editing an Existing Table Join
Creating a New Table Join
Printing a Relationship Report

Importing and Exporting Records

Exporting Data to Microsoft Excel
Exporting Data to a Text File
Importing Data From Microsoft Excel
Importing Data From a Text File
Linking To an External Data Sources

Multiple Table Queries

Understanding Relational Queries
Creating a Multiple Table Query
Filtering a Multiple Table Query
Filtering Related Fields
Adding Additional Tables and Fields
Utilising Hidden Fields in a Query
Understanding Query Joins
Creating an Inner Join in a Query
Creating a Left Outer Join in a Query
Creating a Right Outer Join in a Query

Parameter Queries

Creating Parameter Queries
Displaying All Records in Parameter Queries
Using Parameters to Display a Range of Values
Using Parameters in Query Expressions
Utilising Wildcards in Parameter Queries

Calculations in Queries

Creating Calculated Fields
Formatting Calculated Fields
Summarising Data in Queries
Changing Data Grouping
Date Calculations
Use Filter Criteria in Calculations
Concatenating Fields

Aggregating Data in Queries

Creating an Aggregate Query
Working with Aggregate Queries
Multiple Aggregates in Queries
Modifying Aggregate Field Names
Aggregate Calculated Fields
Applying Criteria to Aggregate Calculations
Understanding Nested Queries (Sub Queries)
Creating an Assembly Query
Creating a Nesting Query

Action Queries

Make Table Query
Creating and Using a Make Table Query
Understanding Expressions in Update Queries
Preparing and Running an Update Query
Updating Fields Using Expressions
Expression-Based Update Queries
Creating a Delete Query
Running a Delete Query
Creating an Append Query to Copy Records
Running an Append Query
Suppressing Action Messages

Modifying Access Forms

Understanding Form Layout and Design
Switching Form Views
Selecting Form Objects and Controls
Working With a Control Stack
Setting Control Widths
Moving Controls on a Form
Aligning Controls on a Form
Understanding Form and Control Properties
Changing Captions on a Label
Adding an Unbound Control to a Form
Adding a Control Source Property
Formatting a Form Control
Setting the Form Tab Order
Changing the Form Tab Order
Inserting the Date into a Control

Do It Yourself Forms

Creating a Main Form Subform
Adjusting the Layout of a Form
Typing Into a Form Layout
Disabling and Hiding Fields
DIY Sub Forms
Removing SubForm Redundancy
Merging and Splitting Table Layout Cells
Adding an Easy Lookup Field (Combobox)
Adding Buttons to a Form Using a Wizard
Adjusting Form Properties

Creating and Running Macros

Macros vs VBA
Creating a Simple Macro
Running Macros
Modify an Existing Macro
Interaction With the Database User
Stepping Through Macros
Documenting Macros

Macro Techniques

Creating a Print Macro
Adding Conditions to Enhance a Macro
Creating a Sequence of Conditions
Understanding the Versatility of MsgBox (Message Box)
Using the MsgBox Function
Reconfiguring a MsgBox
Request Information Using an InputBox

Running Macros on Forms

Understanding Macros on Forms
Creating Navigation Macros
Linking Macros to an Event
Creating Unbound Buttons
Working with Events
Running a Macro via an Event
Modifying an Macro via an Event
Setting Echo Off Property
Adding a Close Button to a Form
Creating a Find Macro
Running the Find Macro
Understanding the Find Macro
Naming and Renaming Macros
Referencing Macro Sheet Macros

Maintaining a Database

Compacting and Repairing a Database
Understanding Database Splitting
Splitting a Database using the Wizard
Managing Linked Tables
Checking Database Object Dependencies
Analysing Database Performance
Documenting a Database

Introduction to VBA

Understanding VBA
Coding VBA via an Event
Running Event Driven VBA Code
Understanding MsgBox and InputBox in VBA
Modifying an Existing Sub Procedure
Understanding the Modified VBA Code
Creating a Standard Module in VBA
Loan Simulator VBA Code
Running the VBA Code
Understanding the Loan Simulator Code
Creating a Button for the Loan Simulator
Binding the Simulator Code