Exploring Microsoft Office Access 2010 Comprehensive

For introductory computer courses on Microsoft Office 2010 or courses in computer concepts with a lab component for Microsoft Office 2010 applications. The goal of theExploringseries has been to move students beyond the point and click, helping them understand the why and how behind each skill. TheExploringseries for Office 2010 also enables students to extend the learning beyond the classroom. Students go to college now with a different set of skills than they did five years ago. With this in mind, theExploringseries seeks to move students beyond the basics of the software at a faster pace, without sacrificing coverage of the fundamental skills that everybody needs to know. A lot of learning takes place outside of the classroom, and theExploringseries provides learning tools that students can access anytime, anywhere.

Dr. Robert T. Grauer, Creator of the Exploring Series

Bob Grauer is an Associate Professor in the Department of Computer Information Systems at the University of Miami, where he is a multiple winner of the Outstanding Teaching Award in the School of Business, most recently in 2009. He has written numerous COBOL texts and is the vision behind the Exploring Office series, with more than three million books in print. His work has been translated into three foreign languages and is used in all aspects of higher education at both national and international levels. Bob Grauer has consulted for several major corporations including IBM and American Express. He received his Ph.D. in operations research in 1972 from the Polytechnic Institute of Brooklyn.

 

 

Keith Mast, Access Author

Keith A. Mast develops a wide range of Access applications that solve challenging business problems and

improve efficiency. His solutions help businesses and organizations in manufacturing, pharmaceutical,

financial services, and agriculture, among other industries. Clients include Visible Filing Concepts, Inc.;

Moyer’s Chicks, Inc.; Marcho Farms, Inc.; TCM America, Inc.; LANsultants, Inc.; Spector, Roseman,

Kodroff, & Willis, PC; Heinz, Inc.; DuPont; and Sony Entertainment. He is an adjunct faculty member at Montgomery County Community College, Blue Bell, Pennsylvania, continuing his long-standing love of teaching and exemplified by his prior experience as a high school teacher, business school instructor, and Access seminar leader.

 

Keith promotes the ethical standards of the consulting profession both in how Mast Consulting, LLC deals with its clients and as an active volunteer leader of the Independent Computer Consultants Association of the Delaware Valley.

 

Keith resides in Norristown, PA, a suburb of Philadelphia. In his free time, he enjoys biking, running,

kayaking, ballroom dancing, and being in nature. For more information, visit him on Facebook, LinkedIn, or at www.keithmast.com.

 

 

Mary Anne Poatsy, Series Editor

Mary Anne is a senior faculty member at Montgomery County Community College, teaching various

computer application and concepts courses in face-to-face and online environments. She holds a B.A.

in psychology and education from Mount Holyoke College and an M.B.A. in finance from Northwestern

University’s Kellogg Graduate School of Management.

 

Mary Anne has more than 12 years of educational experience. She is currently adjunct faculty at

Gwynedd-Mercy College and Montgomery County Community College. She has also taught at Bucks

County Community College and Muhlenberg College, as well as conducted professional training. Before

teaching, she was vice president at Shearson Lehman in the Municipal Bond Investment Banking

Department.

 

 

Dr. Lynn Hogan, Office Fundamentals and File Management and

Windows 7 Author

Lynn Hogan has taught in the Computer Information Systems area at Calhoun Community College for

29 years. She is the author of Practical Computing and has contributed chapters for several computer

applications textbooks. Primarily teaching in the areas of computer literacy and computer applications,

she was named Calhoun’s outstanding instructor in 2006. She received an M.B.A. from the University of

North Alabama and a Ph.D. from the University of Alabama. Lynn resides in Alabama with her husband

and two daughters.

 

OFFICE FUNDAMENTALS AND FILE MANAGEMENT

CHAPTER ONE ¿ Office Fundamentals and File Management

CASE STUDY: RAILS AND TRAILS

OBJECTIVES

FILES AND FOLDERS

Using Windows Explorer

Working with Folders and Files

Selecting, Copying, and Moving Multiple Files and Folders

HANDS-ON EXERCISE 1: Files and Folders

