Access 2003 for Starters: The Missing Manual

Introduction

• What’s a Database?

• About Access Databases

• About This Book

• The Very Basics

• Example Databases

• About MissingManuals.com

• Safari Enabled

TOO MUCH INFORMATION. Seemingly simple tasks like banking and shopping these days require you to have multiple account numbers, credit card numbers, screen names, and passwords at your fingertips. Even a simple address list isn’t so simple anymore, now that folks have multiple phone numbers for cell phones, pagers, and fax machines.

Information is good. If you know how to store, retrieve, and control information, you can manage your own stock portfolio, keep an archive of form letters and documents so that you never have to reinvent the wheel, or print catalogs for your business at great savings over hiring an outside company.

All you need to make information work for you is a computer and Microsoft Access 2003. With Access, you can take thousands or millions of pieces of information, organize them, and retrieve them easily when you need them. Whether you’re keeping track of inventory and customer orders for a business, statistical data for a research project, or even the types and colors of a closet full of shoes, you can do it with Access.

Access is perhaps the most misunderstood member of the Microsoft Office suite. It may look and act like Excel at times, but it’s much more than spreadsheet software. As you’ll see in this introduction, Access is a relational database program, with similarities to software from companies like FileMaker and Oracle. If you’ve never used a database before, there’s no need to be intimidated: Access makes it easy to get started.

What’s a Database?

A database is an organized collection of information, but it includes more than just the information itself. A database also groups, indexes, and catalogs the information so that it’s easy to find when you need it. It stores and displays information in a standard format so you can immediately know which and what kind of information you’re looking at.

Whether computerized or not, a database makes it easy both to find existing information and to add new information. A filing cabinet is an example of a non-computerized database:

• It divides information among drawers, each of which has a label indicating what it contains.

• Within each drawer, file folders contain specific types of information, like receipts, bills, and individual employee records.

• Just like the drawers, each file folder is labeled to show what it contains.

• Within each folder, forms and reports follow a standard format that organizes their content. When you pull out a certain kind of form or report, you know in advance where on the page to find the information you need and what it will be called.

Imagine trying to find the same information without a filing cabinet or file folders. Instead of being organized for easy retrieval, the papers are simply scattered on the floor. It would take at least five times longer to find the information—if you could find it at all.

Database Management Software

Database management software like Microsoft Access organizes computer data just as a filing cabinet organizes paper data. Using database software, you can add, check, and retrieve information with a speed and efficiency that would otherwise be impossible. Database software accomplishes this in much the same way as a filing cabinet:

• It divides information among databases, each of which has a name indicating what it contains.

• Within each database, tables contain specific types of information, like customer records and order records.

• Within each table, information follows a consistent format. When you open a table, you know in advance where to find the information you need and what it will be called.

An example of database management software at work is the airways database maintained by the U.S. National Flight Data Center, as shown in Figure 1. With more than 500 airways between cities, the number of possible routes exceeds the number of stars in our galaxy. Without database management software, it would be impossible to store, check, and retrieve airplane routing information quickly. This image also demonstrates that information doesn’t have to look like columns of words and numbers. Access may not be able to create a map like this one, but it lets you present your information in an enormous variety of ways.

Figure 1. This computer map shows airways in American airspace. The U.S. National Flight Data Center uses MySQL, a database manager similar to Access, to manage information about the routes that airplanes are allowed to take in the United States.

Up to Speed: When Not to Use a Database Manager

Database management software is best for handling large amounts of complex information. However, if your data is simple and your database is small, then database management software—even Microsoft Access—may waste your time. Its extra database features give you more power when you need it, but if you don’t need it, a simpler approach is usually better.

If your database has fewer than 100 records and the information doesn’t change very often, consider keeping it in a Word document or Excel spreadsheet instead of in Access. A personal Rolodex file is an example of this kind of database.

