Your Office

Module 1. Storing the Putts for Paws Golf Tournament Data in a Database Prepare Workshop 1: Understanding the Putts for Paws Main Database Objects

Understanding Database Basics and Tables

What is Access?

What are the Four Main Objects in a database?

Creating a new database and templates

Maneuvering in the Navigation Pane

Ways to view the objects

Shutter Button

Customizing the navigation pane

Enabling the Search Box

File Extensions in Access

Introducing Tables

What is a table?

Import a table

Navigate through the table

Difference between Access and Excel

Discovering a Database (Manual Query)

Understanding Queries, Forms, and Reports

Introducing Queries

Creating a Query using Wizard

Selecting a value using Design View

Printing Query Results

Introducing Forms

Creating a Form

Entering data via a Form

Introducing Reports

What is a Report?

Creating a Report using Wizard

Printing Report Results

Backing up your Database

Compact and Repair

EOW: Practice Problem 1

EOW: Practice Problem 2

Prepare Workshop 2: Gathering the Putts for Paws Data into the Database

Inserting Data into a Database

Database Design

Automated Mechanisms for Data Acquisition

Copy and Pasting

Importing (.accdb, .xlsx, .csv, & .txt)

Manual Data Entry

Entering Data via Data Sheet view

Removing Data

Understanding Tables and Keys

Creating a table in design view

Data types (Text, Memo, Number, Currency, Yes/No, and Date/Time)

Field Size

Formatting

Input Masks

Understanding and Designating Keys

Primary Keys

Foreign Keys

Composite Keys

Natural v. Numeric Keys

Understanding Relational Databases

Understanding Basic Principles of Normalization

Entities, Entity Classes, & Attributes

Redundancy minimization

Understanding and Creating Relationships

Utilizing the Relationships Window

Relationship Types

One-to-many

Adding a foreign key

Forming the relationship

Populate the foreign key

Using two related tables in a report

Many-to-many

One-to-one

Understanding Referential Integrity

Cascade Update

Cascade Delete

EOW: Practice Problem 1

EOW: Practice Problem 2

Module Capstone

More Practice 1

Problem Solve 1

Problem Solve 2

Problem Solve 3

Perform in Your Life

Perform in Your Career 1

Perform in Your Career 2

How Others Performed

Module 2. Organizing the Storage of Turquoise Oasis Spa's Transactions Prepare Workshop 3: Organizing the Storage of Spa Transactional Data

Understanding Relational Databases

Identifying the Advantages of the Relational Database over the Flat File database

Understanding Basic Principles of Normalization

Entities, Entity Classes, & Attributes

Redundancy minimization

Understanding and Creating Relationships

Utilizing the Relationships Window

Relationship Types

Many-to-Many

One-to-Many

One-to-One

Understanding Referential Integrity

Cascade Update

Cascade Delete

Enforcing

Evaluating Transactions at the Spa

Finding data on using datasheet view

Find feature

Find with wildcards

Simple Query Wizard and the limitations

Creating a Multi-tabled Query using Design View

Understand which tables to add to the view

Adding Basic Criteria (Exact Match, Basic Operators, & AND/OR)

Adding a simple Calculated Field using the Builder

Adding Sum, Min, Max, & Average aggregation

Using Other Query Wizards

The Find Unmatched Query Wizard

Using the wizard

Understanding why the simple query wizard is unable to find this data

Find Duplicates Query Wizard

Cross tab query wizard

EOW: Practice Problem 1

EOW: Practice Problem 2

Prepare Workshop 4: Developing a User Interface for Spa Transactions

Creating Forms using the Form Wizard

Moving and resizing controls

Adding a subform on a tab control

Enhancing with Conditional Formatting

Navigating forms

Printing forms

Creating Reports using the Report Wizard

Setting Printing Options

Grouping & Sorting

Adding Subtotals

Printing Reports

EOW: Practice Problem 1

EOW: Practice Problem 2

Module Capstone

More Practice 1

Problem Solve 1

Problem Solve 2

Problem Solve 3

Perform in Your Life

Perform in Your Career 1

Perform in Your Career 2

How Others Performed



Module 3 The Red Bluff Golf Club Pro Shop, Advanced Tables and QueriesPrepare Workshop 5: Advanced Tables

Controlling the Way Data is Entered

