MICROSOFT©

ACCESS 2021
Trish Conner-Cato

Learnit!
 Introduction into How Creating a New Database Entering Data into Tables

MICROSOFT© Relational Databases

Operate
• Examining Database Objects
Learning the Access
Interface
• Planning
• Object naming conventions
• Creating new tables
• Data entry techniques
• Sorting and filtering data

ACCESS 2021 • Examining Table Relationships • Importing data from Excel

MODULE 1 – Introduction into Relating

Table Data
Creating Commonly Used
Queries
Customizing Queries with
Criteria

INTRODUCTIO • Using the Relationships Window • Using the Query Wizard

• Using Design view
• Adding criteria
• Using wildcards in criteria

N INTO HOW
DATABASES
OPERATE - Files Used:
•
LESSONS •
Excel – Import into Access
Word – Manual Data Entry
• Word – Simple Cash Register Instructions
• Word – Super Duper Cash Register Instructions
ACCESS 2021
MODULE 1:
LESSON 1 – Exploring a Relational Database
INTRODUCTIO
N INTO HOW Examining Database Objects
DATABASES
OPERATE How Data is Related Between Tables
ACCESS 2021
Tables – store data
MODULE 1:
LESSON 1 – Queries – ask questions of data

DATABASE Forms – used for data entry or to display information to the user
OBJECTS Reports – can be distributed

Macros – automate tasks

Modules – automate tasks using VBA

ACCESS 2021 Exploring Access Ribbon Tabs
MODULE 1:
LESSON 2 –
LEARNING THE Using the Access Navigation Pane
ACCESS
INTERFACE
Using Data View and Design View of
an Object
ACCESS 2021
MODULE 1:
Planning Steps to Create
LESSON 3 – a New Database
CREATING A
NEW
DATABASE
Creating a New Database
ACCESS 2021 Confer with colleagues
MODULE 1:
LESSON 3 – What is its business purpose?
PLANNING A
NEW
DATABASE Who will use it?

How will users be trained?

ACCESS 2021 Tables – tblCustomers; tblProductsInformation
MODULE 1: Queries - qryCustomers
LESSON 3 –
OBJECT Forms – frmCustomers
NAMING Reports – rptCustomers
CONVENTIONS
Macros – mcrRequirePassword

Modules – modErrorHandling
ACCESS 2021
MODULE 1: Planning Steps for Database Tables
LESSON 4 –
DESIGNING Create Tables in Design View
AND CREATING
NEW TABLES
Import Data from Excel
 Multi-Subject Planner for One Topic
ACCESS 2021 • Related Subjects
MODULE 1: • Related Fields
LESSON 4 – One Table per One Subject
PLANNING
Plan on Paper First
TABLES
Break Data Down to Lowest Form
• Easier for sorting/filtering

Review Table Plan

 Customer Table Field Names

ACCESS 2021 CustomerID (AutoNumber) Zip

MODULE 1: CompanyName Phone

LESSON 4 – Address1 Email (Hyperlink)
SAMPLE PLAN Address2 Active (Yes/No)
FOR City Employee (Number)
CUSTOMER State
AND
EMPLOYEE Employee Table Field Names
TABLES EmployeeID LastName FirstName Extension
ACCESS 2021
MODULE 1:
Manually Enter Data into
LESSON 5 – a Table
ENTERING
DATA INTO
TABLES Data Entry Shortcuts and
Techniques
ACCESS 2021
MODULE 1:
Rearranging Records by
LESSON 6 – Sorting
SORTING &
FILTERING
DATA Hiding Records Using
Filtering
ACCESS 2021 Exploring Different Relationship
MODULE 1: Types
LESSON 7 –
INTRODUCTIO Using the Relationships Window
N INTO The Add Tables task pane
RELATING
TABLE DATA Creating Table Relationships
ACCESS 2021 One-to-many (most common)
MODULE 1: • One customer has many orders
LESSON 7 –
Many-to-many (requires a third table)
RELATIONSHIP
• Many products are on many orders
TYPES
• Many orders have many products

One-to-one (not common)

• One department has one manager
 • tblOrders – tblOrderDetails
ACCESS 2021 • Common Field: OrderID
MODULE 1: • tblProducts – tblOrderDetails
LESSON 7 – • Common Field: ProductID

TABLE • tblCategories – tblProducts

• Common Field: CategoryID
RELATIONSHIP • tblCustomers – tblOrders
S • Common Field: CustomerID

• tblEmployees – tblCustomers
• Common Field: EmployeeID / Employee

• All relationships are one-to-many

• Cascade updates on all relationships
 Introduced to Relational Database Concept
ACCESS 2021
MODULE 1: Reviewed Access Environment
RECAP
Planned and Created New Database

Planned and Created Database Tables

Sorted and Filtered Table Data

Created Table Relationships

 Creating Commonly Used Customizing Queries with
Queries Criteria
MICROSOFT© • Using the Query Wizard
• Using Design view
• Adding criteria
• Using wildcards in criteria
ACCESS 2021
MODULE 2 –
QUERIES, Using Forms for Data Creating Reports for Data
FORMS, AND Entry Output
REPORTS - •
•
Using the Form Wizard
Using Design View
•
•
Basing Reports on Queries
Previewing/Printing Reports
LESSONS •
•
Exploring Form Layouts
Performing Data Entry
•
•
Understanding Design View
Formatting Reports

