Microsoft Access 2019 / 365

COURSE OUTLINE

Module 1: An Introduction to Database Development

• The Database Terminology of Access
  • Databases
  • Tables
  • Records and fi elds
  • Values
• Relational Databases
• Access Database Objects
  • Tables
  • Queries
  • Data-entry and display forms
  • Reports
  • Macros and VBA
  • Planning for database objects
• A Five-Step Design Method
  • Step 1: The overall design—from concept to reality
  • Step 2: Report design
  • Step 3: Data design
  • Step 4: Table design
  • Step 5: Form design

Module 2: Getting Started with Access

• The Access Welcome Screen
• How to Create a Blank Database
• The Access 2019 Interface
  • The Navigation pane
    • Custom
    • Object Type
    • Tables and Related Views
    • Created Date
    • Modified Date
  • The Ribbon
  • The Quick Access toolbar

Module 3: Creating Access Tables

• Table Types
  • Object tables
  • Transaction tables
  • Join tables
• Creating a New Table
  • Designing tables
  • Using the Design tab
    • Primary Key
    • Insert Rows
    • Delete Rows
    • Property Sheet
    • Indexes
  • Working with fi elds
    • Naming a fi eld
    • Specifying a data type
    • Entering a fi eld description
    • Specifying data validation rules
• Creating tblCustomers
  • Using AutoNumber fi elds
  • Completing tblCustomers
• Changing a Table Design
  • Inserting a new fi eld
  • Deleting a fi eld
  • Changing a fi eld location
  • Changing a fi eld name
  • Changing a fi eld size
  • Handling data conversion issues
  • Assigning fi eld properties
    • Common properties
    • Format
    • Input Mask
    • Caption
    • Validation Rule and Validation Text
    • Required
    • AllowZeroLength
    • Indexed
• Understanding tblCustomers Field Properties
• Setting the Primary Key
  • Choosing a primary key
  • Creating the primary key
  • Creating composite primary keys
• Indexing Access Tables
  • The importance of indexes
  • Multiple-fi eld indexes
  • When to index tables
• Printing a Table Design
• Saving the Completed Table
• Manipulating Tables
  • Renaming tables
  • Deleting tables
  • Copying tables in a database
  • Copying a table to another database
• Adding Records to a Database Table
• Understanding Attachment Fields

Module 4: Understanding Table Relationships

• Building Bulletproof Databases
• Data Normalization and Denormalization
• First normal form
• Second normal form
• Identifying entities
• Less obvious entities
• Breaking the rules
• Third normal form
• Denormalization
• Table Relationships
• Connecting the data
• One-to-one
• One-to-many
• Many-to-many
• Integrity Rules
• No primary key can contain a null value
• All foreign key values must be matched by corresponding primary keys
• Keys
• Deciding on a primary key
• Looking at the benefi ts of a primary key
• Designating a primary key
• Single-fi eld versus composite primary keys
• Natural versus surrogate primary keys
• Creating primary keys
• Creating relationships and enforcing referential integrity
• Specifying the join type between tables
• Enforcing referential integrity
• Viewing all relationships
• Deleting relationships
• Following application-specifi c integrity rules

Module 5: Working with Access Tables

• Understanding Datasheets
• Looking at the Datasheet Window
  • Moving within a datasheet
  • Using the Navigation buttons
  • Examining the Datasheet Ribbon
    • Views
    • Clipboard
    • Sort & Filter
    • Records
    • Find
    • Window
    • Text Formatting
• Opening a Datasheet
• Entering New Data
  • Saving the record
  • Understanding automatic data-type validation
  • Knowing how properties affect data entry
    • Standard text data entry
    • Date/Time data entry
    • Number/Currency data entry with data validation
    • OLE object data entry
    • Long Text fi eld data entry
• Navigating Records in a Datasheet
  • Moving between records
  • Finding a specifi c value
• Changing Values in a Datasheet
  • Manually replacing an existing value
  • Changing an existing value
• Using the Undo Feature
• Copying and Pasting Values
• Replacing Values
• Adding New Records
• Deleting Records
• Displaying Records
  • Changing the fi eld order
  • Changing the fi eld display width
  • Changing the record display height
  • Changing display fonts
  • Displaying cell gridlines and alternate row colors
  • Aligning data in columns
  • Hiding and unhiding columns
  • Freezing columns
  • Saving the changed layout
  • Saving a record
