Sams Teach Yourself Microsoft Access 2002 in 21 Days (Sams Teach Yourself)

605 Pages·2016·15.42 MB·English

by  Paul Cassel

00 103301 FM 8/14/03 8:18 AM Page i
Paul Cassel
Craig Eddy
Jon Price
Teach Yourself
MS Access
2002
21
in Days
800 East 96th St.,Indianapolis,Indiana,46240 USA
00 103301 FM 8/14/03 8:18 AM Page ii
Sams Teach Yourself Microsoft Access ASSOCIATEPUBLISHER
Linda Engelman
2002 in 21 Days
ACQUISITIONSEDITORS
Copyright © 2002 by Sams Publishing Neil Rowe
Rosemarie Graham
All rights reserved. No part of this book shall be reproduced,stored in a
retrieval system,or transmitted by any means,electronic,mechanical,photo- DEVELOPMENTEDITOR
copying,recording,or otherwise,without written permission from the publish- Robyn Thomas
er. No patent liability is assumed with respect to the use of the information
MANAGINGEDITOR
contained herein. Although every precaution has been taken in the preparation
Charlotte Clapp
of this book,the publisher and author assume no responsibility for errors or
omissions. Nor is any liability assumed for damages resulting from the use of PROJECTEDITORS
the information contained herein. Heather McNeill
Leah Kirkpatrick
International Standard Book Number:0-672-32103-3
COPYEDITOR
Library of Congress Catalog Card Number:00-109717
Michael Dietsch
Printed in the United States of America
INDEXER
First Printing:December 2001 Becky Hornyak
04 03 4 3 PROOFREADER
Plan-It Publishing
Trademarks
TECHNICALEDITOR
All terms mentioned in this book that are known to be trademarks or service Robyn Thomas
marks have been appropriately capitalized. Sams cannot attest to the accuracy TEAMCOORDINATOR
of this information. Use of a term in this book should not be regarded as
Lynne Williams
affecting the validity of any trademark or service mark.
MEDIADEVELOPER
Warning and Disclaimer Dan Scherf
INTERIORDESIGNER
Every effort has been made to make this book as complete and as accurate as
Gary Adair
possible,but no warranty or fitness is implied. The information provided is on
an “as is”basis. The authors and the publisher shall have neither liability nor COVERDESIGNER
responsibility to any person or entity with respect to any loss or damages aris- Aren Howell
ing from the information contained in this book or from the use of the CD or
programs accompanying it. PAGELAYOUT
Joe Millay
Bulk Sales
Sams Publishing offers excellent discounts on this book when ordered in quan-
tity for bulk purchases or special sales. For more information,please contact
U.S. Corporate and Government Sales
1-800-382-3419
[e-mail]
For sales outside of the U.S.,please contact
International Sales
1-317-428-3341
[e-mail]
00 103301 FM 11/27/01 9:42 AM Page iii
Contents at a Glance
Introduction 1
Week 1 At a Glance 3
Day 1 Database Concepts 5
2 Learning the Basics to Develop an Access Database Application 33
3 Automatic Access 69
4 The Data Foundation—The Table 87
5 Simple Forms 119
6 Introducing Queries 153
7 Basic Reports 181
Week 1 In Review 213
Week 2 At a Glance 215
Day 8 A Macro Primer 217
9 Refining Your Tables 245
10 Improving Your Forms 273
11 Manipulating Queries 293
12 Getting Reports Right 315
13 Learning Structured Query Language or SQL 337
14 Special Query Uses 351
Week 2 In Review 377
Week 3 At a Glance 379
Day 15 Introduction to VBA Concepts 381
16 VBA Language Elements—Part 1 405
17 VBA Programming—Part 2 435
18 Objects and Collections 463
19 Extending Access Using VBA 483
00 103301 FM 11/27/01 9:42 AM Page iv
20 Maintaining and Securing Access Databases 509
21 Access on the Web 523
Week 3 in Review 543
A Answers to Quizzes 545
Index 557
00 103301 FM 11/27/01 9:42 AM Page v
Contents
Introduction 1
WEEK 1 At a Glance 3
DAY 1 Database Concepts 5
Today You Will Learn..............................................................................................5
Access in Theory and Practice................................................................................6
Data Isn’t Information ............................................................................................7
Changing Data into Information........................................................................8
Discovering Information....................................................................................9
The Key to the Transformation........................................................................10
The Relational Model and Access ........................................................................11
Database Structure in Theory................................................................................18
Database Structure in Practice ..............................................................................21
Choosing the Primary Key ..............................................................................21
Choosing Relationship Types ..........................................................................22
Understanding Normalization..........................................................................23
Informal Rules to Live By ....................................................................................26
The Mission of Access..........................................................................................27
Hardware Requirements........................................................................................28
Access Databases Maintenance ............................................................................29
Summary................................................................................................................30
Q&A......................................................................................................................31
Workshop ..............................................................................................................31
Quiz..................................................................................................................31
DAY 2 Learning the Basics to Develop an Access Database Application 33
Today You Will Learn............................................................................................33
The Access User Interface ....................................................................................34
Task Pane..........................................................................................................34
Objects and Actions..........................................................................................36
Groups..............................................................................................................39
Object Views..........................................................................................................40
Opening and Closing Object Views ................................................................40
Managing Access Object Views and Windows................................................42
Options—Customizing Access Globally ..............................................................43
The Objects of Access ..........................................................................................46
Tables................................................................................................................46
Queries..............................................................................................................48
Forms................................................................................................................50
00 103301 FM 11/27/01 9:42 AM Page vi
vi Teach Yourself MSAccess 2002 in 21 Days
Reports..............................................................................................................52
Pages ................................................................................................................54
Macros..............................................................................................................55
Modules............................................................................................................57
Planning Your Database Application ....................................................................59
Don’t Be a Slave to Convention ......................................................................60
Don’t Worry Yet................................................................................................60
Getting Help—The Office Assistant......................................................................61
Real Experts Shun Help—Ha! ........................................................................61
The Ways of Accessing Help............................................................................63
What’s This? ....................................................................................................63
Toolbars..................................................................................................................65
Summary................................................................................................................66
Q&A......................................................................................................................67
Workshop ..............................................................................................................67
Quiz..................................................................................................................67
DAY 3 Automatic Access 69
Today You Will Learn............................................................................................69
Why Automatic Access?........................................................................................70
Database Wizard or Template................................................................................70
The Template....................................................................................................71
Comments on Templates..................................................................................73
Table Wizard..........................................................................................................74
The Query Wizard..................................................................................................76
Automatic Forms ..................................................................................................78
Creating a Form Using AutoForm....................................................................78
Creating a Form Using the Form Wizard ........................................................80
Automatic Reports ................................................................................................81
Pages......................................................................................................................83
Summary................................................................................................................85
Q&A......................................................................................................................85
Workshop ..............................................................................................................86
Quiz..................................................................................................................86
Exercises ..........................................................................................................86
DAY 4 The Data Foundation—The Table 87
Today You Will Learn............................................................................................87
Understanding the Importance of Table Design....................................................88
Examining Tables..................................................................................................88
Creating Tables......................................................................................................91
Understanding Table Design Basics................................................................92
Using the Table Design Grid............................................................................93
00 103301 FM 11/27/01 9:42 AM Page vii
Contents vii
Using the Datasheet View to Make a Table ....................................................95
Using the Table Wizard to Create a Table........................................................97
Analyzing the Wizard’s Table................................................................................98
Violation of First Normal Form ......................................................................99
Possible Wrong Field Types ............................................................................99
Linking Fields—the Heart of the Relationship ..................................................100
Adding Data to Linked Tables Within the Datasheet View............................103
Understanding the Benefits of a Relationship ....................................................103
The Best Is Yet To Come................................................................................104
Not Convinced?..............................................................................................105
Seeing It Yourself ..........................................................................................105
Using Table and Field Properties........................................................................106
Understanding Key Fields and Indexes ..............................................................108
Sorting and Filtering in Tables............................................................................110
Sorting............................................................................................................110
Filtering..........................................................................................................112
Finding Data........................................................................................................115
Find Cautions..................................................................................................116
Summary..............................................................................................................116
Q&A....................................................................................................................116
Workshop ............................................................................................................117
Quiz................................................................................................................117
Exercises ........................................................................................................118
DAY 5 Simple Forms 119
Today You Will Learn..........................................................................................119
Why This Chapter Now ......................................................................................120
Bound and Unbound Forms and Fields ..............................................................120
Control and Record Source Properties................................................................121
Name Property—Standard Naming Conventions................................................123
Creating a First Form..........................................................................................126
Form Design View ..............................................................................................129
Option Group..................................................................................................135
Form Control Alignment................................................................................136
Form Headers and Footers..................................................................................140
Other Form Format Properties............................................................................141
Tab Order ............................................................................................................142
Finding,Filtering,and Sorting in Forms ............................................................146
Summary..............................................................................................................147
Q&A....................................................................................................................147
Workshop ............................................................................................................149
Quiz................................................................................................................149
Exercises ........................................................................................................149
00 103301 FM 11/27/01 9:42 AM Page viii
viii Teach Yourself MSAccess 2002 in 21 Days
DAY 6 Introducing Queries 153
Today You Will Learn..........................................................................................153
The Query in Access............................................................................................154
A First Query ......................................................................................................154
The Simple Query Wizard ..................................................................................158
Filtering and Sorting in Queries..........................................................................160
Query Criteria......................................................................................................162
Quick Query Facts..........................................................................................166
Multi-table Queries..............................................................................................168
Multi-table Queries Without Existing Links ......................................................172
Intermediate Criteria............................................................................................174
Wildcards in Like and Between ....................................................................175
Summary..............................................................................................................177
Q&A....................................................................................................................178
Workshop ............................................................................................................178
Quiz................................................................................................................178
Exercises ........................................................................................................178
DAY 7 Basic Reports 181
Today You Will Learn..........................................................................................181
Reports in Access................................................................................................182
The AutoReport Wizard ......................................................................................183
Report Wizard......................................................................................................184
Report Preview Details ..................................................................................187
The Report Design View......................................................................................189
Grouping in Reports............................................................................................196
The Group......................................................................................................197
Properties........................................................................................................198
Mailing Labels ....................................................................................................201
Expression Details Explained........................................................................204
Page Layout Settings ..........................................................................................204
Sums,Subtotals,and Running Sums ..................................................................205
Summary..............................................................................................................211
Q&A....................................................................................................................211
Workshop ............................................................................................................212
Quiz................................................................................................................212
Exercises ........................................................................................................212
WEEK 1 In Review 213
00 103301 FM 11/27/01 9:42 AM Page ix
Contents ix
WEEK 2 At a Glance 215
DAY 8 A Macro Primer 217
Today You Will Learn..........................................................................................217
Macros and Access..............................................................................................218
And The Point Is?..........................................................................................218
Elements of a Macro............................................................................................218
The Macro Design Grid..................................................................................220
A Very Simple Macro ....................................................................................220
Deleting and Inserting Macro Actions ..........................................................224
Macros and Events..............................................................................................225
Macro Names ......................................................................................................230
More About Macro Actions............................................................................235
Conditional Macros..............................................................................................236
Summary..............................................................................................................240
Q&A....................................................................................................................241
Workshop ............................................................................................................242
Quiz................................................................................................................242
Exercises ........................................................................................................242
DAY 9 Refining Your Tables 245
Today You Will Learn..........................................................................................245
Tables and Data Integrity....................................................................................246
Data Validation Using Table Expressions............................................................248
Data Lookup in Tables........................................................................................251
Input Masks..........................................................................................................254
Using OLE Objects and Hyperlinks in Tables....................................................259
Inserting OLE Objects....................................................................................260
Inserting Hyperlinks ......................................................................................264
Working Around the Hyperlink Edit Catch-22..............................................265
Using Free Floating Hyperlinks ....................................................................265
Saving Tables as HTML......................................................................................268
Summary..............................................................................................................270
Q&A....................................................................................................................271
Workshop ............................................................................................................271
Quiz................................................................................................................271
Exercises ........................................................................................................272
DAY 10 Improving Your Forms 273
Today You Will Learn..........................................................................................273
Using Color to Enhance Forms ..........................................................................274
Altering Object Color ....................................................................................274