Home / Non-Accredited / Microsoft Office & Computer Skills / Microsoft Access Advanced (2 days)
Quick Look Course Summary:Microsoft Access Advanced (2 days)
-
Length: 2 day(s)
-
Price (at your venue): 1 Person R 10,326 EX VAT 3 Person R 7,594 EX VAT 10 Person R 5,537 EX VAT
-
Certification Type:Non-Accredited
-
Locations & Venues: Off-site or in-house. We train in all major city centres throughout South Africa.
Get Free & personalised
Training Advice
Microsoft Access Advanced (2 days)
Your Access database becomes faster, safer and easy for the whole department to use – and its owner knows how to keep it that way.
Once an Access database becomes central to how a team works, it has to be fast, safe for several users at once and simple for non-specialists to use. Many departmental databases are none of these: slow queries, forms that let bad data in, reports rebuilt by hand and a single file on a shared drive that corrupts when two people open it. This two-day advanced Microsoft Access course takes experienced users beyond the basics.
Day one covers advanced table design and data integrity, action, crosstab, union and SQL queries, and advanced forms and reports with subreports, calculated controls and criteria forms. Day two automates routine work with standalone, embedded and data macros, and turns the database into a finished application: navigation forms, startup options, linked data from Excel, SharePoint and SQL Server, splitting into a front end and back end, encryption, ACCDE distribution and a customised Access environment.
The course is taught on Microsoft 365 / Access 2021 and every topic is practised hands-on. Delegates finish by securing, splitting and distributing a multi-user database. Microsoft Access Essentials to Intermediate, or equivalent experience, is recommended first.
Why this course
For your organisation
- Departmental databases that are faster, safer and easier to use
- Fewer data errors through validation, data macros and controlled forms
- Multi-user databases that stop corrupting on the shared drive
- Less dependence on the one person who understands how the database works
For your delegates
- Queries that update, summarise and compare data in one step
- Forms and reports that guide users and present results properly
- Macros that automate routine steps without programming
- The know-how to split, secure and distribute a database
What delegates will be able to do
- Design tables for integrity with junction tables, indexes, validation and calculated fields
- Build make-table, append, update, delete, crosstab, union and duplicate-checking queries
- Read and write simple SQL in SQL view
- Create advanced forms with tab controls, option groups, filtering combo boxes and calculated controls
- Produce reports with subreports, running sums, grouped totals, charts and criteria forms
- Automate tasks with standalone, embedded, AutoExec and data macros
- Link and import data from Excel, text files, SharePoint lists and SQL Server
- Split, encrypt and distribute a database as an ACCDE file for several users
- Set startup options and customise the Navigation Pane and Ribbon for end users
Who should attend
Experienced Microsoft Access users, database owners, analysts and power users who build or look after departmental databases and need them to be faster, more secure, multi-user and easy for others to use. Delegates should have attended Microsoft Access Essentials to Intermediate or be comfortable building tables, queries, forms and reports.
Course outline
Day 1 – Advanced design, queries, forms and reports
Advanced table design and data integrity
- Review of good design and normalisation
- Many-to-many relationships with junction tables
- Single-field and multi-field indexes
- Table-level validation rules and calculated fields
- Lookup fields: when they help and when they cause problems
Action and special-purpose queries
- Make-table, append, update and delete queries
- Protecting data before running an action query
- Crosstab queries for summary reports
- Find Duplicates and Find Unmatched queries
Advanced query techniques
- Inner, left and right outer joins
- Functions in queries: IIf, Nz, DateDiff, Format and DLookup
- Working in SQL view: SELECT, WHERE, ORDER BY and GROUP BY
- Union queries and subqueries
- Keeping queries fast on larger tables
Advanced forms
- Tab controls, option groups and calculated controls
- Split forms, pop-up and modal forms
- Conditional formatting and form properties
- Combo boxes that filter other controls
- Introduction to form and control events
Advanced reports
- Subreports and running sums
- Group headers and footers with calculated totals
- Expressions and conditional formatting in reports
- Charts in reports
- Reports driven by a criteria form
Day 2 – Automation, linked data and a secure multi-user database
Automating with macros
- The Macro Builder: actions and arguments
- Standalone, embedded and AutoExec macros
- Data macros: Before Change and After Insert events
- Validating data and handling errors in macros
- Converting macros to VBA: a first look
A user-friendly database
- Navigation forms as a front menu
- Custom categories and groups in the Navigation Pane
- Startup options: display form, application title and icon
- Hiding the Navigation Pane and limiting what users can change
Importing, exporting and linking data
- Importing and exporting with Excel, text and XML
- Linked tables from Excel, SharePoint lists and SQL Server (ODBC)
- Saved import and export specifications
- Word mail merge and Excel analysis from Access data
- The Linked Table Manager
Database management and security
- Splitting a database into front end and back end for multi-user use
- Setting a password and encrypting the database
- Distributing the front end as an ACCDE file
- Trusted locations and macro security
- Compact and Repair, backups, Database Documenter and Performance Analyzer
Customising Access
- Changing default program options
- Customising the Ribbon and the Quick Access Toolbar
- Final exercise: securing, splitting and distributing a multi-user database
- Course wrap-up
How it is delivered
Two days, 08:30-16:00. In person at your offices anywhere in South Africa, at a BOTI venue, or live online. One delegate or a group of up to 20, each at their own Windows computer with Microsoft Access; laptops with the software can be supplied on request. Workbook and practice databases included. We deliver it at your offices in Johannesburg, Pretoria, Durban, Cape Town and anywhere else in South Africa, at a BOTI venue in Johannesburg, or live online for teams in different places.
What our clients say
“The facilitator effectively encouraged participation and used practical examples, discussions and interactive activities to ensure that the concepts were understood and could be applied in the workplace.”
“Business Optimization Training Institute facilitated Microsoft Excel Training for our company. The course was efficiently scheduled and planned around our timetable. We were impressed by the quality of training.”
“The training was very informative and practical, leaving our employees feeling confident in their ability to leverage Excel more effectively.”
Frequently asked questions
Can you run this course at our offices?
Yes. Most groups are trained at the client's premises anywhere in South Africa; we can also host it at a BOTI venue or deliver it live online.
How many people can attend?
From one delegate to groups of 20, each at their own computer. The price per delegate drops as the group grows – see the price table, or ask us for a quote.
Is it accredited?
No – it is a non-accredited short course and delegates receive a BOTI certificate of attendance.
What should delegates know before attending?
They should be comfortable building tables, relationships, select queries, forms and reports. If not, start with Microsoft Access Beginners (1 day).
Does the course cover VBA?
It shows how to convert a macro to VBA so delegates can see the code. Full VBA programming is not covered; the focus is on macros, which handle most departmental automation.
Which version of Access do you teach?
Microsoft 365 / Access 2021. Most topics also apply to Access 2016 and 2019. Access runs on Windows only.
Related courses
Get your price – the more delegates, the less each pays
Change the options to see your price per person, then email yourself the quote and course outline.
Contact us now – we reply within 15 minutes during office hours
Monday to Friday, 08:00–17:00. After hours, we reply on the next working day – or call 011 882 8853.