O'Reilly Media | Access 2007 for Starters: The Missing Manual

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Understanding Access Databases
Getting Started
Saving and Opening Access Databases
The Navigation Pane

Although Microsoft Won'T Admit It, Access can be intimidating—intimidating enough to trigger a cold sweat in the most confident office worker. Even though Microsoft has spent millions making Access easier to use, most people still see it as the most complicated Office program on the block. They're probably right.

Access seems more daunting than any other Office program because of the way that databases work. Quite simply, databases need strict rules. Other programs aren't as obsessive. For example, you can fire up Word, and start typing a letter straight away. Or you can start Excel, and launch right into a financial report. But Access isn't nearly as freewheeling. Before you can enter a stitch of information into an Access database, you need to create that database's structure. And even after you've defined that structure, you'll probably want to spend more time creating other useful tools, like handy search routines and friendly forms that you can use to simplify data lookup and data entry. All of this setup takes effort, and a good understanding of how databases work.

In this chapter, you'll conquer any Access resistance you have and learn to create a simple but functional database. Along the way, you'll get acquainted with the slick new Access user interface, and you'll learn exactly what you can store in a database. You'll then be ready to tackle the fine art of database design, covered in more detail throughout this book.

As you already know, a database is a collection of information. In Access, every database is stored in a single file. That file contains

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

As you already know, a database is a collection of information. In Access, every database is stored in a single file. That file contains database objects, which are simply the components of a database.

Database objects are the main players in an Access database. Altogether, you have six different types of database objects:

