Database Management with Excel and Access
Database Management with Excel and Access is a practical course in using Excel and Access together, from table design and cleaning data with Power Query to building an Access database with queries, forms and reports, then analysing it back in Excel. It suits administrative and procurement staff, teachers and anyone outgrowing Excel lists, and you leave with a working asset register system.
- Duration18 hours · 3 days
- FormatOnsite / live online
- Next roundOn request
- CertificateIncluded
Course overview
Many organisations start keeping data in Excel because it is easy, but as the data grows and more people need it, they run into duplicates, inconsistent entries, several versions of the same file and reports that have to be compiled by hand every month. Database principles solve these problems: design the tables properly, keep the data in Excel tidy, and move work that involves several related tables into Access, which can check data as it is entered, offers forms for data entry and produces reports straight away.
This course shows learners how to use Excel and Access in Microsoft 365 together as one data workflow. It starts with relational database concepts and table design, organises data in Excel with Excel tables and data validation, and cleans it with Power Query. The data then moves into Access, where learners build tables, relationships and queries, including select, crosstab and action queries through SQL View, followed by forms, reports, modern charts and macros for command buttons. Finally they pull the data from Access back into Excel for analysis with PivotTables, and finish with a workshop that builds an asset register and loans system from start to finish. (3 days, 6 hours per day, 18 hours in total, Beginner level.)
What you’ll gain
- Understand relational database concepts and know when to use Excel and when to use Access
- Design tables and choose data types, primary keys and relationships correctly
- Prepare data in Excel with Excel tables, data validation and Power Query
- Create tables, relationships and validation rules in Access and import data from Excel
- Write queries to find, summarise and update large amounts of data, and read and edit basic SQL
- Build data entry forms, reports and charts, with command buttons driven by macros
- Pull Access data into Excel for analysis, and maintain and back up database files
Who this course is for
- Administrative, procurement, HR and sales staff who keep data in Excel and are starting to see duplicates or inconsistencies
- Teachers, lecturers and registry staff who manage data on students, equipment or documents
- School and university students who want to understand database principles with tools used in real offices
- Anyone who wants to build a small data system for their own team without writing code
Prerequisites
- Comfortable using a computer and Windows, such as managing files and folders
- Basic Excel skills such as entering and formatting data and writing simple formulas; no previous Access experience is needed
- Able to read some English on program screens
- A Windows laptop with Excel and Access from Microsoft 365 or Office 2024 (Access is not available on macOS)
Curriculum
Course Details
This is the 2026 edition of the Database Management with Microsoft Excel and Access course. It runs for 3 days, 6 hours per day (18 hours in total, 09:00-16:00), as lectures with labs built on a single sample asset register throughout, at Beginner level. The content has been rewritten for Excel and Access in Microsoft 365 as a single path from Excel to an Access database, adding Excel tables, data validation,
Power Query, XLOOKUP, the new SQL View, modern charts and pulling Access data into Excel for analysis. Unlike the Basic Excel and Basic Access courses, which each go deep into one program, this course shows how the two work together. Access runs on Windows only. Learners use their own or their company's software. They take home the course handbook, sample files with answers for every lab, and the database they build in the workshop.
Day 1 Thinking in Databases and Preparing Data in Excel
Section 1: What Is a Database? Choosing Excel or Access
- Databases, database management systems (DBMS) and relational databases
- Tables, records, fields, primary keys and foreign keys
- When Excel is enough, and when to move to Access or another database
- Meet SQL, the common language of every database system
- Lab: analyse an Excel file with duplicate and inconsistent data
Section 2: Lab: Designing Tables and Relationships
- Plan a database from real work: what to store and which reports are needed
- Split tables with a plain-language approach to normalisation
- Choose primary keys, composite keys and the data type of each column
- One-to-one, one-to-many and many-to-many relationships
- Lab: design the tables for an asset register and loans system on paper
Section 3: Lab: Organising Data in Excel
- One row per record, no merged cells and a single header row
- Organise data as Excel tables and use structured references
- Data validation and drop-down lists to prevent typing errors
- Sort, filter and highlight duplicates with conditional formatting
- Lab: tidy an Excel equipment list so it is ready to become a database
Section 4: Lab: Cleaning Data with Power Query
- Get data from Excel, CSV files and folders with Get Data
- Trim spaces, split columns, change data types and remove duplicate rows
- Combine files with Append and match tables with Merge
- Save the steps so they refresh whenever new data arrives
- Lab: clean raw data exported from another system before loading it into a database
Section 5: Lab: Finding and Summarising Data in Excel
- Look up data across tables with XLOOKUP
- Return filtered lists with FILTER, UNIQUE and SORT
- Conditional totals with SUMIFS and COUNTIFS
- Lab: summarise equipment by department with a PivotTable and slicers
Day 2 Building a Database and Queries in Access
Section 6: Lab: Getting Started with Access and Creating Tables
- The Access window, the Navigation Pane, the six object types and trusted locations
- Create tables in Design View and choose data types, including Date/Time Extended
- Field properties: Default Value, Input Mask, Validation Rule and Required
- Lookup fields for picking values from another table
- Lab: create tables for equipment, departments, borrowers and loans
Section 7: Lab: Relationships and Referential Integrity
- Create relationships in the Relationships window
- Referential integrity, cascade update and cascade delete
- Many-to-many relationships with a junction table
- Lab: relate every table in the system and test that invalid data is rejected
Section 8: Lab: Importing and Linking Data from Excel
- The difference between importing and linking, and when to use each
- Import cleaned data from Excel and save the import steps for reuse
- Append new data to existing tables and check rows that failed to import
- Lab: move the equipment data from Excel into the Access database
Section 9: Lab: Finding Data with Select Queries
- The Query Wizard and Query Design View
- Criteria with And, Or, Between, Like and wildcards
- Parameter queries that ask for a value each time they open
- Calculated fields and summaries with totals queries
- Lab: find overdue loans and summarise loans by month
Section 10: Lab: Advanced Queries and SQL View
- Crosstab queries for two-dimensional summaries
- Find Duplicates and Find Unmatched queries for checking data
- Action queries: Update, Append, Delete and Make Table, used safely
- Read and edit SQL in the new SQL View with colour coding and autocomplete
- Lab: update the status of a batch of equipment with an update query and check the result in SQL
Day 3 Forms, Reports and Working with Excel
Section 11: Lab: Data Entry Forms
- The Form Wizard, Layout View and Design View
- Combo boxes, subforms and setting the tab order
- Command buttons to save, search and open other forms
- A navigation form as the system's main menu
- Lab: a loans form with a subform listing the equipment borrowed
Section 12: Lab: Reports and Charts
- The Report Wizard, grouping, sorting and subtotals
- Refine reports in Layout View and Design View
- Insert modern charts into reports
- Export reports to PDF and Excel, and send data to a mail merge in Word
- Lab: an equipment report by department with a summary chart
Section 13: Lab: Macros for Repetitive Tasks
- Embedded macros on form buttons in Access
- A macro that opens the menu form when the file opens, and messages for users
- Record macros in Excel with the macro recorder and save as .xlsm
- Security for files with macros and Trust Center settings
- Lab: buttons to print a loan slip and open reports from the main menu
Section 14: Lab: Using Access Data in Excel and Maintaining Files
- Pull tables and queries from Access into Excel with Power Query
- Analyse with PivotTables and PivotCharts, and refresh when data changes
- Compact and Repair, backups, and a first look at splitting into front end and back end
- Cautions when several people use an Access file at once or it is kept in a cloud folder
- Lab: an Excel loans dashboard that pulls its data from Access
Section 15: Workshop: Asset Register and Loans System
- Review the table and relationship design against an organisation's brief
- Import data from Excel and build forms, queries and reports for the whole system
- Test with invalid entries and tighten the validation rules
- Workshop: present the system, review it together and wrap up with a go-live checklist
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 is a primary key, and why should every table have one?
A primary key is a field, or set of fields, whose value is unique for every record, so it identifies that record without doubt, such as an asset code or employee ID. It prevents duplicates and is the link to other tables through foreign keys. In Access you set it in Design View and can use an AutoNumber field if there is no existing code. In Excel you should also keep a unique ID column, which makes XLOOKUP and moving data into a database much easier.
How do you create a many-to-many relationship in a database?
A many-to-many relationship exists when a record in one table relates to many records in another, and the other way round, such as one borrower borrowing many items and one item being borrowed by many people over time. You create it with a junction table, such as a loans table, that holds the foreign keys of both tables, then create a one-to-many relationship from each table to the junction table and turn on referential integrity in the Relationships window.
What is a parameter query in Access, and how do you create one?
A parameter query asks the user for a criterion each time it opens, so one query can handle many searches, such as asking for a department or a date range. Open the query in Design View and type a prompt in square brackets in the Criteria row, for example [Enter department] or Between [Start date] And [End date]. When the query runs, a box appears for the value, and the query can be used as the source of a form or report.
Who is the Database Management with Excel and Access course for, and what should I know beforehand?
It suits administrative, procurement, HR and sales staff who keep data in Excel, teachers, lecturers and registry staff, students, and anyone who wants to build a small data system without writing code. You should be able to use Windows and basic Excel; no Access experience is needed. Bring a Windows laptop with Excel and Access from Microsoft 365 or Office 2024.
What will I be able to do after the Database Management with Excel and Access course?
You will be able to design tables and relationships correctly, organise and clean data in Excel with Excel tables, data validation and Power Query, build an Access database with validation rules, write queries, build forms, reports and macro-driven command buttons, and pull Access data into Excel for analysis, taking home an asset register and loans database to keep using. Participants who complete the training receive a Certificate of Completion with its own number, which can be verified online.
How is this course different from the basic Excel or Access courses?
The Basic Excel and Basic Access courses each go deep into one program. This course focuses on database principles and using both programs together as one system: preparing and cleaning data in Excel, storing it in a structured way in Access, and pulling it back into Excel for analysis. It suits people who need to turn hard-to-manage Excel files into a proper data system. VBA programming continues in the Advanced Microsoft Access course.
Related articles
Articles are published in Thai.