• Sorting and Filtering Records in a Datasheet
  • Sorting records
  • Filtering a selection
  • Filtering by form
• Aggregating Data
• Printing Records
• Previewing Records

Module 6: Selecting Data with Queries

• Introducing Queries
  • What queries can do
  • What queries return
• Creating a Query
  • Adding fi elds to your queries
    • Adding a single fi eld
    • Adding multiple fi elds
  • Running your query
• Working with Query Fields
  • Selecting a fi eld in the QBD grid
  • Changing fi eld order
  • Resizing columns in the QBD grid
  • Removing a fi eld
  • Inserting a fi eld
  • Hiding a fi eld
  • Changing the sort order of a fi eld
• Adding Criteria to Your Queries
  • Understanding selection criteria
  • Entering simple string criteria
  • Entering other simple criteria
• Printing a Query’s Recordset
• Saving a Query
• Creating Multi-table Queries
  • Viewing table names
  • Adding multiple fi elds
  • Recognizing the limitations of multi-table queries
  • Overcoming query limitations
    • Updating a unique index (primary key)
    • Replacing existing data in a query with a one-to-many relationship
    • Updating fi elds in queries
• Working with the Table Pane
  • Looking at the join line
  • Moving a table
  • Removing a table
  • Adding more tables
• Creating and Working with Query Joins
  • Understanding joins
  • Leveraging ad hoc table joins
  • Specifying the type of join
  • Deleting joins

Module 7: Using Operators and Expressions in Access

• Introducing Operators
  • Types of operators
    • Mathematical operators
    • Comparison operators
    • String operators
    • Boolean (logical) operators
    • Miscellaneous operators
  • Operator precedence
    • The mathematical precedence
    • The comparison precedence
    • The Boolean precedence
• Using Operators and Expressions in Queries
  • Using query comparison operators
  • Understanding complex criteria
  • Using functions in select queries
  • Referencing fi elds in select queries
• Entering Single-Value Field Criteria
  • Entering character (Text or Memo) criteria
  • The Like operator and wildcards
  • Specifying non-matching values
  • Entering numeric criteria
  • Entering true or false criteria
  • Entering OLE object criteria
• Using Multiple Criteria in a Query
  • Understanding an Or operation
  • Specifying multiple values with the Or operator
  • Using the Or cell of the QBD pane
  • Using a list of values with the In operator
  • Using And to specify a range
  • Using the BetweenAnd operator
  • Searching for null data
• Entering Criteria in Multiple Fields
  • Using And and Or across fi elds in a query
  • Specifying Or criteria across fi elds of a query
  • Using And and Or together in different fi elds
  • A complex query on different lines

Module 8: Going Beyond Select Queries

• Aggregate Queries
  • Creating an aggregate query
  • About aggregate functions
    • Group By
    • Sum, Avg, Count, StDev, Var
    • Min, Max, First, Last
    • Expression, Where
• Action Queries
  • Make-table queries
  • Delete queries
  • Append queries
  • Update queries
• Crosstab Queries
  • Creating a crosstab query using the Crosstab Query Wizard
  • Creating a crosstab query manually
    • Using the query design grid to create your crosstab query
    • Customizing your crosstab queries
• Optimizing Query Performance
  • Normalizing your database design
  • Using indexes on appropriate fi elds
  • Optimizing by improving query design
  • Compacting and repairing your database regularly

Module 9: Creating Basic Access Forms

• Working with Form Views
• Understanding Different Types of Forms
  • Creating a new form
    • Using the Form command
    • Using the Form Wizard
  • Looking at special types of forms
    • Navigation forms
    • Multiple-items forms
    • Split forms
    • Datasheet forms
  • Resizing the form area
  • Saving your form
• Working with Controls
  • Categorizing controls
  • Adding a control
    • Using the Controls group
    • Using the fi eld list
  • Selecting and deselecting controls
    • Selecting a single control
    • Selecting multiple controls
    • Deselecting controls
  • Manipulating controls
    • Resizing a control 513
    • Sizing controls automatically
    • Moving a control
    • Aligning controls
    • Modifying the appearance of a control
    • Grouping controls
    • Changing a control’s type
    • Copying a control
    • Deleting a control
    • Reattaching a label to a control
• Introducing Properties
  • Displaying the Property Sheet
  • Getting acquainted with the Property Sheet525
  • Changing a control’s property setting
  • Naming control labels and their captions

