Design view allows you to rearrange the order of your fields, add new ones, rename the existing ones, and more. You can also do all these things in Datasheet view, but Access gurus usually find it’s easier to work in Design view, because you won’t be distracted by the data in the table.

Figure 2-3. Design view lists the fields in your table, putting each in a separate row. Fields here are listed from top to bottom, but they appear in datasheet view ordered from left to right. Next to each field is its data type and an optional description. Underneath the field list, the Field Properties section shows more information about the currently selected field. Here, the navigation pane has been collapsed (Section 1.4) to provide extra space.

Here are a few simple ways you can change the structure of your table in Design view:

Figure 2-4. Descriptions can help you remember what’s what if you need to modify a table later on. Descriptions are a great idea if more than one person maintains the same database, in which case you need to make sure your fields are as clear as possible. Descriptions also appear in the status bar when you’re entering information in a table (see Figure 2-5).

Figure 2-5. The status bar text tells you what goes in this column, based on the field description. Sadly, this feature isn’t as useful as it seems, because most people never think to look down in the status bar.

Access doesn’t immediately apply the changes you make in Design view. Instead, it waits until you close the table or switch back to Datasheet view. At that point, Access asks whether you want to save the table. (The answer, of course, is Yes.)

Sometimes, you may apply a change that causes a bit of a problem. You could try to change the data type of a field so that it stores numbers instead of text. (The box in Section 2.3.1, “Changing the Data Type Can Lose Information,” discusses this problem in more detail.) In this situation, you won’t discover the problem until you close the table or switch back to the Datasheet view, which might be a little later than you expect.

If you’ve made a potentially problematic change and you just can’t take the suspense, you’re better off applying your update immediately, so you can see if there’s a problem before you go any further. To do so, click the Quick Access toolbar’s Save button (it’s the diskette icon in the Access window’s top-left corner), or just use the keyboard shortcut Ctrl+S. Access applies your change, and then saves the table. If it runs into a problem, Access tells you about it (and lets you choose how you want to fix it) before you do anything else with the table.

