Microsoft Access 2013, Comprehensive

658 Pages·2013·78.33 MB·English

by  Cynthia Krebs

Looking for a way to distinguish yourself in
the workplace and prove your skills in IT?
Add Microsoft® Office Specialist certification
to your résumé!
Prepare for the MOS Exam of your choice by using the resources for Exploring
Microsoft Access 2013 Comprehensive!
Access® 2013
Find resources in your student text, in MyITLab for Exploring, and on the companion
website at www.pearsonhighered.com/exploring.
S e r i e S
(ex ploring)
•
1. Investigating in a systematic way: examining. 2. Searching into
or ranging over for the purpose of discovery.
Microsoft®
Access 2013
CoMprehensive
Mary Anne Poatsy
Series Editor
Krebs | Cameron | Williams
Series Created by Dr. Robert T. Grauer
Boston Columbus Indianapolis New York San Francisco Upper Saddle River
Amsterdam Cape Town Dubai London Madrid Milan Munich Paris Montréal Toronto
Delhi Mexico City São Paulo Sydney Hong Kong Seoul Singapore Taipei Tokyo
Editor in Chief: Michael Payne
Senior Editor: Samantha McAfee Lewis
Editorial Project Manager: Keri Rand
Product Development Manager: Laura Burgess
Development Editor: Lori Damanti
Editorial Assistant: Laura Karahalis
Director of Marketing: Maggie Moylan Leen
Marketing Manager: Brad Forrester
Marketing Coordinator: Susan Osterlitz
Marketing Assistant: Darshika Vyas
Managing Editor: Camille Trentacoste
Production Project Manager: Ilene Kahn
Senior Operations Specialist: Maura Zaldivar
Senior Art Director: Jonathan Boylan
Interior Design: Studio Montage
Cover Design: Studio Montage
Cover Photos: Supri Suharjoto/Shutterstock, wavebreakmedia/Shutterstock, Terry Chan/Shutterstock, Csaba Peterdi/Shutterstock
Associate Director of Design: Blair Brown
Digital Media Editor: Eric Hakanson
Director of Media Development: Taylor Ragan
Media Project Manager, Production: Renata Butera
Full Service Project Management: Andrea Stefanowicz/PreMediaGlobal
Composition: PreMediaGlobal
Credits and acknowledgments borrowed from other sources and reproduced, with permission, in this textbook appear on the appropriate page within text.
Microsoft and/or its respective suppliers make no representations about the suitability of the information contained in the documents and related graphics
published as part of the services for any purpose. All such documents and related graphics are provided “as is” without warranty of any kind. Microsoft and/or
its respective suppliers hereby disclaim all warranties and conditions with regard to this information, including all warranties and conditions of merchantability,
whether express, implied or statutory, fitness for a particular purpose, title and non-infringement. In no event shall Microsoft and/or its respective suppliers be
liable for any special, indirect or consequential damages or any damages whatsoever resulting from loss of use, data or profits, whether in an action of contract,
negligence or other tortious action, arising out of or in connection with the use or performance of information available from the services.
The documents and related graphics contained herein could include technical inaccuracies or typographical errors. Changes are periodically added to the
information herein. Microsoft and/or its respective suppliers may make improvements and/or changes in the product(s) and/or the program(s) described herein at
any time. Partial screen shots may be viewed in full within the software version specified.
Microsoft® and Windows® are registered trademarks of the Microsoft Corporation in the U.S.A. and other countries. This book is not sponsored or endorsed by or
affiliated with the Microsoft Corporation.
Copyright © 2014 by Pearson Education, Inc. All rights reserved. Manufactured in the United States of America. This publication is protected by Copyright, and
permission should be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means,
electronic, mechanical, photocopying, recording, or likewise. To obtain permission(s) to use material from this work, please submit a written request to Pearson
Education, Inc., Permissions Department, One Lake Street, Upper Saddle River, New Jersey 07458, or you may fax your request to 201-236-3290.
Many of the designations by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in this book, and
the publisher was aware of a trademark claim, the designations have been printed in initial caps or all caps.
10 9 8 7 6 5 4 3 2 1
ISBN 10: 0-13-341220-2
ISBN 13: 978-0-13-341220-8
Dedications
For my husband, Ted, who unselfishly continues to take on more than his share to support me
throughout the process; and for my children, Laura, Carolyn, and Teddy, whose encouragement and
love have been inspiring.
Mary Anne Poatsy
To my students—you continue to inspire me. Thank you for all you have taught me and shared with me.
Cynthia Krebs
I dedicate this book to my fiancée, Anny, for encouraging me throughout the writing process and for
being the person she is, to Sonny, to Drs. Hubey, Boyno, Bredlau, and Deremer at Montclair State
University for educating and inspiring me, and to my students, who I hope will inspire others someday.
Eric Cameron
I offer thanks to my family and colleagues who have supported me on this journey. I would like to dedicate
the work I have performed toward this undertaking to my little grandson, Yonason Meir (known for now
as Mei-Mei), who as his name suggests, is the illumination in my life.
Jerri Williams
About the Authors
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 personal training. Before teaching, she was Vice President at Shearson Lehman
in the Municipal Bond Investment Banking Department.
Cynthia Krebs, Access Author
Cynthia Krebs is the Director of Business and Marketing Education at Utah Valley University. She is a professor in the
Information Systems and Technology Department at Utah Valley University (UVU). In 2008, she received the UVU
College of Technology and Computing Scholar Award. She has also received the School of Business Faculty Excellence
Award twice during her tenure at UVU. Cynthia teaches the Methods of Teaching Digital Media class to future
teachers, as well as classes in basic computer proficiency, business proficiency applications, and business graphics.
Cynthia is active in the Utah Business and Computer Education Association, the Western Business Education Association,
the National Business Education Association, and the Utah Association of Career and Technical Educators. She was
awarded the WBEA Outstanding Educator at the University Level in 2009. Cynthia has written multiple texts on Microsoft
Office software, consulted with government and business, and has presented extensively at the local, regional, and national
levels to professional and business organizations.
Cynthia lives by a peaceful creek in Springville, Utah. When she isn’t teaching or writing, she enjoys spending time with
her children, spoiling her grandchildren Ava, Bode, Solee, and Morgan. She loves traveling and reading.
Eric Cameron, Access Author
Eric holds a M.S. in computer science and a B.S. degree in Computer Science with minors in Mathematics and Physics,
both from Montclair State University. He is a tenured Assistant Professor at Passaic County Community College,
where he has taught in the Computer and Information Sciences department since 2001. Eric is also the author of the
Your Office: Getting Started with Web 2.0 and Your Office: Getting Started with Windows 8 textbooks. Eric maintains a
professional blog at profcameron.blogspot.com.
Jerri Williams, Access Author
Jerri Williams is a Senior Adjunct Instructor at Montgomery County Community College in Pennsylvania. Jerri also
works as an independent corporate trainer, technical editor, and author. She is interested in travel, cooking, and tending
to her colonial farmhouse.
Jerri is married (to Gareth for 32 years!), the mother of two beautiful daughters (an Accounting graduate and a budding
lawyer), a splendid son-in-law (also a soon-to-be lawyer), and grandmother to a handsome young grandson.
Rebecca Lawson, Office Fundamentals Author
Rebecca Lawson is a professor in the Computer Information Technologies program at Lansing Community College. She
coordinates the curriculum, develops the instructional materials, and teaches for the E-Business curriculum. She also serves as
the Online Faculty Coordinator at the Center for Teaching Excellence at LCC. In that role, she develops and facilitates online
workshops for faculty learning to teach online. Her major areas of interest include online curriculum quality assurance, the
review and development of printed and online instructional materials, the assessment of computer and Internet literacy skill
levels to facilitate student retention, and the use of social networking tools to support learning in blended and online learning
environments.
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.
iv About the Authors
Brief Contents

