Microsoft Office Access 2007 On Demand

442 Pages·2007·14.17 MB·English

by  Steve Johnson

Access 2007
Microsoft Office
®
Steve Johnson
Perspection, Inc.
Que Publishing
800 East 96th Street
Indianapolis, IN 46240 USA
Microsoft® Office Access 2007 On Demand Publisher
Paul Boger
Copyright © 2007 by Perspection, Inc.
Associate Publisher
All rights reserved. No part of this book shall be reproduced, stored in a
Greg Wiegand
retrieval system, or transmitted by any means, electronic, mechanical, photo-
Acquisitions Editor
copying, recording, or otherwise, without written permission from the publish-
Stephanie McComb
er. No patent liability is assumed with respect to the use of the information
contained herein. Although every precaution has been taken in the prepara- Managing Editor
tion of this book, the publisher and author assume no responsibility for errors Steve Johnson
or omissions. Nor is any liability assumed for damages resulting from the use
Author
of the information contained herein.
Steve Johnson
Technical Editor
Library of Congress Cataloging-in-Publication Data
Alex Williams
Johnson, Steve, 1961-
Production Editor
Microsoft Office Access 2007 On Demand / Steve Johnson
Holly Johnson
p. cm.
Page Layout
ISBN 0-7897-3641-1 Alex Williams
1. Microsoft Access. 2. Database Management. I. Title Emily Atwood
Dori Hernandez
QA76.9.D3J5865 2006
Jennifer L. Ware
005.75’65--dc22
Interior Designers
2006100341
Steve Johnson
Printed and bound in the United States of America Marian Hartsough
First Printing: December 2006 Indexer
09 08 07 06 4 3 2 1 Katherine Stimson
Proofreader
Que Publishing offers excellent discounts on this book when ordered Emily Atwood
in quantity for bulk purchases or special sales. Holly Johnson
For information, please contact: U.S. Corporate and Government Sales Team Coordinator
Michelle Newcomb
1-800-382-3419 or [email protected]
For sales outside the U.S., please contact: International Sales
1-317-428-3341 or [email protected]
Trademarks
All terms mentioned in this book that are known to be trademarks or service
marks have been appropriately capitalized. Que cannot attest to the accuracy
of this information. Use of a term in this book should not be regarded as
affecting the validity of any trademark or service mark.
Microsoft and the Microsoft Office logo are a registered trademarks of
Microsoft Corporation in the United States and/or other countries.
Warning and Disclaimer
Every effort has been made to make this book as complete and as accurate as
possible, but no warranty or fitness is implied. The authors and the publishers
shall have neither liability nor responsibility to any person or entity with
respect to any loss or damage arising from the information contained in this
book.
a
Acknowledgements
Perspection, Inc.
Microsoft Office Access 2007 On Demandhas been created by the professional
trainers and writers at Perspection, Inc. to the standards you’ve come to expect
from Que publishing. Together, we are pleased to present this training book.
Perspection, Inc. is a software training company committed to providing information
and training to help people use software more effectively in order to communicate,
make decisions, and solve problems. Perspection writes and produces software
training books, and develops multimedia and Web-based training. Since 1991, we
have written more than 80 computer books, with several bestsellers to our credit,
and sold over 5 million books.
This book incorporates Perspection’s training expertise to ensure that you’ll receive
the maximum return on your time. You’ll focus on the tasks and skills that increase
productivity while working at your own pace and convenience.
We invite you to visit the Perspection Web site at:
www.perspection.com
Acknowledgements
The task of creating any book requires the talents of many hard-working people a
pulling together to meet impossible deadlines and untold stresses. We’d like to
thank the outstanding team responsible for making this book possible: the writer,
Steve Johnson; the technical editor, Alex Williams; the production team, Emily
Atwood, Alex Williams, Dori Hernandez, and Jennifer L. Ware; the editors and proof-
readers, Emily Atwood and Holly Johnson; and the indexer, Katherine Stimson.
At Que publishing, we’d like to thank Greg Wiegand and Stephanie McComb for the
opportunity to undertake this project, Michelle Newcomb for administrative support,
and Sandra Schroeder for your production expertise and support.
Perspection
iii
About The Author
SteveJohnson has written more than 35 books on a variety of computer
software, including Microsoft Office 2007 and 2003, Microsoft Windows XP, Apple
Mac OS X Panther, Macromedia Flash MX 2004 and 8, Macromedia Director MX
2004, Macromedia Fireworks, and Adobe Photoshop CS and CS2. In 1991, after
working for Apple Computer and Microsoft, Steve founded Perspection, Inc., which
writes and produces software training. When he is not staying up late writing, he
enjoys playing golf, gardening, and spending time with his wife, Holly, and three
children, JP, Brett, and Hannah. When time permits, he likes to travel to such places
as New Hampshire in October, and Hawaii. Steve and his family live in Pleasanton,
California, but can also be found visiting family all over the western United States.
aa
iv
We Want To Hear From You!
As the reader of this book, youare our most important critic and commentator. We
value your opinion and want to know what we’re doing right, what we could do bet-
ter, what areas you’d like to see us publish in, and any other words of
wisdom you’re willing to pass our way.
As an associate publisher for Que, I welcome your comments. You can email or
write me directly to let me know what you did or didn’t like about this book—as well
as what we can do to make our books better.
Please note that I cannot help you with technical problems related to the topic
of this book. We do have a User Services group, however, where I will forward spe-
cific technical questions related to the book.
When you write, please be sure to include this book’s title and author as well as
your name, email address, and phone number. I will carefully review your com-
ments and share them with the author and editors who worked on the book.
Email: [email protected]
Mail: Greg Wiegand
Que Publishing
800 East 96th Street
Indianapolis, IN 46240 USA
For more information about this book or another Que title, visit our Web site at
www.quepublishing.com. Type the ISBN (excluding hyphens) or the title of a book
in the Search field to find the page you’re looking for.
a
Acknowledgements v
c
Contents
Introduction xv
1 Getting Started with Access 1
Understanding How Databases Store Data 2
Starting Access 4 New!
Using the Ribbon 6 New!
Choosing Commands 7 New!
Working with Toolbars 8 New!
Choosing Dialog Box Options 10 New!
Using the Status Bar 11 New!
Creating a Database 12 New!
Opening a Database 14
Converting an Existing Database 16 New!
Viewing the Access Window 17 New!
Arranging Windows 18
Using Task and Window Panes 19 New!
Getting Help While You Work 20 New!
Saving a Database 22 New!
Saving a Database with Different Formats 24 New!
Getting Updates on the Web 25 c
Diagnosing and Repairing Problems 26 New!
Closing a Database and Exiting Access 28
2 Touring Access Databases 29
Opening a Sample Database 30 New!
Warnings About Macros and Add-Ins 31 New!
Using a Switchboard 32
Changing the Navigation Pane Display 33 New!
Working with Database Objects 34 New!
Grouping and Hiding Database Objects 36
Touring a Table 38
Touring a Form 40
Entering Data 41
vii
Touring a Query 42
Touring a Report 43
Switching Views 44 New!
Changing Database Display Options 46 New!
3 Planning and Creating a Custom Database 47
Creating a Custom Database Template 48
Planning Tables 50 New!
Creating Tables in a Database 51
Creating a Table by Entering Data 52 New!
Creating a Table Using a Template 53 New!
Importing Data into Tables 54
Linking to Data in Tables 55
Working with Table Records 56
Working with a Table in Design View 58
Specifying a Primary Key in Design View 59
Planning Table Relationships 60
Defining Table Relationships 62
Creating and Printing a Table Relationship Report 64
Ensuring Referential Integrity 66
Identifying Object Dependencies 67
Modifying Object Dependencies 68
4 Working with Fields 69
Inserting Fields 70 New!
Working with Fields 72
Specifying Data Types and Field Properties 74 New!
Viewing Field Properties 75
c
Changing Field Properties 76 New!
Updating Field Properties 78
Setting Field Size 79
Formatting Text Values 80
Formatting Memo Text with Rich Text 81 New!
Formatting Date and Time Values 82 New!
Formatting Number and Currency Values 84
Performing a Total Calculation 85 New!
Creating Input Masks 86
Creating Indexed Fields88
Specifying Required Fields and Default Values 90
Adding a Caption to a Field 91
Validating Field Values 92
viii
Creating a Lookup Field 94
Setting Lookup Properties 96
Creating a Multivalued Field 97 New!
Attaching Files to Field Records 98 New!
Setting Table Properties 100 New!
5 Working with Tables 101
Working with Tables 102
Repairing Renaming Errors 104
Managing Linked Tables 105
Working with the Clipboard 106
Editing Text 108
Entering Data Accurately with AutoCorrect 110
Using Smart Tags 112
Finding and Replacing Text 114
Checking Spelling 116 New!
Using Custom Dictionaries 118 New!
Formatting a Datasheet 119
Arranging Columns 120
Changing the Size of Rows and Columns 122
Managing Columns in Datasheet View 123
Sorting Records 124
Viewing a Subdatasheet 126
Filtering Out Records 128 New!
Creating Complex Filters Using Forms 130
6 Locating Specific Information Using a Query 131
Understanding Types of Queries 132
c
Creating a Query in Design View 133
Getting Information with a Query 134
Modifying a Query in Design View 135
Creating a Query Using a Wizard 136
Changing the Query Fields 138
Specifying Criteria for a Single Field 140
Specifying Criteria for Multiple Fields 141
Creating Queries with Comparison and Logical Operators 142
Performing Calculations in Queries 144
Creating a Parameter Query 146
Finding Duplicate Fields 148
Finding Unmatched Records 149
Creating New Tables with a Query 150
Contents ix
Adding Records with a Query 151
Deleting Records with a Query 152
Updating Records with a Query 153
Summarizing Values with Crosstab Query 154
Creating SQL-Specific Queries 156
7 Simplifying Data Entry with Forms 157
Creating Forms 158
Working with Form Controls 159
Creating a Form 160 New!
Creating a Form Using the Form Wizard 161
Creating a Split Form 162 New!
Creating a Datasheet Form 164
Creating a Multiple Items Form 165 New!
Creating a Blank Form 166
Creating a Dialog Form 167
Creating a PivotChart and PivotTable Form 168 New!
Entering and Editing Data in a Form 170 New!
Working with a Form in Layout View 172 New!
Working with a Form inDesign View 174
Modifying a Form 176
Adding and Modifying Controls 178
Using the Control Wizards 180
Creating a Subform 182
Using Windows Themes on Forms 184
8 Creating Reports to Convey Information 187
Exploring Different Ways to Create a Report 188
c
Creating a Report 190 New!
Creating a Blank Report 192
Creating Mailing Labels 193
Using Sections in Design View 194
Working with Controls 195
Creating and Modifying a Report in Design View 196
Using Buttons and Controls 198
Arranging Information 200
Inserting a Title, Page Numbers, or the Date and Time 202
Inserting a Picture as a Logo 204
Setting Properties 205
Performing Calculations in Reports 206
Grouping and Sorting in Reports 208 New!
x