Introduction | Access 2007 for Starters[c] The Missing Manual

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 sizethey 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 convenienceit 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 #1creating 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 #2actually 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 wayby 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 .

The New Face of Access 2007

Ever since Microsoft Office conquered the world (way back in the 1990s), programs like Word, Excel, and Access haven't changed a lot. Although a genuinely useful new feature appears once in a while, Microsoft spends more time wedging in odd gimmicks like a talking paper clip.

Access 2007 breaks this pattern and introduces some of the most dramatic changes Office fans have seen since Office 95. The most obvious change is the thoroughly revamped user interface (the windows , toolbars , menus , and keyboard shortcuts you use to interact with Access). After spending far too long trying to simplify the haphazard, toolbar-choked interfaces in most Office applications, Microsoft finally worked up the courage to redesign it all from scratch.

The Ribbon

The Access 2007 ribbon is a super-toolbar that replaces the various toolbars and menus in previous versions.

Note: Access doesn't show the ribbon until you create a database. If you can't stand the suspense any longer, and you want to be able to look at the ribbon on your monitor as you read the next couple of pages, follow the instructions in Section 1.2.1 to create a blank database.

The ribbon's divided into task-specific tabs Home, Create, External Data, and so on. Initially, Access starts out with four tabs (although other tabs appear when you perform specific tasks). When you launch Access, you start at the Home tab. Click Create (as shown in Figure I-2), and you get access to a slew of powerful commands that let you add new database components .

Figure I-2. The ribbon's full of craftsman-like detail. When you hover over a button, you don't see a paltry two- or three-word description in a yellow box. Instead, you see a friendly pop-up box with a complete mini-description. Here, the mouse is hovering over the Table command.

Tip: Want to reclaim the screen real estate that the ribbon occupies? Just double-click the current tab, and the ribbon collapses, leaving only the row of tab titles visible. Double-click the tab again to pop the buttons back into sight. Section 1.4.2 has more about this feature.

Here's a quick snapshot of the four basic ribbon tabs:

Home gathers together a variety of common commands, including the familiar copy-and-paste tools and formatting commands for tweaking fonts and colors. You'll also find handy features like sorting, searching, and filtering, all of which you'll tackle in Chapter 3.
Create has commands for inserting all the different database objects you'll learn about in this book (see Section 1.1 for the lowdown). These include the tables that store data, the queries that search it, the forms that help you edit it, and the reports that help you print it.
External Data has commands for importing data into Access and exporting it to other programs. You'll use these commands in Chapter 10.
Database Tools features the pro tools that help experts perform deep- tissue data analysis and add Visual Basic code (a scripting language). Most civilians can safely avoid this tab, although you will tap into its abilities occasionally in Chapter 5, when you learn about linking tables.

It's worth spending some time getting accustomed to the tab-based ribbon. Try clicking one tab after the other, rifling back and forth through the four sections to see what they hold. You'll learn more about all these commands as you make your way through this book.

Tip: If you have a scroll mouse, you can breeze through the tabs even faster by moving the mouse pointer over the ribbon, and then moving the scroll wheel up or down.

One nice ribbon tab feature is that they never changein other words, you don't see commands mysteriously moving around or winking out of existence. Microsoft designed the ribbon to be predictable, so commands always remain in the same place. However, commands will change their arrangement a bit if you resize the Access window, so that they better use the available space (Figure I-3).

Using the Ribbon with the Keyboard

