This course is designed to teach students intermediate level skills in Microsoft Access 2016. In this course, students will learn to design tables for improved accuracy in data entry by setting default values and restricting data entry, review the various options to share data with other applications including Word and Excel, learn about Action Queries to automate updating, appending and deleting table data, and review various advanced query techniques such as using Query Joins, Parameter queries, working with totals and Crosstab queries. Students will also learn how to automate processes with Macros, advanced form techniques using Conditional Formatting, Tab controls, Combo Box controls and Sub-Forms, and advanced report techniques using Sub-Reports, columns and inserting charts.
Instructor: Ed McCrae
Student data files are available under Downloads in the course dashboard.
What am I going to get from this course?
- Design Tables for Better Data Entry
- Share Data with Other Applications
- Work with Action Queries
- Advanced Query Techniques
- Automate Processes with Macros
- Advanced Forms
- Advanced Report Techniques
Prerequisites and Target Audience
What will students need to know or do before starting this course?
Access 2016 Introduction or equivalent experience.
Who should take this course? Who should not?
Students who want to take Access to the next level and improve their database design skills.
Module 1: Introduction
Student Data Files
Module 2: Designing Tables for Better Data Entry
Reviewing Table Design Principles
Setting Field Size, Formats, and Captions
Setting Default Values and Required Fields
Restricting Data Entry: Validation Rules
Restricting Data Entry: Input Masks
Module 3: Sharing Data with Other Applications
Exporting Tables and Queries to Excel
Importing Data from a .TXT File
Importing Data from Excel
Exporting Data to a .TXT File
Linking External Data Sources
Using Word Merge
Module 4: Working with Action Queries
What are Action Queries?
Creating an Update Query
Creating an Append Query
Creating a Delete Query
Creating a Make Table Query
Changing the Start Number of an AutoNumber Field
Module 5: Advanced Query Techniques
Creating Query Joins
Creating Find Unmatched and Find Duplicate Queries
Creating Parameter Queries
Using "Like" Keyword in Parameter Queries
Creating a Top X Query
Reviewing Calculated Query Fields
Summarizing Query Data with Totals
Inserting WHERE Statements in Summary Queries
Creating a Crosstab Query
Module 6: Automating Processes with Macros
Understanding Macro Basics
Creating Macros to Open Forms by Record
Validating Data Entry with Macros
Creating a Macro to Automate Data Entry
Advanced Data Entry Using Macros and DLOOKUP Functions
Module 7: Advanced Form Techniques
Using Conditional Formatting
Organizing Form Fields with Tab Controls
Creating a Combo Box Control
Creating an Option Group Control
Using a Subform to Show Data from a Related Table
Module 8: Advanced Report Techniques
Inserting a Chart on a Report
Showing Data in Columns
Inserting a Subreport
Configuring Reports with Parameter Queries
Module 9: Conclusion