MICROSOFT OFFICE SOFTWARE

Identifying Common Interface Components

Getting Office Help

HANDS-ON EXERCISE 2: Microsoft Office Software

BACKSTAGE VIEW TASKS

Opening a File

Printing a File

Closing a File and Application

HANDS-ON EXERCISE 3: Backstage View Tasks

HOME TAB TASKS

Selecting and Editing Text

Using the Clipboard Group Tasks

Using the Editing Group Tasks

HANDS-ON EXERCISE 4: Home Tab Tasks

INSERT TAB TASKS

Inserting Objects

REVIEW TAB TASKS

Reviewing a File

PAGE LAYOUT TAB TASKS

Changing Page Settings

HANDS-ON EXERCISE 5: Tasks on the Insert Tab, Page Layout Tab, and Review Tab

CHAPTER OBJECTIVES REVIEW

KEY TERMS

MULTIPLE CHOICE

PRACTICE EXERCISES

MID-LEVEL EXERCISES

CAPSTONE EXERCISE

 

MICROSOFT OFFICE ACCESS 2010

CHAPTER ONE ¿ Introduction to Access

CASE STUDY: MANAGING A BUSINESS IN THE GLOBAL ECONOMY

OBJECTIVES

DATABASES ARE EVERYWHERE!

Navigating Among the Objects in an Access Database

Understanding the Difference Between Working in Storage and Memory

Practicing Good Database File Management

Backing Up, Compacting, and Repairing Access Files

HANDS-ON EXERCISE 1: Databases Are Everywhere!

FILTERS, SORTS, AND ACCESS VERSUS EXCEL

Creating Filters

Sorting Table Data on One or More Fields

Knowing When to Use Access or Excel to Manage Data

HANDS-ON EXERCISE 2: Filters, Sorts, and Access Versus Excel

RELATIONAL DATABASE

Using the Relationships Windows

Understanding Relational Power

HANDS-ON EXERCISE 3:

Relational Database

CHAPTER OBJECTIVES REVIEW

KEY TERMS

MULTIPLE CHOICE

PRACTICE EXERCISES

MID-LEVEL EXERCISES

CAPSTONE EXERCISE

BEYOND THE CLASSROOM

 

CHAPTER TWO ¿ Relational Databases and Queries

CASE STUDY: BANK AUDITOR UNCOVERS MIDHANDLED

FUNDS

OBJECTIVES

TABLE DESIGN, PROPERTIES, VIEWS, AND WIZARDS

Designing Data

Creating Tables

HANDS-ON EXERCISE 1: Table Design, Properties, Views, and Wizards

MULTIPLE TABLE DATABASES

Understanding Table Relationships

Sharing Data with Excel

Establishing Table Relationships

HANDS-ON EXERCISE 2: Multiple Table Databases

SINGLE-TABLE QUERIES

Creating a Single-Table Query

Specifying Criteria for Different Data Types

Copying and Running a Query

Using the Query Wizard

HANDS-ON EXERCISE 3: Single-Table Queries

MULTI-TABLE QUERIES

Creating a Multi-Table Query

Modifying a Multi-Table Query

HANDS-ON EXERCISE 4: Multi-Table Queries

CHAPTER OBJECTIVES REVIEW

KEY TERMS

MULTIPLE CHOICE

PRACTICE EXERCISES

MID-LEVEL EXERCISES

CAPSTONE EXERCISE

BEYOND THE CLASSROOM

 

 

CHAPTER THREE ¿ Customize, Analyze, and Summarize Query Data

CASE STUDY: HOUSING SLUMP MEANS OPPORTUNITY

FOR COLLEGE STUDENTS

OBJECTIVES

CALCULATIONS, EXPRESSIONS, AND FUNCTIONS

Understanding the Order of Operations

Creating a Calculated Field in a Query

HANDS-ON EXERCISE 1: Calculations, Expressions, and Functions

EXPRESSION BUILDER, FUNCTIONS, AND DATE ARITHMETIC

Creating Expressions with the Expression Builder

Using Built-In Functions in Access

Performing Date Arithmetic

