Microsoft Access 2010: Specialised

Course Overview

The skills and knowledge acquired in Microsoft Access 2010 Specialised are sufficient to be able to use and operate the software at an efficient level.

On completion of the Microsoft training course, Microsoft users will be well equipped to use Access

Related Courses

• Microsoft Access 2010: Introduction for Beginners
• Microsoft Access 2010: Advanced Level 3
• Microsoft Access 2010: Intermediate
• Microsoft Access 2010: Fundamentals
• Microsoft Access 2007/2010: Advanced
• Microsoft Access 2010: Intermediate
• Microsoft Access 2010: Advanced
• Microsoft Access 2007/2010: Advanced Skills
• Microsoft Access 2007/2010: Introduction for Beginners
• Microsoft Access 2007 2010: VBA Programming

Audience

Microsoft Access 2010 Specialised is designed for users who are keen to extend their understanding and knowledge of the software.

Skills Gained

At the completion of Microsoft Access 2010 Specialised you should be able to:

• create select queries for a relational database
• add calculated fields to queries
• add parameters to queries to capture criteria data
• create aggregated queries
• create a form for data entry or data viewing in Access
• produce reports with calculations
• import data into an Access table from a variety of sources
• export records from Access
• export records to HTML for use on the Web
• understand table and query relationships and how they affect the integrity of your data
• create a chart on a form
• create a data access page
• create data access pages using the Design View
• create simple macros
• use conditions in macros
• create macros that used with forms
• create a Crosstab query to analyse data in a table or query
• create action queries to make tables, and update, append, and delete records
• protect your database files against unauthorised access
• completely secure a database file and its objects so that unauthorised access is prevented
• write a simple procedure in VBA
• create sophisticated forms with tab controls and sub-forms
• create a macro for an option group to provide instant filtering
• use the Switchboard Manager to create a menu system
• split the tables from a database file
• create and maintain multiple copies of a database file

Prerequisites

Microsoft Access 2010 Specialised assumes the delegate has completed the Access 2010 Fundamentals course or has equivalent knowledge.

Course Outline

Relational Select Queries

• Creating AND Queries
• Clearing The Criteria From A Query
• Selecting Numeric Data
• Selecting Dates In A Query
• Creating OR Queries
• Using A Range Expression
• Selecting Opposite Values
• Using Wildcards
• Problem Selects
• Adding Criteria Progressively
• Deleting Criteria Progressively

Calculated Fields

• Creating A Calculated Field
• Formatting Calculations
• Calculating Across Two Tables
• Calculating Dates
• Using Criteria In Calculations
• Concatenating String Fields

Parameter Queries

• Creating A Parameter Query
• Displaying All Records
• Using Parameters To Display A Record Range
• Nominating Specific Data Types
• Using Parameters In Expressions

Aggregate Queries

• Creating An Aggregate Query
• Working With Aggregate Queries
• Creating Queries With Multiple Aggregates
• Modifying Aggregate Headings
• Aggregating Calculated Fields
• Applying Criteria To Aggregates
• Creating A Query To Nest
• Creating An Aggregate From A Query

Forms

• Forms - How they Work
• Creating A New Form Using The Form Wizard
• Working With A Form
• Understanding Form Objects
• Modifying Field Widths In A Form
• Moving Fields On A Form
• Adding A Label To A Form
• Changing The Properties of A Form Object
• Adding A Picture To A Form
• Changing The Background Of A Form
• Changing A Field To A Combo Box
• Using A Combo Box Control On A Form
• Creating A Form From A Query

Calculated Reports

• Creating A Report Design
• Laying Out A Report Design
• Creating Totals Using SUM
• Creating Groups In A Report
• Creating Report Subtotals
• Formatting & Aligning Objects
• Adding Labels To A Design
• Formatting Labels
• Creating A Summary Report

Importing Records

• Importing From A Spreadsheet
• Importing From A Text File
• Backing Up A Table
• Importing And Key Violations
• Deleting An Unwanted Table
• Data Integrity And Importing

Exporting Records

• Copying A Table
• Exporting A Table To Microsoft Excel
• Exporting A Table To A Text File
• Exporting A Filtered Table
• Exporting Selected Records
• Creating An Export Query
• Exporting From A Query

Exporting To HTML

• Exporting A Table To HTML
• Viewing An HTML Document
• Exporting A Report To HTML
• Creating Hyperlinks

