Table Of Content

Color profile: Generic CMYK printer profile Composite Default screen Complete Reference/ Microsoft Office Access 2003: TCR / Anderson / 222917-9 Blind Folioi Microsoft Office ® Access 2003: The Complete Reference P:\010Comp\CompRef8\917-9\fm.vp Monday, August 11, 2003 1:13:43 PM Color profile: Generic CMYK printer profile Composite Default screen CCoommpplleettee RReeffeerreennccee// MMiiccrroossoofftt OOffffiiccee AAcccceessss 22000033:: TTCCRR // AAnnddeerrssoonn // 222222991177--99 BBlliinndd FFoolliiooiiii About the Author Virginia Andersenbecame a writer and consultant after retiring from her defense contracting career. Since then, she has written over 35 books about personal-computer-based applications, including database management, word processing, and spreadsheet analysis. Virginia spent nearly 15 years teaching computer science, mathematics and systems analysis at the graduate and undergraduate levels at several southern California universities. During her years as a programmer/systems analyst, Virginia used computers for many diverse projects, including lunar mapping for the Apollo astronauts’landing,reliability engineering,underseasurveillance,weapon system simulation, and naval communications. She has also recently published her memoirs, “Digital Recall: Computers Aren’t the Only Ones with Memory” describing many of these hands-on experiences. P:\010Comp\CompRef8\917-9\fm.vp Monday, August 11, 2003 1:13:43 PM Color profile: Generic CMYK printer profile Composite Default screen Complete Reference/ Microsoft Office Access 2003: TCR / Anderson / 222917-9 Blind Folioiii Microsoft Office ® Access 2003: The Complete Reference Virginia Andersen McGraw-Hill/Osborne New York Chicago San Francisco Lisbon London Madrid Mexico City Milan New Delhi San Juan Seoul Singapore Sydney Toronto P:\010Comp\CompRef8\917-9\fm.vp Monday, August 11, 2003 1:13:43 PM Color profile: Generic CMYK printer profile Composite Default screen Complete Reference/ Microsoft Office Access 2003: TCR / Anderson / 222917-9 / Front Matter Blind FolioFM:iv McGraw-Hill/Osborne 2100 Powell Street, 10th Floor Emeryville, California 94608 U.S.A. To arrange bulk purchase discounts for sales promotions, premiums, or fund-raisers, please contactMcGraw-Hill/Osborne at the above address. For information on translations or book distributors outside the U.S.A., please see the International Contact Information page immediately following the index of this book. Microsoft®Office Access 2003: The Complete Reference Copyright © 2003 by The McGraw-Hill Companies. All rights reserved. Printed in the United States of America. Except as permitted under the Copyright Act of 1976, 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 permission of publisher, with the exception that the program listings may be entered, stored, and executed in a computer system, but they may not be reproduced for publication. 1234567890 DOC DOC 019876543 Book p/n 0-07-222918-7 and CD p/n 0-07-222919-5 parts of ISBN 0-07-222917-9 Publisher Copy Editor Brandon A. Nordin Marcia Baker Vice President & Associate Publisher Proofreaders Scott Rogers Marian Selig, Susan Carlson Greene Acquisitions Editor Indexer Megg Morin Jack Lewis Project Editor Composition Elizabeth Seymour Carie Abrew, Tara A. Davis, Lucie Ericksen Acquisitions Coordinators Illustrators Tana Allen, Athena Honore Kathleen Fay Edwards, Melinda Moore Lytle, Lyssa Wald Technical Editor Margaret Levine Young Series Design Peter F. Hancik, Lyssa Wald This book was composed with Corel VENTURA™Publisher. Information has been obtained byMcGraw-Hill/Osborne from sources believed to be reliable. However, because of the possibility of human or mechanical error by our sources,McGraw-Hill/Osborne, or others,McGraw-Hill/Osborne does not guarantee the accuracy, adequacy, or completeness of any information and is not responsible for any errors or omissions or the results obtained from the use of such information. P:\010Comp\CompRef8\917-9\fm.vp Monday, August 11, 2003 1:13:43 PM Color profile: Generic CMYK printer profile Composite Default screen Complete Reference/ Microsoft Office Access 2003: TCR / Anderson / 222917-9 / Front Matter FM:v Contents at a Glance Part I Getting Started 1 Quick Tour of Microsoft Office Access 2003 . . . . . . . . . . . . . . . . . . . . 3 2 The World of Relational Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 3 Creating a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 4 Creating and Modifying Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 5 Relating Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95 6 Entering and Editing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113 Part II Retrieving and Presenting Information 7 Sorting, Filtering, and Printing Records . . . . . . . . . . . . . . . . . . . . . . . . 159 8 Extracting Information with Queries . . . . . . . . . . . . . . . . . . . . . . . . . . 179 9 Creating Advanced Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221 10 Creating Form and Report Designs . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245 11 Using the Form Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283 12 Customizing Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 307 13 Using the Report Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345 14 Customizing Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375 15 Creating Charts and Graphs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409 P:\010Comp\CompRef8\917-9\fm.vp Monday, August 11, 2003 1:13:43 PM Color profile: Generic CMYK printer profile Composite Default screen Complete Reference/ Microsoft Office Access 2003: TCR / Anderson / 222917-9 / Front Matter vi Microsoft Office Access 2003: The Complete Reference Part III Improving the Workplace 16 Customizing the Workplace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 437 17 Improving Database Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461 18 Understanding Events and the Event Model . . . . . . . . . . . . . . . . . . . . 481 19 Automating with Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 497 20 Customizing Menus and Toolbars . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 525 21 Creating Custom Switchboards and Dialog Boxes . . . . . . . . . . . . . . . 553 Part IV Sharing Data with Others 22 Exchanging Database Objects and Text . . . . . . . . . . . . . . . . . . . . . . . . 585 23 Exchanging Data with Outside Sources . . . . . . . . . . . . . . . . . . . . . . . . 609 24 Sharing with Multiple Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 629 25 Securing a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 643 A Converting to Access 2003 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 667 B What’s on the CD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 675 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 679 P:\010Comp\CompRef8\917-9\fm.vp Monday, August 11, 2003 1:13:44 PM Color profile: Generic CMYK printer profile Composite Default screen Complete Reference/ Microsoft Office Access 2003: TCR / Anderson / 222917-9 / Front Matter FM:vii Contents Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv Part I Getting Started 1 Quick Tour of Microsoft Office Access 2003 . . . . . . . . . . . . . . . . . . . 3 Starting Access and Opening a Database . . . . . . . . . . . . . . . . . . . . . . . 3 Touring the Access Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Opening a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Touring the Database Window . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Looking at Menu Options and Toolbar Buttons . . . . . . . . . . . 12 Using Shortcut Menus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 Opening a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Touring the Datasheet View . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 Looking at a Subdatasheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 Looking at Data in a Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Looking at the Wizards . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Getting Help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Asking a Question . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 Using the Microsoft Access Help Task Pane . . . . . . . . . . . . . . 23 Asking the Office Assistant . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 Asking What’s This? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 Getting Help with What You’re Doing . . . . . . . . . . . . . . . . . . . 26 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 P:\010Comp\CompRef8\917-9\fm.vp Monday, August 11, 2003 1:33:24 PM Color profile: Generic CMYK printer profile Composite Default screen Complete Reference/ Microsoft Office Access 2003: TCR / Anderson / 222917-9 / Front Matter viii Microsoft Office Access 2003: The Complete Reference 2 The World of Relational Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 What Is a Relational Database? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 Purpose of Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Types of Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Referential Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 Defining Database Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 Inspecting the Sample Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 Looking at the Data Distribution . . . . . . . . . . . . . . . . . . . . . . . . 33 Viewing Table Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 The Payoff . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 ACustom Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 ACustom Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 3 Creating a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 Designing the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 Introducing Home Tech Repair . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 Determining Goals of the Database . . . . . . . . . . . . . . . . . . . . . . 42 Distributing the Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 Specifying Key Fields and Relationships . . . . . . . . . . . . . . . . . 46 Completing the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 Using the Database Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 Previewing the Database Templates . . . . . . . . . . . . . . . . . . . . . 48 Stepping Through the Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . 49 Running the New Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 Starting with a Blank Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 4 Creating and Modifying Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 Creating a New Table Structure with the Table Wizard . . . . . . . . . . . 57 Adding Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58 Setting the Primary Key . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 Relating to Existing Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 Creating a New Table from Scratch . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 Touring the Table Design View . . . . . . . . . . . . . . . . . . . . . . . . . 63 Adding Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 Choosing a Primary Key . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 Creating Other Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 Saving the Table Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 P:\010Comp\CompRef8\917-9\fm.vp Monday, August 11, 2003 1:13:44 PM