HANDS-ON EXERCISE 2: Expression Builder, Functions, and Date Arithmetic

AGGREGATE FUNCTIONS

Adding Aggregate Functions to Datasheets and Queries

HANDS-ON EXERCISE 3: Aggregate Functions

CHAPTER OBJECTIVES REVIEW

KEY TERMS

MULTIPLE CHOICE

PRACTICE EXERCISES

MID-LEVEL EXERCISES

CAPSTONE EXERCISE

BEYOND THE CLASSROOM

 

CHAPTER FOUR ¿ Creating and Using Professional Forms and Reports

CASE STUDY: COFFEE SHOP STARTS NEW BUSINESS

OBJECTIVES

FORM BASICS

Creating Forms Using the Form Tools

Modifying a Form

Sorting Records in a Form

HANDS-ON EXERCISE 1: Form Basics

FORM SECTIONS, VIEWS, AND CONTROLS

Identifying Form Sections

Revising Forms Using Form Views

Identifying Control Types in Forms

HANDS-ON EXERCISE 2: Form Sections, Views, and Controls

REPORT BASICS

Creating Reports Using Report Tools

Modifying a Report

Sorting Records in a Report

HANDS-ON EXERCISE 3: Report Basics

REPORT SECTIONS, VIEWS, AND CONTROLS

Identifying Report Sections

Revising Reports Using Report Views

Identifying Control Types in Reports

HANDS-ON EXERCISE 4: Report Sections, Views, and Controls

CHAPTER OBJECTIVES REVIEW

KEY TERMS

MULTIPLE CHOICE

PRACTICE EXERCISES

MID-LEVEL EXERCISES

CAPSTONE EXERCISE

BEYOND THE CLASSROOM

 

 

CHAPTER FIVE ¿ PivotTables and PivotCharts

CASE STUDY: RETAINING EMPLOYEES IN TOUGH TIMES

OBJECTIVES

DATA MINING WITH PIVOTTABLES

Creating a PivotTable

Adding Aggregate Functions to a PivotTable

Modifying a PivotTable

HANDS-ON EXERCISE 1: Data Mining with PivotTables

CHARTS CONVEY INFORMATION

Creating a PivotChart

Identifying Chart Elements

Modifying a PivotChart

HANDS-ON EXERCISE 2: Charts Convey Information

CALCULATIONS IN PIVOTTABLES

Adding Calculations to a PivotTable

Working with Calculations in a PivotTable

HANDS-ON EXERCISE 3: Calculations in PivotTables

CHAPTER OBJECTIVES REVIEW

KEY TERMS

MULTIPLE CHOICE

PRACTICE EXERCISES

MID-LEVEL EXERCISES

CAPSTONE EXERCISE

BEYOND THE CLASSROOM

 

CHAPTER SIX ¿ Data Validation Techniques

CASE STUDY: MANAGER FIRED OVER DATA ENTRY ERRORS

OBJECTIVES

DATA VALIDATION IN TABLES

Establishing Data Validation

Creating an Input Mask

Creating and Modifying a Lookup Field

HANDS-ON EXERCISE 1: Data Validation in Tables

DATA VALIDATION IN FORMS AND DATA MACROS

Setting the Tab Order

Creating a Combo Box Control

Restricting Edits in a Form

Comparing Fields Using a Data Macro

HANDS-ON EXERCISE 2: Data Validation in Forms and Data Macros

DATA VALIDATION WITH QUERIES AND REPORTS

Creating a Query to Display Missing Data

Creating a Missing Data Report

Creating an Aggregate Report

HANDS-ON EXERCISE 3: Data Validation with Queries and Reports

CHAPTER OBJECTIVES REVIEW

KEY TERMS

MULTIPLE CHOICE

PRACTICE EXERCISES

MID-LEVEL EXERCISES

CAPSTONE EXERCISE

BEYOND THE CLASSROOM

 

CHAPTER SEVEN ¿ Advanced Queries

CASE STUDY: REPLACEMENTS, LTD.

OBJECTIVES

ACTION QUERIES

Determining When to Use an Action Query

Updating Data with an Update Query

Adding Records to a Table with an Append Query