If you're a diehard keyboard lover, you'll be happy to hear that you can trigger ribbon commands with the keyboard. The trick's using keyboard accelerators , a series of keystrokes that starts with the Alt key (the same keys you used to use to get to a menu). When using a keyboard accelerator, you don't hold down all the keys at the same time. (As you'll soon see, some of them have enough letters to tie your fingers up better than the rowdiest game of Twister.) Instead, you hit the keys one after the other.

The trick to keyboard accelerators is to understand that once you hit the Alt key, you do two things, in this order:

Pick the correct ribbon tab .
In that tab, choose a command .

Figure I-3. This super-skinny Access window doesn't have much room for ribbon buttons. All the same commands that you saw in Figure I-2 are still in the ribbon, but now you see only small icons with no text. When you're in doubt about a button, hover over it to see its name.

Before you can trigger a specific command, you must select the right tab (even if you're already there). Every accelerator requires at least two key presses after you hit the Alt key. You'll need even more if you need to dig through a submenu.

By now, this whole process probably seems hopelessly impractical . Are you really expected to memorize dozens of different accelerator key combinations?

Fortunately, Access is ready to help you out with a new feature called KeyTips . Here's how it works: Once you press the Alt key, letters magically appear over every tab in the ribbon. Once you hit a key to pick a tab, letters appear over every button in that tab. You can then press the corresponding key to trigger the command. Figure I-4 shows how it works.

Note: In some cases, a command may have two letters, and you need to press both keys, one after the other. You can back out of KeyTips mode at any time without triggering a command by pressing the Alt key again.

Some other shortcut keys don't use the ribbon. These key combinations start with the Ctrl key. For instance, Ctrl+C copies highlighted text, and Ctrl+S saves your current work. Usually, you find out about a shortcut key by hovering over a command with the mouse. Hover over the Paste button in the ribbon's Home tab, and you see a tooltip that tells you its timesaving shortcut key is Ctrl+V. And if you've worked with a previous version of Access, you'll find that Access 2007 keeps most of the same shortcut keys.

Figure I-4. Top: When you press Alt, Access pins KeyTips next to every tab, over the Office menu, and over the buttons in the Quick Access toolbar (more about the Office menu and the Quick Access toolbar in a moment).
Bottom: If you follow up with C (for the Create tab), you'll see letters next to every command in that tab. Now you can click another button to run a command. Don't bother trying to match letters with tab or button names ; the ribbon's got so many features packed into it that in many cases, the letters don't mean anything at all.

NOSTALGIA CORNER
Access 2003 Menu Shortcuts

If you've worked with a previous version of Access, you may have trained yourself to use menu shortcutskey combinations that open a menu and pick out the command you want. When you press Alt+E in Access 2003, the Edit menu pops open (in the main menu). You can then press the S key to choose the Paste Special command.

At first glance, it doesn't look like these keyboard shortcuts amount to much in Access 2007. After all, Access 2007 doesn't even have a main menu! Fortunately, Microsoft went to a little extra trouble to make life easier for longtime Access aficionados. You can still use your menu shortcuts, but they work in a slightly different way.

If you hit Alt+E in Access 2007, a tooltip appears over the top of the ribbon (Figure I-5) that lets you know you've started to enter an Access 2003 menu shortcut. If you go on to press S, then you wind up at the familiar Paste Special dialog box, because Access knows what you're trying to do. It's almost as though Access has an invisible menu at work behind the scenes.

Of course, this feature can't help you out all the time. It doesn't work if you're trying to use one of the few commands that don't exist any longer. And if you need to see the menu to remember what key to press next, you're out of luck. Access just gives you the tooltip.

Figure I-5. By pressing Alt+E, you've triggered the "imaginary" Edit menu. You can't actually see it (because it doesn't exist in Access 2007). However, the tooltip lets you know that Access is paying attention. You can now complete your action by pressing the next key for the menu command.

NOSTALGIA CORNER
Why Reinvent the Wheel?

Some Access veterans are understandably skeptical about the new Access interface. After all, we've had to suffer through some painful experiments. Past versions of Access have introduced kooky ideas like personalized menus that always seem to hide just the command you need, pop-up side panels that appear when you least expect them, and floating toolbars that end up strewn across the screen.

In reality, all the Office applications have been struggling to keep up with more than a decade 's worth of new features. The menus in most Office programs haven't changed since Word 2.0 hit the scene in the early 1990s. In those days, a basic menu and a single toolbar were just the ticket, because the number of commands was relatively small.

Today, the Office programs are drowning in featuresand they're crammed into so many different nooks and crannies that even pros don't know where to look.

That's where the new ribbon fits in. Not only can you easily understand and navigate it, it provides one-stop shopping for everything you need to do. Microsoft's user interface designers have a new mantra: It's all in the ribbon . In other words, if you need to find a feature, then look for it in one of the tabs at the top of the Access window. As you get accustomed to this new system, you'll find it not only helps you quickly use your favorite features, it also helps you discover new features just by browsing.

The Office Menu

One small part of the traditional Access menu's left in Access 2007sort of. The traditional File menu that lets you open, save, and print files has been transformed into the Office menu . You get there using the Office button, which is the big, round logoin the window's top-left corner (Figure I-6).

You generally use the Office menu for three things:

Opening, creating, and saving your database. You'll do plenty of this in Chapter 1.
Printing your work (Chapter 3) and sending it off to other people by email.
Configuring how Access behaves. Choose Access Options at the bottom of the menu to get to the Access Options dialog box, an all-in-one hub for configuring Access settings.

Figure I-6. The Office menu's bigger and easier to read than a traditional menu. When you click it, it displays a list of menu commands (on the left) and a list of the databases you used recently (on the right).

There's one menu quirk that takes a bit of getting used to. Some Office menu commands hide submenus that have more commands. Take the Print command. You can choose Print from the Office menu to fire off a quick printout of your work. But if you click the right-pointing arrow at the edge of the Print command (or if you hover over it for a moment), then you see a submenu with more options, as shown in Figure I-7.

The Quick Access Toolbar

Keen eyes will have noticed the tiny bit of screen real estate that sits on the Office button's right side, just above the ribbon (Figure I-8). This bit of screen holds a series of tiny icons, like the toolbars in older versions of Access. This area's the Quick Access toolbar (or QAT to Access nerds).

Figure I-7. Print's both a clickable menu command and a submenu. To see the submenu, you need to hover over Print (without clicking), or click the arrow at the right edge (shown here). The ribbon also has a few buttons that work this way.

Figure I-8. The Quick Access toolbar puts the Save, Undo, and Redo commands right at your fingertips. Access singles out these commands because people use them more frequently than any other commands.

About This Book

Despite the many improvements in software over the years, one feature hasn't improved a bit: Microsoft's documentation. In fact, with Office 2007, you get no printed user guide at all. To learn about the thousands of features included in this software collection, Microsoft expects you to read the online help.

Occasionally, these help screens are actually helpful, like when you're looking for a quick description explaining a mysterious new function. On the other hand, if you're trying to learn how to, say, create an attractive chart, you'll find nothing better than terse and occasionally cryptic instructions.

This book is the manual that should have accompanied Access 2007. In these pages, you'll find step-by-step instructions and tips for using almost every Access feature, including those you haven't (yet) heard of.

Note: This book is based on Access 2007: The Missing Manual (O'Reilly). That book is a truly complete reference for Access 2007, covering every feature, including geeky stuff like XML, VBA, SQL Server, and other things you'll probably never encounteror even want to. But if you get really deep into Access and want to learn more, Access 2007: The Missing Manual can be your trusted guide.

About the Outline

This book's divided into four parts :

Part One: Storing Information in Tables . In this part, you'll build your first database and learn how to add and edit tables that store information. Then you'll pick up the real-world skills you need to stop mistakes before they happen, browse around your database, and link tables together.
Part Two: Manipulating Data with Queries . In this part, you'll build queries : specialized commands that can hunt down the data you're interested in, apply changes, and summarize vast amounts of information.
Part Three: Printing Reports and Using Forms . This part shows you how to use reports to take the raw data in your tables and format it into neat printouts, complete with fancy formatting and subtotals. You'll also learn how to build formscustomized windows that make data entry easy, even for Access newbies.
Part Four: Sharing Access with the Rest of the World . In this part, you'll learn how to pull your data out of (or put your data into) other types of files, like text documents and Excel spreadsheets.

About These Arrows

Throughout this book, you'll find sentences like this one: "Choose Create Tables Table." This methods a shorthand way of telling you how to find a feature in the Access ribbon. It translates to the following instructions: "On the ribbon, click the Create tab. On the tab, look for the Tables section. In the Tables box, click the Table button." (Look back to Figure I-2 to see the button you're looking for.)

As you saw back in Figure I-3, the ribbon adapts itself to different screen sizes. Depending on your Access window's size , the button you need to click may not include any text. Instead, it shows up as a small icon. In this situation, you can hover over the mystery button to see its name before deciding whether or not to click it.

If you resize the Access window so that it's really small, you might run out of space for a section altogether. In that case, you get a single button that has the section's name. Click this button, and the missing commands appear in a drop-down panel (Figure I-9).

Contextual tabs

Although nice, predictable tabs are a great idea, some features obviously make sense only in specific circumstances. Say you start designing a table. You may have a few more features than when you're entering data. Access handles this situation by adding one or more contextual tabs to the ribbon, based on your current task. These tabs have additional commands that are limited to a specific scenario (Figure I-10).

When dealing with contextual tabs, the instructions in this book always include the title of the tab section (it's Table Tools in Figure I-10). Here's an example: "Choose Table Tools Datasheet Fields & Columns New Fields." Notice that this instructions first part includes the contextual tab title (Table Tools) and the tab name (Datasheet), separated by the character.

Figure I-9. In this example, Access doesn't have the room to display the Home tab's Views, Records, or Find sections, so they're all replaced with buttons. If you click any of these buttons, then a panel appears with the content you're looking for.

Figure I-10. When you're designing a form, a new contextual tab appears, named Datasheet, under the heading Table Tools. Contextual tabs always appear on the ribbon's right side.

Drop-down buttons

From time to time you'll encounter buttons in the ribbon that have short menus attached to them. Depending on the button, this menu appears as soon as you click the button, or it appears only if you click the button's drop-down arrow, as shown in Figure I-11.

Figure I-11. Access lets you switch between several different views of your database. Click the bottom part of the View button to see the menu of choices shown here, or click the top part to switch to the next view in the list, with no questions asked.

When dealing with this sort of button, the last step of the instructions in this book tells you what to choose from the drop-down menu. For example, say you're directed to "Home Views View Design View." That tells you to select the Home tab, look for the Views section, click the drop-down part of the View button (to reveal the menu with extra options), and then choose Design View from the menu.

Note: Be on the lookout for drop-down arrows in the ribbonthey're tricky at first. You need to click the arrow part of the button to see the full list of options. If you click the other part of the button, then you don't see the list. Instead, Access fires off the standard command (the one Access thinks is the most common choice), or the command you used most recently.

About Shortcut Keys

Every time you take your hand off the keyboard to move the mouse, you lose a few microseconds of time. That's why many experienced computer fans use keystroke combinations instead of toolbars and menus wherever possible. Ctrl+S, for one, is a keyboard shortcut that saves your current work in Access (and most other programs).

When you see a shortcut like Ctrl+S in this book, it's telling you to hold down the Ctrl key, and, while it's down, press the letter S, and then release both keys. Similarly, the finger-tangling shortcut Ctrl+Alt+S means hold down Ctrl, then press and hold Alt, and then press S (so that all three keys are held down at once).

About Clicking

This book gives you three kinds of instructions that require you to use your computer's mouse or trackpad. To click means to point the arrow cursor at something on the screen and thenwithout moving the cursor at allpress and release the left-side clicker button on the mouse (or laptop trackpad). To double-click , of course, means to click twice in rapid succession, again without moving the cursor at all. And to drag means to move the cursor while holding down the button.

Examples

As you read this book, you'll see a number of examples that demonstrate Access features and techniques for building good databases. Many of these examples are available as Access database files in a separate download. Just surf to www.missingmanuals.com, click the link for this book, and then click the "Missing CD" link to visit a page where you can download a zip file that includes the examples, organized by chapter.

About MissingManuals.com

At www.missingmanuals.com, you'll find news, articles, and updates to the books in the Missing Manual and For Starters series.

But the Web site also offers corrections and updates to this book (to see them, click the book's title, and then click Errata). In fact, you're invited and encouraged to submit such corrections and updates yourself. In an effort to keep the book as up to date and accurate as possible, each time we print more copies of this book, we'll make any confirmed corrections you've suggested. We'll also note such changes on the Web site, so that you can mark important corrections into your own copy of the book, if you like.

In the meantime, we'd love to hear your own suggestions for new books in the Missing Manual and For Starters lines. There's a place for that on the Web site, too, as well as a place to sign up for free email notification 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.