Module 10: Working with Data on Access Forms

• Using Form View
  • Looking at the Home tab of the Ribbon
    • The Views group
    • The Clipboard groups
    • The Sort & Filter group
    • The Records group
    • The Find group
    • The Window groups
    • The Text Formatting group
  • Navigating among fields
  • Moving among records in a form
• Changing Values in a Form
  • Knowing which controls you can’t edit
  • Working with pictures and OLE objects
  • Entering data in the Long Text fi eld
  • Entering data in the Date fi eld
  • Using option groups
  • Using combo boxes and list boxes
  • Switching to Datasheet view
  • Saving a record
• Printing a Form
• Working with Form Properties
  • Changing the title bar text with the Caption property
  • Creating a bound form
  • Specifying how to view the form
  • Removing the Record Selector
  • Looking at other form properties
• Adding a Form Header or Footer
• Working with Section Properties
  • The Visible property
  • The Height property
  • The Back Color property
  • The Special Effect property
  • The Display When property
  • The printing properties
• Changing the Layout
  • Changing a control’s properties
  • Setting the tab order
  • Modifying the format of text in a control
  • Using the Field List to add controls
• Converting a Form to a Report

Module 11: Working with Form Controls

• Setting Control Properties
  • Customizing default properties
  • Looking at common controls and properties
    • The Text Box control
    • The Command Button control
    • The Combo Box and List Box controls
    • The Check Box and Toggle Button controls
    • The Option Group control
    • The Web Browser control

• Creating a Calculated Control
• Working with Subforms
• Form Design Tips
  • Using the Tab Stop property
  • Tallying check boxes
  • Setting up combo boxes and list boxes
• Tackling Advanced Forms Techniques
  • Using the Page Number and Date/Time controls
  • Using the Image control
  • Morphing a control
  • Using the Format Painter
  • Offering more end-user help
  • Adding background pictures5
  • Limiting the records shown on a form
• Using the Tab Control
• Using Dialog Boxes to Collect Information
  • Designing the query
  • Setting up the command buttons
  • Adding a default button
  • Setting a Cancel button
  • Removing the control menu
• Designing a Form from Scratch
  • Creating the basic form
  • Creating a subform
  • Adding the subform
  • Changing the form’s behavior
    • Setting the form properties
    • Looking up values during data entry
    • Saving the record
  • Changing the form’s appearance

Module 12: Presenting Data with Access Reports

• Introducing Reports
  • Identifying the different types of reports
    • Tabular reports
    • Columnar reports
    • Mailing label reports
  • Distinguishing between reports and forms
• Creating a Report, from Beginning to End
  • Defi ning the report layout
  • Assembling the data
  • Creating a report with the Report Wizard
    • Creating a new report
    • Selecting the grouping levels
    • Defi ning the group data
    • Selecting the sort order
    • Selecting summary options
    • Selecting the layout
    • Opening the report design
    • Adjusting the report’s layout
    • Choosing a theme
    • Creating new theme color schemes
    • Using the Print Preview window
    • Publishing in alternate formats
    • Viewing the report in Design view
  • Printing or viewing the report
    • Printing the report
    • Viewing the report
  • Saving the report
• Banded Report Design Concepts
  • The Report Header section
  • The Page Header section
  • The Group Header section
  • The Detail sections
  • The Group Footer section
  • The Page Footer section
  • The Report Footer section
• Creating a Report from Scratch
  • Creating a new report and binding it to a table
  • Defi ning the report page size and layout
  • Placing controls on the report
  • Resizing a section
    • Modifying the appearance of text in a control
  • Working with Text Box controls
    • Adding and using Text Box controls
    • Entering an expression in a Text Box control
    • Sizing a Text Box control or Label control
    • Deleting and cutting attached labels from Text Box controls
    • Pasting labels into a report section
    • Moving Label and Text Box controls
    • Modifying the appearance of multiple controls
  • Changing Label and Text Box control properties
  • Growing and shrinking Text Box controls
  • Sorting and grouping data
    • Creating a group header or footer
  • Sorting data within groups
    • Removing a group
    • Hiding a section
    • Sizing a section
    • Moving controls between sections
  • Adding page breaks
• Improving the Report’s Appearance
  • Adjusting the page header
  • Creating an expression in the group header
  • Creating a report header