Access 2003 For Dummies


by John Kaufeld





About the Author

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 (;, a shipping system that helps small businesses and eBay sellers save money with the US Postal Service (; and his Feed the News Beast small-business seminars (

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.


Author’s Acknowledgments

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).


Publisher’s Acknowledgments

We’re proud of this book; please send us your comments through our online registration form located at

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 (


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




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!

About This Book

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?

Conventions Used in This Book

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 FileOpen 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.


If you aren’t familiar with all these rodent gymnastics, or if you want to know more about Windows in general, pick up a copy of one of the many Windows For Dummies titles. Every version of Windows has one!

What You Don’t Have to Read

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.


For one thing, feel free to ignore anything marked by the Technical Stuff icon, like the one next to this paragraph. You don’t need to know the stuff marked by these little signposts to make Access function helpfully in your world. If you feel like going deeper into the uncharted depths of the program, you can always start the trip with a glance at the Technical Stuff texts.

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?

Foolish Assumptions

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.

How This Book Is Organized

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.

Part I: Which Came First, the Data or the Base?

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.

Part II: Truly Tempting Tables

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.

Part III: Finding the Ultimate Answer to Almost Everything

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.

Part IV: Turning Your Table into a Book

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.

Part V: Wizards, Forms, and Other Mystical Stuff

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!

Part VI: The Part of Tens

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.

Icons Used in This Book

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:


Tips are really helpful words of wisdom that promise to save you time, energy, and perhaps some hair. Whenever you see a tip, take a second to check it out.


Some things are too important to forget, so the Remember icon points them out. These items are critical steps in a process — points that you don’t want to miss.


Despite my best efforts, sometimes I give in to the nerdy side and slip some technical twaddle into the book. The Technical Stuff icon protects you from obscure details by making them easy to avoid. If you’re in an adventuresome mood, check out the technical stuff. You may find it interesting.


The Warning icon says it all: Skipping this information may be hazardous to your data’s health. Pay attention to these icons and follow their instructions to keep your databases happy and intact.

Where to Go from Here

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!