Section 2.2. Design View | Access 2007[c] The Missing Manual

2.2. Design View

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

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

Tip: 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 doesnt include the very important ID column, so you need to add one, as described in Section 2.4.1.

While Datasheet view shows the content in your table, Design view shows only its structure (see Figure 2-3).

You can use Design view to add, rearrange, and remove fields, but you can't use it to add new records. In the Dolls table, you can use Design view to add a Quantity field to keep track of doll duplicates. However, you can't add your newly purchased Bono bobblehead without switching back to the Datasheet view. Design view isn't intended for data entry.

At first, Design view seems quite intimidating. To simplify what you're looking at, you should start by closing the Property Sheet box on the window's right side. (The Property Sheet lets you set a few highly technical table settings, none of which you need to consider right now.) To banish it, choose Table Tools Design Property Sheet. If you want to bring it back later, then just repeat the same command.

2.2.1. Organizing and Describing Your Fields

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:

Add a new field to the end of your table . Scroll to the last row of the field list, and then type in a new field name. This action's equivalent to adding a new field in Datasheet view.
Add a new field between existing fields . Move to the field that's just under the place where you want to add the new field. Right-click the field, and then choose Insert Rows. Then, type a field name in the new, blank row.
Move a field . Drag the gray square immediately to the left of the field you want to move, to the new position.

Note: Remember, fields' order isn't all that important, because you can change the order in which you view the fields in Datasheet view. However, most people find it's easier to design a table if you organize the fields from the very start.

Delete a field . Right-click the gray square immediately to the left of the field you want to remove, and then choose Delete Rows. Keep in mind that when you remove a field, you also wipe out any data that was stored in that field. This action isn't reversible, so Access prompts you to confirm it's really what you want to do.
Add a description for a field . Type in a sentence or two in the Description column next to the appropriate field. (You might use "The celebrity or fictional character that this bobblehead resembles" as the description for the Character field in the Dolls table, as shown in Figure 2-4.)

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.

2.2.2. How Updates Work in Design View

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.