Microsoft Access 2016 Master Class: Beginner to Advanced

A Quick Note BEFORE Starting Section 2

Bruce's Intro and Three Data Problems Overview

Students will learn why Microsoft Access is the superior tool for managing large amounts of data when compared to spreadsheets. This lesson will explain how Access's database engine solves the common problems of file bloat and slow performance that occur when Excel files become too large.

Students will learn how Microsoft Access solves the common problems associated with sharing data across a team. This lesson will contrast the single-source, multi-user environment of an Access database with the confusion of emailing multiple spreadsheet versions, demonstrating how Access allows everyone to work with live, centralized data simultaneously.

Students will learn how Microsoft Access's relational structure solves the common 'one-to-many' data problem. They will discover how to efficiently manage related information, like one customer having many orders, by splitting it into separate linked tables, which reduces data redundancy and improves accuracy compared to a flat spreadsheet.

Important NOTE on DOWNLOADABLE RESOURCES

The Parts of the Access 2016 Screen

Working with the Navigation Pane, Menu, Ribbons and Objects

Students will learn the three fundamental operations for managing the data in their tables: adding new records, editing existing ones, and deleting old ones. They will master the correct procedures for each of these actions and understand the critical difference between editing a record and permanently deleting it.

Students will learn what a primary key is and why it's one of the most important concepts in database design. They will discover how using a unique identifier for each record is essential for preventing duplicate data and for creating relationships that link tables together.

Students will learn the practical skill of restructuring an existing table to add a primary key. They will discover how to use the AutoNumber data type to automatically generate a unique ID for every record, a common and critical step in proper database design.

Students will learn the basic techniques for sorting and filtering data directly in a table's datasheet view. They will practice rearranging records into alphabetical or numerical order, and how to apply simple filters to temporarily hide records and focus on specific information.

What do reports do?

Students will learn how to take a basic auto-generated report and customize it to create a polished, professional document. They'll master using Layout View for quick visual adjustments like resizing fields and changing colors, and Design View for more detailed structural changes to sections like headers and footers. This lesson is all about transforming a raw data dump into a readable and well-designed summary.

Working with Controls, Report and Layout Views

Basic Queries

Students will learn how to add criteria to their queries to filter their data and find the exact information they need.  They'll master the syntax for filtering text, numbers, and dates, and learn how to use wildcards and comparison operators to ask powerful and specific questions of their database.

Students will learn the specific syntax required to filter their queries based on date fields. They will master using comparison operators with dates and the powerful Between...And... operator to easily select all records that fall within a given date range.

Students will learn how to use OR logic to create more flexible queries that can find records matching one of several different conditions. They will master using the 'Or' row in the query design grid to broaden their search results, such as finding all customers from either California or New York.

Students will learn how to create flexible and interactive parameter queries. This lesson will teach them how to design a query that prompts the user for input, such as a specific date or a customer name, each time it is run, allowing users to get custom results without needing to edit the query's design.

Viewing Table Properties and Custom Formatting PDF

Students will learn how to improve data integrity by using various field properties in table design. They will master how to create field validation rules and custom error messages to prevent incorrect data entry, and also explore other key properties like input masks, default values, and captions.

Students will learn how to modify the overall behavior of their queries by using the Query Property Sheet. They will discover how to use key properties like 'Top Values' to return a specific number of records, such as the top 10 sales, and 'Unique Values' to remove duplicates from their results.1

Students will learn how to use the Property Sheet to control the behavior and appearance of their reports and all of their components. They will discover how to set a report's underlying record source, modify section properties to control page breaks, and adjust control properties to create things like running totals.

Students will learn how to use the Form Wizard to automatically generate a well-structured data entry form in just a few clicks. They'll walk through the step-by-step process of selecting a data source, choosing fields, and picking a layout to instantly create a functional form without needing any manual design work.

Students will learn how to take a form created by the wizard and manually clean it up for a more professional look and feel. They will practice the essential skills of working with controls, including how to select, move, resize, and align text boxes and labels to create a clean and intuitive layout.

Students will learn several advanced techniques to further polish the layout and design of their forms. They will practice how to move labels independently from their text boxes, use the rectangle tool to visually group related fields, and apply custom font colors for emphasis.

Students will learn the final design steps to complete and polish their form. They will practice resizing the form's header and detail sections to eliminate unnecessary white space, resulting in a compact, clean, and professional final product.

Students will learn how to use the Lookup Wizard to create a dropdown selection list for a field, a key technique for improving data integrity and speeding up data entry. They will walk through the process of creating a lookup field that pulls its choices from another table or from a custom list they type themselves.

Students will learn the manual, more powerful method of creating a lookup field without using the wizard. They will discover how to directly configure the lookup properties to create a combo box, control which columns are displayed, and enforce data integrity by limiting entries to the list.

Students will learn advanced techniques to sort and filter their data beyond the basic options. They will discover how to perform multi-level sorts on several fields at once and how to use the Advanced Filter window to construct complex criteria using AND/OR logic.

Working with Related TABLES

Using TOP queries

Using TOTAL Queries

Students will learn how to use a powerful Update Query to modify large amounts of data in their tables all at once. They will discover how to create a query that can, for example, increase prices or reassign a sales rep for thousands of records, while also understanding the importance of backing up their data before running an irreversible action query.

Students will learn how to use an Append Query to copy a large number of records from one table and add them to another in a single operation. They will discover how to select their source data and map it to the fields in the destination table, a common task for archiving data or combining datasets.

Students will learn how to use a Make Table query to take the results of any select query, including one that joins multiple tables, and create a brand new table from them. They will discover how this action query is used to create backups, export data, or create summary tables for reporting.

ACTION queries - DELETE

Students will learn how to perform calculations directly within a query by creating new calculated fields. They will practice combining data from other fields to create new information, such as an extended price, and then use the property sheet to format the results as currency or a percentage.

A Parameter Query Trick