Students will:

• Design and create relational databases from scratch or using templates, navigating the Access interface with confidence.
• Master table creation in both Datasheet and Design views, incorporating specialized field types (Attachment, Calculated, Multivalued) and Primary Keys.
• Implement strict data entry rules using Input Masks, Validation Rules, and Lookup fields to ensure accuracy and consistency.
• Utilize the Query Wizard and Design grid to filter data using wildcards, compound criteria, parameter requests, and aggregate calculations.
• Build professional user interfaces, including split forms, subforms, and multipage tab controls to streamline data entry and navigation.
• Generate sophisticated reports using the Report Wizard and Design view, incorporating grouping, sorting, and embedded subreports.
• Apply the principles of normalization (1NF, 2NF, and 3NF) and enforce referential integrity to manage complex table relationships.
• Write and execute Structured Query Language (SQL) commands to manually retrieve, join, and manipulate data within the Access environment.
• Create UI and data macros using logic (IF statements) and basic VBA modifications to automate repetitive tasks and button actions.
• Manage database health using the Performance Analyzer, Table Analyzer, and security features like password encryption and database splitting.

Course Outline:

Lesson 1: Databases and Database Objects: An Introduction

This module introduces the fundamentals of database management by guiding you through the Access interface, from building tables in both Datasheet and Design views to importing data and modifying primary key properties. You will also learn to extract and present information effectively by running queries, building custom forms, and utilizing wizards to generate professional reports.

Lesson 2: Querying a Database

This module advances your database skills by teaching you how to refine query results using wildcards, compound criteria, and parameter requirements to isolate specific datasets. You will also learn to transform these results into professional reports and forms, perform complex calculations through grouping and crosstab queries, and seamlessly export your data to other applications.

Lesson 3: Maintaining a Database

This module focuses on advanced data management and integrity by teaching you how to build split forms for efficient record navigation and implement validation rules, default values, and lookup fields. You will also master complex operations like using action queries to modify bulk data and establishing table relationships to manage subdatasheets and resolve data inconsistencies.

Lesson 4: Creating Reports and Forms

This module emphasizes the critical aspects of data integrity and advanced navigation through the implementation of validation rules, lookup fields, and split forms. You will also master complex database operations, including the use of action queries for bulk data modification and the establishment of table relationships to manage subdatasheets and resolve inconsistencies.

Lesson 5: Multiple-Table Forms

This module focuses on enhancing user interaction by teaching you how to build complex forms in Design view, including the integration of subforms and various data types like Attachments and Long Text. You will also learn to refine the user experience by applying input masks, setting tab sequences, and managing object dependencies to ensure data is displayed and navigated accurately across multiple tables.

Lesson 6: Advanced Report Techniques

This module advances your reporting capabilities by teaching you how to build custom reports from scratch in Design view and use the Expression Builder to incorporate complex calculations. You will also master professional layout techniques, such as enforcing referential integrity in data sources, integrating subreports, and precisely modifying grouping controls and section properties for a polished final product.

Lesson 7: Advanced Form Technique

This module focuses on professional form enhancement by teaching you how to integrate interactive elements like command buttons, search-enabled combo boxes, and multipage tab controls. You will also learn to visualize data through customized charts and refine the user interface by modifying VBA code, inserting images, and applying consistent formatting with the Format Painter.

Lesson 8: Macros, Navigation Forms, and Control Layouts

This module advances your automation skills by teaching you how to build and troubleshoot macros, including UI and data macros, to streamline database operations with logic like IF statements and variables. You will also learn to create professional navigation systems by designing menu and navigation forms with option groups and custom control layouts to manage the end-user experience.

Lesson 9: Administering a Database System

This module covers the critical administrative and optimization tasks required to maintain a secure, high-performing database, such as splitting a database for multi-user access and encrypting it with a password. You will also learn to use sophisticated diagnostic tools like the Table and Performance Analyzers to streamline your data structure and enhance speed through custom indexing and automatic error checking.

Lesson 10: Using SQL

This module explores the foundational power of SQL (Structured Query Language), teaching you how to write manual queries to select, sort, and group data with advanced aggregate functions and subqueries. You will also learn to perform bulk data modifications using SQL commands to insert, update, and delete records, while bridging the gap between raw code and the Access interface by linking external data.

Lesson 11: Database Design

This module dives into the theoretical foundations of database architecture by exploring the relationships between entities and attributes and identifying the critical role of functional dependence. You will master the essential process of normalization, learning to systematically convert unnormalized data into first, second, and third normal forms to ensure a robust, efficient, and error-free database design.

All necessary course materials are included.

Certification(s):

This course prepares a student to take the Microsoft Access Expert (Office 2019): Exam MO-500 national certification exam.