Microsoft Access

On-site and Off-site training *

Microsoft Access Half Day Querying Data

Pre-requisites

Delegates attending this course should be able to open and close a database and understand the purpose of Tables, Queries, Forms and Reports.

Course Objectives

By the end of the course users will be able to create, run and save queries; extract accurate data that matches criteria based on text, numbers, dates and expressions. Users will also be able to summarise data using totals and simple calculations.

Target Audience

Existing Microsoft Access users who need to interrogate and interpret data in Flat or Related tables within an existing database using a variety of criteria.

Course Content

The Theory Behind a Query

 Navigating Table Data

 Sorting & Filtering Data in a Table

 Showing & Hiding Table Columns

Essential Query Skills

 Adding, Moving & Removing Fields

 Showing & Hiding Query Fields

 Sorting Queries by One or More Fields

 Entering a Simple Criteria Value

Extracting Data using Query Techniques

 Using Multiple Criteria in a Query

 Using AND & OR Criteria

 Entering Wild Card Criteria

 Creating a Parameter Query

 Querying Data from Related Tables

 Summarising Results in a Query

 Creating a Simple Calculated Field

Query Wizards

 Creating a Select Query with the Wizard

 Find Unmatched Data Query Wizard

 Find Duplicate Query Wizard

 Crosstab Query Wizard

Microsoft Access Half Day Creating Reports

Pre-requisites

This course is designed for existing Microsoft Access users who have a sound understanding of databases. Users should be able to open, navigate and close tables and be able to create queries using a selection of criteria.

Course Objectives

By the end of the course users will be able to create reports in a variety of layouts and summarise data. Users will also be able to manage the structure of a report and understand the importance of the sections in a report to produce the required output.

Target Audience

This course is designed existing Access users who need to produce accurate reports for viewing on screen and printing.

Course Content

Reporting Essential Skills

 Creating Reports using the Wizard

 Creating Auto-Reports

 Switching Report Views

 Navigating Records in Layout View

 Previewing Reports in Print Preview

 Working with Sections in Design View

 Modifying ‘Tabular’ & ‘Stacked’ Layouts

 Aligning & Sizing Text Boxes & Labels

 Formatting Text & Controls

Creating Custom Reports

 Sorting & Grouping Report Data

 Forcing Page Breaks in Reports

 Creating Simple Calculated Controls

 Summarising Report Data with Calculations

 Showing & Hiding Report Sections

 Switching Data Sources

Microsoft Access Half Day Using a Database

Pre-requisites

Users must be proficient with a mouse and keyboard and be able to confidently work within a Windows environment – minimise, maximise, open, close.

Course Objectives

To understand the purpose of the four main database objects: Tables, Queries, Forms and Reports. Users will be able to open and close Access objects, navigate between the existing database records and update the data in the database.

Target Audience

Users who are required to view, find, add, edit and delete data in an existing database.

Course Content

Introducing Access Databases

 Opening & Closing a Database

 Opening Objects using the Navigation Pane

 Opening & Closing Tables, Queries, Forms & Reports

Working with Data in Tables

 Navigating Table Data

 Entering, Editing & Deleting Records

 Sorting & Filtering Data in a Table

 Showing & Hiding Columns

Working with Forms

 Navigating Records in Forms

 Finding Records

 Adding New Records

 Editing & Deleting Records

 Sorting & Filtering Data in a Form

Previewing Data in Reports

 Opening Reports in Report View

 Previewing & Printing Reports

Microsoft Access Start Building a Database (1)

Pre-requisites

Users must be proficient with a mouse and keyboard and be able to confidently work within a Windows environment – minimise, maximise, open, close. Familiarity of either Word or Excel is useful, but not essential.

Course Objectives

By the end of the course, users will be familiar with the concepts of a flat file database and be able to create a single-table database. They will be able to interrogate and present data using queries and reports and create simple Access Forms to view and input data on screen.

Target Audience

Users who need to design and create basic databases in Microsoft Access.

Course Content

Database Basics

 Introducing Databases

 The Access Objects

Navigation & Data Entry

 Opening a Table

 Navigating in Datasheet View

 Searching & Finding Records

 Adding & Deleting Records

Creating Databases & Defining Tables

 Creating & Saving a Database

 Creating a Table

 Inserting, Deleting & Moving a Field

 Changing the Data Type

 Modifying Field Properties

Lookups

 What is a Relational Database?

 About Lookups

 Working with Data in Tables

 Changing the Look of Data

 Sorting in Tables

 Filtering Data

Queries

 Creating & Running Queries

 Adding Criteria to Queries

Forms

 AutoForms & the Form Wizard

 Designing Forms