Advanced Field Options

Using Input Masks

Defining Input Masks

Using the Input Mask Wizard

Using Custom Formatting

Defining Data Validation Rules

Working with Captions

Creating Indexes

Defining a Single Field Index

Defining Multiple Field Indexes

Requiring Data Property

Advanced Data Types

Creating Lookup Fields

Using the Lookup Field Wizard

Lookup Field Properties

Calculated Data Type

Yes/No Data Type

Autonumber (and preference for natural keys)

Attachments, Hyperlinks, and OLE Objects

Filtering Data

Using the Table Analyzer Wizard

EOW: Practice Problem 1

EOW: Practice Problem 2

Prepare Workshop 6: Utilizing Queries to Retrieve Business Data

Advanced Criteria & Calculations

Using Wildcard Characters in String Comparisons

LIKE Function

Wildcard Characters

Find Records with the "Most" or "Least" Values

Top Values

Make a Query Ask for Input

Using Parameters in a Query

Specify Parameter Data Types

Concatenate Function

Not, In and Other Advanced Operators

Advanced Functions in Queries

If Functions

Basic If Function for Individual Condition

Nested If Functions for Various Conditions

IsNull Function

Working with Fields that Contain No Valid Data

Dates

Date versus Now Function

Use DateDiff to Determine a Time Interval

Use DateAdd to Subtract a Time Interval

Use DateSerial to Return a Date (Year, Month, and Day)

Use DatePart to Evaluate a Date

Round

Rounding to a Specific Number of Decimal Places

Distinguishing Between Rounding and Formatting

EOW: Practice Problem 1

EOW: Practice Problem 2

Module Capstone

More Practice 1

Problem Solve 1

Problem Solve 2

Problem Solve 3

Perform in Your Life

Perform in Your Career 1

Perform in Your Career 2

How Others Performed

Module 4 The Turquoise Oasis Spa, Advanced Calculations and Action QueriesPrepare Workshop 7: Creating Business Information Using Aggregation with Advanced Calculations

Understanding the GROUP BY Clause in Aggregated Calculations

Calculating Sales Volume

Using GROUP BY to Summarize Duplicate Data

Understanding the WHERE Clause in Aggregated Calculations

Calculating Sales Revenue

Business Calculations Using Sub-Querying

Creating a Query on a Query

Calculating the Percentage of Sales Revenue

Calculating the Percentage of Sales Volume

Calculating Physical Volume

Calculating the Percentage of Physical Volume

Understanding Structured Query Language

SELECT Statement Basics

Viewing SQL Statements

Creating a Basic SQL Query

Using the WHERE Clause

Using the HAVING Clause with AS and GROUP BY

Creating a Union Query

Creating a SQL Sub-query

Creating a Crosstab Query

Crosstab Query Wizard

Editing a Crosstab Query

EOW: Practice Problem 1

EOW: Practice Problem 2

Prepare Workshop 8: Action Queries and Advanced Relationships

Understanding Action Queries

Creating a New Table

Creating a Make Table Query

Running a Make Table Query

Adding Data to a Table

Creating an Append Query

Running an Append Query

Editing and Deleting Data

Creating, Testing and Running Update Queries

Simple Update Queries

Complex Update Queries

Creating, Testing and Running Delete Queries

Simple Delete Queries

Complex Delete Queries

Advanced Relationships Using Multiple Tables

Creating Inner Joins

Creating Outer Joins

Unmatched Query Wizard

EOW: Practice Problem 1

EOW: Practice Problem 2

Module Capstone

More Practice 1

Problem Solve 1

Problem Solve 2

Problem Solve 3

Perform in Your Life

Perform in Your Career 1

Perform in Your Career 2

How Others Performed



Module 5 The Red Bluff Golf Course, Advanced Forms and ReportsPrepare Workshop 9: Customized Forms

Advanced Form Settings

Understanding the property sheet

Understanding the data tab of the property sheet

Understanding the format tab of the property sheet

Changing tab order in a form

Adding a title, logo, date, and time to a form

Adding a footer to a form

Modifying a form in design view

Creating a form with a subform

Adding fields to a form

Adding calculated fields to a form

Finding a record with a combo box

Adding command buttons to a form

Using buttons for record navigation

Using buttons for form operations

Advanced forms

