GO! with Access 2007 Comprehensive

Shelley Gaskin, Series Editor for the GO! Series, is a professor of business and computer technology at Pasadena City College in Pasadena, California. She holds a master's degree in business education from Northern Illinois University and a doctorate in adult and community education from Ball State University. Dr. Gaskin has 15 years of experience in the computer industry with several Fortune 500 companies and has developed and written training materials for custom systems applications in both the public and private sector. She is also the author of books on Microsoft Outlook and word processing.

Chapter 1 Creating Database Tables
Scenario: Eastern Cape Inn




Project 1A – Guest Rooms 


Objective 1: Start Access
    Activity 1.1 Starting Access
Objective 2: Create a Database and Enter Data Using a Template
    Activity 1.2 Creating a Database Using a Template
    Activity 1.3 Enabling Database Content
    Activity 1.4 Navigating the Database Window
    Activity 1.5 Entering Data into the Database
Objective 3: Modify a Template Table
    Activity 1.6 Hiding Fields in a Template Table
    Activity 1.7 Adding Fields and Data in a Template Table
Objective 4: Find, Modify, and Print Records
    Activity 1:8 Adding Records in a Template Table and Checking Spelling
    Activity 1.9 Finding and Deleting Records
    Activity 1.10 Finding and Modifying Records
    Activity 1.11 Printing the Table and a Report
Objective 5: Use the Access Help System and Exit Access
    Activity 1.12 Using Help to Find Access Information
    Activity 1.13 Closing the Database and Exiting Access

Project 1B – Inventory


Objective 6: Create a New Database and Tables from Scratch
    Activity 1.14 Creating a New Database
    Activity 1.15 Creating Fields in a New Table in Datasheet View
    Activity 1.16 Adding the First Record to a Table in Datasheet View
    Activity 1.17 Changing Data Types
    Activity 1.18 Creating Additional Records in a Table in Datasheet View
    Activity 1.19 Creating a Second Table in Design View
    Activity 1.20 Creating Records in the Second Table
Objective 7: Modify the Table Design
    Activity 1.21 Changing the Primary Key
    Activity 1.22 Deleting, Adding, and Moving Fields in Datasheet View and Design View
    Activity 1.23 Renaming a Field in Datasheet View and Design View
Objective 8: Format a Table for Printing
    Activity 1.24 Changing Column Widths
    Activity 1.25 Viewing a Table in Print Preview and Printing a Table
 
Chapter 2 Sorting, Filtering, and Querying Databases
Scenario: Laurel County Community College

Project 2A – Athletic Events


Objective 1: Open and Rename an Existing Database
    Activity 2.1 Opening and Renaming an Existing Database
    Activity 2.2 Adding a File Location to Trusted Locations
Objective 2: Sort Records
    Activity 2.3 Sorting Records in Ascending and Descending Order
    Activity 2.4 Sorting Records on Multiple Fields
Objective 3: Filter Records
    Activity 2.5 Filtering by Data in One Field
    Activity 2.6 Filtering by Data in More than One Field
Objective 4: Open, Edit, Sort, and Print an Existing Query
    Activity 2.7 Opening and Modifying an Existing Query
    Activity 2.8 Adding a Record in the Underlying Table
    Activity 2.9 Sorting an Existing Query
    Activity 2.10 Printing a Query
Objective 5: Create Table Relationships and a Simple Query
    Activity 2.11 Creating Table Relationships
    Activity 2.12 Creating a Simple Query Using Joined Tables
    Activity 2.13 Printing Selected Records from a Query

Project 2B – Students


Objective 6: Create a Select Query
    Activity 2.14 Creating Multiple Table Relationships
    Activity 2.15 Creating a Select Query in Design View
Objective 7: Specify Criteria in a Query
    Activity 2.16 Specifying Text Criteria in a Query
    Activity 2.17 Hiding Fields in the Query Results
    Activity 2.18 Finding Empty Fields
    Activity 2.19 Specifying Numeric Criteria in a Query
    Activity 2.20 Using Comparison Operators in a Query
    Activity 2.21 Using a Wildcard in Query and Rearranging Fields
