Microsoft Access 2007 Comprehensive

Table of Contents

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

Read More