0011__557777665533--ffffiirrss..iinndddd iiii 33//1199//1100 99::3333 PPMM Access® 2010 by Faithe Wempen 0011__557777665533--ffffiirrss..iinndddd ii 33//1199//1100 99::3333 PPMM Teach Yourself VISUALLY™ LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTA- Access® 2010 TIONS OR WARRANTIES WITH RESPECT TO THE ACCU- RACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, Published by INCLUDING WITHOUT LIMITATION WARRANTIES OF Wiley Publishing, Inc. FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMO- 10475 Crosspoint Boulevard TIONAL MATERIALS. THE ADVICE AND STRATEGIES Indianapolis, IN 46256 CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDER- www.wiley.com STANDING THAT THE PUBLISHER IS NOT ENGAGED IN Published simultaneously in Canada RENDERING LEGAL, ACCOUNTING, OR OTHER PROFES- SIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS Copyright © 2010 by Wiley Publishing, Inc., Indianapolis, REQUIRED, THE SERVICES OF A COMPETENT PROFES- Indiana SIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR No part of this publication may be reproduced, stored in a DAMAGES ARISING HEREFROM. THE FACT THAT AN retrieval system or transmitted in any form or by any means, ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE electronic, mechanical, photocopying, recording, scanning or OF FURTHER INFORMATION DOES NOT MEAN THAT otherwise, except as permitted under Sections 107 or 108 of THE AUTHOR OR THE PUBLISHER ENDORSES THE the 1976 United States Copyright Act, without either the prior INFORMATION THE ORGANIZATION OR WEBSITE MAY written permission of the Publisher, or authorization through PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FUR- THER, READERS SHOULD BE AWARE THAT INTERNET payment of the appropriate per-copy fee to the Copyright WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, OR DISAPPEARED BETWEEN WHEN THIS WORK WAS (978) 750-8400, fax (978) 646-8600. Requests to the WRITTEN AND WHEN IT IS READ. Publisher for permission should be addressed to the Permissions FOR PURPOSES OF ILLUSTRATING THE CONCEPTS Department, John Wiley & Sons, Inc., 111 River Street, AND TECHNIQUES DESCRIBED IN THIS BOOK, THE Hoboken, NJ 07030, 201-748-6011, fax 201-748-6008, or AUTHOR HAS CREATED VARIOUS NAMES, COMPANY online at http://www.wiley.com/go/permissions. NAMES, MAILING, E-MAIL AND INTERNET Library of Congress Control Number: 2010923552 ADDRESSES, PHONE AND FAX NUMBERS AND SIMILAR INFORMATION, ALL OF WHICH ARE FICTITIOUS. ANY ISBN: 978-0-470-57765-3 RESEMBLANCE OF THESE FICTITIOUS NAMES, Manufactured in the United States of America ADDRESSES, PHONE AND FAX NUMBERS AND SIMILAR 10 9 8 7 6 5 4 3 2 1 INFORMATION TO ANY ACTUAL PERSON, COMPANY AND/OR ORGANIZATION IS UNINTENTIONAL AND PURELY COINCIDENTAL. Trademark Acknowledgments Wiley, the Wiley Publishing logo, Visual, the Visual logo, Teach Contact Us Yourself VISUALLY, Read Less - Learn More and related trade dress are trademarks or registered trademarks of John Wiley & For general information on our other products and services Sons, Inc. and/or its affiliates. Access is a registered trademark please contact our Customer Care Department within the U.S. of Microsoft Corporation in the United States and/or other at 877-762-2974, outside the U.S. at 317-572-3993 or fax countries. All other trademarks are the property of their 317-572-4002. respective owners. Wiley Publishing, Inc. is not associated with For technical support please visit www.wiley.com/techsupport. any product or vendor mentioned in this book. Disclaimer In order to get this information to you in a timely manner, this book was based on a pre-release version of Microsoft Office 2010. There may be some minor changes between the screenshots in this book and what you see on your desktop. As always, Microsoft has the final word on how programs look and function; if you have any questions or see any discrepancies, consult the online help for further information about the software. Sales Contact Wiley at (877) 762-2974 or fax (317) 572-4002. 0011__557777665533--ffffiirrss..iinndddd iiii 33//1199//1100 99::3333 PPMM Credits Executive Editor Project Coordinator Jody Lefevere Lynsey Stanford Project Editor Graphics and Production Christopher Stolle Specialists Carrie A. Cesavice Technical Editor Andrea Hornberger Joyce Nielsen Jennifer Mayberry Mark Pinto Copy Editor Marylouise Wiack Quality Control Technician Jessica Kramer Editorial Director Robyn Siesky Proofreading and Indexing Shannon Ramsey Business Manager Johnna VanHoose Dinse Amy Knies Screen Artist Senior Marketing Manager Jill Proll Sandy Smith Illustrators Vice President and Executive Rhonda David-Burroughs Group Publisher Cheryl Grubbs Richard Swadley Vice President and Executive Publisher Barry Pruett 0011__557777665533--ffffiirrss..iinndddd iiiiii 33//1199//1100 99::3333 PPMM About the Author Faithe Wempen, M.A., is a Microsoft Office Specialist Master Instructor and the author of over 100 books on computer hardware and software, including the PowerPoint 2010 Bible, Microsoft Office 2010 for Seniors for Dummies, and A+ Certification Workbook for Dummies. Faithe is currently an adjunct instructor of computer information technology at IUPUI, where she teaches PC hardware and software architecture and A+ certification. Her online courses for corporate clients, including Hewlett Packard, Sony, and CNET, have educated over a quarter of a million students all over the world. Author’s Acknowledgments Thanks to my wonderful team of editors at Wiley for another job well done, including Jody Lefevere, Christopher Stolle, Marylouise Wiack, and Joyce Nielsen. 0011__557777665533--ffffiirrss..iinndddd iivv 33//1199//1100 99::3333 PPMM How to Use This Book Who This Book Is For 3 Icons and Buttons This book is for the reader who has never used this Icons and buttons show you exactly what you need to particular technology or software application. It is also click to perform a step. for readers who want to expand their knowledge. 4 Tips Tips offer additional information, including warnings The Conventions in This Book and shortcuts. 1 Steps 5 Bold This book uses a step-by-step format to guide you Bold type shows command names, options, or text or easily through each task. Numbered steps are actions numbers you must type. you must do; bulleted steps clarify a point, step, or optional feature; and indented steps give you the 6 Italics result of the action. Italic type introduces and defines a new term. 2 Notes Notes give additional information — special conditions that may occur during an operation, a situation that you want to avoid, or a cross-reference to a related area of the book. Sort 2 Records Entering and Editing Data chapter 6 One way to quickly find a particular record is to S1or tC Rlicekc Aorddvsa nbcye dM ounl ttihpel eH oFmieeld tsab. 11 5 rYeoourd cearn, osro rsot rbt,y tahney r eficeoldrd isn b eyit ah epra artsicceunldarin fige l(dA. to 2 Click Advanced Filter/Sort. 22 Z) or descending (Z to A) order. You can also do a CC mevuenltti fioefl da stoier ti nt htahte sppreicmifiaersy wsohratt fifieelldd. to use in the B A DDaattaa RReeccoorrddss 3● AD rqauge trhye g fireidld asp ypoeua rws.ant to sort 55 by into the grid — in the order by Sort Records which you want to apply them. 3 1 S(tohret RRiebcboordns M beyt hao Sdi)ngle Field 22 4 hFoerr ee a(ch )fi teold c hino othsee gArsicde, ncdlicinkg or 33 1 Click anywhere in the field by Descending. which you want to sort. 5 Click Toggle Filter. 2 Click the Ascending button on The datasheet is sorted by the the Home tab to sort in ascending fields that you specified. order. ● Alternatively, you can sort in 11 44 descending order by clicking the Descending button. 33 The data is sorted. S(tohret RRiegchotr-dCsli cbky Ma eStihnogdle) Field 4 1 Right-click anywhere in the column by which you want to sort. 11 Hreocwor ddso iIn s ao rt HI roewm doove 11 22 2 Choose Sort A to Z from the form? a sort? shortcut menu to sort in 22 All the same Press + ascending order. techniques for to undo the 33 ● Alternatively, you can sort in sdoarttainshge ae t also lsaosrtt )a. cItfi oyonu ( the descending order by choosing work in a form, have performed Sort Z to A from the shortcut even though most forms show only other actions since the sort, close the datasheet without saving menu. one record at a time. The sort affects your changes: 2 The data is sorted. twhhee onr dyeoru inm wovheic ahm reocnogr dths eampp beya r 1 Right-click on the datasheet tab. Nmfieoalydte ,b :te hD ede icpfofeemnrdeminnatg.n dFoosn r a terhexe a Sfimoerpltdl eO ,tl ydfpoeers ,ta tt hoDe aN steeow/rteT siomtp aetin odn s uRfoseric neogar dcthh be ru eNtcteooxnrtds R. sTetahcyoesr rdte hcaeon rdsda Pmnrueemv.iobuesr 23 ACth othoes ep rColmospet tforo smav teh ec hsahnogretcsu tto m theen ud.esign of the table, click No. Sort Newest to Oldest. 0011__557777665533--ffffiirrss..iinndddd vv 33//1199//1100 99::3333 PPMM Table of Contents 1 chapter Getting Started with Access 2010 An Introduction to Access 2010 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Start and Exit Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 Orders Create a Blank Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 I0D1 /30255/00 9T h– eO Grdoeord S Ehaiprtphed Close a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Customers I0D1 /31025/029 G –r oOwrdinerg SThhiipnpgesd ID 3250 The Good Earth 0ID1/ 1302/5009 T –h Bea Gcko oOdr dEearrth Create a Database by Using a Template. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 I6D0 3 W25i1c Gkreeri Cnte.r, CGhaircdaegno SILu 6p0p6ly12 0ID1/ 1332/0519 G –r Oeirndeerr G Sahridpepne dSupp OUChnpadennegr aset aDthnaedta iNnbgaa vstiehg eaF tiAlieoc .nc .e P.s as. n.2 e.0 .1V .0i e. wI.n . t..e ..r f..a ..c ..e .. ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... 111624 91II63D0D3I7 WD32 54I33 2D 6G722e3 4W 552usZ35235t2lo. d45B9GFSee5 tltrWr oW rha.Ao, wnCSi wlSaclthdeetyki.r,o.n,ine ,c CC CnP’rsgahh ho, PGg TCiiwiaccorchharae aeIiikggenLcrg nooa Gog6 ghs aII0ILLooLr6 du 66I16L2e00s0 ne6666i0000n6223g02 01I0D/I011DI6 /D131 //23 16033529/2/50505 –A2499 O l WG––ler rOBidncorea’ksdwr ceGe rSkirr nhP eSOaigehprrn iTpkdph ehGepodreaiundrsdgeesnin Open and Close an Object. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 View an Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 2 chapter Entering and Editing Data Enter New Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 Navigate between Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Burrows Editing Edit Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 Canton Art Attach Files to Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 Lauder Editing Smith Open, Save, and Remove File Attachments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Da Editing Insert an OLE Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 eeeeem L Open, Edit, and Remove OLE Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 Jordan Art Enter Data in a Multivalued Field . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 Parker Editing Delete Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 yKane Layout a K Resize Datasheet Columns and Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 Sort Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 Display Summary Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 Print a Datasheet or Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 0022__557777665533--ffttoocc..iinndddd vvii 33//1199//1100 99::3388 PPMM 3 chapter Working with Tables Plan Effective Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 Create a Table in Datasheet View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 Save a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 Create a Table in Design View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 Open a Table in Design View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 Rearrange Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 Insert and Delete Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 Understanding Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 # Business Name Change a Field’s Data Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 1 ID 3250 The Good Earth 2 ID 3251 Greiner Garden Understanding Primary and Composite Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 3 ID 3252 Growing Things Set the Primary or Composite Key . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54 4 ID 3253 Flower Power 5 Rename a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55 ID 3254 Wicker Park Ga 6 Delete a Table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 7 ID 3255 Allen’s Greenh Copy a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 8 IIDD 33225567 Bloomin’ Betty 4 chapter Working with Fields Understanding Field Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 Understanding Field Sizes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 Change a Field Size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 Set a Field Caption . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 Set a Field’s Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Set a Default Value . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 Make a Field Required . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 Index a Field . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 DDAATTEE ACprepalyte S amna Irntp Tuatg Ms .a .s .k .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. 6790 MMaarrcchh 1122,, 22001100 Create a Validation Rule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 Create a Record-Level Validation Rule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 0022__557777665533--ffttoocc..iinndddd vviiii 33//1199//1100 99::3388 PPMM Table of Contents 5 chapter Working with Relationships and Lookups Understanding Relationships. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 Create a Relationship between Two Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80 ERAPVDUdreironeirimncdtwa utenao maOr gvRs eeRbetea nejtlaenahlt ac dtRetittohie inRDonleagene st DlshpLiaohaoietpniitnoopa s.dkn bh R.uesa ie.phnps p.icse po...i es....r s t....W ...... i......n ......d ...... o...... w...... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... ....... 88888984235806C33232D32222225255555U555#012345768 S TTTTTT A GFWGGGGGGGGBHAhhhhhhhllrlCrrrrrieT olocoNeeeeeeeereeeeeeewokiwnuu iiiiiietmnnnnnneOGGGGaew’isgrrssssnieeeeeeeoooo en LGrrrrrgggggP oooooPtttttt’MFe raddddoalGGGGGGBTTTTTToooooooeor fwwewhhhhhhhhhheaaaaakEEEmmmmmmmmtneeeeeiiiirrrr EtaaaannnnnnrGhrrrrryddd rrrrrggggggoS’aaaaaeeeRstttttssseeeeeeuhrrrrrnnnnnnnhhhhossssdddddddrrrrrrriiiiipeeeeeeeeeennnnSnnnnngggggsss 333333333I2222222D22555555555#423508671 IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIOLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLL333333333333333333333333333333333333333333333333r8888888888888888888888888888777777777777d7777777777733342222221111100000009999999888888867777777e O r # R 3333333333333333333333333333333332222222222222222222I2222222D5D5555555555555535074920#5E 0 0000000033333S Create a Table for Use as a Field Lookup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92 Create a Field Lookup Based on a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94 Create a Field Lookup with Values That You Specify . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98 Set Up a Multivalued Field . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100 6 chapter Finding and Filtering Data Understanding Find and Replace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104 Find Data by Using Find and Replace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105 Replace Data by Using Find and Replace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106 Filter to Show Only Blank or Nonblank Entries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108 Filter by Selection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 Filter for Multiple Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110 Using Text Filters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111 Saving Filter by Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112 Save a Filter As a Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114 0022__557777665533--ffttoocc..iinndddd vviiiiii 33//1199//1100 99::3388 PPMM