Exploring MS Office Access Comprehensive 2003 - Revised Edition

Dr. Robert T. Grauer        Dr. Robert T. Grauer is an Associate Professor in the Department of Computer Information Systems at the University of Mniami where he has been honored with the Outstanding Teacher Award in the School of Business. Dr. Grauer has written more than 20 books on programming and information systems. His work has been translated into three foreign languages and is in use at all levels of higher education at both the national and international level. Dr. Grauer has also been a consultant to several major corporations including IBM and American Express. He received his Ph.D. in Operations Research in 1972 from the Polytechnic Institute of Brooklyn.   Maryann M. Barber Maryann M. Barber is a lecturer in the Department of Computer Information Systems at the University of Miami. She has been honored with several teaching awards, including the Excellence in Teaching Awards for the School of Business, the Freshman Teaching Award, the Apple Polishing Award, the Student Government Professor of the Year Award, the Interfraternity Council Professor of the Year Award, and the University of Miami Excellence in Teaching Award. Maryann is also the Faculty Master at Stanford Residential College. She is a member of numerous leadership honor societies at the University of Miami, including Iron Arrow, Mortar Board, and Omicron Delta Kappa.

 

Chapter 1: Introduction to Access: What Is a Database?



Objectives

Case Study: The South Vancouver Preschool

The College Bookstore

Introduction to Microsoft Access

The Database Window

Tables

Forms, Queries, and Reports

HANDS-ON EXERCISE 1: INTRODUCTION TO ACCESS

Filters and Sorting

HANDS-ON EXERCISE 2: FILTERS AND SORTING

Looking Ahead: A Relational Database

HANDS-ON EXERCISE 3: A LOOK AHEAD

Summary

Key Terms

Multiple Choice

Practice Exercises

Mini Cases

 

Chapter 2: Tables and Forms: Design, Properties, Views, and Wizards

Objectives

Case Study: Debbie's Fine Fashions

A Student Database

Include the Necessary Data

Store Data in Its Smallest Parts

Avoid Calculated Fields

Creating a Table

            Primary Key

            Views

            Properties

HANDS-ON EXERCISE 1: CREATING A TABLE

Forms

Properties

AutoForms and the Form Wizard

Modifying a Form

HANDS-ON EXERCISE 2: CREATING A FORM

A More Sophisticated Form

HANDS-ON EXERCISE 3: A MORE SOPHISTICATED FORM

Summary

Key Terms

Multiple Choice

Practice Exercises

Mini Cases

 

Chapter 3: Information from the Database: Reports and Queries

Objectives

Case Study: The Garden Club

Data Versus Information

Reports

Anatomy of a Report

The Report Wizard

Apply What You Know

Bound, Unbound, and Calculated Controls

HANDS-ON EXERCISE 1: THE REPORT WIZARD

Introduction to Queries

Query Design View

Selection Criteria

HANDS-ON EXERCISE 2: CREATING A SELECT QUERY

Access Functions and Calculated Controls

Grouping Records

HANDS-ON EXERCISE 3: GROUPING RECORDS

Crosstab Queries

Action Queries

HANDS-ON EXERCISE 4: CROSSTAB AND ACTION QUERIES

Summary

Key Terms

Multiple Choice

Practice Exercises

Mini Cases

 

Chapter 4: Proficiency: Relational Databases, Pivot Charts, and the Switchboard

Objectives

Case Study: The Disc Jockey

The Investment Database

Multiple-table Queries

Maintaining the Database

The Import Spreadsheet Wizard

HANDS-ON EXERCISE 1: IMPORTING DATA FROM EXCEL

Total Queries

Microsoft Graph

Pivot Tables and Pivot Charts

HANDS-ON EXERCISE 2: TOTAL QUERIES, CHARTS, AND PIVOT TABLES

The User Interface

The Switchboard Manager

Other Access Utilities

HANDS-ON EXERCISE 3: THE SWITCHBOARD MANAGER

Summary

Key Terms

Multiple Choice

Practice Exercises

Mini Cases

 

Chapter 5: One-to-many Relationships: Subforms and Multiple-table Queries

Objectives

Case Study: The Residential Colleges

A Database for Consumer Loans

The AutoNumber Field Type

Referential Integrity

Implementation in Access

HANDS-ON EXERCISE 1: ONE-TO-MANY RELATIONSHIPS

Subforms

The Form Wizard

HANDS-ON EXERCISE 2: CREATING A SUBFORM

Multiple-table Queries

HANDS-ON EXERCISE 3: QUERIES AND REPORTS

Expanding the Database

Multiple Subforms

HANDS-ON EXERCISE 4: LINKED SUBFORMS

Summary

Key Terms

Multiple Choice

Practice Exercises

Mini Cases

 

Chapter 6: Many-to-many Relationships: A More Complex System

Objectives

Case Study: At Your Service

The Computer Superstore

The AutoNumber Field Type

The Relationships Window

HANDS-ON EXERCISE 1: RELATIONSHIPS AND REFERENTIAL INTEGRITY

Subforms, Queries, and AutoLookup

HANDS-ON EXERCISE 2: SUBFORMS AND MULTIPLE-TABLE QUERIES

Parameter Queries

Total Queries

Learning by Doing

HANDS-ON EXERCISE 3: ADVANCED QUERIES

Expanding the Database

The Sales Commission Query

HANDS-ON EXERCISE 4: EXPANDING THE DATABASE