Files Used:
• Learnit Logo
ACCESS 2021 Adding Criteria to Limit Query
MODULE 2: Results
LESSON 2 –
CUSTOMIZING
QUERIES WITH Introduction to Using Wildcards
CRITERIA in Criteria
• Wildcards in queries and parameters in Ac
cess
ACCESS 2021
MODULE 2: Creating Forms
LESSON 3 –
USING FORMS • Exploring Different Form
FOR DATA Layouts
ENTRY
• Performing Data Entry in
Forms
ACCESS 2021
MODULE 2: Basing Reports on Queries
LESSON 4 –
Previewing and Printing Reports
CREATE
REPORTS FOR Understanding the Report Design Interface
DATA OUTPUT
Formatting Reports for Professional-Looking Output
ACCESS 2021 Created Commonly Used Queries
MODULE 2:
RECAP Customized Queries with Criteria

Created and Used Forms for Data Entry

Created Reports for Data Output

MICROSOFT©
ACCESS 2021 Controlling Query Creating Complex
Results with Join Queries with
MODULE 3 Types Multiple Criteria
LESSONS

Calculating Data
with Queries
ACCESS 2021
MODULE 3:
LESSON 1 –
CONTROLLING
QUERY Join tables and que
RESULTS WITH
JOIN TYPES
ries (microsoft.co
m)
ACCESS 2021
MODULE 3: Observed how Join Types Impact Query Results
RECAP
Created Complex Queries with Multiple Criteria

Calculated Data with Queries

Summarized Data with Grouping

MICROSOFT©
ACCESS 2021 Automating
Using Query
Queries with
MODULE 4 Parameters
Wizards
LESSONS
Advanced
Exploring
Report Design
Action Queries
Techniques
ACCESS 2021 Creating Automated Requests for
MODULE 4: Criteria
LESSON 1 –
AUTOMATING Using Wildcards in Parameter Queries
QUERIES WITH
PARAMETERS
Using Range Operators in Parameter
Queries
ACCESS 2021 Category of Queries that
MODULE 4:
LESSON 3 – Change Table Data
ACTION • Make-Table Query
QUERIES
• Append Query
• Update Query
• Delete Query
ACCESS 2021
MODULE 4: Understanding Report Control Types
LESSON 4 –
ADVANCED Grouping Report Data
REPORT
DESIGN
TECHNIQUES
Summarizing Report Data
ACCESS 2021
Created Parameter Queries
MODULE 4:
RECAP Used Query Wizard

Created Action Queries

Used Advanced Report Design
Techniques
MICROSOFT©
ACCESS 2021 Advanced Form
Using
Switchboards to
MODULE 5 – Design
Navigate Database
ADVANCED Techniques
Objects
FORM DESIGN Introduction to
TECHNIQUES, Designing Macros Access Visual
AND to Automate Basic for
Database Tasks Complex
AUTOMATION - Automation
LESSONS
MICROSOFT©
ACCESS 2021
Tabbed
MODULE 5 – Subforms
LESSON 1 - Forms
ADVANCED
FORM DESIGN Split
TECHNIQUES
Forms
MICROSOFT©
ACCESS 2021
MODULE 5 –
LESSON 2 -
CREATING
Default Tab
“KEYSTROKE-
LESS” FIELD
Values Order
CONTROLS
MICROSOFT©
ACCESS 2021 Use Message Automate
MODULE 5 – Close / Open DB
Boxes to Notify Parameter Queries
Objects
LESSON 4 – Users with Macros

DESIGNING
View Related
MACROS TO Records in
Hiding/Unhiding Use Macros to
AUTOMATE Field Controls Validate Data
Another Form
with Macros Entry
DATABASE with Macros

TASKS
MICROSOFT©
ACCESS 2021 Use VBA to Use VBA to
MODULE 5 – Secure Forms Create User Input
LESSON 5 – with Passwords Boxes
INTRODUCTION
TO VISUAL Use Logic File Used: VBA
Programming to Code for Access
BASIC FOR Interpret 2021 – Text
COMPLEX Conditions Document
AUTOMATION
MICROSOFT©
ACCESS 2021 Advanced Form
Using
Switchboards to
MODULE 5 – Design
Navigate Database
ADVANCED Techniques
Objects
FORM DESIGN Introduction to
TECHNIQUES, Designing Macros Access Visual
AND to Automate Basic for
Database Tasks Complex
AUTOMATION - Automation
RECAP
MICROSOFT©
ACCESS 2021
MODULE 6 – Linking Tables
Customizing
DATABASE to Ensure
Database
SECURITY, AND Database
DATABASE Options
Security
OPTIONS -
LESSONS
MICROSOFT©
ACCESS 2021
MODULE 6 – Linking Tables
Customizing
DATABASE to Ensure
Database
SECURITY, AND Database
DATABASE Options
Security
OPTIONS -
RECAP
ACCESS 2021 The Linked Table Manager
MODULE 6: • Depending on the version of Access you have,
LESSON 1 – the Linked Table Manager dialog box works
LINKING differently. In Microsoft 365, you can use this
TABLE TO dialog box to refresh, relink, add, edit, search,
ENSURE and delete linked tables, but you cannot export
DATABASE information to Excel. In Access 2016 and later,
the dialog box is much simpler, but you can
SECURITY export information to Excel.