Many newspaper reporters keep their contact lists in Word files, with a separate paragraph for each contact. In addition to contact names, each paragraph includes keywords about the contact’s expertise. When reporters need to find a contact, they can quickly search for names or keywords by using Word’s search feature. If they start each paragraph with the contact’s last name, they can even sort the contact records in alphabetical order.

That kind of solution isn’t appropriate for more complex or rapidly changing data. But for simple databases, use a simple solution.

About Access Databases

Access takes a modular approach to organizing information: It’s a kind of wrapper to hold—and connect—individual parts like tables, forms, and reports. This approach is very powerful because you can add as many items as you need, reprocessing and repurposing your data in infinite ways. For lack of a better term, Access calls these database parts objects (much more on that in Chapter 1). All Access databases have one thing in common—a Database window, where you can switch between objects and create new ones.

An Access database is also relational, which simply means that you can link tables together to combine their data. As you’ll see later in this book, connecting tables of fundamentally different information is what gives you the power to create invoices and statements, sort direct mail lists, and generate reports that Excel never dreamed of.

About This Book

As database managers go, Access 2003 is, well, quite accessible. Like all Microsoft Office programs, it comes with tons of built-in help screens and a wealth of online assistance. Microsoft—like most software companies—seems to believe that as a result, you don’t need any printed documentation.

But sometimes you need a handy reference that’s written with you in mind. You want a book that puts information you need at your fingertips, that you can mark up and pepper with sticky notes. Most of all, you need a friendly guide to walk you through the procedures, whispering insider secrets and commonsense advice along the way.

Access 2003 for Starters: The Missing Manual is meant to do just that. It gets you started creating your first database in Chapter 1. Step-by-step instructions show you how to build a simple table and progress to a complex, query-based report. You’ll master the features you use most of the time, find out which ones to disregard, and learn how to call on more advanced features when you’re ready.

Note

For highly advanced features beyond the scope of this book, you’ll see notes like this one referring you to other books where you can learn more. Sometimes these books are published by the Missing Manual series’ parent company, O’Reilly Media, but not always! If there’s a great book on the subject by any publisher, the Missing Manual believes you have a right to know.

About the Outline

Access 2003 for Starters: The Missing Manual has eight chapters, progressing from basic techniques into early intermediate territory. Along the way, real-life examples and step-by-step tutorials help you apply what you’re learning. Three appendixes supplement the main material and give you a launch pad for learning more.

• Part One: Building A Database shows you how to create the fundamental parts of any database: tables to hold data, and forms to enter or display data. You’ll learn both how to use Access’s Database Wizard and start from scratch. Chapter 2 and Chapter 3 cover designing your own tables and forms in great detail. Finally, Chapter 4 introduces you to relational database work. You’ll learn not only how to understand relationships, but also how easy it is to make them.

• Part Two: Organizing Information helps you sift through the information you’ve amassed using Access’s many options for sorting and filtering. In Chapter 6 and Chapter 7, you’ll learn how to use queries to take sorting and filtering to the next level. With a query, you can apply more powerful filtering features and save them for use in other parts of your database. Chapter 8 is a primer on generating reports that present your database to the world.

• Part Three: Appendixes provides references and resources for your ongoing Access career. Appendix A walks you through installing Access (if you haven’t already) and takes you on a tour of the program’s many built-in and online help features, so you can look up any information you need. Appendix B deals with Frequently Asked Questions about hot topics like sharing Access information with other Office programs and how to post your data on the Internet. Last but not least, Appendix C lists and explains most of the commands in Access’s menu bar. It’s a great way to figure out how to do something in a hurry.

  Note

  Don’t be surprised if you find some obscure Access menu commands that aren’t included in Appendix C. Frankly, Access has so many commands, not even Bill Gates can find them all.

About → These → Arrows

Throughout this book, and throughout the Missing Manual series, you’ll find sentences like this one: “Open the Program Files → Microsoft Office → Templates folder.” That’s shorthand for a much longer instruction that directs you to open three nested folders in sequence, like this: “On your hard drive, you’ll find a folder called Program Files. Open that. Inside the Program Files window is a folder called Microsoft Office; double-click it to open it. Inside that folder is yet another one called Templates. Double-click to open it, too.” See Figure 2 for an example.

