Course Overview
This course will teach students how to design a relational database, join tables, use data
validation, create advanced queries, organize a database for efficiency, and use advanced
reporting techniques.
Course Introduction 4m
Course Introduction
Chapter 01 – Designing a Relational Database 35m
Topic A: Relational Database Design
Database Relationship
Relational Database Design Process
Statement of Purpose
Existing Data
Business Rules
Primary Keys
Primary and Foreign Keys
Composite Keys
One-to-One Relationships
One-to-Many Relationships
Relationships Window
Guidelines for Designing Databases
Demo – Exploring Table Relationships
Topic B: Create a Table
Table Views
List of Data Types
Calendar for Picking Dates
Lookup Wizard
The Table Properties Dialog Box
Demo – Creating a Table
Topic C: Create Table Relationships
Edit Relationships Dialog Box
Enforce Referential Integrity Options
Join Lines
Relationship Reports
Guidelines for Enforcing Referential Integrity
Demo – Creating a Table Relationship
Chapter 01 Review
Chapter 02 – Joining Tables 24m
Topic A: Create Query Joins
Query Joins
Inner Joins
Left and Right Outer Joins
Join Properties Dialog Box
What the Join Options Return
An Unrelated Table Join
Demo – Creating Inner and Outer Joins
Demo – Joining Unrelated Tables
Topic B: Relate Data Within a Table
Self Joins
Demo – Creating a Self Join
Topic C: Work with Subdatasheets
Subdatasheets
Demo – Modifying Data in a Subdatasheet
Chapter 02 Review
Chapter 03 – Using Data Validation 31m
Topic A: Use Field Validation
Field Validation
Field Properties
Input Masks
Input Mask Wizard
Demo – Validating Data by Using a Field Property
Demo – Validating Data by Using an Input Mask
Topic B: Use Form and Record Validation
The Combo Box Wizard
List Box Control
Option Group Control
Demo – Using a Combo Box Control to Limit Option Values
Demo – Using a Record Validation Rule
Chapter 03 Review
Chapter 04 – Creating Advanced Queries 37m
Topic A: Create Parameter Queries
A Parameter Query Displays a Prompt
Demo – Creating a Parameter Query
Demo – Using Wildcards in a Parameter Query
Demo – Creating a Query with Multiple Parameters
Topic B: Summarize Data
Crosstab Query Wizard
Analyzing Customer Payments
Demo – Creating a Crosstab Query
Topic C: Create Subqueries
Subquery Example
Subquery Expression Example
Demo – Creating Subqueries
Topic D: Create Action Queries
Action Query Update Expression
Demo – Creating Action Queries
Topic E: Create Unmatched and Duplicate Queries
Find Unmatched Query Wizard
Find Duplicates Query Wizard
Demo – Creating Queries to Search for Unmatched and Duplicate Records
Chapter 04 Review
Chapter 05 – Organizing a Database for Efficiency 20m
Topic A: Data Normalization
First Normal Form
Second Normal Form
Third Normal Form
Denormalization
Table Analyzer Wizard
Demo – Running the Table Analyzer Wizard
Topic B: Create a Junction Table
Junction Table
Demo – Using a Junction Table to Resolve a Many-to-Many Relationship
Topic C: Improve Table Structure
Demo – Improving Table Structure
Chapter 05 Review
Chapter 06 – Using Advanced Reporting Techniques 18m
Topic A: Include Control Formatting in a Report
Data Bars
Demo – Adding Data Bars to a Report
Topic B: Add a Calculated Field to a Report
Demo – Adding a Calculated Field to a Report
Topic C: Add a Subreport to an Existing Report
Demo – Adding a Subreport to an Existing Report
Chapter 06 Review
Course Closure
Total Duration: 2h 48m