This course is designed for people who already have a good understanding of Microsoft Access, and need to upgrade their skills to creating secure, multi-user systems.   This course assumes a thorough knowledge of database design, tables, queries, forms & reports.

The skills and knowledge covered in this course build on the skills covered in the ‘Fast Track to Microsoft Access 2003 ’ course – and upon completion the student should be able to effectively design a secured, multi-user database.

At the completion of the formal sessions a workshop is conducted where the students will be encouraged to use their own database model / ideas.

* Create effective, formula driven validation (on table    data)
* Create a split database system, and describe the    advantages gained by using Split Model
* Normalise a database to ensure optimum    performance
* Use Action queries to manage datasets
* Analyse data using Aggregate & Cross Tab Queries
* Create Queries to locate Orphan Records
* Understand the difference between Inner/Left/Right    Joins, Union Queries & Cartesian Products
* Be Familiar with Access SQL Syntax
* Design and Deploy Pivot Tables / Charts within    Access
* Be able to Determine an appropriate security/logon    regime and understand the differing models that
   may be applied
* Use basic Access VBA commands with Access Forms    & Reports
* Design and Modify Complex Forms, Reports
* Build a Report Generator, forming part of a larger    Access GUI
* Understand the limits of Access and how to upsize to    a Server Model

Unit 1: Data Validation

* Using Expressions to Validate Data
* Limitations of Data Validation

Unit 2: Normalising A Database

* First Normal Form
* Second Normal Form
* Third Normal Form
* Bryce-Codd Normalisation
* Breaking Normalisation Rules

Unit 3: Splitting A Database

* Why Split a Database?
* Using the Database Splitter
* Linking Tables Manually
* Updating Links
* Using Multiple Linked Tables

Unit 4: Action Queries

* Make Table Queries
* Append Queries
* Update Queries
* Delete Queries

Unit 5: Aggregate Queries

* Grouping Data
* Analysing Data
     - Sum & Average
     - Count, Min, Max

Unit 6: Cross tab Queries And Pivot Tables

* Analysis Periodic Data Using Pivot Tables
* Using Pivot Tables
* Using Pivot Charts

Unit 7: Table And Query Relationships

* Cascading Updates & Deletes
* Searching for Orphan Records
* Join Types
     - Inner Joins
     - Left & Right Joins
     - Union
* Understanding Access SQL

Unit 8: Database Security

* Database Passwords
* User Level Security
* Creating a Custom Login Page

Unit 9: Introducing Access VBA

* VBA Fundamentals
     - Msgbox & InputBox
     - IF Statement
     - Reacting to User Input

Unit 10: Advanced Form Design

* GUI Design
     - Developing a Standard GUI
     - Screen Size / Resolution Issues
* Applying & Clearing Form Filters
* Modifying Where Clause’s
     - AND & OR
     - Including String Delimiters
* Cascading Combo Boxes
* Validating Form Data

Unit 11: Building A Reporting System

* Passing Parameters
     - Via Forms
     - Via Objects
* Parameter Fields
     - Combo Boxes
     - Text Boxes
     - Check Boxes
     - Option Groups

Unit 12: Advanced Reporting System

* Hiding Sections
* 'On NoData’ Event
* Report Formatting Techniques
     - Shading Alternate Lines
     - Custom Numbering Techniques

Unit 13: Extending Beyond Access

* Access ADP Format
* Moving to a Server Model
     - SQL Server 2005
     - MySQL
* Using XML Data within Access
* Using Access Data on the web