Microsoft Access Training Courses

Access basics

• Familiarity with access screen
• Ribbon and toolbar explanations
• Prebuilt database template access and creation

Database Terms Planning

• An explanation of databases in general, database objects within access and terminology
• Plan your Database (Tables, Queries, Forms, Reports, Macros, Pages and Modules)
• Plan a database structure. (tables) (normalisation Techniques)
• Create a new database file

Working with Tables

• Design a Table, Conventions for entering Naming objects and fields (Design View)
• Create a table in datasheet view and edit field names
• Select data types and enter field descriptions
• Set a Primary Key
• Remove a Primary Key
• Define Field Properties (Default values, Input masks, Validation rules and format options)
• Insert, Delete, Move and Rename Fields (design view and datasheet view)
• Create a Table with a Wizard
• Using the ribbon to set field properties

Entering and Editing Data

• Accessing objects through the navigation pane
• Switch between object Tabs
• Switch Views in Access (design, layout and data entry)
• Entering and Editing Data (Records & Fields)
• Navigating the datasheet
• Working in Data Entry Mode
• Sorting Data, Finding & Replacing Data
• Hiding and Un hiding Fields
• Create multiple tables to normalise data

Relationships

• Relationships explained?
• Different types of relationships (one to many etc)
• Conditions needed for setting up a relationship
• Ensuring Referential Integrity
• Printing Relationships in a Report
• Insert Sub Datasheets into a table
• Change or remove Sub Datasheets from a table
• Expand and Contract Sub Datasheets
• Enter data in related tables

Queries & Filters

• Create a filter by selection
• Create a filter by form
• Use AND/ OR options
• Save a filter as a select query
• Run a Select Query
• Build a query from the create ribbon
• Creating a query using a wizard
• Build a query using the Query Design Window
• Adding fields to a query, choose which fields to show
• Removing fields from a query
• Sort data in a select query
• Enter Text, numeric, date, and logic criteria
• Enter sub string searches using wild Card Characters
• Use the BETWEEN, AND, OR, NOT, NULL criteria in your query
• Calculate a field
• Concatenate text
• Save and name a query
• Rename, edit or delete a query

Multi Table Queries

• Build a query across more than one table
• Joining Tables for a query (more relationships)
• Setting Join Properties - Inner and Outer Joins
• Find Unmatched records Query Wizard
• Find Duplicate records Query Wizard

Creating Forms

• Create a Form using the Auto Form tool Tabular/Datasheet/Pivot Form wizard
• Create a Form using the Form Wizard
• Create a form in design view
• Create a form in layout view
• Understand the views and switch between them
• Setting data source for a form
• Setting the data source using SQL source code
• The Field List
• Resizing Forms
• Control types
• Adding controls
• Resize and Move Controls
• Changing control properties
• Selecting Controls
• Formatting Controls
• Use the Format Painter and AutoFormat
• Set basic form properties
• Data Entry in Forms
• Navigation in data entry view
• Add, edit, delete a record in form view

Reports

• Standard Reports using Wizard
• Insert, Move, Size and delete controls
• Editing record source
• Page setup
• Print your report

Advanced Tables

• Input mask
• Applying validation
• Validation text
• Calculating a field
• Multiple primary keys
• Lookup fields
• Formatting a field
• Link to external data source

Recap and More on Relationships

• Why create relationships between tables?
• Different types of relationships
• Adding tables to the relationship window
• Creating, editing and deleting relationships
• Conditions for setting up a relationship
• Understanding Referential Integrity
• Using the Cascade Update option
• Using the Cascade Delete option
• Saving relationships

Advanced & Action Queries

• Creating Calculated fields
• Creating Parameter Queries
• Creating Summary Queries
• Creating a Crosstab Query
• Editing a Crosstab Query
• Use the Top Values option in a Query
• Create and use a Make Table Query
• Create and use an Append Query
• Create and use a Delete Query
• Create and use an Update Query

Advanced Forms

• Main Form and sub forms
• Exploring Layout View
• Split forms
• Using Checkboxes & toggle buttons
• Using combo boxes
• Adding Option group controls
• Adding Command button Controls
• Setting more form properties
• Calculation in a Form
• Concatenating Text Values
• Changing the data source options
• Setting the Tab Order
• Adding and Removing Filters (Selection and Forms)
• Defining Validation Rules
• Using advanced controls
• Template Forms

Advanced Reports

• Customise your Report
• Format the controls
• Using Expressions
• Adding Sub Reports
• Parameter reports
• Report Templates
• The Keep Together property
• Mailing Labels
• Grouping interval properties
• Group Headers/footers calculations
• Page setup
• Page breaks

Importing & Linking Data

• Importing from Access and Excel
• Linking to an external Access Database Table
• Linking to an Excel Workbook
• Renaming and Copying Tables
• Linked table manager
• Export to various formats
• Charts and Pivots
• Create a PivotChart / PivotTable
• Edit PivotTable options
• Formatting a chart
• Changing the chart type
• Parameter chart
• Chart based on single record

Macros

• An Overview
• Creating and Running Macros
• Various Macro Actions
• Applying Macros

Names Macros

• Using ^ + letter
• Using ^ + Number
• Function keys

Conditional Macros

• Msgbox
• Set value
• Using functions

Event Procedures

• On open/close
• On got/lost focus

Database Utilties

• Compact and repair
• Save as alternate formats
• Split database
• Package and distribute
• Password protect
• Application options (created database application)
• Compile code
• Make accde file
• Backup database
• Database Templates

Creating a Switchboard (User Interface)

• Design a switchboard
• Use Macros with Command Buttons
• Use Macros with Code
• Format switchboard forms
• Create a Splash Screen
• Use an autoexec Macro
• Customise a ribbon
• Customise the Quick access toolbar
• Start-up Menu

VBA

• Convert macro’s to code
• Sample subroutine
• Sample function
• Libraries
• Navigate the vba window
• Compiling

Macros

• Creating Macros
• Assigning Macros to Controls
• Group Macros
• Conditional Macros
• The AutoExec Macro
• The AutoKeys Macro

VBA

• An overview
• The Environment
• Working with Modules
• Converting Macros -DOCMD
• Events

Coding Structures

• Control Structures (If, ElseIf, SelectCase)
• Looping Structures (Do, For..Next)

Functions

• Purpose
• Creating and using Functions

Variables

• Option Explicit
• Variable Type
• Scoping a Variable
• Testing Variable Contents

User Interaction

• MsgBox
• InputBox

Debugging and Error Handling

• Breakpoints
• Stepping Code
• The Immediate Window
• Creating Error Traps

Data Access Objects (DAO)

• The Workspace Session
• Manipulating Recordsets
• Creating Queries
• Creating Tables

Active X Controls

• VBA Menus and Toolbars