Creating a Table with a Make Table Query

Deleting Records with a Delete Query

HANDS-ON EXERCISE 1: Action Queries

QUERIES FOR SPECIAL CONDITIONS

Creating a Parameter Query to Provide Flexibility

Summarizing Data with a Crosstab Query

Finding Unmatched Records with a Query

Finding Duplicate Records with a Query

HANDS-ON EXERCISE 2: Queries for Special Conditions

CHAPTER OBJECTIVES REVIEW

KEY TERMS

MULTIPLE CHOICE

PRACTICE EXERCISES

MID-LEVEL EXERCISES

CAPSTONE EXERCISE

BEYOND THE CLASSROOM

 

CHAPTER EIGHT ¿ Get Connected

CASE STUDY: DATA EXCHANGE

OBJECTIVES

CONNECTING ACCESS TO EXTERNAL FILES

Creating a Hyperlink Field

Adding an Attachment Field

Adding Attachment Controls to Forms and Reports

HANDS-ON EXERCISE 1: Connecting Access to External Files

EXPORTING DATA TO OFFICE APPLICATIONS

Exporting Data to Excel

Exporting Data to Word

Exporting Data to a PDF or XPS Document

Exporting Objects to Another Access Database

HANDS-ON EXERCISE 2: Exporting Data to Office Applications

IMPORTING DATA INTO ACCESS

Linking to an Excel Spreadsheet

Linking to an Access Table

Importing a Text File

HANDS-ON EXERCISE 3: Importing Data into Access

CHAPTER OBJECTIVES REVIEW

KEY TERMS

MULTIPLE CHOICE

PRACTICE EXERCISES

MID-LEVEL EXERCISES

CAPSTONE EXERCISE

BEYOND THE CLASSROOM

 

 

CHAPTER NINE ¿ Fine-Tuning the Database

CASE STUDY: THE METROPOLITAN ZOO

OBJECTIVES

NORMALIZING DATABASE TABLES

Verifying First Normal Form

Verifying Second Normal Form

Verifying Third Normal Form

HANDS-ON EXERCISE 1: Normalizing Database Tables

USING THE BUILT-IN ANALYSIS AND DESIGN TOOLS

Using the Database Documenter Tool

Using the Performance Analyzer Tool

Using the Table Analyzer Tool

Using the Database Splitter Tool

HANDS-ON EXERCISE 2: Using the Built-In Analysis and Design Tools

SAFEGUARDING YOUR DATABASE APPLICATION

Creating a Menu System

Encrypting and Password-Protecting a Database

Digitally Signing and Publishing a Database

Saving a Database as an ACCDE File

HANDS-ON EXERCISE 3: Safeguarding Your Database Application

CHAPTER OBJECTIVES REVIEW

KEY TERMS

MULTIPLE CHOICE

PRACTICE EXERCISES

MID-LEVEL EXERCISES

CAPSTONE EXERCISE

BEYOND THE CLASSROOM

 

CHAPTER TEN ¿ Using Macros and SQL in Access

CASE STUDY: RETIREMENT PLAN HELPS RETAIN EMPLOYEES

OBJECTIVES

WORKING WITH MACROS

Understanding the Purpose of a Macro

Creating a Stand-Alone Macro

Using the Macro Builder

Attaching an Embedded Macro to an Event

HANDS-ON EXERCISE 1: Working with Macros

WORKING WITH DATA MACROS

Identifying When to Use a Data Macro

Creating an Event-Driven Data Macro

Creating a Named Data Macro

HANDS-ON EXERCISE 2: Working with Data Macros

USING STRUCTURED QUERY LANGUAGE

Understanding the Fundamentals of SQL

Interpreting an SQL Select Statement

Using an SQL Select Statement as a Record Source

HANDS-ON EXERCISE 3: Using Structured Query Language

CHAPTER OBJECTIVES REVIEW

KEY TERMS

MULTIPLE CHOICE

PRACTICE EXERCISES

MID-LEVEL EXERCISES

CAPSTONE EXERCISE

BEYOND THE CLASSROOM

 

 

GLOSSARY

INDEX