0 r e t 1 r 0 a C 2 n h o s J s | e z e c r a c u J a n o J e c fi f o M D D ® A L t I M # f 11 0 2 o 5 9 0 0 7 s /18 /1 0 C o Y A N M r A G c Y E L O i B m L K S P O T U V SimNet for Office 2010 Online training and assessment Teach Me, Show Me, Let Me Try! INCLUDES: ■ Microsoft® Office Suite ■ Microsoft® Outlook ■ Windows XP ■ Windows Vista ■ Windows 7 ■ Internet Explorer 7 ■ Internet Explorer 8 ■ Computer Concepts Since 1999, instructors have been using SimNet to measure student outcomes in a virtual Microsoft® Office environment. Now completely online, with nothing to install, students can practice and study their skills at home or in the school lab. Moreover, this resource is an ideal course solution but even more valuable as it can be used beyond the course for self-study! IT’S EASY! SIMGRADER for OFFICE 2010 . . . YOUR PROJECT LIBRARY! SimNet is an EASY & INTUITIVE, true turn-key design. Instructors can quickly and efficiently assign content SimGrader is the most recent addition to SimNet Online, around the needs of your course, edit throughout the McGraw-Hill’s online training and assessment software semester and copy to multiple sections and instructors! for Microsoft Office skills and more. This online program SimNet is scannable so students can quickly scan the provides automatic grading of projects for Microsoft tasks in a lesson to identify the skills they know and the Office Suite and can be used seamlessly within SimNet M ones they don’t . . . saving them time! Online or can be used separately if needed. Available D D with a full project library, SimGrader offers the widest A L STUDENTS LEARN BEYOND THE BOOK! range of projects from any of our Office series. Moreover, IM this offers instructors the benefit of utilizing projects that # SimNet offers a complete computer-based learning are specifically related to their student’s needs and areas 110 side that presents each skill or topic in several of study. SimNet and SimGrader are a completely online 259 different modes: 0 system that is easy-to-use for both instructors and 0 ■ Teach Me: combines instructional text, graphics, and students alike. Together, they provide an ideal solution 7/1 8 interactivity to present each skill. for students to gain complete knowledge of Office skills /1 0 ■ Show Me: uses animation with audio narration to show and application. CY how the skill is implemented. A N ■ Let Me Try: allows students to apply and practice M A what they have learned on their own to master the G learning objective. Y E L O STUDENTS LEARN BEYOND THE COURSE! B L K SimNet allows students to perform their best in the B A course and SimNet allows students to continue learning C K Office skills for future classes and beyond through its U Self-Study material! Need to learn an advanced topic or a P refresher on a certain skill? Use SimSearch to search or pull up specific content when you need it. For more information, contact your McGraw-Hill sales TM representative or visit the SimNet Online Website, www.mhhe.com/simnet2010 jjuuaa3311224499__ffmm__ii--xxlliivv..iinndddd PPaaggee ii 3311//0088//1100 77::3399 PPMM uusseerr--ff446677 //UUsseerrss//uusseerr--ff446677//DDeesskkttoopp Microsoft® Office Access 2010 A Lesson Approach, Complete jjuuaa3311224499__ffmm__ii--xxlliivv..iinndddd PPaaggee iiii 3311//0088//1100 77::3399 PPMM uusseerr--ff446677 //UUsseerrss//uusseerr--ff446677//DDeesskkttoopp jjuuaa3311224499__ffmm__ii--xxlliivv..iinndddd PPaaggee iiiiii 3311//0088//1100 77::3399 PPMM uusseerr--ff446677 //UUsseerrss//uusseerr--ff446677//DDeesskkttoopp Microsoft® Office Access 2010 A Lesson Approach, Complete Jon Juarez John Carter TM jjuuaa3311224499__ffmm__ii--xxlliivv..iinndddd PPaaggee iivv 3311//0088//1100 77::3399 PPMM uusseerr--ff446677 //UUsseerrss//uusseerr--ff446677//DDeesskkttoopp TM MICROSOFT® OFFICE ACCESS® 2010: A LESSON APPROACH, COMPLETE Published by McGraw-Hill, a business unit of The McGraw-Hill Companies, Inc., 1221 Avenue of the Americas, New York, NY, 10020. Copyright © 2011 by The McGraw-Hill Companies, Inc. All rights reserved. No part of this publication may be reproduced or distributed in any form or by any means, or stored in a database or retrieval system, without the prior written consent of The McGraw-Hill Companies, Inc., including, but not limited to, in any network or other electronic storage or transmission, or broadcast for distance learning. Some ancillaries, including electronic and print components, may not be available to customers outside the United States. This book is printed on acid-free paper. 1 2 3 4 5 6 7 8 9 0 RMN/RMN 1 0 9 8 7 6 5 4 3 2 1 0 ISBN 978-0-07-733124-5 MHID 0-07-733124-9 Vice president/Editor in chief: Elizabeth Haefele Vice president/Director of marketing: John E. Biernat Executive editor: Scott Davidson Developmental editor: Alan Palmer Marketing manager: Tiffany Wendt Lead digital product manager: Damian Moshak Digital developmental editor: Kevin White Director, Editing/Design/Production: Jess Ann Kosic Lead project manager: Rick Hecker Buyer II: Sherry L. Kane Senior designer: Marianna Kinigakis Media project manager: Cathy L. Tepper Interior design: Kay Lieberherr Typeface: 10.5/13 New Aster Compositor: Aptara, Inc. Printer: R. R. Donnelley Library of Congress Cataloging-in-Publication Data Juarez, Jon. Microsoft Office Access 2010: a lesson approach, complete / Jon Juarez, John Carter. p. cm. Includes index. ISBN-13: 978-0-07-733124-5 (alk. paper) ISBN-10: 0-07-733124-9 (alk. paper) 1. Microsoft Access. 2. Database management. I. Carter, John, 1949- II. Title. QA76.9.D3J824 2011 005.75’65—dc22 2010028934 The Internet addresses listed in the text were accurate at the time of publication. The inclusion of a Web site does not indicate an endorsement by the authors or McGraw-Hill, and McGraw- Hill does not guarantee the accuracy of the information presented at these sites. www.mhhe.com jjuuaa3311224499__ffmm__ii--xxlliivv..iinndddd PPaaggee vv 3311//0088//1100 77::3399 PPMM uusseerr--ff446677 //UUsseerrss//uusseerr--ff446677//DDeesskkttoopp CONTENTS Preface xiii Windows Tutorial xv Case Study xliii ACCESS 2010 UNIT 1 Understanding Access Databases LESSON 1: GETTING STARTED WITH Lesson Applications AC-32 A DATABASE AC-2 Exercise 1-23 AC-32 Exercise 1-24 AC-32 Identifing Basic Database Structure AC-3 Exercise 1-25 AC-33 Exercise 1-1: Manage a Database AC-5 Exercise 1-26 AC-34 Exercise 1-2: Start a Database AC-6 On Your Own AC-34 Exercise 1-3: Open a Database AC-6 Exercise 1-27 AC-34 Identifying Components of Access AC-7 Exercise 1-28 AC-35 Exercise 1-4: Manipulate the Navagation Pane AC-7 Exercise 1-29 AC-35 Exercise 1-5: Explore Tabs, Ribbons and LESSON 2: VIEWING AND MODIFYING Groups AC-9 Exercise 1-6: Open and Close Major Objects AC-9 RECORDS AC-36 Exercise 1-7: Explore Datasheet and Design Modifying Recordsets in a Table AC-36 Views AC-10 Exercise 2-1: Open a Database AC-37 Navigating Access Recordsets AC-11 Exercise 2-2: Edit Fields in a Table AC-37 Exercise 1-8: Use Navigation Buttons in Exercise 2-3: Add a Record in a Table AC-38 a Table AC-12 Exercise 2-4: Delete a Record in a Table AC-39 Exercise 1-9: Use Navigation Shortcut Modifying Recordsets through a Query AC-40 Keys in a Query AC-12 Exercise 2-5: Edit Fields through a Query AC-41 Modifying Datasheet Appearance AC-13 Exercise 2-6: Add a Record through a Query AC-41 Exercise 1-10: Hide and Unhide Columns AC-14 Exercise 2-7: Delete a Record through Exercise 1-11: Change Column Widths and a Query AC-42 Row Heights AC-15 Using Office Editing Tools AC-42 Exercise 1-12: Use the Font Command Group AC-16 Exercise 2-8: Use Autocorrect AC-43 Saving and Printing a Recordset AC-17 Exercise 2-9: Use Copy, Paste, and the Office Exercise 1-13: Print a Query AC-17 Clipboard AC-44 Exercise 1-14: Print a Table AC-18 Exercise 2-10: Use Undo AC-46 Exercise 1-15: Save a Table to an External File AC-19 Viewing and Modifying Recordsets Managing Access Files AC-20 through a Form AC-47 Exercise 1-16: Use Compact and Repair AC-20 Exercise 2-11: Navigate through Forms AC-47 Exercise 1-17: Back Up a Database AC-21 Exercise 2-12: Edit Fields through a Form AC-48 Exercise 1-18: Close a Database AC-21 Exercise 2-13: Add Records through a Form AC-49 Lesson 1 Summary AC-21 Exercise 2-14: Delete Records through a Form AC-50 Concepts Review AC-25 Managing Attachments AC-51 True/False Questions AC-25 Exercise 2-15: Attach an Image AC-51 Short Answer Questions AC-25 Exercise 2-16: Extract an Image from Critical Thinking AC-26 the Database AC-52 Skills Review AC-26 Previewing, Printing, and Exporting Exercise 1-19 AC-26 Data Through a Report AC-53 Exercise 1-20 AC-27 Exercise 2-17: Preview a Report AC-53 Exercise 1-21 AC-29 Exercise 2-18: Print a Report AC-54 Exercise 1-22 AC-30 Exercise 2-19: Save a Report AC-55 v jjuuaa3311224499__ffmm__ii--xxlliivv..iinndddd PPaaggee vvii 3311//0088//1100 77::3399 PPMM uusseerr--ff446677 //UUsseerrss//uusseerr--ff446677//DDeesskkttoopp vi Contents Lesson 2 Summary AC-56 Exercise 3-20 AC-95 Exercise 3-21 AC-96 Concepts Review AC-58 Lesson Applications AC-98 True/False Questions AC-58 Exercise 3-22 AC-98 Short Answer Questions AC-58 Exercise 3-23 AC-98 Critical Thinking AC-59 Exercise 3-24 AC-99 Skills Review AC-59 Exercise 3-25 AC-100 Exercise 2-20 AC-59 On Your Own AC-101 Exercise 2-21 AC-61 Exercise 3-26 AC-101 Exercise 2-22 AC-62 Exercise 3-27 AC-101 Exercise 2-23 AC-64 Exercise 3-28 AC-101 Lesson Applications AC-66 Exercise 2-24 AC-66 LESSON 4: CREATING NEW Exercise 2-25 AC-66 DATABASES AND TABLES AC-102 Exercise 2-26 AC-67 Exercise 2-27 AC-68 Creating Databases AC-102 On Your Own AC-69 Exercise 4-1: Create a Database Using Exercise 2-28 AC-69 a Template AC-103 Exercise 2-29 AC-69 Exercise 4-2: Set Database Properties AC-104 Exercise 2-30 AC-69 Exercise 4-3: Create a Blank Database AC-105 Exercise 4-4: Create a Table in Design View AC-106 LESSON 3: FINDING, FILTERING, Exercise 4-5: Modify Table Properties AC-109 SORTING, AND SUMMARIZING DATA AC-70 Managing External Data AC-110 Exercise 4-6: Copy Table Structure from Finding and Replacing Data AC-70 an External Table AC-110 Exercise 3-1: Use the Search Tool AC-71 Exercise 4-7: Export a Table to Access AC-111 Exercise 3-2: Use the Find Command AC-72 Exercise 4-8: Export Data to Word AC-113 Exercise 3-3: Use the Replace Command AC-73 Exercise 4-9: Import Data From Excel AC-113 Using Wildcards AC-74 Adding and Deleting Fields in a Table AC-114 Exercise 3-4: Find Data Using an Asterisk “*” AC-74 Exercise 4-10: Add and Delete Fields Exercise 3-5: Find Data Using a in Datasheet View AC-115 Question Mark “?” AC-74 Exercise 4-11: Add and Delete Fields Sorting Records AC-75 in Design View AC-115 Exercise 3-6: Sort Records in Datasheet View AC-75 Controlling Field Appearance AC-116 Exercise 3-7: Sort Records in a Form AC-76 Exercise 4-12: Change Field Property Exercise 3-8: Sort Records in a Report AC-76 Caption AC-117 Adding and Modifying the Totals Row in Exercise 4-13: Change Field Property Datasheets AC-77 Default Value AC-118 Exercise 3-9: Add a Totals Row to a Query AC-77 Exercise 4-14: Change Field Property Format AC-119 Exercise 3-10: Modify a Totals Row in a Query AC-79 Exercise 4-15: Change Field Property Using Filters AC-79 Input Mask AC-120 Exercise 3-11: Create and Apply a Filter Exercise 4-16: Change Field Property By Selection AC-79 Date Picker AC-121 Exercise 3-12: Filter For a Specific Value AC-81 Controlling Data Integrity AC-122 Exercise 3-13: Filter For a Range of Values AC-82 Exercise 4-17: Set the Primary Key AC-122 Exercise 3-14 Create and Apply a Filter Exercise 4-18: Set the Field Property By Form AC-83 Field Size AC-123 Exercise 3-15 Use Filter By Form or Option AC-84 Exercise 4-19: Set the Field Property Using the Database Documenter AC-85 Validation Text and Rules AC-123 Exercise 3-16 Save a Report for a Table AC-85 Lesson 4 Summary AC-125 Exercise 3-17 Save Report for Other Objects AC-87 Concepts Review AC-127 Lesson 3 Summary AC-89 True/False Questions AC-127 Concepts Review AC-91 Short Answer Questions AC-127 True/False Questions AC-91 Critical Thinking AC-128 Short Answer Questions AC-91 Skills Review AC-128 Critical Thinking AC-92 Exercise 4-20 AC-128 Skills Review AC-92 Exercise 4-21 AC-130 Exercise 3-18 AC-92 Exercise 4-22 AC-131 Exercise 3-19 AC-94 Exercise 4-23 AC-133 jjuuaa3311224499__ffmm__ii--xxlliivv..iinndddd PPaaggee vviiii 3311//0088//1100 77::3399 PPMM uusseerr--ff446677 //UUsseerrss//uusseerr--ff446677//DDeesskkttoopp vii LESSON 1 BaCsoicn tSekniltlss Lesson Applications AC-135 UNIT 1 APPLICATIONS AC-140 Exercise 4-24 AC-135 Exercise 4-25 AC-135 Unit Application 1-1 AC-140 Exercise 4-26 AC-136 Unit Application 1-2 AC-141 Exercise 4-27 AC-138 Unit Application 1-3 AC-142 On Your Own AC-139 Unit Application 1-4 AC-143 Exercise 4-28 AC-139 Exercise 4-29 AC-139 Exercise 4-30 AC-139 UNIT 2 Designing and Managing Database Objects LESSON 5: MANAGING DATA On Your Own AC-180 INTEGRITY AC-146 Exercise 5-26 AC-180 Exercise 5-27 AC-181 Creating Relationships between Tables AC-147 Exercise 5-28 AC-181 Exercise 5-1: Look at an Existing Relationship AC-147 LESSON 6: DESIGNING QUERIES AC-182 Exercise 5-2: Create a Relationship in the Creating and Modifying Select Queries AC-183 Relationships Window AC-150 Exercise 6-1: View a Select Query and Exercise 5-3: Save Relationships AC-152 Its Report AC-183 Working with Referential Integrity AC-153 Exercise 6-2: Create a Select Query Based Exercise 5-4: Enforce Referential Integrity AC-153 On a Single Table AC-185 Exercise 5-5: Remove Referential Integrity AC-155 Exercise 6-3: Add Fields to a Query AC-186 Working with Subdatasheets AC-155 Exercise 6-4: Create a Select Query Based Exercise 5-6: Insert a Subdatasheet AC-156 on Two Tables AC-187 Exercise 5-7: Remove a Subdatasheet AC-157 Adding Criteria and Operators Using the Lookup Wizard AC-157 to a Query AC-188 Exercise 5-8: Create a Lookup Field AC-158 Exercise 6-5: Use a Single Criterion AC-189 Exercise 5-9: Add Data With a Lookup Field AC-159 Exercise 6-6: Use Comparison Operators AC-190 Exercise 5-10: Create a List Lookup Field AC-160 Exercise 6-7: Use Wildcards in Criteria AC-191 Exercise 5-11: Modify a Lookup Field AC-160 Exercise 6-8: Use Keywords in Criteria AC-192 Exercise 5-12: Create a Multi-Valued Applying Logical Operators AC-193 Lookup Field AC-161 Exercise 6-9: Use and Criteria AC-193 Exercise 5-13: Add Data With a Exercise 6-10: Use or Criteria AC-194 Multi-Valued Lookup Field AC-162 Modifying Query Properties AC-195 Exercise 5-14: View Relationships Exercise 6-11: Find the Top and Created by Lookup Fields AC-161 Bottom Values AC-195 Using Analyzing Tools AC-164 Exercise 6-12: Create a Query with Exercise 5-15: Analyze a Table AC-164 a Subdatasheet AC-197 Tracking Object Dependency AC-166 Adding Calculations to a Query AC-197 Exercise 5-16: View Object Dependency AC-166 Exercise 6-13: Use a Formula in a Exercise 5-17: View a Missing Dependency AC-167 Calculated Field AC-198 Lesson 5 Summary AC-168 Exercise 6-14: Use a Function in a Concepts Review AC-170 Calculated Field AC-199 Exercise 6-15: Use a Concatenation True/False Questions AC-170 Expression in a Calculated Field AC-200 Short Answer Questions AC-170 Creating Queries with Wizards AC-201 Critical Thinking AC-171 Exercise 6-16 Use the Simple Skills Review AC-171 Query Wizard AC-202 Exercise 5-18 AC-171 Exercise 6-17 Use the Crosstab Query Exercise 5-19 AC-172 Wizard AC-203 Exercise 5-20 AC-174 Exercise 6-18 Use the Find Duplicate Exercise 5-21 AC-175 Query Wizard AC-204 Lesson Applications AC-177 Exercise 6-19 Use the Find Unmatched Exercise 5-22 AC-177 Query Wizard AC-205 Exercise 5-23 AC-177 Applying PivotChart/PivotTable Views AC-206 Exercise 5-24 AC-178 Exercise 6-20 Use PivotTable View AC-207 Exercise 5-25 AC-179 Exercise 6-21 Use PivotChart View AC-208