Creating a split form

Creating a multiple items form

Adding a chart to a form

Creating a PivotTable form

Creating a PivotChart form

EOW: Practice Problem 1

EOW: Practice Problem 2

Prepare Workshop 10: Customized Reports

Design view

Build a report with subtotals and totals

Modify group footer

Move and resize total fields

Hide duplicate values

Add and remove fields from a report

Add totals to a report

Hide detail on a summary report

Decide when totals are inappropriate

Parameter report

Create a report with a query as source

Add conditional formatting

Create a report from a parameter query

Create a report based on parameters in a form

Create a form for entering parameters

Add report buttons to a form

Mailing labels

Create mailing labels

EOW: Practice Problem 1

EOW: Practice Problem 2

Module Capstone

More Practice 1

Problem Solve 1

Problem Solve 2

Problem Solve 3

Perform in Your Life

Perform in Your Career 1

Perform in Your Career 2

How Others Performed



Module 6 The Turquoise Oasis Spa, Creating a refined User ExperiencePrepare Workshop 11: Navigation Forms and User Interface

Navigation Forms

View a Navigation Form

Open and Use a Navigation Form

Create a Navigation Form

Use a Predefined Layout to Create a Navigation Form

Modify a Navigation Form

Add a Form Tab

Delete a Form Tab

Move a Form Tab

Modify the Appearance of a Form Tab

Refining the User Interface

Create a Main Menu

Create a Navigation Form for Main Menu

Add Command Buttons to the Navigation Form

Create Navigation Buttons on a Form

Set Startup Display Options

Making Changes to the Startup Options

Test the Application

View the User Interface as a User

EOW: Practice Problem 1

EOW: Practice Problem 2



Prepare Workshop 12: Macros

Understanding Macros

Trusted Locations

The Macro Designer

Troubleshooting Macros

Finding errors using Single-Step

Automating Routine Tasks with Macros

Export database objects with Filters

Importing Data

Run Action Queries

Macro Groups

Sub macros

Key-assignment macro

Auto Exec Macro

Database Events

Common events

Sequence of events

Embedding macros

Data macros

Table level events

EOW: Practice Problem 1

EOW: Practice Problem 2





Module Capstone

More Practice 1

Problem Solve 1

Problem Solve 2

Problem Solve 3

Perform in Your Life

Perform in Your Career 1

Perform in Your Career 2

How Others Performed



Module 7. The Red Bluff Golf Course, Sharing the database and automating with Visual Basic for ApplicationsPrepare Workshop 13: VBA in Access

VBA Basics

What is VBA and Why Use it in Access

Convert a Pre-Existing Macro to VBA

Examine the VBA

Formatting and Structuring VBA Code

The DoCmd Object

The TransferSpreadsheet Method

Editing the Routine

The FileDialog Object

The With Statement

If Statements in VBA

Looping in VBA

Creating VBA Routines

Create an OnClick Event

Using the Edit Toolbar and Adding Comments

Error Handling and Debugging

Compiling and Securing your VBA

Compiling VBA Modules

Working with ACCDE Files

Password Protecting the VBA in a database

EOW: Practice Problem 1

EOW: Practice Problem 2



Prepare Workshop 14: Implementing Your Database

Implementing Databases

Shared and Exclusive Modes

Preparing the Database for a Single User

Performance Analyzer

Saving Access as a Previous Version

Preparing the Database for Multiple Users

Linking and Refreshing Tables

The Linked Table Manager

The Database Splitter

Encryption and Passwords

Setting the User's Experience

Controlling Navigation Options

Special Keys

Setting Startup Preferences

Methods of Implementation

EOW: Practice Problem 1

EOW: Practice Problem 2



Module Capstone

More Practice 1

Problem Solve 1

Problem Solve 2

Problem Solve 3

Perform in Your Life

Perform in Your Career 1

Perform in Your Career 2

How Others Performed



Appendix: Terra Cotta Brew, Normalizing a Database for Effective Design

Appendix: Normalizing a Database for Effective Design

Normalizing a Database

Identifying Anomalies

Satisfying the First Normal Form

Satisfying the Second Normal Form

Satisfying the Third Normal Form

Joining tables with Relationships

Understanding an Entity -Relationship Diagram



Perform in Your Career 1

How Others Performed