Intermediate Microsoft Access for Microsoft 365
Intermediate Microsoft Access for Microsoft 365 is an intermediate course focused on forms and reports, covering intermediate queries, main forms and subforms, cascading combo boxes, grouped and totalled reports, macros and database maintenance. It suits people who already know Access basics and staff who look after databases, and you leave able to build a records system with forms and reports.
- Duration12 hours · 2 days
- FormatOnsite / live online
- Next roundOn request
- CertificateIncluded
Course overview
Once you can build tables, relationships and queries in Access, the next job is making the database easy for others to use and producing results that are ready to print. Forms created by a wizard rarely fit the work, you cannot enter data for several tables on one screen, pick lists do not filter by an earlier choice, reports do not group or total the way managers want, and the database file keeps growing and slowing down.
This course builds on Access basics. It starts with queries that feed forms and reports, then designing forms in Design View, using controls and calculated expressions, building main forms with subforms and cascading combo boxes, and creating search forms, menu pages and charts on forms. You then design reports that group, total and control page breaks, build subreports and mailing labels, link forms and reports with macros and keep the file performing well. It finishes with a workshop that builds a records system with forms and reports. (2 days, 6 hours per day, 12 hours in total, Intermediate level.)
What you’ll gain
- Build intermediate queries to use as the source of forms and reports
- Design forms in Design View and set form and control properties
- Use controls, calculated expressions and conditional formatting on forms
- Build main forms with subforms and combo boxes that filter by an earlier choice
- Create search forms, menu pages and charts on forms
- Design reports that group, total and control page breaks
- Use macros to open forms and reports by condition without writing code
- Use tools to analyse, repair and back up the database file
Who this course is for
- People who have completed Basic Microsoft Access or can already build tables and queries
- Administrative, HR and warehouse staff who maintain databases and regular reports
- Teachers, lecturers and students who want forms and reports that work in practice
- IT staff who need to improve existing Access files in their organisation
Prerequisites
- Able to create tables, set data types and primary keys
- Able to create relationships between tables
- Able to create select queries and totals queries
- Some experience creating forms and reports with the wizards
- A Windows laptop with Access from Microsoft 365 or Access 2024 (Access is not available on macOS)
Curriculum
Course Details
This is the 2026 edition of the Intermediate Microsoft Access 2016/2019 course. It runs for 2 days, 6 hours per day (12 hours in total, 09:00-16:00), as lectures with hands-on labs on a sample sales database, at Intermediate level. The content has been rewritten for Access in Microsoft 365 and Access 2024, using Modern Charts in place of the old charts and adding intermediate queries, conditional formatting, cascading combo boxes without VBA and subreports. It sits between Basic and Advanced Microsoft Access for Microsoft 365. Learners use their own or their company's Access for Windows and take home the course handbook, sample databases and the records system from the workshop.
Day 1 Forms That Are Easy to Use and Accurate
Section 1: Lab: Queries for Forms and Reports
- Crosstab queries to summarise data as a grid
- Find Duplicates and Find Unmatched to check repeated and orphaned data
- Queries that read values from a form instead of typed parameters
- Lab: prepare the queries for the sales system's forms and reports
Section 2: Lab: Designing Forms in Design View
- Form sections: Form Header, Detail and Form Footer
- Key properties: Record Source, Default View, Allow Additions and Data Entry
- Arranging controls with layouts, tab order and themes
- Lab: improve a wizard-built form so it is easier to use
Section 3: Lab: Controls and Calculations on Forms
- Combo boxes and list boxes from a Row Source, option groups and tab controls
- Calculated controls with expressions such as IIf, Nz and DLookup
- Conditional formatting to highlight values that need attention
- Lab: a product form that calculates totals and warns of low stock
Section 4: Lab: Main Forms, Subforms and Cascading Combo Boxes
- Linking a main form and subform with Link Master Fields and Link Child Fields
- Showing subform totals on the main form
- Combo boxes that filter by an earlier choice, without VBA
- Lab: an order form with line items and a total
Section 5: Lab: Search Forms and Menu Pages
- Search forms with combo boxes, and Filter by Form
- Command buttons that open a form at the selected record
- Navigation forms and setting the form that opens with the file
- Lab: a sales system menu with a customer search form
Section 6: Lab: Charts and Display on Forms
- Modern Charts on forms: choosing the chart type, axis and values
- Charts that change with the record on the main form
- Split forms, datasheet forms and zooming views on large screens
- Lab: a monthly sales dashboard on a form
Day 2 Reports, Macros and Database Maintenance
Section 7: Lab: Designing Reports in Design View
- Report sections: Report Header, Page Header, Detail and footers
- Layout View, Design View and report properties
- Page Setup, margins and Print Preview
- Lab: make a customer list report ready to print
Section 8: Lab: Grouping, Sorting and Calculating in Reports
- Group, Sort, and Total, with group headers and footers
- Subtotals, grand totals, Running Sum and line numbers
- Keep Together and Force New Page to control page breaks
- Lab: a sales report grouped by month and salesperson
Section 9: Lab: Subreports, Mailing Labels and Export
- Subreports to show related data in one report
- The Label Wizard for labels and envelopes
- Modern Charts in reports, and exporting to PDF or Excel
- Lab: a customer statement with order lines and shipping labels
Section 10: Lab: Macros for Forms and Reports
- The Macro Builder, and common actions and arguments
- Embedded macros on buttons: OpenForm and OpenReport with a Where Condition
- If and MessageBox to check values before opening a report
- Lab: a button that prints a report for the date range chosen on a form
Section 11: Lab: Keeping the File Fast and Secure
- Performance Analyzer, Table Analyzer and Database Documenter
- Compact and Repair, Compact on Close and Object Dependencies
- Backing up and encrypting the database with a password
- Lab: analyse, repair and back up a sample database
Section 12: Workshop: A Records System with Forms and Reports
- Workshop: data entry forms with a subform and cascading combo boxes
- A search form, menu page and macro buttons that open reports
- A monthly summary report with a chart, exported to PDF
- A file maintenance checklist and next steps towards the advanced level
Schedule & training options
For individuals — public rounds
No public rounds are open right now. Join the waiting list and we will contact you first when the next round opens, or ask us on LINE. Or call 02-570-8449 or 088-807-9770
For organisations — in-house / private
- Tailor the content to your team’s tools and projects
- Your dates, at your office or live online
- Quotation with tax ID for procurement
Instructors
Frequently asked questions
What are a main form and a subform in Access, and how do you create them?
A main form shows the main record, such as an order header, while a subform sits inside it to show related records, such as the products on that order, linked through Link Master Fields and Link Child Fields. The easiest way is to create the relationship between the two tables first, then use the Form Wizard to pick fields from both, or drag an existing form onto the main form in Design View. You can show the subform's total on the main form with a calculated control.
How do you make a combo box filter its list by an earlier choice in Access?
Traditionally you wrote VBA or a macro to requery the second control when the first value changed. Recent Access in Microsoft 365 adds Link Master Fields and Link Child Fields properties to combo boxes and list boxes on the Data tab of the property sheet, where you name the source control and the filter field, which must be in the dependent control's Row Source. On versions without these properties, the requery approach still works.
How do you group and total data in an Access report?
Open the report in Design View or Layout View and use Group, Sort, and Total to choose the grouping field, such as department or month. Access adds a group header and footer. Place a text box in the group footer with a formula such as =Sum([Amount]) to total each group, and the same formula in the report footer for the grand total. To start each group on a new page, set Force New Page.
What does Compact and Repair Database do in Access, and when should I use it?
When you delete data or objects in Access, the space in the file is not released straight away, so the file keeps growing and slows down. Compact and Repair Database reorganises the file, recovers free space and fixes minor errors. Run it from the Database Tools tab or turn on Compact on Close. Do it regularly, especially after deleting or importing large amounts of data, and always back up the file first.
Who is the Intermediate Microsoft Access for Microsoft 365 course for, and what should I know beforehand?
It suits people who have completed Basic Microsoft Access or can already build tables and queries, staff who maintain databases and regular reports, and anyone who builds forms and reports for others. You should be able to create tables, relationships and queries, and bring a Windows laptop with Access from Microsoft 365 or Access 2024. VBA is covered at the advanced level rather than here.
Can the Intermediate Microsoft Access for Microsoft 365 course be run in-house for our team?
Yes. It can be run in-house for your team, with examples adapted to your own work, such as the data entry forms and regular printed reports your team needs from the database you already use. You can request a quotation from this page, or contact us on LINE @itgenius or by phone on 02-570-8449.
Related articles
Articles are published in Thai.