Microsoft Access Training – Advanced

This advanced Microsoft Access training course will build on your existing skills to teach you advanced techniques for making the most of Access’s functionality.

Microsoft Access is a very popular and widely used database program enabling the user to manipulate data in a wide variety of ways. Data can be analysed and sorted, specific information can be searched for, and the results can be used to produce reports, lists and more.

Access is part of the Microsoft Office suite. We offer training on all versions of Microsoft Access – 2003, 2007, 2010 and 2013.

  • export records to and import records from a wide variety of sources and applications
  • create queries based on one or more tables
  • create calculated queries
  • create and use parameter queries
  • create and work with aggregation queries
  • modify and adapt an existing form according to specific needs
  • create and use macros in Access
  • work with a number of macro techniques
  • create and work with macros in forms
  • create some simple programming code using VBA
  • design a relational database project

A good working knowledge of Access is required; delegates should have an understanding of concepts covered in the Access Introduction/Intermediate course. These include creating and modifying table structures, creating a relational database, working with records, creating queries, forms and reports.

Our courses run from 9.30 am to approximately 4.30pm. While you are with us we are focused on offering you a relaxed and productive learning environment. There is no dress code and you do not need to bring anything with you. Please wear whatever you are comfortable in.
When you book a course with use you get the following:
  • A full colour advanced MS Access manual or book.
  • The exercises that you have worked on during your course to allow you to practice afterwards.
  • USB stick to take the exercises away (where relevant).
  • Freshly prepared lunch in our onsite cafeteria.
  • Refreshments though out the day.
  • Training in fully air conditioned facilities from an experienced Access instructor.
  • Free parking at our Guildford training centre.
This introductory session is a quick revision of all of the key concepts around importing and exporting data from Access databases to Excel and text files. We also cover using Access to interface with an external datasource.
The key concepts trained in this session include:
  • Exporting Records To Microsoft Excel
  • Exporting Records To A Text File
  • Importing From Microsoft Excel
  • Importing From A Text File
  • Linking To An External Data Source
Learning to run multi-table queries is a key skill when working with an database. This session covers the basics skills. It covers the basics of creating and filtering a multi-table query before looking at cross-tab queries.
The key concepted trained in the session include:
  • Understanding Relational Queries
  • Creating a Relational Query Design
  • Filtering a Relational Query
  • Filtering Related Fields
  • Adding More Tables and Fields
  • Utilising Hidden Fields
  • Understanding Query Joins
  • Creating an Inner Join
  • Creating a Left Outer Join
  • Creating a Right Outer Join
Working with calculated values in queries can be extremely useful when working with numerical data, saving the user from running a query and then needing to carry out subsequent data processing.
This session covers this useful skill in detail. It starts with creating a calculated field and works through increasingly advanced applications including calculated dates and using criteria in calculations.
The key topics trained include:
  • Creating A Calculated Field
  • Formatting Calculated Fields
  • Summarising Data Using A Query
  • Changing The Grouping
  • Calculating Dates
  • Using Criteria In Calculations
  • Concatenating String Fields
It is often the case that we want to ask the same question time and again (for example, the names of all customers in the last month) but the key details are different each time (the date range). A parameter query will allow you to do this very simply without having to create a new MS Access query each time.
A parameter query presents you with a dialogue box each time you run it. The dialogue box asks you to set the parameter for the query. This session covers parameter queries from simple queries through to using parameters in expressions at the more complex end.
The key topics trained include:
  • Creating A Parameter Query
  • Displaying All Records
  • Using Parameters To Display A Range
  • Using Parameters In Expressions
This section covers more advanced queries. It stars covering aggregation queries. These queries do not present each item of information but the aggregation of this. For example, if you would like to know your total sales for the last month that would be an aggregation query as it would calculate all sales for last month and then aggregate them.
This session coves aggregation queries in detail. It covers working with simple and multiple aggregations, for simple and calculated fields. It then moves to looks at using queries to modify your data and also look at working with expressions in queries.
The key topics trained include:
  • Creating An Aggregation Query
  • Working With Aggregation Queries
  • Multiple Aggregations
  • Modifying Aggregate Headings
  • Aggregating Calculated Fields
  • Applying Criteria To Aggregations
  • Understanding Nested Queries
  • Creating An Assembly Query
  • Creating The Nesting Query
This session looks at advanced form use in MS Access. It begins by looking at more advanced form design in Access, before looking at controls and tab orders in forms.
Forms are an essential way to make databases accessible to non-users by controlling and managing their access to the underlying tables.
The key skills that are trained include:
  • Understanding Form Design And Layout
  • Switching 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 Tab Order of a Form
  • Changing Tab Order
  • Inserting the Date and Time Into A Form Header
As with all Microsoft Office products, MS Access allows you to record macros to automate repetitive tasks. This session covers the basics of Access macros. So creating, saving, editing and running a macro.
For experienced macro users in Excel or Word this will be relatively familiar. For people who are not experience marco users this can be an eye-opener when they learn how simple it can be to automate tasks in an Office program.
The key skills trained in this session are:
  • Understanding Macros and VBA
  • Creating A Macro
  • Running A Macro
  • Modifying An Existing Macro
  • Interacting With The User
  • Stepping Through A Macro
  • Documenting Macros
Having learned the basics of macros in the previous session this session covers some more advanced techniques. It looks at using macros to print work, working with message boxes to interact with a macro and other ways to get the most from macros.
The key concepts trained are:
  • 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
The use of macros on user forms can be extremely powerful. It allows even non-technical users of Access databases to be able to access macros to automate repetitive tasks.
This session explains how this can be done, through event macros. It shows you want one is, how to create one and how to create a button to trigger a macro. It also walks delegates through how to create a search macro for MS Access.
The key topics trained are:
  • 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 Search Macro
  • Naming Macros
  • Referencing Macro Sheet Macros
This session will be run on if there is time. This session is an introduction to VBA for MS Access. Coding in VBA gives the user complete control over MS Access. It allows them to create custom functions and controls and so to completely customise MS Access for their purposes.
This session serves and an introduction and overview of MS Access. It explains what VBA is and how to access and run VBA code in MS Access. It then shows delegates how to attach VBA code to a button or form.
The key concepts trained are:
  • Understanding VBA
  • Coding VBA For An Event
  • Running VBA Event Code
  • Understanding MsgBox and InputBox
  • Modifying an Existing Procedure
  • Understanding the Modified Code
  • Creating a Standard Module
  • Loan Simulator Code
  • Running the Code
  • Understanding the Loan Simulator Code
  • Creating a Button for the Simulator
  • Attaching the Simulator Code
As with the VBA session in Appendix 1 above this session will only run if there is time. It is a more detailed session looking at database design and principally at database normalisation.
Normalisation is the process of organising and designing a database for maximum efficiency. At it’s core it’s aim is to ensure that no information is duplicated within the database and so ensure efficiency however it extends well beyond that.
The key concepts trained are:
  • Designing A Relational Database
  • Scoping The System
  • Determining The Inputs
  • Normalising A Database
  • First Normal Form (1NF)
  • Second Normal Form (2NF)
  • Second Normal Form – Case Study
  • Third Normal Form (3NF)
  • Database Indexing

Related courses