Thư viện tri thức trực tuyến
Kho tài liệu với 50,000+ tài liệu học thuật
© 2023 Siêu thị PDF - Kho tài liệu học thuật hàng đầu Việt Nam
How to do everything with Microsoft Office Access 2003
Nội dung xem thử
Mô tả chi tiết
Microsoft®
Office
Access
2003
HowTo-Tght (8) / How to Do Everything with Microsoft Office Access 2003 / Andersen / 222938-1 / Front Matter
blind folio i
P:\010Comp\HowTo8\938-1\fm.vp
Friday, August 08, 2003 2:31:44 PM
Color profile: Generic CMYK printer profile
Composite Default screen
HowTo-Tght (8) / How to Do Everything with Microsoft Office Access 2003 / Andersen / 222938-1 / Front Matter
Blind Folio FM:ii
P:\010Comp\Build8\769-9\fm.vp
Tuesday, January 07, 2003 1:28:08 PM
Color profile: Generic CMYK printer profile
Composite Default screen
This page intentionally left blank
Microsoft®
Office
Access
2003
Virginia Andersen
McGraw-Hill/Osborne
New York Chicago San Francisco Lisbon
London Madrid Mexico City Milan New Delhi
San Juan Seoul Singapore Sydney Toronto
HowTo-Tght (8) / How to Do Everything with Microsoft Office Access 2003 / Andersen / 222938-1 / Front Matter
blind folio iii
P:\010Comp\HowTo8\938-1\fm.vp
Friday, August 08, 2003 2:31:46 PM
Color profile: Generic CMYK printer profile
Composite Default screen
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
contact McGraw-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.
How to Do Everything with Microsoft® Office Access 2003
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 FGR FGR 019876543
ISBN 0-07-222938-1
Publisher: Brandon A. Nordin
Associate Publisher
& Editor-in-Chief: Scott Rogers
Acquisitions Editor: Megg Morin
Senior Project Editor: Jody McKenzie
Acquisitions Coordinators: Tana Allen, Athena Honore
Technical Editor: Margaret Levine Young
Copy Editor: Andy Saff
Proofreader: Stefany Otis
Indexer: Valerie Perry
Computer Designers: Tara A. Davis, Lucie Erickson, Jim Kussow, Dick Schwartz
Illustrators: Kathleen Fay Edwards, Melinda Moore Lytle, Lyssa Wald
Series Design: Mickey Galicia
Cover Series Design: Dodie Shoemaker
Cover Illustration: Eliot Bergman
This book was composed with Corel VENTURA™ Publisher.
Information has been obtained by McGraw-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.
HowTo-Tght (8) / How to Do Everything with Microsoft Office Access 2003 / Andersen / 222938-1 / Front Matter
Blind Folio FM:iv
P:\010Comp\HowTo8\938-1\fm.vp
Friday, August 08, 2003 2:31:46 PM
Color profile: Generic CMYK printer profile
Composite Default screen
About the Author
When Grace Hopper, the originator of the concept of program compilers, told Virginia Andersen
in 1948 that there was a future for women in digital computers, she responded, “In what?”
Nevertheless, after graduating from Stanford University, Virginia pursued the idea and carved
out a career applying computers to many challenging projects such as mapping the moon’s
surface in preparation for the Apollo landing; managing large industrial construction projects;
conducting undersea surveillance; simulating navy weapon systems; and building reliability
mathematical models. She also found time to teach computer science, mathematics, and system
analysis at the graduate and undergraduate levels at several Southern California universities.
Since retiring from the defense industry, Virginia has written or contributed to more than 30
books about personal computer–based applications, including database management, word
processing, and spreadsheet analysis. She has recently completed the story of her varied uses for
computers over the last 50 years, Digital Recall: Computers Aren’t the Only Ones with Memory.
HowTo-Tght (8) / How to Do Everything with Microsoft Office Access 2003 / Andersen / 222938-1 / Front Matter
Blind Folio FM:v
P:\010Comp\HowTo8\938-1\fm.vp
Friday, August 08, 2003 2:31:47 PM
Color profile: Generic CMYK printer profile
Composite Default screen
HowTo-Tght (8) / How to Do Everything with Microsoft Office Access 2003 / Andersen / 222938-1 / Front Matter
Blind Folio FM:ii
P:\010Comp\Build8\769-9\fm.vp
Tuesday, January 07, 2003 1:28:08 PM
Color profile: Generic CMYK printer profile
Composite Default screen
This page intentionally left blank
Contents at a Glance
Part I Get Started
1 Get Acquainted with Access 2003 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
2 Create a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
3 Create and Modify Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
4 Relate Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
5 Enter and Edit Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
Part II Retrieve and Present Information
6 Sort, Filter, and Print Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
7 Extract Information with Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
8 Create Advanced Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
9 Understand Form and Report Design Basics . . . . . . . . . . . . . . . . . . . . . . . 189
10 Create Custom Forms and Subforms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217
11 Create and Customize Reports and Subreports . . . . . . . . . . . . . . . . . . . . . . 255
12 Create Charts and Graphs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295
Part III Improve the Access 2003 Workplace
13 Customize the Workplace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 319
14 Speed Up Your Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 341
15 Automate with Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357
16 Customize Menus and Toolbars . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 377
17 Create Custom Switchboards and Dialog Boxes . . . . . . . . . . . . . . . . . . . . 399
vii
HowTo-Tght (8) / How to Do Everything with Microsoft Office Access 2003 / Andersen / 222938-1 /Front Matter
P:\010Comp\HowTo8\938-1\fm.vp
Friday, August 08, 2003 2:31:47 PM
Color profile: Generic CMYK printer profile
Composite Default screen
Part IV Exchange Data with Others
18 Exchange Database Objects and Text . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 425
19 Exchange Data with Outside Sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447
20 Share with Multiple Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 465
21 Secure a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 477
Appendix Convert to Access 2003 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 501
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 509
viii How to Do Everything with Microsoft Office Access 2003
HowTo-Tght (8) / How to Do Everything with Microsoft Office Access 2003 / Andersen / 222938-1 / Front Matter
P:\010Comp\HowTo8\938-1\fm.vp
Friday, August 08, 2003 2:31:47 PM
Color profile: Generic CMYK printer profile
Composite Default screen
Contents
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi
PART I Get Started
CHAPTER 1 Get Acquainted with Access 2003 . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Start Access and Open a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Take a Tour of the Access Window . . . . . . . . . . . . . . . . . . . . . . . 5
Open a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Open the Sample Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Tour the Database Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Look at Menu Options and Toolbar Buttons . . . . . . . . . . . . . . . . 12
Use Shortcut Menus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Open a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Take a Tour of the Datasheet View . . . . . . . . . . . . . . . . . . . . . . . . 15
Check Out the Subdatasheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Get Help When You Need It . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Ask a Question . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Use the Microsoft Access Help Task Pane . . . . . . . . . . . . . . . . . . 21
Ask the Office Assistant . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Ask What’s This? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Get Help with What You’re Doing . . . . . . . . . . . . . . . . . . . . . . . . 23
CHAPTER 2 Create a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Design an Efficient Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Determine the Goals of the Database . . . . . . . . . . . . . . . . . . . . . . 28
Distribute the Data Among the Tables . . . . . . . . . . . . . . . . . . . . . 28
Identify the Data Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Specify Key Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Define Table Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Complete the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Create a Database with the Database Wizard . . . . . . . . . . . . . . . . . . . . . 33
Preview the Database Templates . . . . . . . . . . . . . . . . . . . . . . . . . 33
Work with the Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
ix
HowTo-Tght (8) / How to Do Everything with Microsoft Office Access 2003 / Andersen / 222938-1 /Front Matter
P:\010Comp\HowTo8\938-1\fm.vp
Friday, August 08, 2003 2:31:47 PM
Color profile: Generic CMYK printer profile
Composite Default screen
Run the New Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Start with a Blank Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
CHAPTER 3 Create and Modify Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Create a Table with the Table Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
Choose a Table and Add Fields . . . . . . . . . . . . . . . . . . . . . . . . . . 42
Set the Primary Key . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Relate to Existing Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Create a Table from Scratch in Design View . . . . . . . . . . . . . . . . . . . . . 47
Tour the Table Design View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Add Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
Choose a Primary Key . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
Create Other Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
Create a Table in Datasheet View . . . . . . . . . . . . . . . . . . . . . . . . . 60
Save the Table Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
Modify the Table Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
Switch Table Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
Add or Delete Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
Change the Field Order . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
Change a Field Name or Type . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
Change a Field Size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
Modify or Delete the Primary Key . . . . . . . . . . . . . . . . . . . . . . . . 65
Ensure Data Validity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
Define Field Validation Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
Define a Record Validation Rule . . . . . . . . . . . . . . . . . . . . . . . . . 67
Require an Entry and Prevent Duplicates . . . . . . . . . . . . . . . . . . . 69
Handle Blank Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
Assign a Default Value . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
Copy an Existing Table Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
CHAPTER 4 Relate Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
Define a Relationship . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
Use Ready-Made Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . 74
Use the Relationships Window . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
View and Edit Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
Modify or Delete a Relationship . . . . . . . . . . . . . . . . . . . . . . . . . 85
Change a Table Design from the Relationships Window . . . . . . . 86
Print the Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
CHAPTER 5 Enter and Edit Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
Enter New Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
Copy and Move Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
Insert Pictures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
Insert Hyperlinks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
x How to Do Everything with Microsoft Office Access 2003
HowTo-Tght (8) / How to Do Everything with Microsoft Office Access 2003 / Andersen / 222938-1 / Front Matter
P:\010Comp\HowTo8\938-1\fm.vp
Friday, August 08, 2003 2:31:47 PM
Color profile: Generic CMYK printer profile
Composite Default screen
Customize Data Entry . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
Add Custom Input Masks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
Create Lookup Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Change the Datasheet Appearance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
Move and Resize Columns and Rows . . . . . . . . . . . . . . . . . . . . . 106
Freeze and Hide Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
Change the Font . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Change Grid Lines and Cell Appearance . . . . . . . . . . . . . . . . . . . 110
Set Datasheet Default Options . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Change Table Definition in Datasheet View . . . . . . . . . . . . . . . . . . . . . . 112
Insert/Delete a Subdatasheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112
Insert/Delete a Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Change Field Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Edit Record Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Locate Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
Find and Replace Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
Delete Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
PART II Retrieve and Present Information
CHAPTER 6 Sort, Filter, and Print Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
Sort Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
Sort on a Single Field . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
Sort by Two or More Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
Save the Sort Order . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
Filter Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
Filter By Selection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
Use an Exclusion Filter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
Filter By Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
Filter For . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
Filter with Advanced Filter/Sort . . . . . . . . . . . . . . . . . . . . . . . . . . 133
Save a Filter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135
Remove and Delete Filters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
Preview and Print Sorted or Filtered Table Data . . . . . . . . . . . . . . . . . . . 136
CHAPTER 7 Extract Information with Queries . . . . . . . . . . . . . . . . . . . . . . . . . . 139
Create a Select Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
Use the Simple Query Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
Tour the Query Design Window . . . . . . . . . . . . . . . . . . . . . . . . . . 143
Without the Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
Relate Multiple Tables in a Query . . . . . . . . . . . . . . . . . . . . . . . . 146
Add/Remove Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148
Run and Save the Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
Hide and Show Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150
Contents xi
HowTo-Tght (8) / How to Do Everything with Microsoft Office Access 2003 / Andersen / 222938-1 / Front Matter
P:\010Comp\HowTo8\938-1\fm.vp
Friday, August 08, 2003 2:31:47 PM
Color profile: Generic CMYK printer profile
Composite Default screen
Specify the Record Order . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
Show Highest or Lowest Values . . . . . . . . . . . . . . . . . . . . . . . . . . 152
Add Selection Criteria . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Use Wildcards and Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Use a Single Criterion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
Use Multiple Criteria . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
Get Help from the Expression Builder . . . . . . . . . . . . . . . . . . . . . 156
Set Query Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158
Modify a Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159
Insert a Field and Change the Field Order . . . . . . . . . . . . . . . . . . 159
Change Field Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
Perform Calculations in a Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
Add a Calculated Field . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
Summarize with the Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
Summarize in the Query Design . . . . . . . . . . . . . . . . . . . . . . . . . . 164
Create Special Queries with the Query Wizard . . . . . . . . . . . . . . . . . . . . 166
Create a Find Duplicates Query . . . . . . . . . . . . . . . . . . . . . . . . . . 166
Create a Find Unmatched Query . . . . . . . . . . . . . . . . . . . . . . . . . 167
Create a Crosstab Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
CHAPTER 8 Create Advanced Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Create Special Purpose Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
Parameter Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
AutoLookup Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
Update Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
Append Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
Delete Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
Make-Table Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
Look at Structured Query Language (SQL) . . . . . . . . . . . . . . . . . . . . . . 185
Review SQL Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
Create a Subquery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187
Define a Criterion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187
Define a New Field . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188
CHAPTER 9 Understand Form and Report Design Basics . . . . . . . . . . . . . . . . . 189
Use AutoForm and AutoReport Features . . . . . . . . . . . . . . . . . . . . . . . . 190
Common Form and Report Design Elements . . . . . . . . . . . . . . . . . . . . . 191
Understand Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192
Work in the Design Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
Start a New Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
Tour the Design Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194
Add Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197
Modify Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202
Select Controls and Other Objects . . . . . . . . . . . . . . . . . . . . . . . . 202
Move and Resize Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204
xii How to Do Everything with Microsoft Office Access 2003
HowTo-Tght (8) / How to Do Everything with Microsoft Office Access 2003 / Andersen / 222938-1 / Front Matter
P:\010Comp\HowTo8\938-1\fm.vp
Friday, August 08, 2003 2:31:48 PM
Color profile: Generic CMYK printer profile
Composite Default screen
Align and Space Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206
Use Property Sheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206
Use the Formatting Toolbar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
Format Conditionally . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
Change a Control Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212
Delete Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212
Modify Form or Report Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213
Change the Record Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213
Apply Filters and Sort Orders . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214
Use AutoFormat . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214
Add a Background Picture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215
CHAPTER 10 Create Custom Forms and Subforms . . . . . . . . . . . . . . . . . . . . . . . 217
Create a New Form Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218
Use the Form Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220
Create a Form Without the Wizard . . . . . . . . . . . . . . . . . . . . . . . . 221
Modify the Form Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
Add Form Header and Footer Sections . . . . . . . . . . . . . . . . . . . . 223
Place and Customize Data-Related Controls . . . . . . . . . . . . . . . . 223
Add Yes/No Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231
Add User-Interactive Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . 234
Use the Form for Data Entry . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
Size the Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238
Navigate in the Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238
Change the Tab Order . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238
Locate Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239
Sort and Filter Data in a Form . . . . . . . . . . . . . . . . . . . . . . . . . . . 239
View Multiple Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240
Create a Multiple-Page Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241
Add a Page Break . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
Add a Tab Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
Customize a Tab Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
Add Special Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
Add Calculated Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
Add an AutoDial Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
Create a Hierarchical Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246
Use the Form Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246
Use the Subform Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248
Use the Hierarchical Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251
Modify a Subform . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251
Add Custom User Guidance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
Add Data Validation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
Validate with Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253
Validate with Events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253
Contents xiii
HowTo-Tght (8) / How to Do Everything with Microsoft Office Access 2003 / Andersen / 222938-1 / Front Matter
P:\010Comp\HowTo8\938-1\fm.vp
Friday, August 08, 2003 2:31:48 PM
Color profile: Generic CMYK printer profile
Composite Default screen
CHAPTER 11 Create and Customize Reports and Subreports . . . . . . . . . . . . . . . 255
Start a New Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 256
Use the Report Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257
Preview and Print the Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262
Work in the Print Preview Window . . . . . . . . . . . . . . . . . . . . . . . 262
Use the Layout Preview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 264
Print the Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 264
Modify the Report Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266
Examine the Report Sections . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266
Set Report and Section Properties . . . . . . . . . . . . . . . . . . . . . . . . 267
Change the Report Style . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 268
Add Page Numbers and Date/Time Controls . . . . . . . . . . . . . . . . 268
Save the Report Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270
Sort and Group Records in a Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270
Change the Sort Order . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271
Add Group Sections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271
Customize Group Headers and Footers . . . . . . . . . . . . . . . . . . . . 273
Add Summaries and Running Totals . . . . . . . . . . . . . . . . . . . . . . 274
Apply the Finishing Touches . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275
Modify and Add Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275
Create a Summary Report with the Report Wizard . . . . . . . . . . . 278
Print an Alphabetic Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280
Add a Subreport . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281
Create a Subreport with the Report Wizard . . . . . . . . . . . . . . . . . 282
Create a Subreport Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283
Insert an Existing Subreport . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286
Link the Report and Subreport . . . . . . . . . . . . . . . . . . . . . . . . . . . 287
Modify a Subreport Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 288
Design a Multiple-Column Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289
Print Mailing Labels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290
Use the Label Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291
CHAPTER 12 Create Charts and Graphs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295
Choose a Chart Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296
Create a New Chart with the Chart Wizard . . . . . . . . . . . . . . . . . . . . . . . 296
Select the Data for the Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296
Use the Access Chart Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299
Save the Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302
Link the Chart to Record Data . . . . . . . . . . . . . . . . . . . . . . . . . . . 302
Add an Existing Chart to a Form or Report . . . . . . . . . . . . . . . . . . . . . . 303
Modify the Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 304
Modify with Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305
Edit with Microsoft Graph . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308
xiv How to Do Everything with Microsoft Office Access 2003
HowTo-Tght (8) / How to Do Everything with Microsoft Office Access 2003 / Andersen / 222938-1 / Front Matter
P:\010Comp\HowTo8\938-1\fm.vp
Friday, August 08, 2003 2:31:48 PM
Color profile: Generic CMYK printer profile
Composite Default screen