Summary

Key Terms

Multiple Choice

Practice Exercises

Mini Cases

 

Chapter 7: Building Applications: Macros and a Multilevel Switchboard

Objectives

Case Study: The Movie Studio

A Recreational Sports League

The Switchboard Manager

The Linked Tables Manager

HANDS-ON EXERCISE 1: THE SWITCHBOARD MANAGER

Introduction to Macros

The Macro Window

The AutoExec Macro

Debugging

Application Development

HANDS-ON EXERCISE 2: MACROS AND PROTOTYPING

The Player Draft

Macro Groups

HANDS-ON EXERCISE 3: THE PLAYER DRAFT

Summary

Key Terms

Multiple Choice

Practice Exercises

Mini Cases

 

Chapter 8: Creating More Powerful Applications: Introduction to VBA

Objectives

Case Study: Return to the Auction

Introduction to VBA

A Better Student Form

Modules and Procedures

HANDS-ON EXERCISE 1: CREATING A COMBO BOX AND

ASSOCIATED VBA PROCEDURE

Facilitating Data Entry

HANDS-ON EXERCISE 2: FACILITATING DATA ENTRY

Error Trapping

HANDS-ON EXERCISE 3: ERROR TRAPPING

Data Validation

HANDS-ON EXERCISE 4: DATA VALIDATION

Summary

Key Terms

Multiple Choice

Practice Exercises

Mini Cases

 

Chapter 9: Design and Implementation: A Capstone Chapter

Objectives

Case Study: The Group Project

The Development Cycle

From Output to Input

Database Design

HANDS-ON EXERCISE 1: DATABASE DESIGN

Detailed Design

AutoNumber and Field Type

Referential Integrity and Required fields

HANDS-ON EXERCISE 2: THE DETAILED DESIGN

The Visual Design - Version 1.0

The Templates

HANDS-ON EXERCISE 3: THE VISUAL DESIGN

A Basic Form and Report

HANDS-ON EXERCISE 4: A BASIC FORM AND REPORT

Prototyping - "Complete the System"

Macros - A Quick Review

HANDS-ON EXERCISE 5: PROTOTYPING - COMPLETE THE SYSTEM

Add The Reports

HANDS-ON EXERCISE 6: ADD THE REPORTS

The Orders Form

HANDS-ON EXERCISE 7: THE ORDERS FORM

Security Issues

HANDS-ON EXERCISE 8: SUBMISSION TO THE CLIENT

Summary

Key Words and Concepts

Multiple Choice

Practice Exercises

Mini Case

 

Appendix A: The Design of a Relational Database

Objectives

Overview

Case Study: Fast Food Franchises

Single Table Solution

Multiple Table Solution

Queries to the Database

The Relational Model

Case Study: Student Transcripts

Introduction to Normalization

Case Study: Order Entry

The Normalization Process

An Easy Way to Third Normal Form

Queries to the Database

Summary

Key Terms

Multiple Choice

 

Appendix B: Toolbars

 

Appendix C: Mail Merge

Overview

 

Appendix D: Using XML with Microsoft® Access 2003

Overview

Introduction to XML

HANDS-ON EXERCISE 1: INTRODUCTION TO XML

Microsoft Access and XML

HANDS-ON EXERCISE 2: ACCESS AND XML

Summary

Key Terms

Multiple Choice

 

 

Getting Started with Microsoft® Windows XP

Objectives

Case Study: Unforeseen Circumstances

Welcome to Windows XP

The Desktop

Moving and Sizing a Window

Anatomy of a Window

Pull-down Menus

Dialog Boxes

Help and Support Center

HANDS-ON EXERCISE 1: WELCOME TO WINDOWS XP

Files and Folders

The Exploring Office Practice Files

Connecting to the Internet

HANDS-ON EXERCISE 2: DOWNLOAD THE PRACTICE FILES

Windows Explorer

Personal Folders

Moving and Copying a File

Deleting a File

Backup

HANDS-ON EXERCISE 3: WINDOWS EXPLORER

Increasing Productivity

The Control Panel

Shortcuts

The Search Companion

HANDS-ON EXERCISE 4: INCREASING PRODUCTIVITY

Fun with Windows XP

Windows Media Player

Digital Photography

Windows Messenger

HANDS-ON EXERCISE 5: FUN WITH WINDOWS XP

Summary

Key Terms

Multiple Choice

Practice with Windows XP

Mini Cases

 

Getting Started with VBA: Extending Microsoft Office 2003

Objectives

Case Study: On-the-Job Training

Introduction to VBA

The MsgBox Statement

The InputBox Function

Declaring Variables

The VBA Editor

HANDS-ON EXERCISE 1: INTRODUCTION TO VBA

If . . . Then . . . Else Statement

Case Statement

Custom Toolbars

HANDS-ON EXERCISE 2: DECISION MAKING

For . . . Next Statement

Do Loops

Debugging

HANDS-ON EXERCISE 3: LOOPS AND DEBUGGING

Putting VBA to Work (Microsoft Excel)

HANDS-ON EXERCISE 4: EVENT-DRIVEN PROGRAMMING (MICROSOFT EXCEL)

Putting VBA to Work (Microsoft Access)

HANDS-ON EXERCISE 5: EVENT-DRIVEN PROGRAMMING (MICROSOFT ACCESS)

Summary

Key Terms

Multiple Choice