Text is the all-purpose data type. It accepts any combination of letters, numbers, and other characters. So you can use a text field for a word or two (like “Mary Poppins”), a sentence (“The candidate is an English nanny given to flights of song.”), or anything else (“@#$d sf_&!”).

Figure 2-8. Don’t say you weren’t warned. Here, Access lets you know (in its own slightly obscure way) that it can’t make the change you want—modifying the data type of field from Text to Date—without throwing out the values in four records. The best course of action is to click No to cancel the change and then take a closer look at your table in Datasheet view to track down the problematic values.

Sometimes it seems that the Text data type’s just too freewheeling. Fortunately, you can apply some stricter rules that deny certain characters or force text values to match a preset pattern. For example, Access usually treats phone numbers like text, because they represent a series of characters like 123-4444 (not the single number 1,234,444). However, you don’t want to let people put letters in a phone number, because they obviously don’t belong. To put this restriction into action, you can use input masks (Section 4.2) and validation (Section 4.3), two features discussed in Chapter 4.

Microsoft designed the Memo data type to store large quantities of text. If you want to place a chapter from a book, an entire newspaper article, or just several paragraphs into a field, you need the Memo data type. The name’s a little odd—although a memo field could certainly store the information from an inter-office memorandum, it’s just as useful any time you have large blocks of text.

When creating a memo field, you don’t need to supply a maximum length, because Access stores the data in a memo field differently from other data types. Essentially, it stuffs memo data into a separate section, so it can keep the rest of the record as compact and efficient as possible, but accommodate large amounts of text.

Figure 2-9. To set a maximum length, choose your field, and then click the Field Size box in the Field Properties list (shown here). (All the field properties you need in this chapter are on the General tab.) When you click a field property box, that field property’s description appears on the right.

A memo field tops out at 65,536 characters. To put it in perspective, that’s about the same size as this chapter. If you need more space, then add more than one memo field.

If you need to edit a large amount of text while you’re working on the datasheet, then you can use the Zoom box (Figure 2-10). Just move to the field you want to edit, and then press Shift+F2.

Formatted text

Like a text field, the memo field stores unformatted text. However, you can also store rich text in a memo field—text that has different fonts, colors, text alignment, and so on. To do so, set the Text Format setting to Rich Text (rather than Plain Text).

To format part of your text, you simply need to select it and then choose a formatting option from the ribbon’s Home → Font Home → Rich Text sections. However, most of the time you won’t take this approach, because it’s difficult to edit large amounts of text in the datasheet’s narrow columns. Instead, use Shift+F2 to open a Zoom box, and then use the minibar (Figure 2-11).

Figure 2-10. If you have lengthy text in a field, it’s hard to see it all at once without a lot of scrolling. By opening a Zoom box (Shift+F2), you can see more content and edit it more easily. You’ll need to click OK (to accept your edits) or Cancel (to abandon them) to get back to the datasheet.

UP TO SPEED: Maximum Length Guidelines

Here are some recommended maximum lengths:

• First names and last names. 25 characters handles a first name, while 50 characters each plays it safe for a long, hyphenated last name.

• Middle initial. One character. (Sometimes common sense is right.)

• Email address. Go with 50 characters. Email addresses closer to 100 characters have turned up in the wild (Google “world’s longest email address” for more), but they’re unlikely to reach your database.

• Cities, states, countries, and other places. Although a Maori name for a hill in New Zealand tops out at over 80 characters (see http://en.wikipedia.org/wiki/Longest_word_in_English), 50 is enough for most practical purposes.

• Street address. A street address consists of a number, followed by a space, then the street name, another space, and the street abbreviation (like Rd or St). Fifty characters handles it, as long as you put postal codes, cities, and other postal details in other fields.

• Phone numbers, postal codes, credit card numbers, and other fixed-length text. Count the number characters and ignore the placeholders, and set the maximum to match. If you want to store the phone number (123) 456-7890, make the field 10 characters long. You can then store the phone number as 1234567890, but use an input mask (Section 4.2) to add the parentheses, spaces, and dash when you display it. This approach is better because it avoids the headaches that result from entering similar phone numbers in different ways.

• Description or comments. 255 characters fits three or four average sentences of information. If you need more, consider the memo data type instead (Section 2.3.1.1).

Figure 2-11. To show the minibar (sadly, of the non-alcoholic variety), select some text, and then hover over it with the mouse. The minibar—a compact toolbar with formatting options—gradually fades into view. The minibar’s sometimes a little finicky, and you may need to reselect the text more than once to get it to appear.

Tip

There’s another, even easier way to get formatted text into a memo field. Create the text in a word processing program (like Word), format it there, and then copy and paste it into the field. All the formatting comes with it.

As neat as this feature may seem at first glance, it’s rarely worth the trouble. Database purists believe that tables should store raw information and let other programs (or fancy forms) decide how to format it. The problem is that once you’ve created your formatted text, it can be quite a chore to maintain it. Just imagine having to change the font in 30,000 different records.

If you really do want to store formatted content, then consider linking your database to a separate document, like a Word file. In Access, you can do this in two ways:

• Create a field that points to the file. For example, c:myfileBonoBobblehead-Description.docx). For this trick, use the Text or Hyperlink data type (Section 2.3.7).

• Embed the file inside your database. This way, it’s impossible to lose the file (or end up pointing to the wrong location). However, you’ll need to pull the file out every time you want to update it. To do this, you need to use the Attachment data type (Section 2.3.8).

The Number data type includes a wide variety of differently sized numbers. You can choose to allow decimal numbers, and you can use negative values (just precede the value with a minus sign). You should use the Number data type for every type of numeric information you have—except currency amounts, in which case the Currency data type (Section 2.3.4) is a better match.

When you use numeric fields, you don’t include information about the units you’re using. You may have a field that represents a Weight in pounds, a Height in Meters, or an Age in Years. However, these fields contain only a number. It’s up to you to know what that number signifies. If you think other people may be confused, consider explaining the units in the description (Section 2.2.1), or incorporate it into the field name (like HeightInMeters).

Currency’s a slight variation on the Number data type that’s tailored for financial calculations. Unlike the Number data type, here you can’t choose a Field Size for the Currency data type—Access has a one-size-fits-all policy that requires eight bytes of storage space.

You can adjust the number of decimal places Access shows for currency values on the datasheet by setting the Decimal Places field property. Usually, it’s set to 2.

The formatting that Access uses to display currency values is determined by the Regional and Language Options settings on your computer (Section 2.3.5). However, these settings might produce results you don’t want—for example, say you run an artisanal cereal business in Denmark that sells all its products overseas in U.S. dollars (not kroner). You can control exactly how currency values are formatted by setting the Format field property, which gives you the following options:

There’s a simple recipe for cooking up format strings with a custom currency symbol. Start by adding the character for the currency symbol (type in whatever you want) and then add #,###.## which is Access code for “give me a number with thousands separators and two decimal places.”

For example, the Danish cereal company could use a format string like this to show the U.S. currency symbol:

	$#,###.##

Whereas a U.S. company that needs to display a Danish currency field (which formats prices like kr 342.99) would use this:

	kr #,###.##

Access uses the Date/Time data type to store a single instant in time, complete with the year, month, day, and time down to the second. Behind the scenes, Access stores dates as numbers, which lets you use them in calculations.

Although Access always uses the same amount of space to store date information in a field, you can hide some components of it. You can choose to display just a date (and ignore any time information) or just the time (and ignore any date information). To do this, you simply need to set the Format field property. Table 2-3 shows your options.

The format affects only how the date information’s displayed—it doesn’t change how you type it in. Access is intelligent enough to interpret dates correctly when you type any of the following:

To add date and time information, just follow the date with the time, as in 23-Feb-08 5:06 PM. Make sure to include the AM/PM designation at the end, or use a 24hour clock.

If it’s too much trouble to type in a date, then consider using the calendar smart tag instead. The smart tag is an icon that appears next to the field whenever you move to it, as shown in Figure 2-12.

Figure 2-12. Access automatically pops up the calendar smart tag for all date fields. Click the calendar icon to pop up a mini calendar where you can browse to the date you want. However, you can’t use the calendar to enter time information.

Figure 2-13. The Regional and Language Options dialog box lets you customize how dates appear on your computer. Use the drop-down lists to specify the date separator; order of month, day, and year components in a date; and how Access should interpret two-digit years. You can mix and match these settings freely, although you could wind up with a computer that’s completely counterintuitive to other people.

	yyyy-mm-dd

	2008-01-01

A Yes/No field is a small miracle of efficiency. It’s the leanest of Access data types, because it allows only two possible values: Yes or No.

When using a Yes/No field, imagine that your field poses a yes or no question by adding an imaginary question mark at the end of your field name. You could use a field named InStock to keep track of whether or not a product’s in stock. In this case, the yes or no question is “in stock?” Other examples include Shipped (in a list of orders), Male (to separate the boys from the girls), and Republican (assuming you’re willing to distinguish between only two political orientations).

Although every Yes/No field is essentially the same, you can choose to format it slightly differently, replacing the words “Yes” and “No” with On/Off or True/False. You’ll find these three options in the Format menu. However, it doesn’t make much difference because on the datasheet, Yes/No fields are displayed with a checkbox, as shown in Figure 2-14.

Figure 2-14. In this example, ForSale is a Yes/No field. A checked checkbox represents Yes (or True or On). An unchecked checkbox represents No (or False or Off).

The Hyperlink data type comes in handy if you want to create a clickable link to a Web page, file, or email address. You can mix and match any combination of the three in the same table.

Access handles hyperlinks a little differently in the Datasheet view. When you type text into a hyperlink field, it’s colored blue and underlined. And when you click the link, Access pops it open in your browser (Figure 2-15).

Figure 2-15. Click this hyperlink, and you’ll head straight to the welcoming arms of Office Online.

One hyperlink field feature isn’t immediately obvious. Hyperlink fields actually store more than one piece of information. Every hyperlink includes these three components:

When you type a link into the datasheet, all three of these are set to the same value—whatever you’ve just typed in. In other words, when you type http://www.FantasyPharmacologists.com, the text you see, the URL link, and the tooltip are all set to hold the same content, which is the URL http://www.FantasyPharmacologists.com.

Most of the time, this approach is good, because it lets you quickly size up a link. However, you aren’t limited to this strategy. If you want to set these three components to have different values, move to the value, and then hit Ctrl+K to pop up the Edit Hyperlink window (see Figure 2-16). Or right-click it, and then choose Hyperlink → Edit Hyperlink.

The Attachment data type’s new in Access 2007. It lets you add files to your database record in much the same way that you tack on attachments to your email messages. Access stores the files you add to an attachment field as part of your table, embedded inside your database file.

The Attachment data type’s a good choice if you need to insert a picture for a record, a short sound file, or even a document from another Office application, like Word or Excel. You could create a People table with a picture of each person in your contact list, or a product catalog with pictures of the wares you’re selling. In these cases, attachments have an obvious benefit—because they’re stored inside your database file, you’ll never lose track of them.

Figure 2-16. Using the Edit Hyperlink window, you can change the text that appears in the cell (at the top of the window) and the page that Access opens when you click it (at the bottom). You can also create links that use email addresses (in which case Access opens the email program that’s configured on your computer) or links to file paths (use the folder browsing area to pick the file you want).

However, attachments aren’t as graceful with large files, or files you need to modify frequently. If you place a frequently modified document into an Access database, it isn’t available on your hard drive for quick editing, printing, and searching. Instead, you’ll need to fire up Access, and then find the corresponding record before you can open your document. If you want to make changes, then you’ll also need to keep Access open so it can take the revised file and insert it back into the database.

When you use the Attachment data type, make sure you set the Caption field property, which determines the text that appears in the column header for that field. (Often, you’ll use the field name as the caption.) If you don’t set a caption, the column header shows a paper clip but no text.

You’ll recognize an attachment field in the datasheet because it has a paper clip icon next to it (Figure 2-17).

To attach a file or review the list of attached files, double-click the paper clip icon. You’ll see the Attachments dialog box (see Figure 2-18).

Here’s what you can do in the Attachments window:

Unfortunately, the Attachment data type doesn’t give you a lot of control. Here are some of its limitations:

Figure 2-19. Top: In this example, the file “The Story of the Count. doc” is still open. If you continue, then any changes you make (or any changes you’ve made so far and haven’t saved) aren’t reflected in the database. Bottom: If Access notices you’ve saved your file since you first opened it, then Access also asks if you want to update the database with the last saved version. (To avoid such headaches, attach only files that you don’t plan to edit.)

An AutoNumber is a special sort of data type. Unlike all the other data types you’ve seen, you can’t fill in the value for an AutoNumber field. Instead, Access does it automatically whenever you insert a new record. Access makes sure that the AutoNumber value is unique—in other words, it never gives two records the same AutoNumber value.

Ordinarily, the AutoNumber field looks like a sequence of numbers—Access tends to give the first record an AutoNumber value of 1, the second an AutoNumber of 2, and so on. However, the truth isn’t so straightforward. Sometimes, Access skips a number. This skipping could happen when several people are using a database at once, or if you start adding a new record, and then cancel your action by pressing the Esc key. You may also delete an existing record, in which case Access never reuses that AutoNumber value. As a result, if you insert a new record and you see it’s assigned an AutoNumber value of 401, then you can’t safely assume that there are already 400 records in the table. The actual number’s probably less.

Truthfully, an AutoNumber value doesn’t represent anything, and you probably won’t spend much time looking at it. The AutoNumber field’s sole purpose is to make sure you have a unique way to point to each record in your table. Usually, your AutoNumber field’s also the primary key for your table, as explained in Section 2.4.

Using replication IDs

Imagine you’re working at a company with several regional sales offices, each with its own database for tracking customers. If you use an ordinary AutoNumber field, then you’ll end up with several customers with the same ID, but at different offices. If you ever want to compare data, you’ll quickly become confused. And you can’t combine all the data into one database for further analysis later on.

Access gives you another choice—a replication ID. A replication ID’s a strange creation—it’s an extremely large number (16 bytes in all) that’s represented as a string of numbers and letters that looks like this:

	38A94E7B-2F95-4E7D-8AF1-DB5B35F9700C

This ID’s obviously more cumbersome than an ordinary integer. After all, it’s much easier to thank someone for submitting Order 4657 than Order 38A94E7B-2F95-4E7D-8AF1-DB5B35F9700C. In other words, if you use the AutoNumber value for tracking or bookkeeping, then the replication ID’s a bad idea.

However, the replication ID solves the problem described earlier, where multiple copies of the same database are being used in different places. That’s because replication IDs are guaranteed to be statistically unique. In other words, there are so many possible replication IDs that it’s absurdly unlikely that you’ll ever generate the same replication ID twice. So even if you have dozens of separate copies of your database, and they’re all managing hundreds of customers, you can rest assured that each customer has a unique customer ID. Even better, you can periodically fuse the separate tables together into one master database. (This process is called replication, and it’s the origin of the term replication ID. You’ll learn more about transferring data from one database to another in Chapter 19.)

Note

A replication ID is also called a GUID (short for "globally unique identifier”). In theory, the chance of two GUIDs being identical are one in 2¹²⁸, which is small enough that you could set one billion people to work, ask them to create one billion GUIDs a year, and still be duplicate-free for the next decade or two. In practice, the real limitation’s how good the random number generator is in Access.

Figure 2-20 shows a table that uses replication IDs.

Figure 2-20. This figure shows 4 records in the FictionalCharacters table, each with a statistically unique AutoNumber value.

If your database doesn’t have an ID field (perhaps because you created it using the Create → Tables → Table Design command), it’s up to you to create one and set the primary key. Here’s how to do it:

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:

You should also give careful thought to naming your tables. Once again, consistency is king. For example, database nerds spend hours arguing about whether or not to pluralize table names (like Customers instead of Customer). Either way’s fine, but try to keep all your tables in line.

Be careful that you don’t include too much information in a single field. You want to have each field store a single piece of information. Rather than have a single Name field in a table of contacts, it makes more sense to have a FirstName and a LastName field.

There are many reasons for breaking down information into separate fields. First of all, it stops some types of errors. With a Name field, the name could be entered in several different ways (like “Last, First” or “First Last”). Splitting the name avoids these issues, which can create headaches when you try to use the data in some sort of automated task (like a mail merge). But more importantly, you can more easily work with data that’s broken down into small pieces. Once the Name field’s split into FirstName and LastName, you can perform sorts or searches on just one of these two pieces of information, which you couldn’t otherwise do. Similarly, you should split address information into columns like Street, City, State, and Country—that way, you can far more easily find out who lives in Nantucket.

Figure 2-21 (top) shows an example of proper separation. Figure 2-21 (bottom) shows a dangerous mistake—an attempt to store more than one piece of information in a single field.

Figure 2-21. This example shows the right way to subdivide information in the Contacts table (top), and the wrong way (bottom). Notice that it’s technically still possible to break the information down further—the street address information could theoretically be split into StreetNumber, StreetName, and StreetType. However, that added bit of complexity doesn’t add anything, so database gurus rarely go to the extra trouble.

Often, you’ll use the same table in many different tasks. You may use the Dolls table to check for duplicates (and avoid purchasing the same bobblehead twice), to identify the oldest parts of your collection, and to determine the total amount of money you’ve spent in a given year (for tax purposes). Each of these tasks needs a slightly different combination of information. When you’re calculating the total money spent, you aren’t interested in the Character field that identifies the doll. When checking for a duplicate, you don’t need the DateAcquired or Purchase-Price information.

Even though you don’t always need all these fields, it’s fairly obvious that it makes sense to put them all in the same table. However, when you create more detailed tables, you may not be as certain. It’s not difficult to imagine a version of the Dolls table that has 30 or 40 fields of information. You may use some of these fields only occasionally. However, you should still include them all in the same table. All you’ll see in this book, you can easily filter out the information you don’t need from the datasheet, as well as in your forms and printed reports.

As you start to fill a table with fields, it’s sometimes tempting to include information that doesn’t really belong. This inclusion causes no end of headaches, and it’s a surprisingly easy trap to fall into. Figure 2-22 shows this problem in action with a table that tries to do too much.

Figure 2-22. This table lists the available pets at an exotic animal breeder’s. It also lists some helpful information about the life expectancy, temperament, and meal requirements of each type of animal. Initially, this design seems fairly reasonable. However, a problem appears when you have several of the same type of animals (in this case, three elephants). Now the elephant-specific details are repeated three separate times.

Duplicate data like that shown in Figure 2-22 is inefficient. You can easily imagine a table with hundreds of similar records, needlessly wasting space repeating the same values over and over again. However, this concern’s minor compared to the effort of updating that information, and the possibility of inconsistency. What happens if you want to update the life expectancy information for every elephant based on new studies? Based on the current design of the table, you need to change each record that has the same information. Even worse, it’s all too easy to change some records but leave others untouched. The overall result’s inconsistent data—information in more than one spot that doesn’t agree—which makes it impossible to figure out the correct information.

This problem occurs because the information in the Pets table doesn’t all belong. To understand why, you need to delve a little deeper into database analysis.

As a rule, every table in a database stores a single thing. In the Pets table, that thing is pets. Every field in a table is a piece of information about that thing.

In the Pets table, fields like Name, Animal, and Weight all make sense. They describe the pet in question. But the LifeSpan, Temperament, and Diet fields aren’t quite right. They don’t describe the individual pet. Instead, they’re just standards for that species. In other words, these fields aren’t based on the pet (as they should be)—they’re based on the animal type. The only way to solve this problem is to create two tables: Pets and AnimalTypes (Figure 2-23).

Figure 2-23. Now the animal-specific information is maintained in one place, with no duplicates. It takes a little more work to get all the pet information you need—for example, to find out the life expectancy for Beatrice, you need to check out the Elephant record in the AnimalTypes table—but the overall design’s more logical.

It takes experience to spot fields that don’t belong. And in some cases, breaking a table down into more and more sub-tables isn’t worth the trouble. You could theoretically separate the address information (contained in fields like Street, City, Country, and PostalCode) from a Customers table, and then place it into a separate Addresses table. However, it’s relatively uncommon for two customers to share the same address, so this extra work isn’t likely to pay off. You’ll consider how to define formal relationships between tables like Pets and AnimalTypes in Chapter 5.

Another type of data that just doesn’t belong is redundant information—information that’s already available elsewhere in the database, or even in the same table, sometimes in a slightly different form. As with duplicated data, this redundancy can cause inconsistencies.

Calculated data’s the most common type of redundant information. An AverageOrderCost field in a Customers table is an example. The problem here is that you can determine the price of an average order by searching through all the records in the Orders table for that customer, and averaging them. By adding an AverageOrderCost field, you introduce the possibility that this field may be incorrect (it may not match the actual order records). You also complicate life, because every time a customer places an order, you need to recalculate the average, and then update the customer record.

Here are some more examples of redundant information:

As you learned earlier, Access automatically creates an ID field when you create a table in Datasheet view and sets it to be the primary key for the table. But even now that you’ve graduated to Design view, you should still add an ID field to all your tables. Make sure it uses the AutoNumber data type so Access fills in the numbers automatically, and set it to be the primary key.

In some cases, your table may include a unique field that you can use as a primary key. Resist the temptation. You’ll always buy yourself more flexibility by adding an ID field. You never need to change an ID field. Other information, even names and social insurance numbers, may change. And if you’re using table relationships, Access copies the primary key into other tables. If a primary key changes, you’ll need to track down the value in several different places.