Figure 2. When this book says “Choose View → Database Objects → Reports,” it means: “Click the View menu to open it. Next, click Database Objects in that menu. Finally, choose Reports from the resulting submenu.”

The Very Basics

This book avoids the nerd terminology and jargon that pollute so many computer books. However, you need to know a few terms:

• Clicking. In this book, you’ll find three kinds of instructions for clicking the mouse. To click a screen object (such as a button) means to point the mouse pointer at it, and then quickly press and release the left mouse button. Right-clicking means the same, except that you press the right mouse button instead. Double-clicking a screen object means to point the mouse pointer at it, and then quickly press and release the left mouse button twice, making a “click-click” sound. Dragging an object means to point the mouse pointer at it, hold down the left mouse button, move the pointer to a new position for the object, and then release the left mouse button.

  Up to Speed: What About the Big Guys?

  The word database is a little abused in the computer world. Both Microsoft Access and MySQL—an open source database that you can use free, if you have the skills, manpower, hardware and know-how—are considered database programs, but they’re about as similar as chocolate cake and dry flour. In reality, there are two kinds of database programs.

  One kind is very powerful (as in, run-the-federal-government powerful) and very complicated (MySQL is in this class). These programs just hold data, and computer programmers use these sophisticated, and expensive, tools when they build big systems for big companies.

  The other kind of database program—sometimes called a desktop database—is less powerful and a lot easier to use, but it actually does a lot more. In addition to holding lots of data, these programs provide an interface to access, organize, and search the data. This interface includes the menus, graphics, and text that let you work with the data, much like any other computer program.

  In other words, you don’t need a computer science degree to create a powerful database when you’ve got a desktop program like Access.

• Choosing menu items. This book uses very compact terminology for talking about menus. When you’re told to Choose View → Database Objects → Reports, for example, it means “Open the View menu, click Database Objects on the menu, and then click Reports on the submenu.”

• Keyboard shortcuts. The mouse is a mixed blessing. It makes computers easier to use, but it can also be a time-waster. Every time you use the mouse, you must take one hand off the keyboard for a few seconds. Do that a hundred or more times a day, and you’re burning up some serious time. Where keyboard shortcuts exist that work faster than the mouse, this book gives them to you. For example, the keyboard shortcut for saving an Access database is Ctrl+S, which means you hold down the Ctrl key and press the S key. You can still use the mouse if you prefer; keyboard shortcuts simply give you another option.

Example Databases

As you work through the tutorials in this book, you’ll see a number of example databases that demonstrate Access features and good database-building techniques. You can download these files to work in, or to check your work against if you prefer. Just surf to the “Missing CDs” page at www.missingmanuals.com/cds using Internet Explorer (or any Web browser). Then click the link next to the title of this book to view these files organized by chapter. Simply click a file’s blue, underlined link to download it to your PC.

About MissingManuals.com

At the Missing Manuals Web site (www.missingmanuals.com) you’ll find articles, tips, and updates for the book. In fact, if you have feedback about the book—whether suggestions, kudos, or corrections—you’re invited to submit it on the Web site. Any time we update the book, we’ll include any confirmed corrections you’ve suggested. We’ll also note the corrections on the Web site.

In the meantime, we’d love to hear your suggestions for new books in the Missing Manual line. There’s a place for that on the Web site, too, as well as a place to go for free email notifications of new titles in the series.

Safari Enabled

When you see a Safari^® Enabled icon on the cover of your favorite technology book, that means the book is available online through the O’Reilly Network Safari Bookshelf.

Safari offers a solution that’s better than e-books. It’s a virtual library that lets you easily search thousands of top tech books, cut and paste code samples, download chapters, and find quick answers when you need the most accurate, current information. Try it for free at http://safari.oreilly.com.