Creating Fields

Creating fields in tables is among the most important Access tasks. In this section, I discuss naming fields; selecting a data type; if appropriate, designating the field as a primary key; and providing a description.

Naming Fields

I discussed several general rules for naming fields in Chapter 3. Field names should fully describe the field. Like names of other Access objects, a field name cannot include more than 64 characters. Access allows you to include spaces in field names, but because of possible naming conflicts, field names should be spelled solidly: PurchasePrice, SerialNumber, PostalCode, and so on. If you must include a space to avoid confusion, use an underscore instead; for example, in a field of IDs for VIPs, use VIP_ID.

NOTE

Field names cannot include certain symbols, such as periods and exclamation marks. Microsoft also recommends that certain "reserved words" (such as Description and Report) not be used for field names. A list of these reserved words and symbols is available online at http://support.microsoft.com/kb/286335.

Spelling field names solidly might seem like another instance of simply making life more difficult for yourself. But this rule is usually adhered to. You can easily include a caption (discussed later) so that the public face of the field (the field as it appears in datasheet column headings, in form and report labels, and so on) is, say, Last Name (with a space) rather than LastName. Actually, after a while, in your own mind's eye, you'll add spaces to solidly spelled field names and recognize UnitPrice as Unit Price. Nevertheless, for your own ease of use, especially for external users, you'll want to assign captions to make field names more readable.

In the case of common field names that might be required in more than one table (such as LastName), prefix the field name with an abbreviation of the table. Naming fields SuppAddress (for supplier address) and CustAddress (customer address) helps distinguish the address field of the supplier from that of the customer. (Some Access experts argue that there is little point to abbreviating supplier and customer: Because you can use up to 64 characters, you might as well name the fields SupplierAddress and CustomerAddress.) In the case of a field that you know you're going to use in only one table (say, a social security number in an Employees table), you might want to dispense with the table prefix.

As crucial as any of these rules are individually, most important is to adopt them consistently in all tables of your database. I can't guarantee that the cost/benefit analysis of naming a field of last names CustLastName instead of Last Name will necessarily prove positive, but it is good practice.

Assigning Data Types

Data types are selected by opening and selecting the type from the drop-down list in the Data Type column (see Figure 5.9).

Figure 5.9. A table in design view. As you move from field to field, the field properties in the lower pane change to match the field. The Data Type drop-down list is open to display the data types.

The first chapter discussed data types in some detail, and the discussion on lookup fields earlier in this chapter highlights their importance. The topic of changing data types is discussed later in the chapter. The case example at the end of the chapter will reinforce your knowledge of data types. Finally, the complete list of data types and their specifications can be found in the Access Help article "Field Data Types Available in Access (MDB)."

Designating a Primary Key

I don't think more needs to be said at this point about the importance of creating a primary key, a unique identifier, for each table in your database. Nor is there any difficulty in designating a primary key: Select the row (or rows) that makes up the primary key and choose Edit, Primary Key, or click the Primary Key button on the toolbar (refer to Figure 5.9). When you create a primary key, you automatically create an index for that field. Indexes are discussed in the upcoming "Assigning Field Properties" section.

Adding a Description

The Description column is entirely optional, but it can be helpful for reminding you what the field does, or providing any special directions for the field. For example, it can inform you that the SupplierID field in the Products table contains matching data in the SupplierID field of the Suppliers table (refer to Figure 5.9). It can prove especially useful if the field name is somewhat cryptic. If you do include a description, it appears as a Status bar message whenever you work in that field in Datasheet or Form view.

TIP

If you edit a field name, data type, or description and decide that your original was better, you can get it back by choosing Edit, Undo Property Setting. (Although it's not immediately apparent from the Design window, Field Name, Data Type, and Description are all field properties.)