Access 2003 For Dummies®
Published by
Wiley Publishing, Inc.
111 River St.
Hoboken, NJ 07030-5774
www.wiley.com
Copyright © 2003 by Wiley Publishing, Inc., Indianapolis, Indiana
Published by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8700. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4447, e-mail: permcoordinator@wiley.com.
Trademarks: Wiley, the Wiley Publishing logo, For Dummies, the Dummies Man logo, A Reference for the Rest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates. All other trademarks are the property of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: While the publisher and author have used their best efforts in preparing this book, they make no representations or warranties with respect to the accuracy or completeness of the contents of this book and specifically disclaim any implied warranties of merchantability or fitness for a particular purpose. No warranty may be created or extended by sales representatives or written sales materials. The advice and strategies contained herein may not be suitable for your situation. You should consult with a professional where appropriate. Neither the publisher nor author shall be liable for any loss of profit or any other commercial damages, including but not limited to special, incidental, consequential, or other damages.
For general information on our other products and services or to obtain technical support, please contact our Customer Care Department within the U.S. at 800-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002.
Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.
Library of Congress Control Number: 2003101916
ISBN: 0-7645-3881-0
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
is a trademark of Wiley Publishing, Inc.
John Kaufeld got hooked on computers a long time ago. Somewhere along the way, he discovered that he really enjoyed helping people resolve computer problems. John finally achieved his B.S. degree in management information systems from Ball State University and he became the first PC support technician for what was then Westinghouse near Cincinnati, Ohio.
Since then, he has logged nearly a decade of experience working with normal people who were stuck with a “friendly” PC that turned on them. He’s also trained more than 1000 people in many different PC and Macintosh applications. Today, John conducts media skills and promotion seminars for up-and-coming entrepreneurs and writes in his free moments. His other ventures include More Than Games, an amazingly cool board and card game store (www.morethangames.com); ShipperTools.com, a shipping system that helps small businesses and eBay sellers save money with the US Postal Service (www.shippertools.com); and his Feed the News Beast small-business seminars (www.feedthenewsbeast.com).
His other titles include the best-selling AOL For Dummies, plus too many other database and Internet books to leave him emotionally unscarred. John lives with his wife, two children, and two gerbils in Fort Wayne, Indiana.
To Jenny, because without you, I’d be completely nuts.
To J.B. and the Pooz for reminding Daddy to smile when all he could do was write.
To John Wiley & Sons for the opportunity of a lifetime.
My sincere thanks to you, one and all.
As with any good magic trick, there’s more to putting out a book than meets the eye. Granted, writing a book like this demands long periods of intense sitting, but it actually takes a team of intense sitters to complete the finished product you hold in your hands.
Kudos upon kudos go to my project editor, Susan “Spink” Pink for her diligent efforts to make my ramblings follow commonly accepted semantic guidelines. As an extra added bonus, she even laughs at my jokes. Sometimes. Equally significant thanks go to technical editor Allen Wyatt for verifying that I didn’t make most of this stuff up.
More gratitude than I can express here goes to Senior Acquisitions Editor Steve Hayes and to the King of Acquisitions (or whatever his real title is), Andy Cummings. This year, they both proved that there’s more to working relationships than meets the eye. If it wasn’t for you two . . . well . . . I don’t even want to think about it. You both mean more to me than you’ll ever know.
Finally, ten years worth of sincere thanks go to Diane Steele for her support, encouragement, and willingness to take a chance on a proven geek (but an unproven writer).
We’re proud of this book; please send us your comments through our online registration form located at www.dummies.com/register/.
Some of the people who helped bring this book to market include the following:
Acquisitions, Editorial, and Media Development
Project Editor: Susan Pink
Acquisitions Editor: Steven H. Hayes
Technical Editor: Allen Wyatt, Discovery Computing Inc.
Editorial Manager: Carol Sheehan
Media Development Supervisor: Richard Graves
Editorial Assistant: Amanda Foxworth
Cartoons: Rich Tennant (www.the5thwave.com)
Production
Project Coordinator: Maridee Ennis
Layout and Graphics: Seth Conley, Lynsey Osborn, Shae Wilson
Proofreaders: Carl William Pierce, Toni Settle
Indexer: TECHBOOKS Production Services
Publishing and Editorial for Technology Dummies
Richard Swadley, Vice President and Executive Group Publisher
Andy Cummings, Vice President and Publisher
Mary C. Corder, Editorial Director
Publishing for Consumer Dummies
Diane Graves Steele, Vice President and Publisher
Joyce Pepple, Acquisitions Director
Composition Services
Gerry Fahey, Vice President of Production Services
Debbie Stailey, Director of Composition Services
Title
Introduction
About This Book
Conventions Used in This Book
What You Don’t Have to Read
Foolish Assumptions
How This Book Is Organized
Icons Used in This Book
Where to Go from Here
Chapter 1: The 37-Minute Overview
In the Beginning, There Was Access 2003
Opening an Existing Database
Touring the Database Window
Finding Information Amongst the Grass Clippings
Making a Few Changes
Reporting the Results
Saving Your Hard Work
The Great Backup Lecture
Making a Graceful Exit
Chapter 2: Finding Your Way Around like a Native
Making Sense of the Sights
Windows Shopping for Fun and Understanding
Belly Up to the Toolbar, Folks!
Menus, Menus Everywhere
Playing with the Other Mouse Button
Chapter 3: Calling the Online St. Bernard and Other Forms of Help
Finding Help Here, There, and Waaaay Over There
Asking Questions of the Software
Your Internet Connection Knows More Than You May Think
Talking to a Human
Part I : Which Came First, the Data or the Base?
Part II : Truly Tempting Tables
Chapter 4: Designing and Building a Home for Your Data
Database Terms to Know and Tolerate
Frolicking through the Fields
Flat Files versus Relational Databases: Let the Contest Begin!
Great Tables Start with Great Designs
Building a Database
Creating Tables at the Wave of a Wand
Building Tables by Hand, Just like in the Old Days
Chapter 5: Relationships, Keys, and Indexes (and Why You Really Do Care)
The Joy (and Necessity) of a Primary Key
Divulging the Secrets of a Good Relationship
Linking Your Tables with the Relationship Builder Thingy
Indexing Your Way to Fame, Fortune, and Significantly Faster Queries
Chapter 6: New Data, Old Data, and Data in Need of Repair
Dragging Your Table into the Digital Workshop
Adding Something to the Mix
Changing What’s Already in a Record
Kicking Out Unwanted Records
Recovering from a Baaaad Edit
Chapter 7: Making Your Table Think with Formats, Masks, and Validations
Finding the Place to Make a Change
To Format, Perchance to Better See
What Is That Masked Data?
Validations: The Digital Breathalyzer Test
Chapter 8: Making Your Datasheets Dance
Wandering Here, There, and Everywhere
Seeing More or Less of Your Data
Fonting around with Your Table
Giving Your Data the 3-D Look
Chapter 9: Table Remodeling Tips for the Do-It-Yourselfer
This Chapter Can Be Hazardous to Your Table’s Design
Putting a New Field Next to the Piano
Saying Good-bye to a Field (and All Its Data)
A Field by Any Other Name Still Holds the Same Stuff
Part III : Finding the Ultimate Answer to Almost Everything
Chapter 10: Quick Searches: Find, Filter, and Sort
Finding Stuff in Your Tables
Sorting Out Life on the Planet
Filtering Records with Something in Common
Chapter 11: Pose a Simple Query, Get 10,000 Answers
Database Interrogation for Fun and Profit
On Your Way with a Simple Query — Advanced Filter/Sort
Plagued by Tough Questions? Try an Industrial Strength Query!
Build a Better Query and the Answers Beat a Path to Your Monitor
Toto, Can the Wizard Help?
Chapter 12: Searching a Slew of Tables
Some General Thoughts about Multiple-Table Queries
Calling on the Query Wizard
Rolling Up Your Sleeves and Building the Query by Hand
Chapter 13: Lions AND Tigers OR Bears? Oh My!
Comparing AND to OR
Finding Things between Kansas AND Oz
Multiple ANDs: AND Then What Happened?
Are You a Good Witch OR a Bad Witch?
AND and OR? AND or OR?
Chapter 14: Teaching Queries to Think and Count
Super-Powering Queries with the Total Row
Adding the Magical Total Row to Your Queries
Putting the Total Row to Work
Choose the Right Field for the Summary Instruction
Chapter 15: Calculating Your Way to Fame and Fortune
A Simple Calculation
Bigger, Better (and More Complicated) Calculations
Expression Builder (Somewhat) to the Rescue
Chapter 16: Automated Editing for Big Changes
First, This Word from Our Paranoid Sponsor
Quick and Easy Fixes: Replacing Your Mistakes
Different Queries for Different Jobs
You’re Outta Here: The Delete Query
Making Big Changes
Part IV : Turning Your Table into a Book
Chapter 17: AutoReport: Like the Model-T, It’s Clunky but It Runs
AutoReport Basics for High-Speed Information
Putting the Wheels of Informational Progress in Motion
Previewing Your Informational Masterpiece
Truth Is Beauty, So Make Your Reports Look Great
Chapter 18: Wizardly Help with Labels, Charts, and Multilevel Reports
Creating Labels
Using the Chart Wizard in Your Report
Creating More Advanced Reports
Chapter 19: It’s Amazing What a Little Formatting Can Do
Taking Your Report to the Design View Tune-Up Shop
Striking Up the Bands (and the Markers, Too)
Formatting This, That, These, and Those
Taking a Peek at Your Report
AutoFormatting Your Way to a Beautiful Report
Lining Up Everything
Drawing Your Own Lines
Inserting Page Breaks
Sprucing Up the Place with a Few Pictures
Passing Your Reports around the (Microsoft) Office
Chapter 20: Headers and Footers for Groups, Pages, and Even (Egad) Entire Reports
Everything in Its Place
Fine-Tuning the Layout
Filling in Those Sections
Part V : Wizards, Forms, and Other Mystical Stuff
Chapter 21: Spinning Your Data onto the Web
Access and the Internet: A Match Made in Redmond
Building Hyperlinks in Your Table
Pushing Your Data onto the Web
Advanced Topics for Your Copious Nerd Time
Chapter 22: Making Forms that Look Cool and Work Great
Tax Forms and Data Forms Are Different Animals
Creating a Form at the Wave of a Wand
Giving the Form Just the Right Look
Mass Production at Its Best: Forms from the Auto Factory
Ultimate Beauty through Cosmetic Surgery
Chapter 23: If Love Is Universal, Why Can’t I Export to It?
Importing Only the Best Information for Your Databases
Sending Your Data on a Long, One-Way Trip
Chapter 24: The Analyzer: Your Data’s Dr. Freud, Dr. Watson, and Dr. Jekyll
It Slices, It Dices, It Builds Relational Databases!
Documentation: What to Give the Nerd in Your Life
Performance: Toward a Better Database
Chapter 25: Talking to Your Computer
What Is Speech Recognition (and What Can I Do with It)?
Installing Speech Recognition
Sending Access to Voice Training School
Speaking to Access
Improving Speech Recognition
Part VI : The Part of Tens
Chapter 26: Ten Timesaving Keyboard Shortcuts
Select an Entire Field: F2
Insert the Current Date: Ctrl+; (Semicolon)
Insert the Current Time: Ctrl+: (Colon)
Insert the Same Field Value as in the Last Record: Ctrl+’ (Apostrophe)
Insert a Line Break: Ctrl+Enter
Add a Record: Ctrl++ (Plus Sign)
Delete the Current Record: Ctrl+ – (Minus Sign)
Save the Record: Shift+Enter
Undo Your Last Changes: Ctrl+Z
Open the Selected Object in Design View: Ctrl+Enter
Chapter 27: Ten Common Crises and How to Survive Them
You Type 73.725, but It Changes to 74
You Run a Query but the Results Look Screwy
And When You Looked Again, the Record Was Gone
The Validation That Never Was
The Sometimes-There, Sometimes-Gone Menus
You Can’t Link to a dBASE Table
You Can’t Update a Linked dBASE or Paradox Table
You Get a Key Violation While Importing a Table
Try as You May, the Program Won’t Start
The Wizard Won’t Come Out of His Keep
Chapter 28: Ten Tips from Database Nerds
Document As if Your Life Depends on It
Don’t Make Your Fields Too Big
Use Number Fields for Real Numbers
Validate Data
Use Understandable Names
Take Great Care When Deleting
Keep Backups
Think First and Then Think Again
Get Organized and Keep It Simple
Know When to Ask for Help
B eing a normal human being, you probably have work to do. In fact, you may have lots of work piled precariously around your office or even stretching onto the Internet. Someone, possibly your boss (or, if you work at home, your Significant Other), suggested that Access may help you do more in less time, eliminate the piles, and generally make the safety inspector happy.
So you picked up Access, and here you are. Whee!
If you feel confused instead of organized, befuddled instead of productive, or just completely lost on the whole database thing, Access 2003 For Dummies is the book for you. And don’t worry — you aren’t alone in those feelings. Unlike word processors and presentation programs, few people catch on to databases by themselves. (Those few who manage the feat usually turn into computer support people as a way of working through the trauma.)
This book has a simple purpose: to show you how Access works, what to do with it, and why you might actually care, while carefully not turning you into a world-class nerd in the process. What more could you want?
Every now and then, you need to tell Access to do something or other. Likewise, there are moments when the program wants to toss its own comments and messages back to you (so be nice — communication is a two-way street). To easily show the difference between a human-to-computer message and vice-versa, I format the commands differently.
Here are examples of both kinds of messages as they appear in the book.
This is something you type into the computer.
This is how the computer responds to your command.
Because Access is a Windows program, you don’t just type all day — you also mouse around quite a bit. Although I don’t use a cool font for mouse actions, I do assume that you already know the basics. Here are the mouse movements necessary to make Access (and any other Windows program) work:
U Click: Position the tip of the mouse pointer (the end of the arrow) on the menu item, button, check box, or whatever else you happen to be aiming at, and then quickly press and release the left mouse button.
U Double-click: Position the mouse pointer as though you’re going to click, but fool it at the last minute by clicking twice in rapid succession.
U Click and drag (highlight): Put the tip of the mouse pointer at the place you want to start highlighting and then press and hold the left mouse button. While holding down the mouse button, drag the pointer across whatever you want to highlight. When you reach the end of what you’re highlighting, release the mouse button.
U Right-click: Right-clicking works just like clicking, except that you’re exercising the right instead of the left mouse button.
Of course, the Access menu comes in handy, too. When I want you to choose something from the main menu bar, the instruction looks like this:
Choose File⇒Open Database.
If you think that mice belong in holes, you can use the underlined letters as shortcut keys to control Access from the keyboard. To use the keyboard shortcut, hold down the Alt key and press the appropriate underlined letter. In the example above, the keyboard shortcuts are Alt+F, then Alt+O. Press them one right after the other, with the Alt key down the whole time.
Must you completely ingest this entire tome before understanding Access? Goodness, no! (Besides, I don’t think the book ingests well — at least not without a trip or two through the shredder.) Certain stuff made it into the book simply because I couldn’t find any way to leave it out.
If you use Access only for working with your company’s big corporately designed databases, don’t worry about the database design chapter. Your Information Systems department probably won’t let you mess around with the database structure anyway, so why worry with design details in the meantime?
You need to know only a few things about your computer and Windows to get the most out of Access 2003 For Dummies. (Turning yourself into a full-bore computer nerd is totally out of the question.) In the following pages, I presume that you
U Know the basics of whichever flavor of Windows you’re using.
U Want to work with databases that other people have created.
U Want to use and create queries, reports, and an occasional form.
U Want to make your own databases from scratch every now and then.
U Have Microsoft Windows 98, 98 SE, ME, 2000, NT 4, or any flavor of XP, and Access for Windows on your computer (if you have the entire Office suite, that’s fine, too). If your computer still uses Windows 95, spend some quiet time with the machine. After that, give it a decent burial and go splurge on a new computer. Your old one deserves a well-earned rest (and you deserve a gold star for putting up with an old machine for that long).
The good news is that you don’t have to know (or even care) about table design, field types, relational databases, or any of that other database stuff to make Access work for you. Everything you need to know is right here, just waiting for you to read it.
To give you an idea of what’s ahead, here’s a breakdown of the six parts in this book. Each part covers a general topic of Access. The part’s individual chapters dig into the details.
Right off the bat, this book answers the lyrical question “It’s a data-what?” By starting with an overview of both database concepts in general and Access in particular, this book provides the information you need to make sense of the whole database concept. This part also contains suggestions about solving problems with (or even without) Access. If you’re about to design a new Access database to fix some pesky problem, read this section first — it may change your mind.
Arguably, tables (where the data lives) are at the center of this whole database hubbub. After all, without tables, you wouldn’t have any data to bully around. This part gives you the information you need to know about designing, building, using, changing, and generally coexisting in the same room with Access tables.
If tables are at the center of the Access universe, then queries are the first ring of planets. In Access, queries ask the power questions; they unearth the answers you know are hiding somewhere in your data. In addition to covering queries, this part also explains how to answer smaller questions using Find, Filter, and Sort — Query’s little siblings.
Seeing your data on-screen just isn’t enough, sometimes. To make your work really shine, you have to commit it to paper. Part IV covers the Access report system, a portion of the software entirely dedicated both to getting your information onto the printed page and to driving you nuts in the process.
At some point, technology approaches magic (one look at the control panel for a modern microwave oven is proof of that). This part explores some of the mystical areas in Access, helping you do stuff faster, seek assistance from the wizards, get your computer to do what you want just by talking to it, and even venture into a bit of programming. If the Internet’s limitless possibilities pique your online fancy, look in this part for info about the new Web connectivity features in Access. They’re really amazing!
The words For Dummies book immediately bring to mind the snappy, irreverent Part of Tens. This section dumps a load of tips and cool ideas onto, and hopefully into, your head. You can find a little bit of everything here, including timesaving tips and the solutions to the most common problems awaiting you in Access.
When something in this book is particularly valuable, I go out of my way to make sure that it stands out. I use these cool icons to mark text that (for one reason or another) really needs your attention. Here’s a quick preview of the ones waiting for you in this book and what they mean:
Now nothing’s left to hold you back from the wonders of Access. Cleave tightly to Access 2003 For Dummies and dive into Access.
U If you’re brand new to the program and don’t know which way to turn, start with the general overview in Chapter 1.
U If you’re about to design a database, I salute you — and recommend flipping through Chapter 4 for some helpful design and development tips.
U Looking for something specific? Try the Table of Contents or the Index, or just flip through the book until you find something interesting.
Bon voyage!