Access 2007: The Missing Manual

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

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 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

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. (Chapter 16 has the full story on Visual Basic.)

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.

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).

If task #1 (creating the database) is done well, task #2 (using the database) can be extremely easy. In fact, if the database is well designed, people who have little under-standing of Access can still use it to enter, update, and look up information. Amazingly, they don't even need to know they're running Access at all!

You'll learn more about sharing Access with groups of people in Chapter 18.

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

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.

This automatic save process takes place behind the scenes, and you probably won't notice anything. But don't be alarmed when you exit Access and it doesn't prompt you to save changes, as all changes are saved the moment you make them.

The automatic save feature can pose a problem if you make a change mistakenly. If you're fast enough, you can use the Undo feature to reverse your last change (Figure 1-12). However, the Undo feature reverses only your most recent edit, so it's no help if you edit a series of records and then discover the problem. It also doesn't help if you close your table and then reopen it.

Figure 1-12: The Undo command appears in the Quick Access toolbar at the top left of the Access window (circled), so it's always available.

For these reasons, it's a good idea to make frequent database backups. To make a database backup, you simply need to copy your database file to another folder, or make a copy with another name (like Bobblehead_Backup1.accdb). You can perform these tasks with Windows Explorer, but Access gives you an even easier option. Just choose Office button → Manage → Back Up Database, and Access creates a copy of your database for you, in the location you choose (Figure 1-13).

It's still up to you to remember to back up your database. Access doesn't include an automatic backup feature, but you can use another tool to periodically copy your database file. One example is the Windows Task Scheduler that's included with most versions of Windows. (You can read a quick no-nonsense Task Scheduler tutorial at

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.

Figure 1-19: Unhappy with the space consumed by the navigation pane? Click the Open/Close button 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.

The navigation pane shows the objects (Section 1.2) 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. (You'll learn how to customize the navigation pane in Chapter 14.)

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

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.

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.

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.

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."

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.

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.6. 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. You'll use those to analyze your data in a pivot table, as described in Chapter 9.)

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.

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.

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-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.

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.)

In an Employees table, the Social Security number could serve as the primary key. This method works well, because when you insert a new record, Access can check for duplicates by breezing through the list of Social Security numbers, which is much faster than scanning through the entire table.

Unfortunately, that just won't do—after all, many are the address books that have two Sean Smiths.

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.

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.

Here are some tips:

Keep it short and simple. The field name should be as short as possible. Long names are tiring to type, more prone to error, and can be harder to cram into forms and reports.
CapitalizeLikeThis. It's not a set-in-stone rule, but most Access fans capitalize the first letter of every word (known as CamelCase), and then cram them all together to make a field name. Examples include UnitsInStock and DateOfExpiration.
Avoid spaces. Spaces are allowed in Access field names, but they can cause problems. In SQL (the database language you'll use to search for data), spaces aren't kosher. That means you'll be forced to use square brackets when referring to field name that includes spaces (like [Number Of Guests]), which gets annoying fast. If you really must have spaces, then consider using underscores instead.

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 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 Four). 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.

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.

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. (Refer 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).

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 essentials of editing, including how to add, delete, and modify records. However, Access has a few finer points that you haven't seen yet. In the following sections, you'll tackle two great conveniences in Access—the spell checker and AutoCorrect—and you'll learn a simple way to insert special characters in your fields.

The spell checking functionality in Access is almost exactly the same as in other Office applications like Word—it uses the same dictionary, catches the same sorts of errors, and gives you the option to ignore things it doesn't recognize, or add them to the dictionary.

The difference is that when you perform a spell check with Access, it examines only the content in text and memo fields. Numbers, dates, and everything else get a pass. Of course, many of your fields are likely to contain text you don't want to spell check—like names, places, or product titles. You have two ways to handle this. You can perform a spell check on a single field, thereby ignoring everything else. Or, you can start a datasheet-wide spell check, but choose to ignore certain fields on the fly.

Here's how it works:

Move to the field where you want to start the spell check.

If you want to check the entire datasheet from start to finish, then move to the first field in the first record.

If you want to check part of the datasheet, then move to the location where you want to start checking. Keep in mind that when Access reaches the end of your datasheet, it loops around and starts again at the top, continuing until it's reviewed every field in every record. (Of course, you can cancel a spell check at any time.)

If you want to check just a single field, then select that field before continuing by clicking the column header.

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 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 preview your table before you print it, as described in the next section.

The print preview feature in Access gives you the chance to tweak your margins, paper orientation, and so on, before you send your table to the printer. This way, you can make sure the final printout's genuinely usable. To preview a table, open it (or select it in the navigation pane), and then choose Office button →Print →Print Preview.

The print preview shows a picture of what your data will look like once it's committed to paper. Unlike the datasheet view, the print preview paginates your data (Figure 3-20). You see exactly what fits on each page and how many pages your printout requires (and what content shows up on each page).

Figure 3-20: This table's too wide to fit on one sheet of paper, so some of the columns are relocated to a second page.

If you decide you're happy with what you see, then you can fire off your printout by choosing Print Preview → Print → Print from the ribbon. This opens the familiar Windows Print dialog box, where you can pick a printer and seal the deal.

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

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 basics include duplicates, required fields, and default values.
Input masks format ordinary text into patterns, like postal codes and phone numbers.
Validation rules lay down strict laws for unruly fields.
Lookups limit 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.

Figure 4-8: Top: Here's a PhoneNumber field with a mask that's ready to go. So far, the person entering the record hasn't typed anything. The PhoneNumber field automatically starts out with this placeholder text.
Bottom: The mask formats the numbers as you type. If you type 1234567890 into this phone number mask, then you see the text (123) 456-7890. Behind the scenes, the databases stores 1234567890, but the information's presented in the datasheet using a nicely formatted package. That package is the mask.

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

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 by drawing on the full power of the SQL language.

You'll get a more thorough introduction to SQL starting in Chapter 6. Fortunately, you need only a dash of SQL to write a validation rule. The key ingredient's a validation expression, and you'll see several practical examples of expressions that you can drop straight into your tables.

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.

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

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.

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.

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 shaded columns are the new ones):

ID	Character	Manufacturer	Manufacturer-Location	Manufacturer-OpeningYear	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 relation-ship 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·

Table of Contents