Tables store information. Tables are the heart of any database, and you can create as many tables as you need to store different types of information. A fitness database could track your daily running log, your inventory of exercise equipment, and the number of high-protein whey milkshakes you down each day, as three separate tables.
Queries let you quickly perform an action on a table. Usually, this action involves retrieving a choice bit of information (like the 10 top-selling food items at Ed's Roadside Dinner, or all the purchases you made in a single day). However, you can also use queries to apply changes.
Forms are attractive windows that you create, arrange, and colorize. Forms provide an easy way to view or change the information in a table.
Reports help you print some or all of the information in a table. You can choose where the information appears on the printed page, how it's grouped and sorted, and how it's formatted.
Macros are mini-programs that automate custom tasks. Macros are a simple way to get custom results without becoming a programmer.
Modules are files that contain Visual Basic code. You can use this code to do just about anything—from updating 10,000 records to firing off an email. For more on Visual Basic, see

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

It's time to begin your journey and launch Access. You'll start at a spiffy Getting Started page (Figure 1-1).

Figure 1-1: The Getting Started page is a bit of a cross between a Windows program and a Web page. Use the links on the left to browse through different categories of templates (ready-to-go databases that you can download and fill with your own information). Or check out the links on the bottom, which show you the latest Access news and tips.

The Getting Started page looks a little dizzying at first glance, but it really serves just three purposes:

It shows you recent content from Microsoft's Office Online Web site. For example, you can read helpful articles about Access, find timesaving tips, or download updates. All links open in a separate browser window.
It lets you open a database you used recently. Look for the Open Recent Data-base section on the right, which gives you a list.

Can I use an Access database I didn't design?

Although every database follows the same two-step process (first somebody creates it, and then people fill it with information), the same person doesn't need to perform both jobs. In fact, in the business world, different people often work separately on these two tasks.

For example, a summer student whiz kid at a beer store may build a database for tracking orders (task #1). The sales department can then use the database to enter new orders (task #2), while other employees look up orders and fill them (also task #2). Warehouse staff can make sure stock levels are OK (again, task #2), and the resident accountant can keep an eye on total sales (task #2).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Unlike other programs, Access doesn't require that you save your work. It automatically saves any changes you make.

When you create a new database (Section 1.2.1), Access saves your database file. When you add a table or another object to the database, Access saves the database again. And when you enter new data or edit existing data, Access saves the database almost instantaneously.

When you add information to a database, Access doesn't always pack the data as compactly as possible. Instead, Access is more concerned with getting information in and out of the database as quickly as it can.

After you've been working with a database for a while, you might notice that its size bloats up like a week-old fish in the sun. If you want to trim your database back to size, you can use a feature called compacting. To do so, just choose Office button → Manage → Compact and Repair Database. The amount of space you reclaim varies widely, but it's not uncommon to have a 10 MB database shrink down to a quarter of its size.

The only problem with the database compacting feature is that you need to remember to use it. If you want to keep your databases as small as possible at all times, you can switch on a setting that tells Access to compact the current database every time you close it. Here's how:

Open the database that you want to automatically compact.
Choose Office button → Access Options. Access opens the Access Options window where you can make a number of configuration changes.
In the list on the left, choose Current Database.
In the page on the right, turn on the Compact on Close" checkbox.
Click OK to save your changes.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

It's time to step back and take a look at what you've accomplished so far. You've created the Bobblehead database, and added a single database object: a table named Dolls. You've filled the Dolls table with several records. You don't have the fancy windows, reports, and search routines that make a database work really smoothly, but you do have the most important ingredient—organized data.

One issue you haven't tackled yet is how you manage the objects in your database. For example, if you have more than one table, you need a way to move back and forth between the two. That tool's the navigation pane, shown in Figure 1-19.

The navigation pane shows the objects (Section 1.1) that are part of your database, and it lets you manipulate them. However, you don't necessarily see all your database objects at all times. The navigation pane has several different viewing modes, so you can home in on exactly what interests you.

When you first create a database, the navigation pane shows only the tables in your database. That's good enough for now—after all, your database doesn't contain anything but the tables you've created.

Figure 1-19: Unhappy with the space consumed by the navigation pane? Click the Open/Close but-ton in the top-right corner (top), and the navigation bar slides out of the way to give more room for the datasheet (bottom). Click the button again to expand it back into view.

To really try out the navigation pane, you need a database with more than one table. To give it a whirl, choose Create → Table from the ribbon to add a new blank table. Follow all the steps in Section 1.2.3 to define the table and insert a record or two.

Not sure what table to create? Try creating a Collectors table that tracks all the friends you know who share the same bobbleheaded obsession. Now try to come up with a few useful fields for this table (while remembering that there's no need to go crazy with the details yet), and then compare your version to the example in Figure 1-20.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Understanding Data Types
Design View
Access Data Types
The Primary Key
Six Principles of Database Design

In The Previous Chapter, you learned how to dish out databases and pop tables into them without breaking a sweat. However, there's bad news. The tables you've been creating so far aren't up to snuff.

Most significantly, you haven't explicitly told Access what type of information you intend to store in each field of your table. A database treats text, numbers, dates, and other types of information differently. If you store numeric information in a field that expects text, then you can't do calculations later on (like find the average value of your bobblehead dolls), and you can't catch mistakes (like a bobblehead with a price value of "fourscore and twenty").

To prevent problems like these, you need to define the data type of each field in your table. This is the central task you'll tackle in this chapter. Once you've mastered data types, you're ready to consider some of the finer points of database design.

All data's not created equal. Consider the Dolls table you created in Chapter 1 (Section 1.2.3). Its fields actually contain several different types of information:

Text. The Character and Manufacturer fields.
Numbers. The ID and PurchasePrice fields.
Dates. The DateAcquired field.

You may naturally assume that the PurchasePrice field always includes numeric content, and the DateAcquired field always includes something that can be interpreted as a date. But if you haven't set the data types correctly, Access doesn't share your assumptions, and doesn't follow the same rules.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

All data's not created equal. Consider the Dolls table you created in Chapter 1 (Section 1.2.3). Its fields actually contain several different types of information:

Text. The Character and Manufacturer fields.
Numbers. The ID and PurchasePrice fields.
Dates. The DateAcquired field.

You may naturally assume that the PurchasePrice field always includes numeric content, and the DateAcquired field always includes something that can be interpreted as a date. But if you haven't set the data types correctly, Access doesn't share your assumptions, and doesn't follow the same rules.

When you create a new field in Datasheet view, Access makes an educated guess about the data type by examining the information you've just typed in. If you type 44, then Access assumes you're creating a number field. If you type Jan 6, 2007, then Access recognizes a date. However, it's easy to confuse Access, which leads to the problems shown in Figure 2-1.

In order to prevent invalid entries, you need to tell Access what each field should contain. Once you set the rules, Access enforces them rigorously. You put these requirements in place using another window—the Design view of your table.

Figure 2-1: Here, Access doesn't recognize the date format used for the DateAcquired field when it was created. As a result, Access treats that field as ordinary text. You can enter dates in several different formats (which makes the DateAcquired information harder to read and impossible to sort). You also let in completely nonsensical entries, like "fourscore bananas."

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

When you create a new database, Access starts you off with a single table and shows that table in Datasheet view. (As you learned last chapter, Datasheet view is the grid-like view where you can create a table and enter data.) To switch to Design view, right-click the tab name (like "Dolls: Table"), and then choose Design View. (Or you can use the Home → View command, the Table Tools | Datasheet → View command, or the View buttons at the bottom of the Access window. Figure 2-2 shows all your options. All of these commands do the same thing, so pick whichever approach seems most convenient.)

If you've opened an old Access 2003 database, you won't see any tabs. Instead, you'll get a bunch of overlapping windows. You can remedy this problem and get your tabs back by following the instructions in Section 1.3.5. Or, if you want to keep the overlapping windows, just use the view buttons or the ribbon to change views (instead of the right-click-the-tab-title approach described above).

Figure 2-2: Right-click the tab name to see this menu. You can switch to Design view (choose Design View) and back again (choose Datasheet View). Alternatively, you can use the tiny view buttons in the window's bottom-right corner to jump back and forth. (Don't worry about the other two view buttons. Those are used to analyze data in a pivot table, an advanced form of data presentation covered in Access 2007: The Missing Manual.)

If you switch to Design view on a brand-new table that you haven't saved yet, Access asks you for a table name. Access then saves the table before switching you to Design view.

For a handy shortcut, you can create a new table and automatically start in Design view. To do this, choose Create → Tables → Table Design. However, when you take this route, your table doesn't include the very important ID column, so you need to add one, as described in Section 2.4.1.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Design view's a much more powerful place for defining a table than Datasheet view. As you'll see throughout this chapter, Design view allows you to tweak all sorts of details that are hidden in Datasheet view (or just awkward to change).

One of these is the data type of your field—a setting that tells Access what type of information you're planning to store. To change the data type, make a selection in the Data Type column next to the appropriate field (Figure 2-6). Here's where you separate the text from numbers (and other data types). The trick's choosing the best data type from the long list Access provides—you'll get more help in the following section.

Depending on the data type you choose, there are other field properties that you can adjust to nail down your data type even more precisely. If you use a text data type, then you use field properties to set the maximum length. If you choose a decimal value, then you use field properties to set the number of decimal places. You set field properties in the Field Properties part of the Design view, which appears just under the field list. You'll learn more about field properties throughout this chapter (and you'll consider them again in Chapter 4).

The most important decision you'll make for any field is choosing its data type. The data type tells Access what sort of information you plan to store in that field. Access uses this information to reject values that don't make sense (see Figure 2-7), to perform proper sorting, and to provide other features like calculations, summaries, and filtering.

Figure 2-6: To choose a data type, click the Data Type column next to the appropriate field. A drop-down list box appears, with 11 choices.

Figure 2-7: This currency field absolutely does not allow text. Access lets you fix the problem by entering a new value (the right choice) or changing the field data type to text so that it allows anything (the absolutely wrong choice).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Design view also allows you to set a table's primary key, which is a field (or a combination of fields) that's unique for each record. Every table must have a primary key. To understand why the primary key's important, you need to consider a little bit more about how databases work. The box "How Access Prevents Duplicate Records" in Section 2.4.1 has the full story.

Choosing a primary key is trickier than it seems. Imagine you have a list of friends (and their contact information) in a table named People. You may logically assume that you can create a primary key using a combination of first and last name. Unfortunately, that just won't do—after all, some address books have two Sean Smiths.

Your best solution's to invent a new piece of information. You can label every individual in your contact list with a unique ID number. Best of all, you can get Access to automatically create this number for you (and make sure that no two people get the same number), so you don't even need to think about it. That way, if you have two Sean Smiths, each one has a different ID. And even if Ferris Wheel Simpson decides to change his first name, the ID remains the same.

In order to function correctly, a database program like Access needs to be able to tell the difference between each and every record in your table. In other words, you can't insert two records with exactly the same information. Databases are notoriously fussy, and they don't tolerate this sort of sloppiness.

The challenge of preventing duplicates isn't as easy as it seems. Access is designed to be blisteringly fast, and it can't afford to double-check your new record against every other record in the table to see if there's a duplicate. So instead, it relies on a primary key. As long as every record in a table has a unique, never-duplicated primary key, you can't have two identical records. (At worst, they'll be two almost identical records that have the same information in all their other fields, but have different primary keys. And this is perfectly acceptable to Access.)

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

With great power comes great responsibility. As a database designer, it's up to you to craft a set of properly structured tables. If you get it right, you'll save yourself a lot of work in the future. Well-designed databases are easy to enhance, simpler to work with, and lead to far fewer mind-bending problems when you need to extract information.

Sadly, there's no recipe for a perfect database. Instead, a number of recommendations can guide you on the way. In this section, you'll learn about a few of the most important.

You won't completely understand why it's so important for each record to have a unique ID number until you work with the more advanced examples in later chapters. However, one of the reasons is that other programs that use your database need to identify a record unambiguously.

To understand why there's a problem, imagine that you've built a program for editing the Dolls table. This program starts by retrieving a list of all your table's bobbleheads. It displays this list to the person using the program, and lets her make changes. Here's the catch—if a change is made, the program needs to be able to apply the change to the corresponding record in the database. And in order to apply the change, it needs some unique piece of information that it can use to locate the record. If you've followed the best design practices described above, the unique "locator" is the bobblehead's ID.

Building a good database is an art that takes practice. For best results, read these guidelines, and then try building your own test databases.

Access doesn't impose many rules on what field names you can use. It lets you use 64 characters of your choice. However, field names are important. You'll be referring to the same names again and again as you build forms, create reports, and even write code. So it's important to choose a good name from the outset.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Datasheet Customization
Datasheet Navigation
Printing the Datasheet

In chapter 1, you took your first look at the datasheet—a straightforward way to browse and edit the contents of a table. As you've learned since then, the datasheet isn't the best place to build a table. (Design view's a better choice for database control freaks.) However, the datasheet is a great tool for reviewing the records in your table, making edits, and inserting new data.

Based on your experience creating the Dolls table (Section 1.2.3), you probably feel pretty confident breezing around the datasheet. However, most tables are considerably larger than the examples you've seen so far. After all, if you need to keep track of only a dozen bobbleheads, then you really don't need a database—you'll be just as happy jotting the list down in any old spreadsheet, word processor document, or scrap of unused Kleenex.

On the other hand, if you plan to build a small bobblehead empire (suitable for touring in international exhibitions), you need to fill your table with hundreds or thousands of records. In this situation, it's not as easy to scroll through the mass of data to find what you need. All of a sudden, the datasheet seems more than a little overwhelming.

Fortunately, Access is stocked with datasheet goodies that can simplify your life. In this chapter, you'll become a datasheet expert, with tricks like sorting, searching, and filtering at your fingertips. You'll also learn a quick-and-dirty way to print a snapshot of the data in your table.

It's entirely up to you how much time you spend using datasheets. Some Access experts prefer to create forms for all their tables (as described in Part Three). With forms, you can design a completely customized window for data entry. Designing forms takes more work, but it's a great way to satisfy your inner Picasso.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Getting tired of the drab datasheet, with its boring stretch of columns and plain text? You can do something about it. Access lets you tweak the datasheet's appearance and organization to make it more practical (or suit it to your peculiar sense of style). Some of these customizations—like modifying the datasheet font—are shameless frills. Other options, like hiding or freezing columns, can genuinely make it easier to work with large tables.

Access doesn't save formatting changes immediately (unlike record edits, which it stores as soon as you make them). Instead, Access prompts you to save changes the next time you close the datasheet. You can choose Yes to keep your customizations or No to revert to the table's last look and feel (which doesn't affect any edits you've made to the data in that table).

Access lets you format the datasheet with eye-catching colors and fonts. Do these options make any difference to the way the datasheet works? Not really. But if your computer desktop looks more like a '60s revival party than an office terminal, then you'll enjoy this feature.

To find the formatting features, look at the ribbon's Home → Font section (see Figure 3-1).

Figure 3-1: The Home → Font section lets you change the text font and colors in the entire datasheet. The most practical frill is the ability to turn off some or all of the gridlines and use alternating row colors to highlight every other row, as shown here.

Every formatting change you make affects the entire table. You may think it's a nifty idea to apply different formatting to different columns, but Access doesn't let you. If this limitation's frustrating you, be sure to check out forms and reports later in this book. Both are more complicated to set up, but give you more formatting power.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In Chapter 1, you learned the basics of moving around the datasheet. Using your mouse and a few select keystrokes, you can cover a lot of ground. (Flip back to Section 1.2.4.2 for a review of the different keys you can use to jump from place to place and perform edits.)

However, you haven't seen a few tricks yet. One's the timesaving record navigation buttons at the bottom of the datasheet (Figure 3-7).

Figure 3-7: You could easily overlook the navigation buttons at the bottom of the datasheet. These buttons let you jump to the beginning and end of the table, or, more interestingly, head straight to a record at a specific position. To do this, type the record number (like "4") into the box (where it says "3 of 6" in this example), and then hit Enter. Of course, this trick works only if you have an approximate idea of where in the list your record's positioned.

Several more datasheet features help you orient yourself when dealing with large amounts of data, including sorting (which orders the records so you can see what you want), filtering (which cuts down the data display to include only the records you're interested in), and searching (which digs specific records out of an avalanche of data). You'll try all these features out in the following sections.

In some cases, you can most easily make sense of a lot of data by putting it in order. You can organize a customer list by last name, a product catalog by price, a list of wedding guests by age, and so on.

To sort your records, pick a column you want to use to order the records. Click the drop-down arrow at the right edge of the column header, and then choose one of the sort options at the top of the menu (see Figure 3-8).

Figure 3-8:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

If you want to study your data at the dinner table (and aren't concerned about potential conflicts with non-Access-lovers), nothing beats a hard copy of your data. You can dash off a quick printout by choosing File → Print from the menu while your datasheet's visible. However, the results you get will probably disappoint you, particularly if you have a large table.

The search feature doubles as a powerful (but somewhat dangerous) way to modify records.

Initially, when the Find and Replace dialog box appears, it shows the Find tab. However, you can click the Replace tab to be able to find specific values and replace them with different text. All the settings for a replace operation are the same as for a find operation, except you have an additional text box, called Replace With, to supply the replacement text.

The safest way to perform a replace operation is to click the Find Next button to jump to the next match. At this point, you can look at the match, check that you really do want to modify it, and then click Replace to change the value and jump to the next match. Repeat this procedure to move cautiously through the entire table.

If you're a wild and crazy skydiving sort who prefers to live life on the edge, you can use the Replace All button to change every matching value in the entire table in a single step. Although this procedure's ridiculously fast, it's also a little risky. Replace operations can't be reversed (the Undo feature's no help here because it can reverse only a single record change), so if you end up changing more than you intend, there's no easy way back. If you're still seduced by the ease of a Replace All, consider creating a backup of your database file (Section 1.3.1) before going any further.

The key problem's that Access isn't bothered about tables that are too wide to fit on a printed page. It deals with them by splitting the printout into separate pages. If you have a large table and you print it out using the standard Access settings, you could easily end up with a printout that's four pages wide and three pages long. Assembling this jigsaw is not for the faint of heart. To get a better printout, it's absolutely crucial that you

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Data Integrity Basics
Input Masks
Validation Rules
Lookups

Even the best database designer has spent a sleepless night worrying about the errors that could be lurking in a database. Bad data's a notorious problem—it enters the database, lies dormant for months, and appears only when you discover you've mailed an invoice to customer "Blank Blank" or sold a bag of peanuts for –$4.99.

The best way to prevent these types of problems is to stop bad data from making it into your database in the first place. In other words, you need to set up validation rules that reject suspicious values as soon as someone types them in. Once bad data's entered your database, it's harder to spot than a blueberry in a swimming pool.

This chapter covers the essential set of Access data validation tools:

The basicsinclude duplicates, required fields, and default values.
Input masksformat ordinary text into patterns, like postal codes and phone numbers.
Validation ruleslay down strict laws for unruly fields.
Lookupslimit values to a list of preset choices.

All of Access's data validation features work via the Design view you learned about in Chapter 2. To put them in place, you choose a field and then tweak its properties. The only trick's knowing what properties are most useful. You've already seen some in Chapter 2, but the following sections fill in a few more details.

Remember, Access gives you three ways to switch to Design view. You can right-click the table tab title and then choose Design View from the menu, use the Home → View button on the ribbon, or use the tiny view buttons at the Access window's bottom-right corner. And if you're really impatient, then you don't even need to open your table first—just find it in the navigation pane, right-click it there, and then choose Design View.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

All of Access's data validation features work via the Design view you learned about in Chapter 2. To put them in place, you choose a field and then tweak its properties. The only trick's knowing what properties are most useful. You've already seen some in Chapter 2, but the following sections fill in a few more details.

Remember, Access gives you three ways to switch to Design view. You can right-click the table tab title and then choose Design View from the menu, use the Home → View button on the ribbon, or use the tiny view buttons at the Access window's bottom-right corner. And if you're really impatient, then you don't even need to open your table first—just find it in the navigation pane, right-click it there, and then choose Design View.

Every record needs a bare minimum of information to make sense. However, without your help, Access can't distinguish between critical information and optional details. For that reason, every field in a new table is optional, except for the primary-key field (which is usually the ID value). Try this out with the Dolls table from Chapter 1; you'll quickly discover that you can add records that have virtually no information in them.

You can easily remedy this problem. Just select the field that you want to make mandatory in Design view, and then set the Required field property to Yes (Figure 4-1).

Figure 4-1: The Required field property tells Access not to allow empty values (called nulls in tech-speak).

Access checks the Required field property whenever you add a new record or modify a field in an existing record. However, if your table already contains data, there's no guarantee that it follows the rules.

Imagine you fill the Dolls table with a few bobbleheads before you decide that every record requires a value for the Character field. You switch to Design view, choose the Character field, and then flip the Required field property to Yes. When you save the table (by switching back to Datasheet view or closing the table), Access gives you the option of verifying the bobblehead records that are already in the table (Figure 4-2). If you choose to perform the test and Access finds the problem, it gives you the option of reversing your changes (Figure 4-3).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

As you've already learned, databases prize consistency. If you have a field named Height, you better be sure every value in that field uses the same type of measurements; otherwise, your data's not worth its weight in sock lint. Similarly, if you have a PhoneNumber field, you better make sure every phone number has the same format. If some phone numbers are written with dashes, spaces, and parentheses (like (844) 547-1123), while others are a bit different (say 847-547-1123), and a few leave out the area code information altogether (547-1123), then you've got a small problem on your hands. Because of the lack of consistency, you'll have a hard time working with this information (say, searching for a specific phone number or sorting the phone numbers into different categories based on area code).

To help you manage values that have a fixed pattern—like phone numbers—you can use an input mask. Essentially, an input mask (or just mask for short) gives you a way to tell Access what pattern your data should use. Based on this pattern, Access changes the way values are entered and edited to make them easier to understand and less error-prone. Figure 4-8 shows how a mask lets Access format a series of characters as they're being typed into a field.

You can add a mask to any field that uses the Text data type. Masks give you several advantages over ordinary text:

Masks guide data entry. When empty, a masked edit control shows the place-holders where values need to go. A phone number mask shows the text (_ _ _) _ _ _-_ _ _ _ when it's empty, clearly indicating what type of information it needs.
Masks make data easier to understand. You can read many values more easily when they're presented a certain way. Most people can pick out the numbers in this formatted Social Security number (012-86-7180) faster than this unformatted one (012867180).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Input masks are a great tool, but they apply to only a few specific types of information—usually fixed-length text that has a single, unchanging pattern. To create a truly bulletproof table, you need to use more sophisticated restrictions, like making sure a number falls in a certain range, checking that a date hasn't yet occurred, or verifying that a text value starts with a certain letter. Validation rules can help you create all these restrictions.

A validation rule's premise is simple. You set up a restriction that tells Access which values to allow in a field and which ones are no good. Whenever someone adds a new record or edits a record, Access makes sure the data lives up to your validation rules. If it doesn't, then Access presents an error message and forces you to edit the offending data and try again.

Figure 4-13: To add your own mask, use the record scrolling buttons (at the bottom of the window) to scroll to the end. Or you can use this window to change a mask. For example, the prebuilt telephone mask doesn't require an area code. If that's a liberty you're not willing to take, then replace it with the more restrictive version (000) 000-0000.

Each field can have a single validation rule. The following set of steps show you how to set one up. You'll start out easy, with a validation rule that prevents a numeric field from accepting 0 or any negative number (and in the following sections you'll hone your rule-writing abilities so you can tackle other data types).

Here's how to add your validation rule:

In Design view, select the field to which you want to apply the rule.

All data types—except Memo, AutoNumber, and OLE Object—support validation. The validation rule in this example works with any numeric data type (like Number or Currency).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In a database, minor variations can add up to big trouble. Suppose you're running International Cinnamon, a multinational cinnamon bun bakery with hundreds of orders a day. In your Orders table, you have entries like this:

	Quantity  Product
	10        Frosted Cinnamon Buns
	24        Cinnamon Buns with Icing
	16        Buns, Cinnamon (Frosted)
	120       FCBs
	…

(Other fields, like the ID column and the information about the client making the order, are left out of this example.)

All the orders shown here amount to the same thing: different quantities of tasty cinnamon and icing confections. But the text in the Product column's slightly different. This difference doesn't pose a problem for ordinary human beings (for example, you'll have no trouble filling these orders), but it does create a small disaster if you want to analyze your sales performance later. Since Access has no way to tell that a Frosted Cinnamon Bun and an FCB are the same thing, it treats them differently. If you try to total up the top-selling products or look at long-range cinnamon sales trends, then you're out of luck.

This example emphasizes a point that you've seen before. Namely, databases are strict, no-nonsense programs that don't tolerate minor discrepancies. In order for your databases to be useful, you need to make sure you store top-notch information in them.

Lookups are one more tool to help standardize your data. Essentially, a lookup lets you fill a value in a field by choosing from a ready-made list of choices. Used properly, this tool solves the problem in the Orders table—you simply need a lookup that includes all the products you sell. That way, instead of typing the product name in by hand, you can choose Frosted Cinnamon Buns from the list. Not only do you save some time, but you also avoid variants like FCBs, thereby ensuring that the orders list is consistent.

Access has two basic types of lookup lists: lists with a set of fixed values that you specify, and lists that are drawn from a linked table. In the next section, you'll learn how to create the first type. Then, in Chapter 5, you'll graduate to the second.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Relationship Basics
Using a Relationship
More Exotic Relationships
Relationship Practice

The tables you've seen so far lead lonely, independent lives. You don't find this isolation with real-world databases. Real databases have their tables linked together in a web of relationships.

Suppose you set out to build a database that can manage the sales of your custom beadwork shop. The first ingredient's simple enough—a Products table that lists your merchandise—but before long you'll need to pull together a lot more information. The wares in your Products table are sold in your Orders table. The goods in your Orders table are mailed out and recorded in a Shipments table. The people in your Customers table are billed in your Invoices table. All these tables—Products, Orders, Shipments, Customers, and Invoices—have bits of related information. As a result, if you want to find out the answer to a common question (like, "How much does Jane Malone owe?" or "How many beaded wigs did we sell last week?"), you'll need to consult several tables.

Based on what you've learned so far, you already know enough to nail down the design for a database like this one. But relationships introduce the possibility of inconsistent information. And once a discrepancy creeps in, you'll never trust your database the same way again.

In this chapter, you'll learn how to explicitly define the relationships between tables. This process lets you prevent common errors, like data in different tables that doesn't sync up. It also gives you a powerful tool for browsing through related information in several tables.

One of any database's key goals is to break information down into distinct, manageable pieces. In a well-designed database, you'll end up with many tables. Although each table records something different, you'll often need to travel from one table to another to get all the information you want.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

One of any database's key goals is to break information down into distinct, manageable pieces. In a well-designed database, you'll end up with many tables. Although each table records something different, you'll often need to travel from one table to another to get all the information you want.

To better understand relationships (of the non-romantic kind, anyway), consider an example. The following section demonstrates two ways to add information to the bobblehead database: one that risks redundant data, and one that avoids the problem by properly using a relationship.

Think back to the Dolls table you created in Chapter 1 to store a list of bobblehead dolls. One of the Dolls table's pieces of information is the Manufacturer field, which lists the name of the company that created each doll. Although this seems like a simple-enough detail, it turns out that to properly assess the value of a bobblehead, you need to know a fair bit more about the manufacturing process. You may want to know things like where the manufacturing company's located, how long it's been in business, and if it's had to fight off lawsuits from angry customers.

If you're feeling lazy, you could add all this information to the Dolls table, like so (the grayed-out columns are the new ones):

ID	Character	Manufacturer	Manufacturer Location	Manufacturer Opening Year	Manufacturer Lawsuits

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The relationship between Dolls and Manufacturers is implicit, which is a fancy way of saying that you know the relationship exists, but Access doesn't. Database pros aren't satisfied with this arrangement. Instead, they almost always define their relationships explicitly. When you create an explicit relationship, you clearly tell Access how two tables are related. Access then stores the information about that relationship in the database file.

You have good reasons to bring your relationships out into the open. Once Access knows about a relationship, it can enforce better error checking. It can also provide handy features for browsing related data and editing linked fields. You'll see all these techniques in the following sections. But first, you need to learn how to define a relationship.

You can try out the following steps with the Bobblehead.accdb file, which is included with the online examples for this chapter. It contains the Dolls and Manufacturers tables, in their original form (with no relationships defined). The BobbleheadRelationships.accdb database file shows the final product: two tables with the right relationship.

Here's what you need to do to set up a relationship:

Every relationship links two fields, each in a different table. Your first step is to identify the field you need to use in the parent table.

In a well-designed database, you use the primary-key field (Section 2.4) in the parent table. For example, in the Manufacturers table, you use the ID column, which uniquely identifies each manufacturer.
Open the child table in Design view. (The quickest way is to right-click it in the navigation pane, and then choose Design View.)

In this example, the child table is Dolls.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

As you learned in Section 5.1.4, a one-to-many (a.k.a. parent-child) relationship that links a single record in one table to zero, one, or more records in another table is the most common relationship. A single manufacturer could be linked to one bobble-head, several bobbleheads, or no bobbleheads at all.

Along with one-to-many relationships, there are two subtly different types of relationships: one-to-one relationships and many-to-many relationships. You'll learn about both in the following sections.

A one-to-one relationship links one record in a table to zero or one record in another table. People sometimes use one-to-one relationships to break down a table with lots of fields into two (or more) smaller tables.

A Products table may include detailed information that describes the product and its price, and additional information that describes how it's built. This information's important only to the people in the e

Table of Contents