Objective 8: Specify Compound Criteria in a Query
    Activity 2.22 Using AND in a Query
    Activity 2.23 Using OR in a Query
    Activity 2.24 Using AND and OR in a Query
 
Chapter3 Creating Forms and Reports
Scenario: Seattle-Tacoma Job Fair

Project 3A – Job Fair Employers

Objective 1: Create a Form
    Activity 3.1 Using the Form Tool to Create a Form
    Activity 3.2 Using the Form Wizard to Create a Form
    Activity 3.3 Using the Blank Form Tool to Create a Form
Objective 2: Add, Delete, Edit, and Print Records in a Form
    Activity 3.4 Adding Records to a Table Using a Form
    Activity 3.5 Deleting Records from a Table Using a Form
    Activity 3.6 Editing a Record in a Table Using a Form
    Activity 3.7 Filtering and Sorting Records in a Form
    Activity 3.8 Printing a Simple Form
Objective 3: Modify the Design of a Form in Layout View
    Activity 3.9 Inserting a Logo and a Date into a Form
    Activity 3.10 Adding a Field to and Deleting a Field from a Form
    Activity 3.11 Moving Fields in a Form
Objective 4: Modify the Design of a Form in Design View
    Activity 3.12 Inserting a Footer into a Form and Aligning Controls
    Activity 3.13 Changing the Tab Order and Printing a Selected Record

Project 3B – Job Openings

Objective 5: Create a Report
    Activity 3.14 Using the Report Tool to Create a Report
    Activity 3.15 Using the Report Wizard to Create a Report
    Activity 3.16 Using the Blank Report Tool to Create a Report
    Activity 3.17 Using the Label Wizard to Create Labels
Objective 6: Modify the Design of a Report in Layout View
    Activity 3.18 Adjusting and Formatting Controls in a Report Created Using the Blank Report Tool
    Activity 3.19 Inserting a Title, Logo, Date, Sum, and Page Number into a Report
    Activity 3.20 Adjusting Controls in a Report Created With the Report Wizard
Objective 7: Modify the Design of a Report in Design View
    Activity 3.21 Adding a Control in the Page Footer Section
Objective 8: Keep Grouped Records Together and Print a Report
    Activity 3.22 Keeping Grouped Records Together and Printing a Report
 
Chapter 4 Enhancing Tables
Scenario: City of Westland Plains

Project 4A – City Directory

Objective 1: Modify Existing Tables
    Activity 4.1 Backing Up a Database
    Activity 4.2 Analyzing the Structure of the Tables
    Activity 4.3 Copying a Table and Modifying the Structure
    Activity 4.4 Appending Records to a Table
    Activity 4.5 Splitting a Table into Two Tables
    Activity 4.6 Appending Records from Another Database
    Activity 4.7 Setting a Primary Key
Objective 2: Customize the Navigation Pane
    Activity 4.8 Creating a Custom Category and Group
    Activity 4.9 Adding Objects to a Custom Group and Hiding a Group
Objective 3: Create and Modify Table Relationships
    Activity 4.10 Creating Table Relationships
    Activity 4.11 Testing Referential Integrity
    Activity 4.12 Set and Test Cascade Options
Objective 4: Enter Records Using a Subdatasheet
    Activity 4.13 Entering Records Using a Subdatasheet

Project 4B – Tasks

Objective 5: Change Data Types
    Activity 4.14 Changing Data Types
Objective 6: Set Field Properties
    Activity 4.15 Creating an Input Mask Using the Input Mask Wizard
    Activity 4.16 Creating an Input Mask Using the Input Mask Properties Box
    Activity 4.17 Specifying a Required Field
    Activity 4.18 Setting Default Values for Fields
    Activity 4.19 Indexing Fields in a Table
Objective 7: Create Data Validation Rules and Validation Text
    Activity 4.20 Creating Data Validation Rules and Validation Text
Objective 8: Create a Lookup Field
    Activity 4.21 Creating a Lookup Field Based on Data in Another Table
    Activity 4.22 Creating a Lookup Field Based on a List of Values
