Microsoft Office Access 2003: The Complete Reference (Osborne Complete Reference Series)

754 Pages·2003·7.82 MB·English

by  Virginia Andersen

Y

L
F

M

A

E

T

Team-Fly®
Color profile: Generic CMYK printer profile
Composite Default screen Complete Reference/ Microsoft Office Access 2003: TCR / Anderson / 222917-9
Blind Folioi
This Book Is Distributed By http://pdfstore.tk/ Please
Make Sure That This E-Book Dont Have Any Or Damage This will cause you
Missing Pages And Missing Tutorials.www.pdfstore.tk
will automaticly `check . is this book is ready for read
Attention :- Before You read this Book Please Visit www.pdfstore.tk and check
you can Free Download any kind of Free matirials from www.pdfstore.tk
web site
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
Color profile: Generic CMYK printer profile
Composite Default screen Complete Reference/ Microsoft Office Access 2003: TCR / Anderson / 222917-9 / Front Matter
Contents ix
Modifying the Table Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Switching Table Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Adding/Deleting Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
Changing the Field Order . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Changing a Field Name or Type . . . . . . . . . . . . . . . . . . . . . . . . 81
Changing a Field Size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
Modifying or Deleting the Primary Key . . . . . . . . . . . . . . . . . . 83
Ensuring Data Validity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
Defining Field Validation Rules . . . . . . . . . . . . . . . . . . . . . . . . . 85
Defining a Record Validation Rule . . . . . . . . . . . . . . . . . . . . . . 87
Requiring an Entry and Preventing Duplicates . . . . . . . . . . . . 88
Handling Blank Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
Assigning a Default Value . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
Copying an Existing Table Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
Setting Table Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
How to Change the Default Table Design Properties . . . . . . . 92
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
5 Relating Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Defining a Relationship . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Ready-Made Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
Using the Relationships Window . . . . . . . . . . . . . . . . . . . . . . . . 96
Viewing and Editing Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
Viewing Existing Relationships . . . . . . . . . . . . . . . . . . . . . . . . . 107
Modifying or Deleting a Relationship . . . . . . . . . . . . . . . . . . . . 107
Changing a Table Design from the Relationships Window . . 108
Printing the Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
6 Entering and Editing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Entering New Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Copying and Moving Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
Inserting Pictures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
Inserting Hyperlinks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
Customizing Data Entry . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
Adding Custom Input Masks . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
Creating Lookup Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
P:\010Comp\CompRef8\917-9\fm.vp
Monday, August 11, 2003 1:13:44 PM