Access 2007 For Dummies®
Published by
Wiley Publishing, Inc.
111 River St.
Hoboken, NJ 07030-5774
www.wiley.com
Copyright © 2007 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-8600. 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-4355, or online at http://www.wiley.com/go/permissions.
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 in the United States and other countries, and may not be used without written permission. MySQL is a registered trademark of MySQL. 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: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Website is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Website may provide or recommendations it may make. Further, readers should be aware that Internet Websites listed in this work may have changed or disappeared between when this work was written and when it is read.
For general information on our other products and services, 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.
For technical support, please visit www.wiley.com/techsupport.
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: 2006934825
ISBN-13: 978-0-470-04612-8 ISBN-10: 0-470-04612-0
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2
1B/QV/RS/QW/IN
Laurie Ulrich Fuller has been writing about and teaching people to use Microsoft Office since the 1980’s. Her teaching career goes back to the time before Microsoft Windows – which means she also remembers the first time she taught people to use a Windows-based application, and a student picked up the mouse and aimed it at the computer screen as though using a TV remote. Nobody laughed (except Laurie, after class), because everyone was new to the mouse back then. As new as the mouse was, so was the idea of keeping a database on a computer that could fit on your desk — and Laurie’s been there through every new version of Access — as Office has evolved to meet the needs of users from all walks of life — from individuals to huge corporations, from growing business to non-profit organizations.
Since those early days of Office and Windows, Laurie has personally trained more than 10,000 people to make better, more creative use of their computers, has written and co-written more than 25 nationally-published books on computers and software — including several titles on Microsoft Office. In the last few years, she’s also created two video training courses — one on Word 2003, and the other on the entire Office 2003 suite. She runs her own company, Limehat & Company, offering training, educational materials, and web development services. She invites you to contact her at laurie@limehat.com, and to visit her personal website, www.planetlaurie.com, for more information.
Laurie would also like you to know that despite being able to remember the world before Windows, she does not remember a time before cars, television, or fire.
Ken Cook has built and managed a successful computer consulting business since 1990 serving clients in New Jersey, New York, Pennsylvania, and California. He began as a trainer - training numerous users (too many to count!) on a variety of software packages — specializing in Microsoft Office. Currently, he “dabbles in training” but his main focus is creating expert Microsoft Office solutions and Microsoft Access database solutions for Fortune 500 and small business clients.
He can be contacted through his Web site www.kcookpcbiz.com or email: ken@kcookpcbiz.com.
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: Pat O’Brien (Previous Edition: Susan Pink)
Acquisitions Editor: Steve Hayes
Copy Editor: Andy Hollandbeck
Technical Editor: Michael Alexander
Editorial Manager: Kevin Kirschner
Media Development Specialists: Angela Denny, Kate Jenkins, Steven Kudirka, Kit Malone, Travis Silvers
Media Development Coordinator: Laura Atkinson
Media Project Supervisor: Laura Moss
Media Development Manager: Laura VanWinkle
Media Development Associate Producer: Richard Graves
Editorial Assistant: Amanda Foxworth
Sr. Editorial Assistant: Cherie Case
Cartoons: Rich Tennant (www.the5thwave.com)
Composition Services
Project Coordinator: Erin Smith
Layout and Graphics: Jonelle Burns, Carl Byers, Lavonne Cook, Denny Hager, Joyce Haughey, Barbara Moore, Barry Offringa, Alicia South, Julie Trippetti, Erin Zeltner
Proofreaders: Johnna VanHoose Dinse, John Greenough, Christy Pingleton
Indexer: Techbooks
Publishing and Editorial for Technology Dummies
Richard Swadley, Vice President and Executive Group Publisher
Andy Cummings, Vice President and Publisher
Mary Bednarek, Executive Acquisitions Director
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
Part I : Basic Training
Chapter 1: Getting to Know Access 2007
What Is Access Good For, Anyway?
How Access Works and How You Work with It
Chapter 2: Finding Your Way Around Access
The Getting Started Window
Working with Access’s On-screen Tools
Customizing the Access Workspace
Mousing Around
Navigating Access with the Alt Key
Chapter 3: Database Basics
Database Lingo
Field Types and Uses
Choosing between Flat and Relational Databases
Building a Database
Adding and Removing Tables
Part II : Getting It All on the Table
Chapter 4: Keys, Relationships, and Indexes
The Primary Key to Success
Making Tables Get Along
Building Table Relationships
Indexing for Faster Queries
Chapter 5: Remodeling Your Data
Opening a Table for Editing
Inserting Records and Fields
Modifying Field Content
Name Calling
Turn Uh Oh! into Yi Hah!
Chapter 6: What’s Happening Under the Table?
Access Table Settings
Field Data Formats
Gaining Control of Data Entry
Part III : Data Mania and Management
Chapter 7: Creating Data Forms
Generating Forms
Customizing Form Parts
Chapter 8: Importing and Exporting Data
Retrieving Data from Other Sources
Get This Data Out of Here
Chapter 9: Automatically Editing Data
Please Read This First!
Creating Consistent Corrections
Using Queries to Automate the Editing Process
Chapter 10: Gather Locally, Share Globally
Access and the Web
Click! Using Hyperlinks in Your Access Database
Publishing Your Data to the Web
Part IV : Ask Your Data, and Ye Shall Receive Answers
Chapter 11: Fast Finding, Filtering, and Sorting Data
Using the Find Command
Sorting from A to Z or Z to A
Fast and Furious Filtering
Chapter 12: I Was Just Asking . . . For Answers
Simple (Yet Potent) Filter and Sort Tools
Select Queries
Getting Your Feet Wet with Ad Hoc Queries
Chapter 13: I’ll Take These AND Those OR Them
Working with AND and/or OR
Combining AND with OR and OR with AND
Chapter 14: Queries That Think Faster Than You
Kissing That Calculator Goodbye via the Total Row
Adding the Total Row to Your Queries
Giving the Total Row a Workout
Creating Your Own Top-Ten List
Choosing the Right Field for the Summary Instruction
Chapter 15: Calculating with Your Data
A Simple Calculation
Complex Calculations
Expression Builder (Somewhat) to the Rescue
Part V : Plain and Fancy Reporting
Chapter 16: Quick and Not-So-Dirty Automatic Reporting
Fast and Furious Automatic Reporting
Previewing Your Report
Beauty Is Only Skin (Report) Deep
Chapter 17: Dazzling Report Design
Taking Your Report In for Service
Report Organization
Formatting This, That, and the Other
Sneaking a Peek
Getting an AutoFormat Makeover
Adding Additional Design Elements
Chapter 18: Headers and Footers and Groups, Oh My!
A Place for Everything and Everything in Its Place
Customizing Properties
Chapter 19: Magical Mass Mailings
Massive Mailings with the Label Wizard
Part VI : More Power to You
Chapter 20: Making It All Better with the Analyzer Tools
Convert Your Flat Files to Relational Tables with Analyzer
Record Database Object Details with the Documenter
Improve Database Performance without Steroids
Chapter 21: Hello! Creating an Interface to Welcome Data Users
The Comings and Goings of a Switchboard
Am I in the Right Place? Switchboard Testing
Maintaining the Switchboard
Displaying the Switchboard at Startup
Part VII : The Part of Tens
Chapter 22: Ten Common Problems
You Type 73.725, but it Changes to 74
The Words They Are A-Changing
The Record Was There and Now It’s Gone
You Run a Query, but the Results Aren’t What You Expect
The Validation That Never Was
The Slowest Database in Town
Your Database File Is as Big as a Whale
You Get a Mess When Importing Your Spreadsheet
We’re Sorry; Your Database File Is Corrupt
The Program Won’t Start
Chapter 23: Ten Uncommon Tips
Document Everything as Though You’ll be Questioned by the FBI
Keep Your Fields as Small as Possible
Use Number Fields for Real Numbers
Validate Your Data
Use Understandable Names to Keep Things Simple
Delete with Great Caution
Backup, Backup, Backup
Think, Think, and Think Again
Get Organized and Stay Organized
There’s No Shame in Asking for Help
Appendix: Getting Help
Asking Access for Help
Online Help
Who’s Our Next Caller?
: Further Reading
You’ve picked up this book and are hoping it will teach you to use Microsoft Access. Of course, as the authors, we believe that it was some sort of divine intervention that led you to our pages, and we’re quite certain that this is The Book For You. We could be wrong, but that happens so infrequently that we’re hardly considering it. No, the reason you picked up this book is that you want to learn Access, and this is the best place to do that. Really. No kidding.
Of course, being a normal human being, you probably have work to do, and whether we’re right about this being The Book For You or not, you need Access. You need it to organize your data. You need it to store and allow you to use all the information that’s currently spilling out of notebooks, file drawers, your pockets, your glove compartment, everywhere. You need it so you can print out snappy looking reports that make you look like the genius you are. You need it so you can create cool forms that will help your staff enter all the data you’ve got stacked on their desks — and in a way that lets you know that the data was entered properly, so it’s accurate and useful. You need Access so you can find little bits of data out of the huge pool of information you need to store. You just need it.
Because with all the power that Access has (and that it therefore gives you), there comes a small price: complexity. Access isn’t one of those applications you can just sit down and use, “right out of the box”. It’s not scarily difficult or anything, but there’s a lot going on and you need some guidance, some help, some direction, to really use it and make it sing and dance. And that’s where this book, a “reference for the rest of us” comes in.
So you’ve picked up this book. Hang on to it. Clutch it to your chest and run gleefully from the store (stop and pay for it first, please). And then start reading — whether you begin with Chapter 1 or whether you dive in and start with a particular feature or area of interest that’s been giving you fits on your own. Just read, and then go put Access through its paces.
As you work with Access, you’re going to need to tell it to do things. You’ll also find that at times, Access has questions for you, usually in response to your asking it to do something. This book will show you how to talk to Access, and how Access will talk to you. To show the difference between the two sides of that conversation, we format the commands as follows:
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, type, type — you also mouse around quite a bit. Here are the mouse movements necessary to make Access (and any other Windows program) work:
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.
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.
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.
Right-click: Right-clicking works just like clicking, except that you’re exercising the right instead of the left mouse button.
Now that we’ve told you that you should read the book, we’re telling you don’t have to read all of it. Confused? Don’t be. This section of the introduction exists to put your mind at ease, so you won’t worry that you have to digest every syllable of this book in order to make sense of Access. And more than just being a required section of the introduction, this is true. You don’t have to read the whole book.
You should read the chapters that pertain to things you don’t know, but you can skip the stuff you do know or that you’re fairly sure you don’t need to know. If the situation changes and you eventually do need to know something, you can go back and read that part later.
If you only use Access at work, and you’re using an Access database that some geek in your IT department created, chances are you can’t tinker with it. Therefore, if you only need to know about using an existing Access database, you can skip the chapters on designing databases.
On the other hand, it might be nice to know what’s happening “behind the scenes”, but you don’t have to read those chapters if you don’t want to.
You need to know only a few things about your computer and Windows to get the most out of Access 2007 For Dummies. In the following pages, we presume that you:
Know the basics of Windows — how to open programs, save your files, create folders, find your files once you’ve saved them, print, and do basic stuff like that.
Want to build your own databases.
Want to work with databases that other people have created.
Want to use and create queries, reports, and an occasional form.
Have either Windows XP with Service Pack 2 or Windows Vista.
If your computer uses Windows 98 or 2000, you can’t run Office 2007.
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. Of course, you may want to know what’s going on under the hood (so to speak). You’ll find that information within this book’s pages.
Here’s a breakdown of the parts in this book. Each part covers a general aspect of Access. The part’s individual chapters dig into the details.
In this first part of the book, you’ll find out what Access is, what it isn’t, how it works, and how you open it up and start using it. You’ll find out how to navigate and tame the Access workspace, and for people who’ve used previous versions of Access, you’ll find out about all the new doo-dads that are part of Access 2007.
Part I also takes you through the process of planning your database — deciding what to store, how to structure your database, and how to use some of Access 2007’s very helpful tools for starting a database with templates — cookie cutters, if you will — for a variety of common database designs. Be prepared to pick up some helpful jargon, as you learn a bit about a few specialized terms that you really need to know.
Part II takes you a bit deeper, starting out with a chapter on setting up more than one table to store related data — and moving on with chapters on setting up relationships between those tables, customizing the way data is stored in your tables, and ways to control how data is entered into the tables in your database.
You’ll find out all about forms, the customized interfaces you create to make it easier to enter, edit, and look at your database. You’ll also discover cool ways to share your Access data with other programs and how to bring content from Word documents and Excel worksheets into Access to save time, reduce the margin for data entry errors, and build consistency within all the work you do in Microsoft Office. You’ll also find out about using Access tables on the web, and how to publish your database to the internet. Look out world!
In Part IV, you’ll discover how to ask questions like “How many customers do we have in Peoria?” and “How long has that guy in Accounting worked here?” Of course, you already know how to form sentences that go up at the end (so people know you’re asking a question), but when you ask a question in Access, the pitch of your voice rarely makes any difference. You’ll need, therefore, to know how to sort, filter, and query your data to get at the information you’re storing in your Access database.
Reports are compilations of data from one or more tables in your database. That statement might sound a bit scary, because “compilations” has four syllables and you might not be sure what a table is yet. Have no fear, however, because Access provides some cool automatic tools that let you pick and choose what you want in your report, and then it goes and makes the report for you. How neat is that?
Automatic reports weren’t good enough for you, eh? If your jobs relies upon reports not only being informative but also attractive and attention-grabbing, Part V will be like opening a birthday present. Well, not really, but you’ll find out about charts, printing labels, and putting page numbers on your reports.
Part VI gives more power in the form of the Access Analyzer, a tool that tunes up your database for better performance. It also gives you more power by showing you how to create a user interface that controls what people see, which tables they can edit, and how they work with your database overall.
The format of these chapters is designed to give you a lot of information in a simple, digestible fashion so you can absorb it without realizing you’re actually learning something. Sneaky, huh?
This isn’t really a whole part, but it’s darn useful. Remember how your mom told you the only foolish question is the one you don’t ask? In this appendix, you’ll find out about the online and built-in help that Access offers.
When something in this book is particularly valuable, we go out of our way to make sure that it stands out. We 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:
.jpg)
Now nothing’s left to hold you back from the delights and amazing wonders of Access. Hold on tight to this copy of Access 2007 For Dummies and leap into Access.
If you’re brand new to the program and don’t know which way to turn, start with the general overview in Chapter 1.
If you’re about to design a database, I we salute you — and recommend flipping through Chapter 4 for some helpful design and development tips.
Looking for something specific? Try the Table of Contents or the Index.
Now, go. Have fun. And look both ways before crossing the street.
In this part . . .
Don’t worry, even though this part of the book is called “Access Basic Training”, nobody’s going to shout at you, demand you call them “Sir!”, or make you do pushups. I promise. Instead, you’ll find out what Access is, what it does, and how to get started using it.
The three chapters in this part of the book introduce you to the Access 2007 workspace, and show you how to start building your first database. You’ll also find out about some essential terms and concepts that will help you make better use of the rest of the book and any other print, online, or even in-person discussions of databases. This will help you talk about your database needs at work, with clients, or if you’re trying to bore people to death at a party.
Ready? Then let’s get started!