Objective 9: Attach Files to Records
    Activity 4.23 Attaching a Word Document to a Record
 
Chapter 5 Enhancing Queries
Scenario: Board Anywhere Surf and Snowboard Shop

Project 5A – Inventory

Objective 1: Create Calculated Fields
    Activity 5.1 Creating a Calculated Field Based on Two Existing Fields
    Activity 5.2 Creating a Calculated Field Based on One Existing Field and a Number
Objective 2: Use Aggregate Functions
    Activity 5.3 Adding a Total Row to a Query
    Activity 5.4 Creating a Totals Query
Objective 3: Create a Crosstab Query
    Activity 5.5 Creating a Select Query as the Source for the Crosstab Query
    Activity 5.6 Creating a Crosstab Query
Objective 4: Find Duplicate and Unmatched Records
    Activity 5.7 Finding Duplicate Records
    Activity 5.8 Finding Unmatched Records
Objective 5: Create a Parameter Query
    Activity 5.9 Creating a Parameter Query Using One Criteria
    Activity 5.10 Creating a Parameter Query Using Multiple Criteria

Project 5B – Orders

Objective 6: Create a Make Table Query
    Activity 5.11 Creating a Select Query
    Activity 5.12 Converting a Select Query to a Make Table Query
Objective 7: Create an Append Query
    Activity 5.13 Creating an Append Query for a Table in the Current Database
    Activity 5.14 Creating an Append Query for a Table in Another Database
Objective 8: Create a Delete Query
    Activity 5.15 Creating a Delete Query
Objective 9: Create an Update Query
    Activity 5.16 Creating an Update Query
Objective 10: Modify the Join Type
    Activity 5.17 Viewing the Results of a Query Using an Inner Join
    Activity 5.18 Changing the Join Type to an Outer Join
 
Chapter 6 Customizing Forms and Reports
Scenario: Wild Islands Breeze

Project 6A – Franchises

Objective 1: Create a Form in Design View
    Activity 6.1 Creating a Form in Design View
    Activity 6.2 Adding Sections to the Form
Objective 2: Change and Add Controls
    Activity 6.3 Changing Controls on a Form
    Activity 6.4 Adding Controls to a Form
Objective 3: Format Form
    Activity 6.5 Adding a Background Color
    Activity 6.6 Adding a Background Picture to a Form
    Activity 6.7 Modifying the Borders of Controls
Objective 4: Make a Form User Friendly
    Activity 6.8 Adding a Message to the Status Bar
    Activity 6.9 Creating Custom ControlTips
    Activity 6.10 Changing the Tab Order

Project 6B – Wild Islands Breeze (WIB)

Objective 5: Create a Report in Design View
    Activity 6.11 Creating a Report in Design View
    Activity 6.12 Modifying the Sections of a Report
Objective 6: Add Controls to a Report
    Activity 6.13 Add Label and Text Box Controls to a Report
    Activity 6.14 Adding an Image Control and a Line Control to a Report
Objective 7: Group, Sort, and Total Records in Design View
    Activity 6.15 Adding a Grouping and Sort Level to a Report
    Activity 6.16 Adding Calculated Controls to a Report
    Activity 6.17 Creating Calculated Fields in a Report
Objective 8: Create a Crosstab Report
    Activity 6.17 Creating a Crosstab Report
    Activity 6.18 Modifying a Crosstab Report
 
Chapter 7Creating Advanced Forms and Reports
Scenario: Southwest Gardens

Project 7A – TV Shows

Objective 1: Create a Split Form
    Activity 7.1 Creating a Split Form Using the Split Form Tool
    Activity 7.2 Formatting a Split Form
    Activity 7.3 Converting an Existing Form into a Split Form
Objective 2: Create a Form and a Subform
    Activity 7.4 Creating a Form and a Subform Using the Form Tool
    Activity 7.5 Creating a Form and a Subform Using the Form Wizard
    Activity 7.6 Creating a Subform by Dragging a Related Table onto An Existing Form
