Course Description
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.
Curriculum
Module 1: Introduction
00:52
Resource 1
Student Data Files
Module 2: Designing Tables for Better Data Entry
20:30
Lecture 2
Reviewing Table Design Principles
03:39
Lecture 3
Setting Field Size, Formats, and Captions
05:33
Lecture 4
Setting Default Values and Required Fields
02:58
Lecture 5
Restricting Data Entry: Validation Rules
02:06
Lecture 6
Restricting Data Entry: Input Masks
06:14
Module 3: Sharing Data with Other Applications
13:49
Lecture 7
Exporting Tables and Queries to Excel
02:49
Lecture 8
Importing Data from a .TXT File
02:51
Lecture 9
Importing Data from Excel
02:12
Lecture 10
Exporting Data to a .TXT File
01:31
Lecture 11
Linking External Data Sources
02:43
Lecture 12
Using Word Merge
01:43
Module 4: Working with Action Queries
13:47
Lecture 13
What are Action Queries?
01:50
Lecture 14
Creating an Update Query
02:27
Lecture 15
Creating an Append Query
02:55
Lecture 16
Creating a Delete Query
01:47
Lecture 17
Creating a Make Table Query
01:39
Lecture 18
Changing the Start Number of an AutoNumber Field
03:09
Module 5: Advanced Query Techniques
24:13
Lecture 19
Creating Query Joins
05:13
Lecture 20
Creating Find Unmatched and Find Duplicate Queries
02:46
Lecture 21
Creating Parameter Queries
03:46
Lecture 22
Using "Like" Keyword in Parameter Queries
02:13
Lecture 23
Creating a Top X Query
01:22
Lecture 24
Reviewing Calculated Query Fields
02:43
Lecture 25
Summarizing Query Data with Totals
02:23
Lecture 26
Inserting WHERE Statements in Summary Queries
01:02
Lecture 27
Creating a Crosstab Query
02:45
Module 6: Automating Processes with Macros
23:32
Lecture 28
Understanding Macro Basics
05:44
Lecture 29
Creating Macros to Open Forms by Record
04:05
Lecture 30
Validating Data Entry with Macros
04:25
Lecture 31
Creating a Macro to Automate Data Entry
03:31
Lecture 32
Advanced Data Entry Using Macros and DLOOKUP Functions
05:47
Module 7: Advanced Form Techniques
12:32
Lecture 33
Using Conditional Formatting
01:33
Lecture 34
Organizing Form Fields with Tab Controls
03:17
Lecture 35
Creating a Combo Box Control
02:06
Lecture 36
Creating an Option Group Control
02:48
Lecture 37
Using a Subform to Show Data from a Related Table
02:48
Module 8: Advanced Report Techniques
12:32
Lecture 38
Inserting a Chart on a Report
03:17
Lecture 39
Showing Data in Columns
01:37
Lecture 40
Inserting a Subreport
02:11
Lecture 41
Configuring Reports with Parameter Queries
03:49
Lecture 42
Sending Reports
01:38
Module 9: Conclusion
00:55
Lecture 43
Course Recap
00:55