Office Chapter 1 Office Fundamentals and File Management 1

access Chapter 1 Introduction to access 83
Chapter 2 tables and Queries in relational Databases 141
Chapter 3 Customize, analyze, and Summarize Query Data 211
Chapter 4 Creating and Using professional Forms and reports 257
Chapter 5 Data Validation and Data analysis 309
Chapter 6 action and Specialized Queries 357
Chapter 7 advanced Forms and reports 403
Chapter 8 Get Connected 447
Chapter 9 Fine-tuning the Database 519
Chapter 10 Using Macros and SQL in access 569
application Introductory (Chapters 1–4) 617
Capstone Comprehensive (Chapters 5–10) 620
exercises
GLOSSary 623
InDex 629
Brief Contents v
Contents
Microsoft Office 2013
■ ChApter One Office Fundamentals and File Management: taking
the First Step 1
ObjeCtIVeS 1 Hands-On ExErcisE 4:
CaSe StUDy: SpOtteD beGONIa art GaLLery 1 the backstage View tasks 41
WINDOWS 8 StartUp 2 hOMe tab taSkS 45
Logging In with Your Microsoft Account 2 Selecting and editing text 45
Identifying the Start Screen Components 3 Using the Clipboard Group Commands 49
Interacting with the Start Screen 4 Using the editing Group Commands 52
Accessing the Desktop 4
Hands-On ExErcisE 5:
Hands-On ExErcisE 1: home tab tasks 55
Windows 8 Startup 6
INSert tab taSkS 60
FILeS aND FOLDerS 10 Inserting Objects 60
Using File explorer 10 reVIeW tab taSkS 62
Working with Folders and Files 13 reviewing a File 62
Selecting, Copying, and Moving Multiple Files and Folders 15 paGe LayOUt tab taSkS 65
Hands-On ExErcisE 2: Using the page Setup Dialog Box 66
Files and Folders 17 Hands-On ExErcisE 6:
MICrOSOFt OFFICe SOFtWare 22 Insert tab tasks, page Layout tab tasks, and
Identifying Common Interface Components 22 review tab tasks 68
Getting Office help 28 Chapter ObjeCtIVeS reVIeW 72
Hands-On ExErcisE 3: key terMS MatChING 73
Microsoft Office Software 31 MULtIpLe ChOICe 74
praCtICe exerCISeS 75
the baCkStaGe VIeW taSkS 36
MID-LeVeL exerCISeS 79
Opening a File 36
beyOND the CLaSSrOOM 81
printing a File 38
CapStONe exerCISe 82
Closing a File and Application 39
Microsoft Office Access 2013
■ ChApter One Introduction to access: Finding Your Way through
an Access Database 83
ObjeCtIVeS 83 Hands-On ExErcisE 3:
CaSe StUDy: MaNaGING a bUSINeSS IN the GLObaL access Versus excel, and relational Databases 115
eCONOMy 83 aCCeSS DatabaSe CreatION 119
DatabaSeS are eVeryWhere! 84 Creating a Database 119
Understanding Database Fundamentals 84
Hands-On ExErcisE 4:
Using an existing Database 91
access Database Creation 123
Hands-On ExErcisE 1:
Chapter ObjeCtIVeS reVIeW 127
Databases are everywhere! 95
key terMS MatChING 128
SOrtS aND FILterS 102 MULtIpLe ChOICe 129
Sorting table Data on One or Multiple Fields 102 praCtICe exerCISeS 130
Creating, Modifying, and removing Filters 103 MID-LeVeL exerCISeS 135
Hands-On ExErcisE 2: beyOND the CLaSSrOOM 139
Sorts and Filters 106 CapStONe exerCISe 140
aCCeSS VerSUS exCeL, aND reLatIONaL DatabaSeS 111
Knowing When to Use Access or excel to Manage Data 111
Understanding relational power 112
vi Contents
■ ChApter tWO tables and Queries in relational Databases: Designing
Databases and extracting Data 141
ObjeCtIVeS 141 running, Copying, and Modifying a Query 177
CaSe StUDy: baNk aUDIt 141 Using the Query Wizard 178
tabLe DeSIGN, CreatION, aND MODIFICatION 142 Hands-On ExErcisE 3:
Designing a table 142 Single-table Queries 181
Creating and Modifying tables 146
MULtItabLe QUerIeS 184
Hands-On ExErcisE 1: Creating a Multitable Query 184
table Design, Creation, and Modification 152 Modifying a Multitable Query 185
MULtIpLe-tabLe DatabaSeS 157 Hands-On ExErcisE 4:
Sharing Data 157 Multitable Queries 189
establishing table relationships 160
Chapter ObjeCtIVeS reVIeW 193
Hands-On ExErcisE 2: key terMS MatChING 195
Multiple-table Databases 165 MULtIpLe ChOICe 196
SINGLe-tabLe QUerIeS 171 praCtICe exerCISeS 197
Creating a Single-table Query 171 MID-LeVeL exerCISeS 202
Specifying Query Criteria for Different Data types 173 beyOND the CLaSSrOOM 206
Understanding Query Sort Order 177 CapStONe exerCISe 208
■ ChApter three Customize, analyze, and Summarize Query Data: Creating
and Using Queries to Make Decisions 211
ObjeCtIVeS 211 aGGreGate FUNCtIONS 233
CaSe StUDy: hOUSING SLUMp MeaNS OppOrtUNIty Adding Aggregate Functions to Datasheets 233
FOr COLLeGe StUDeNtS 211 Creating Queries with Aggregate Functions 234
CaLCULatIONS aND expreSSIONS 212 Hands-On ExErcisE 3:
Creating a Calculated Field in a Query 212 aggregate Functions 240
Formatting and Saving Calculated results 215
Chapter ObjeCtIVeS reVIeW 243
Hands-On ExErcisE 1: key terMS MatChING 244
Calculations and expressions 218 MULtIpLe ChOICe 245
the expreSSION bUILDer aND FUNCtIONS 223 praCtICe exerCISeS 246
Creating expressions with the expression Builder 223 MID-LeVeL exerCISeS 249
Using Built-In Functions in Access 225 beyOND the CLaSSrOOM 253
Hands-On ExErcisE 2: CapStONe exerCISe 255
the expression builder and Functions 228
■ ChApter FOUr Creating and Using professional Forms and reports:
Moving Beyond tables and Queries 257
ObjeCtIVeS 257 Modifying a report 287
CaSe StUDy: COFFee ShOp StartS NeW bUSINeSS 257 Sorting records in a report 290
FOrM baSICS 258 Hands-On ExErcisE 2:
Creating Forms Using Form tools 258 report basics 292
Using Form Views 265
Chapter ObjeCtIVeS reVIeW 296
Working with a Form Layout Control 268
key terMS MatChING 298
Sorting records in a Form 270
MULtIpLe ChOICe 299
Hands-On ExErcisE 1: praCtICe exerCISeS 300
Form basics 272 MID-LeVeL exerCISeS 303
repOrt baSICS 278 beyOND the CLaSSrOOM 306
Creating reports Using report tools 278 CapStONe exerCISe 307
Using report Views 285
Contents vii
■ ChApter FIVe Data Validation and Data analysis: reducing errors and
extracting Better Information 309
ObjeCtIVeS 309 Hands-On ExErcisE 2:
CaSe StUDy: IMpLeMeNtING a NeW DatabaSe at Data analysis Using advanced Select Queries 339
tOMMy'S SheLter 309 Chapter ObjeCtIVeS reVIeW 344
Data VaLIDatION IN tabLeS 310 key terMS MatChING 345
establishing Data Validation 310 MULtIpLe ChOICe 346
Creating an Input Mask 313 praCtICe exerCISeS 347
Creating and Modifying a Lookup Field 315 MID-LeVeL exerCISeS 350
Hands-On ExErcisE 1: beyOND the CLaSSrOOM 352
Data Validation in tables 320 CapStONe exerCISe 354
Data aNaLySIS USING aDVaNCeD SeLeCt QUerIeS 325
Creating a parameter Query 325
Using Advanced Functions in the expression Builder 329
performing Date Arithmetic 335
■ ChApter SIx action and Specialized Queries: Moving Beyond
the Select Query 357
ObjeCtIVeS 357 Finding Duplicate records with a Query 381
CaSe StUDy: repLaCeMeMt ChINa, INC. 357 Finding Unmatched records with a Query 383
aCtION QUerIeS 358 Hands-On ExErcisE 2:
Determining When to Use an Action Query 358 Queries for Special Conditions 387
Updating Data with an Update Query 360
Chapter ObjeCtIVeS reVIeW 391
Adding records to a table with an Append Query 363
key terMS MatChING 392
Creating a table with a Make table Query 366
MULtIpLe ChOICe 393
Deleting records with a Delete Query 367
praCtICe exerCISeS 394
Hands-On ExErcisE 1: MID-LeVeL exerCISeS 398
action Queries 370 beyOND the CLaSSrOOM 401
QUerIeS FOr SpeCIaL CONDItIONS 377 CapStONe exerCISe 402
Summarizing Data with a Crosstab Query 377
■ ChApter SeVen advanced Forms and reports: Moving Beyond the Basics 403
ObjeCtIVeS 403 Hands-On ExErcisE 2:
CaSe StUDy: teChNICaL SerVICeS 403 Controls and Sections 431
aDVaNCeD FOrMS 404
Chapter ObjeCtIVeS reVIeW 436
restricting edits in a Form 404
key terMS MatChING 437
Creating a Drop-Down Menu 406
MULtIpLe ChOICe 438
Setting the tab Order 409
praCtICe exerCISeS 439
Using Subforms 410
MID-LeVeL exerCISeS 442
Hands-On ExErcisE 1: beyOND the CLaSSrOOM 444
advanced Forms 414 CapStONe exerCISe 446
CONtrOLS aND SeCtIONS 419
Using Advanced Controls 419
Using Sections 423
■ ChApter eIGht Get Connected: exchanging Data Between Access
and Other Applications 447
ObjeCtIVeS 447 Hands-On ExErcisE 1:
CaSe StUDy: prOperty MaNaGeMeNt Data exChaNGe 447 Connecting access to external Files 456
CONNeCtING aCCeSS tO exterNaL FILeS 448
expOrtING Data tO OFFICe aND Other appLICatIONS 463
Creating a hyperlink Field 448
exporting Data to excel 463
Adding an Attachment Field 450
exporting Data to Word 467
Adding Attachment Controls to Forms and reports 453
viii Contents