Objective 3: Create a Multi-Page Form
    Activity 7.7 Creating a Multi-Page Form Using the Tab Control

Project 7B – Web Site Orders

Objective 4: Create and Modify a Subreport
    Activity 7.8 Using the Subreport Wizard to Create a Subreport
    Activity 7.9 Modifying a Subreport
    Activity 7.10 Creating a Subreport by Adding an Object to an Existing Report
    Activity 7.11 Displaying a Total from a Subreport on the Main Report
Objective 5: Create a Report Based on a Parameter Query
    Activity 7.12 Creating a Report Based on a Parameter Query
    Activity 7.13 Printing the Parameters in the Report
Objective 6: Create an Alphabetic Index
    Activity 7.16 Creating an Alphabetic Index
 
Chapter 8 Creating Macros, PivotTables, and PivotCharts
Scenario: Providence and Warwick Medical Center

Project 8A – Benefits Information Sessions

Objective 1: Create a Standalone Macro with One Action
    Activity 8.1 Creating a Standalone Macro
    Activity 8.2 Opening a Form in Its Own Window
    Activity 8.3 Creating a Second Standalone Macro That Automatically Executes
Objective 2: Add Multiple Actions to a Standalone Macro
    Activity 8.4 Adding Multiple Actions to an Existing Standalone Macro
Objective 3: Create a Macro Group
    Activity 8.5 Creating the First Macro in a Macro Group
    Activity 8.6 Creating a Second Macro in a Macro Group
    Activity 8.7 Creating a Third Macro in a Macro Group
Objective 4: Associate a Macro with an Event
    Activity 8.8 Associating a Command Button with a Macro
Objective 5: Create an Embedded Macro
    Activity 8.9 Creating an Embedded Macro
Objective 6: Print Macro Details
    Activity 8.10 Printing Macro Details

Project 8B – Nursing Salaries

Objective 7: Create a PivotTable from a Query
    Activity 8.11 Creating a PivotTable from a Query
    Activity 8.12 Pivoting the Data and Adding Totals
    Activity 8.13 Removing and Adding Fields from the PivotTable
Objective 8: Create a PivotChart from a PivotTable
    Activity 8.14 Creating a PivotChart from a PivotTable
 
Chapter 9 Integrating Access with Other Applications
Scenario: Penn Liberty Motors

Project 9A – Penn Liberty Motors

Objective 1: Import Data from a Word Table
    Activity 9.1 Prepare a Word Table for Importing
    Activity 9.2 Importing Data from a Word Table
Objective 2: Use Mail Merge to Integrate Access and Word
    Activity 9.3 Merging an Access Table with a Word Document
Objective 3: Import Data from an Excel Workbook
    Activity 9.4 Importing Data from an Excel Worksheet
    Activity 9.5 Appending Data from Excel to a Table
Objective 4: Link an Excel Chart to a Report
    Activity 9.6 Create a Query and a Report
    Activity 9.7 Linking an Excel Chart to a Report
Objective 5: Import from and Link to Another Access Database
    Activity 9.8 Importing Data from Another Access Database
    Activity 9.9 Linking to a Table in Another Access Database


Project 9B – Used Car Inventory

Objective 6: Export Data to Word
    Activity 9.10 Export an Access Query to Word
    Activity 9.11 Export an Access Report to Word
Objective 7: Export Data to Excel
    Activity 9.12 Export Selected Records to Excel
    Activity 9.13 Copying Selected Records to an Existing Excel Workbook
Objective 8: Export Data to an HTML File and an XML File
    Activity 9.14 Export a Report to an HTML File
    Activity 9.15 Export a Report to an XML File

 
Chapter 10 Administering Databases
Scenario: Image Medtech

Project 10A – Customers

Objective 1: Compact and Repair a Database
    Activity 10.1 Compacting and Repairing a Database
Objective 2: Back Up a Database
    Activity 10.2 Backing Up a Database
Objective 3: Use the Database Splitter
    Activity 10.3 Splitting a Database
