MS Access Advanced

Duration: One Day

Interested in this course?

Complete the form and let us know your preferred training date. Our team will contact you to discuss your requirements and arrange a suitable course schedule.

Whether you're booking for yourself or a team, we'll work with you to find a convenient training date.

Prefer to get in touch directly? You can also email us at forrest@forresttraining.com.au, and we'll be happy to assist you.

    Target Audience & Purpose

    Access Advanced is for skilled developers who wish to add high-end sophistication, automation and security.

    Objectives

    By the end of this course participants should be able to:

    • Design & Create a Relational Database
    • Set Table Relationships
    • Import and Export Records
    • Create Multi-Table, Parameter & Calculation Queries
    • Create Aggregate & Action Queries
    • Modify Forms
    • Create & Use Macros
    • Create simple VBA procedures

    Course Inclusions

    • Comprehensive learning materials incl. exercise files
    • Post course telephone support
    • ‘Certificate of Attendance’ on completion of course
    • Light lunch plus morning & afternoon tea

    Course Prerequisites

    Participants should be skilled users of Access, having attended the Basics and Intermediate Access courses and should already be able to:

    • Create a database
    • Define the fields and properties for a table
    • Create table relationships
    • Create simple calculation and select queries
    • Create forms for Data Entry, Switchboard and Query-By-Form
    • Create and modify reports

    MS Access Advanced Course Content

    Download Exercise Files

    Relational Database Design
    Designing a Relational Database, Scoping the System, Determining the Inputs, Normalising a Database, First Normal Form, Second Normal Form, Third Normal Form, Database Indexing

    Creating a Relational Database
    Creating a new Relational Database File, Creating Lookup Tables, Defining a Primary Key, Saving and Closing a Table, Creating the Expense Type Table, Creating the Transactions Table, Creating the Details Table

    Setting Table Relationships
    Understanding Table Relationships, Understanding Lookup Relationships, Looking Up the Employees Table, Looking Up the Expense Types Table, Viewing Table Relationships, Understanding Table Joins, Editing the Employees Table Join, Editing the Expense Type Table Join, Creating a New Join, Creating a Relationships Report

    Importing and Exporting Records
    Exporting Records to Microsoft Excel, Exporting Records to dBase, Exporting Records to Paradox, Exporting Records to a Text File, Importing from Microsoft Excel, Importing from a Text File, Linking to an External Source

    Multi-Table Queries
    Understanding Relational Queries, Creating a Relational Query Design, Filtering a Relational Query, Filtering Related Fields, Adding More Tables and Fields, Utilising Hidden Fields

    Parameter Queries
    Creating a Parameter Query, Displaying all Records, Using Parameters to Display a Range, Using Parameters in Expressions

    Calculations in Queries
    Creating a Calculated Query, Formatting Calculated Fields, Summarising Data Using A Query, Changing the Grouping, Calculating with Dates, Using Criteria in Calculations, Concatenating String Fields

    Aggregation Queries
    Creating an Aggregation Query, Working with Aggregation Queries, Multiple Aggregations, Modifying Aggregation Headings, Aggregating Calculated Fields, Applying Criteria to Aggregations, Understanding Nested Queries, Creating an Assembly Query, Creating the Nesting Query

    Action Queries
    Creating a Make Table Query, Using a Make Table Query, Expressions and Update Queries, Preparing an Update Query, Running an Update Query, Updating Using Expressions, Running an Expression Based Update, Creating a Delete Query, Running a Delete Query, Creating an Append Query, Running an Append Query, Turning Action Messages Off

    Modifying Forms
    Understanding Form Design and Layout, Switching between Form Views, Selecting Form Objects, Working with a Control Stack, Changing Control Widths, Moving Controls on a form, Aligning Controls, Understanding Properties, Changing Label Captions, Adding an Unbound Control, Adding a Control Source, Formatting a Control, Checking the Current Tab Order, Changing the Tab Order, Inserting the Date into the Form Header

    Creating and Using Macros
    Understanding Macros and VBA, Creating a Macro, Running a Macro, Modifying an Existing Macro, Interacting with the User, Stepping through a Macro, Documenting Macros

    Macro Techniques
    Creating a Print Macro, Using Conditions to Enhance a Macro, Creating a Sequence of Conditions, Understanding the Versatility of MsgBox, Using the MsgBox Function, Reconfiguring a Message Box, Using the InputBox Function

    Macros On Forms
    Understanding Macros and Forms, Creating Navigation Macros, Accessing Event Macros, Creating Unassigned Buttons, Programming an Event, Running an Event Macro, Modifying an Event Macro, Setting Echo Off, Adding a Close Button, Creating a Search Macro, Running the Search Macro, Understanding the a Search Macro, Naming Macros, Referencing Macro Sheet Macros

    An Introduction to VBA
    Understanding VBA, Coding VBA for an Event, Running VBA Event Code, MsgBox and InputBox, Modifying an Existing Procedure, Understanding the Modified Code, Creating a Standard Module, Running the Code, Understanding the Loan Simulator Code, Creating a Button for the Simulator, Attaching the Simulator Code