Reports

 Creating Reports using Report Wizard

 Report Sections

Printing in Access

 Printing from Tables & Queries

 Printing Forms & Reports

Microsoft Access Create Relational Databases (2)

Pre-requisites

Users should be able to create a simple flat file database in Access, including creating basic lookups. Users should also be able to interrogate and present data by creating simple queries, forms and reports.

Course Objectives

By the end of the course, users will be familiar with the concepts of a relational database and be able to create a multi-table database. They will be able to present data using queries and reports and create simple Access Forms to view and input data on screen. They will be able to utilise functionality such as Action and Summary queries and create calculations in queries, forms and reports.

Users will also be able to apply basic automation through the use of command buttons to make viewing, printing and closing forms and reports quick and easy.

Target Audience

Users who already work with Microsoft Access and need to develop more complex relational databases, by using more sophisticated querying tools and creating multi-table forms and reports.

Course Content

Relationships & Lookups

 Relationship Types

 Creating a Relationship

 Using Lookups

 Referential Integrity

Creating Tables, Importing, Exporting & Linking Data

 Exploring Successful Table Design

 Working with Field Properties

 Import & Export Access Data

 Import & Export Data from Other Sources

More Queries

 Parameter Queries

 Creating a Calculated Field in a Queries

 Use the Expression Builder to Create a Calculation

 Summarise Data in a Query

 More Forms & Reports

 Working with Form & Report Sections

 Working with Bound & Unbound Controls

 Creating a Calculated Control

 Exploring the Form Design Work Surface

 Multi-Table Forms

o Creating Main Forms & Subforms

o Creating Linked Forms

 Understanding Control Properties

 Changing Control Properties

 Applying Themes to Forms & Reports

 Customising Theme Colours & Fonts

 Using the Command Button Wizard

 Automating Forms with Command Buttons

Microsoft Access Automate Relational Databases (3)

Pre-requisites

Users must be able to confidently design and develop multiple table Access databases. Users will require a good understanding of relationships and the four core elements of Microsoft Access, i.e. tables, queries, forms and reports.

Course Objectives

By the end of the course the user will be able to develop databases which include automated features such as macros and command buttons and utilise complex queries, forms and reports using grouping tools and including calculations.

Target Audience

Users who need to develop databases which have complex structures or require an easy to use interface for a team or organisation to use.

Course Content

Advanced Query Features

 Find Unmatched Queries

 Find Duplicates Query

 Crosstab Queries

 Action Queries

Advanced Form Features

 Bound & Unbound Controls

  Creating a Calculated Control

  Toggle Buttons, Options & Check Boxes

  Option Groups

  List Boxes and Combo Boxes

  Page Tabs

 Advanced Report Features

 Multiple Table Reporting

 Sorting & Grouping Report Data

 Performing Calculations on Reports

Macros & Database Automation

 Creating a Macro

 Running a Macro

 Macro Examples

 AutoExec Macros

Database Management Tools

  Backing Up a Database

  Compacting & Repairing A Database

  Front End & Back End Databases

Microsoft Access VBA Fundamentals (4)

Pre-requisites

An advanced understanding of creating Access databases, including table and form design.

Course Objectives

At the end of the course users will be able to understand and create VBA code. You will be able to create procedures and functions and use VBA to manipulate data from tables and queries.

Target Audience

Users who need to use the added functionality of Access Visual Basic for Applications to build, maintain, manipulate and automate databases.

Course Content

Introducing VBA

 Exploring the Visual Basic Editor

 Running Code from the Immediate Window

Introducing Visual Basic Code

 Understanding Objects & Dot Syntax

 Creating & Organising Modules

 Commenting & Editing VBA Code

Writing VBA Code

 Understanding Procedures

 Organising Code using With…End With

 Writing Clear & Structured Code

 Calling Procedures from Different Locations

 Using the DoCmd Object to Control Access Interface Features

Variables, Constants & Scope

 Understanding & Naming Variables

 Declaring Variables & Data Types

 Declaring & Using Constants

 Assigning Values & Objects to Variables

 Performing Calculations in VBA Code Message Boxes

 Creating & Customising Message

Boxes Input Boxes

 Creating Input Boxes to Receive User Data

 Understanding & Customising Input Boxes

Conditional Control Structures

 Controlling Program Flow Using IF Structures

 Using Select Case Structures

Looping Control Structures

 Repeating Code Using Loops

 Definite Loops

 Indefinite Loops

Coding with Forms & Reports

 Writing Code for Forms & Reports

 Working with Controls

Error Handling & Debugging VBA

 Identifying Error Types

 Reducing & Debugging Errors

 Writing Code to Handle Errors