Objective 4: Convert Databases to Other Versions
    Activity 10.4 Creating a Secure ACCDE File
    Activity 10.5 Converting an Access 97 Database
    Activity 10.6 Converting to a 2002-2003 Database
Objective 5: Replicate and Synchronize a Database
    Activity 10.7 Creating a Replica of a Database
    Activity 10.8 Synchronizing a Database

Project 10B – Invoices

Objective 6: Use Microsoft Analysis Tools
    Activity 10.9 Using the Table Analyzer
    Activity 10.10 Using the Performance Analyzer
    Activity 10.11 Viewing Object Dependencies
    Activity 10.12 Using the Database Documenter
Objective 7: Add Smart Tags
    Activity 10.13 Adding Smart Tags
Objective 8: Modify Access Views and Behaviors
    Activity 10.14 Modifying Access Options
    Activity 10.15 Customizing the Quick Access Toolbar
    Activity 10.16 Setting Current Database Options
    Activity 10.17 Customizing the Navigation Pane


Chapter 11 Securing Databases and Writing SQL Statements
Scenario: DeLong Grant Law Partners

Project 11A – Lawyers

Objective 1: Set a Database Password
    Activity 11.1 Encrypting a Database with a Password
    Activity 11.2 Encoding and Password Protecting a 2003 Database
Objective 2: Secure the Access Administrator Account
    Activity 11.3 Securing the Administrator Account
Objective 3: Create an Access Administrative User
    Activity 11.4 Adding an Administrative User Account
Objective 4: Create Users and Groups
    Activity 11.5 Creating a Group and Adding Users
Objective 5: Change Ownership and Assign Permissions to Database Objects
    Activity 11.6 Assigning Permissions to Database Objects
    Activity 11.7 Changing Ownership of Database Objects
Objective 6: Test and Reset Default Security Settings
    Activity 11.8 Testing Default Security Settings
    Activity 11.9 Copying and Resetting Default Security Settings
Objective 7: Create and Modify Workgroups Using the Security Wizard
    Activity 11.10 Adding Users and Groups in a New Workgroup Information File
    Activity 11.11 Testing the New Workgroup Security Settings

Project 11B – Overdue Accounts

Objective 8: Modify a Query in SQL View
    Activity 11.12 Modifying a Query in SQL View
Objective 9: Create a Query in SQL View
    Activity 11.13 Creating an SQL Statement
    Activity 11.14 Specifying the Join Type in SQL
Objective 10: Create a Union Query Using SQL
    Activity 11.15 Creating a Union Query in SQL View
Objective 11: Create Calculated Fields and SQL Aggregate Functions
    Activity 11.16 Creating Calculated Fields in SQL
    Activity 11.17 Writing SQL Aggregate Function

 
Chapter 12 Customizing Access Using Visual Basic for Applications
Scenario: Cross Oceans Music

Project 12A – Compact Discs

Objective 1: Modify an Existing VBA Module
    Activity 12.1 Opening and Viewing an Existing VBA Module
    Activity 12.2 Documenting Code with Comments
    Activity 12.3 Editing an Existing VBA Procedure
Objective 2: Debug and Test VBA Code
    Activity 12.4 Debugging and Testing VBA Code
    Activity 12.5 Debugging Run-Time Errors
Objective 3: Write an Event Procedure
    Activity 12.6 Writing an Event Procedure
Objective 4: Use Variables, Properties, and Methods
    Activity 12.7 Declaring Variables and Assigning Values to Variables
    Activity 12.8 Changing Form Properties and Using Methods

Project 12B – Shipping

Objective 5: Prompt for User Input
    Activity 12.9 Prompting the User for Input
    Activity 12.10 Testing User Input
Objective 6: Write Control Structures
    Activity 12.11 Writing an If Then Statement
    Activity 12.12 Writing a Select Case Statement
Objective 7: Perform Calculations and Make Comparisons
    Activity 12.13 Performing Calculations and Comparing Values
Objective 8: Add a Subroutine to Trap Errors
    Activity 12.14 Adding a Subroutine to Trap Errors