Table & Query Relationships

• Understanding Join Types
• Using Default Joins
• Orphan Records
• Viewing Orphan Records
• Deleting Orphan Records
• Creating An Exception Query
• Viewing A Query In SQL

Charting

• Creating A Chart Using A Wizard
• Modifying Chart Size
• Changing Chart Fonts
• Changing Chart Backgrounds
• Updating Chart Data

Data Access Pages

• Creating A Data Access Page
• Applying Themes To Data Access Pages
• Working With Data Access Pages
• Using A Data Access Page Outside Access

Data Access Page Techniques

• Creating A Data Access Page Design
• Promoting And Demoting Fields
• Working With Data Access Page Fields
• Displaying Related Records
• Formatting A Pivot Table List
• Adding Columns To A Pivot Table List
• Creating A Scrolling Marquee
• Formatting A Page

Macro Basics

• Macros Versus VBA
• Creating A Macro
• Running A Macro
• Modifying An Existing Macro
• Interacting With The User
• Documenting Macros

Macro Techniques

• Creating A Print Macro
• Using Conditions To Enhance A Macro
• Creating A Sequence Of Conditions
• Using The MsgBox Action Statement
• The MsgBox Function
• Using MsgBox In A Condition
• Reconfiguring A Message Box
• Using The InputBox Function

Macros On Forms

• Creating An Event Macro
• Creating A Group Macro
• Attaching Macros From A Group
• Creating A Group Macro For Forms
• Attaching Macros To Form Events
• Setting Form Properties
• Testing Form Property Changes

Crosstab Queries

• Crosstab Query Theory
• Using The Crosstab Query Wizard
• Modifying A Crosstab Query
• Formatting Crosstab Query Values
• Creating A Query As A Data Source
• Basing A Crosstab Query On A Query
• Using Criteria In Crosstab Queries
• Adding Parameters To A Crosstab Query

Action Queries

• Creating A Make Table Query
• Viewing A Table Created By A Make Table Query
• Expressions & Update Queries
• Creating An Update Query
• Running An Update Query
• Creating A Saved Update Query
• Running A Saved Query
• Creating A Delete Query
• Creating An Append Query

Database Protection

• Setting A Password
• Opening A File With A Password
• Removing A Password
• Encoding A Database File
• Decoding A Database File
• Hiding Database Objects
• Viewing And Unhiding An Object

Database Security

• Understanding Database Security
• Creating A Workgroup
• Creating An Administrator
• Removing The Default Admin User
• Logging On As The System Administrator
• Securing A Database
• Adding Groups
• Adding Users
• Assigning Permissions
• Testing Database Security
• Joining Another Workgroup

VBA Introduction

• Introductory VBA Terminology
• Writing A Simple Form Procedure
• Examining A Simple Sub Procedure
• The MSGBOX And INPUTBOX Functions
• Editing An Existing Procedure
• Creating A Standard Module
• Project - Loan Simulator Code
• Testing The Code
• Modifying A Module
• Create A Button On A Form
• Running A Procedure From A Control
• Navigating The VBA Editor

Display Controls

• Controls For Forms
• Control Properties
• Creating A Blank Form
• Adding Unbound Controls
• Attaching Tables & Fields
• Renaming Control Text Boxes
• Naming Label Controls
• Adding A Tab Control
• Adding A Subform
• Enhancing A Subform
• Understanding Calculated Controls
• Adding Calculated Controls To Subforms
• Working With Calculated Controls
• Adding Another Tab Page

Option Groups And Macros

• Testing A Sample Option Button Macro
• Creating An Option Group
• Copying Toggle Buttons
• Option Group Properties
• The ApplyFilter Macro Action
• Creating A Filter Macro
• Using The ApplyFilter Macro Action
• Handling Exceptions
• Attaching A Macro To An Option Group

Using The Switchboard

• Creating A Switchboard
• Creating Subsidiary Menus
• Creating A Menu Item
• Returning To Another Menu
• Creating A Number Of Menu Items
• Linking The Menus
• Modifying The Menu Forms
• Changing The Startup

Splitting A Database

• Using The Database Splitter
• Working With A Split Database
• Updating Database Links

Database Replication

• Understanding Database Replication
• Creating A Replica
• Creating Additional Replicas
• Creating A Partial Replica
• Synchronising To A Replica
• Synchronising From A Replica
• Synchronising To A Partial Replica
• Concluding Remarks