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
