Access 2007 for Starters: The Missing Manual

Introduction

• What You Can Do with Access

• The New Face of Access 2007

• About This Book

In the past, people have tried a variety of techniques to organize information. They've used Rolodexes, punch cards, cardboard boxes, vertical files, Post-it notes, 10,000-page indexes, and (when all else failed) large piles on top of flat surfaces. But after much suffering, people discovered that computers were far better at dealing with information, especially when that information's large, complex, or changes frequently.

That's where Microsoft Access comes into the picture. Access is a tool for managing databases—carefully structured catalogs of information (or data). Databases can store just about any type of information, including numbers, pages of text, and pictures. Databases also range wildly in size—they can handle everything from your list of family phone numbers to a ginormous product catalog for Aunt Ethel's Discount Button Boutique.

In this book, you'll learn how to design complete databases, maintain them, search for valuable nuggets of information, and build attractive forms for quick and easy data entry.

Best of all, this book was written from scratch for Access 2007, the latest and greatest incarnation of Microsoft's bestselling database software. Access 2007's quite a bit different from previous versions, with a slick new interface that has computer geeks buzzing. And for once, it's not just a gimmick. As you'll see in this book, once you master Access's new style, you'll be able to build great databases in record time.

What You Can Do with Access

The modern world is filled with information. A Web search for a ho-hum topic like "canned carrots" nets more than a million Web pages. As a result, it's no surprise that people from all walks of life need great tools to store and manage information.

It's impossible to describe even a fraction of the different databases that Access fans create every day. But just to get you thinking like a database maven, here are some common types of information that you can store handily in an Access database:

• Catalogs of books, CDs, rare wine vintages, risqué movies, or anything else you want to collect and keep track of

• Mailing lists that let you keep in touch with friends, family, and co-workers

• Business information, like customer lists, product catalogs, order records, and invoices

• Lists of guests and gifts for weddings and other celebrations

• Lists of expenses, investments, and other financial planning details

Think of Access as a personal assistant that can help you organize, update, and find any type of information. This help isn't just a convenience—it also lets you do things you could never accomplish on your own.

Imagine you've just finished compiling a database for your collection of 10,000 rare comic books. On a whim, you decide to take a look at all the books written in 1987. Or just those that feature Aquaman. Or those that contain the words "special edition" in the title. Performing these searches with a paper catalog would take days. On an average computer, Access can perform all three searches in under a second.

Access is also the king of small businesses, because of its legendary powers of customization. After all, you can use virtually any database product to create a list of customer orders. But only Access makes it easy to build a full user interface for that database (as shown in Figure I-1).

The Two Sides of Access

As you'll see, there are actually two separate tasks you'll perform with Access:

• Designing your database. This task involves creating tables to hold data, queries that can ferret out important pieces of information, forms that make it easy to enter information, and reports that produce attractive printouts.

• Dealing with data. This task involves adding new information to the database, updating what's there, or just searching for the details you need. In order to do this work, you use the tables, queries, forms, and reports that you've already built.

Most of this book's dedicated to task #1—creating and perfecting your database. This job's the heart of Access, and it's the part that initially seems the most daunting. It's also what separates the Access masters from the neophytes.

Once you've finished task #1, you're reading to move on to task #2—actually using the database in your day-to-day life. Although task #1 is more challenging, you'll (eventually) spend more time on task #2. For example, you might spend a couple of hours creating a database to keep track of your favorite recipes, but you'll wind up entering new information and looking up recipes for years (say, every time you need to cook up dinner).

Figure I-1. This sales database includes handy forms that sales people can use to place new orders (shown here), customer service representatives can use to sign up new customers, and warehouse staff can use to review outgoing shipments. Best of all, the people who are using the forms in the database don't need to know anything about Access. As long as a database pro (like your future self, once you've finished this book) has designed these forms, anyone can use them to enter, edit, and review data.

Access vs. Excel

Access isn't the only Office product that can deal with lists and tables of information. Microsoft Excel also includes features for creating and managing lists. So what's the difference?

UP TO SPEED: The Benefits of a Good Database

Many people use an address book to keep track of close friends, distant relatives, or annoying co-workers. For the most part, the low-tech address book works great. But consider what happens if you decide to store the same information in an Access database. Even though your contact list isn't storing Google-sized volumes of information, it still offers a few features that you wouldn't have without Access:

• Backup. If you've ever tried to decipher a phone number through a coffee stain, you know that sometimes it helps to have things in electronic form. Once you place all your contact information into a database, you'll be able to preserve it in case of disaster, and print up as many copies as you need (each with some or all of the information showing). You can even share your list with a friend who needs the same numbers.

• Space. Although most people can fit all the contacts they need into a small address book, a database ensures you'll never fill up that "M" section. Not to mention that there are only so many times you can cross out and rewrite the address for your itinerant Uncle Sy before you run out of room.

• Searching. An address book organizes contacts in one way—by name. But what happens once you've entered everyone in alphabetical order by last name, and you need to look up a contact you vaguely remember as Joe? Access can effortlessly handle this search. It can also find a matching entry by phone number, which is great if your phone gives you a log of missed calls and you want to figure out who's been pestering you.

• Integration with other applications. Access introduces you to a realm of timesaving possibilities, like mail merge. You can feed a list of contacts into a form letter you create in Word, and automatically generate dozens of individually addressed letters. You'll see how to do this in Chapter 10.

All these examples demonstrate solid reasons to go electronic with almost any type of information.

Although Excel's perfectly good for small, simple amounts of information, it just can't handle the same quantity and complexity of information as Access. Excel also falters if you need to maintain multiple lists with related information (for example, if you want to track a list with your business customers, and a list of the orders they've made). Excel forces you to completely separate these lists, which makes it harder to analyze your data and introduces the possibility of inconsistent information. Access lets you set up strict links between tables, which prevents these problems.

Access also provides all sorts of features that don't have any parallel in the spreadsheet world, such as the ability to create customized search routines, design fine-tuned forms for data entry, and print a variety of snazzy reports.

Note

Looking to polish up your Excel skills? Check out Excel 2007: The Missing Manual.