Creating and Customizing Tables

A table is the basic unit for storing and organizing information in an Access database. One database can contain any number of tables, as well as links to tables stored in other locations and other formats. Data within a table is arranged in a basic grid: Each row contains all the data fields in a single record, and each column represents a field, where similar information (first name and last name, for instance) is stored in all the records. In turn, tables directly or indirectly form the basis for all other objects within an Access database, including queries, forms, and reports.

As a general rule, however, each field definition has four elements, all of them visible when you open a table in Design view and select a field, as in Figure 34.1.

• The field name must follow the same rules as those for other database objects: It can consist of up to 64 characters, including letters, numbers, spaces, and any special characters except a period, exclamation point, accent grave (`), or brackets. Spaces are allowed within object names, but not as the first character.

• Each field definition also includes a data type; this setting controls formats for display and input and also serves as a way of validating data entry. (See the following section for a detailed list of available data types.)

• The field description is optional; if you choose to enter text here, it helps to document the database. It also helps when viewing a table's contents in Datasheet view; the Description text for the current column appears in the lower-left side of the status bar. Maximum length is 255 characters.

• Finally, the field properties for the selected field appear at the bottom of the table window. The exact properties for each field vary, depending on the data type you've selected. Use these properties to define more details about the type of data permitted in each field, as well as its display format and the default caption that appears when you place the field on a form or report.

Choosing a Field's Data Type

By default, every new field you create uses the Text data type, with a maximum length of 50 characters. More often than not, that setting will be inappropriate for the type of data you plan to store in a field. The Text field enables you to enter nearly any type of data, including numbers, currency, dates, and times. In many cases, however, another data type is a better choice. For example, if you intend to enter invoice dates in a field, specify Date/Time as the field type; this option prevents you from inadvertently entering a value that isn't a legal date, such as 2/30/2001. You can choose from the following data types for any field:

• Text— Enables you to enter a maximum of 255 characters, including letters, numbers, and punctuation. This data type is also appropriate for entering numeric data that you don't want to use for calculations or sorting, such as Social Security numbers and phone numbers.

• Memo— This data type allows for long blocks of text, up to 64,000 characters. Memo fields do not allow formatting; they're most useful for notes and descriptions that exceed 255 characters.

• Number— Allows entry of numeric characters only. Choose an entry from the Field Size property box to further define the format. Byte is the most efficient and most limited choice, permitting you to enter whole numbers from 0 to 255. To store whole numbers, positive or negative, without fractions, choose Integer (–32,768 to 32,767) or Long Integer (–2,147,483,648 to 2,147,483,647). Single, Double, and Decimal formats allow increasingly more precise numbers with fractions.

• Date/Time— This data type enables you to enter dates and times by using a variety of formats and date separator characters. Use the Format property to control the display of data in Datasheet view.

• Currency— Like Number formats, except these values always display using the default Currency symbol, as defined in Control Panel's Regional Settings option. A Currency field is accurate to 15 digits to the left of the decimal point and 4 digits to the right; this option cannot be adjusted.

• AutoNumber— This data type results in a field of consecutive integers, supplied by Access as you add new records to your database. AutoNumber fields are commonly used for invoice numbers and for primary keys.

• Yes/No— Use this data type for fields that have only one of two values. Use a Yes/No field to identify customers who are exempt from sales tax, for example. Use the Format property to change the value displayed from Yes/No to True/False or On/Off.

• OLE Object— Enables you to create a field for storing pictures, documents, or OLE objects developed in other programs. Note that you can't sort, index, or group by any field that uses this data type.

• Hyperlink— Enables you to enter clickable links to Web addresses, folders, files, and other objects.

→ For a detailed description of how Office hyperlinks work and how to create and manage them, see "Working with Hyperlinks".

If you imported a list that included a column of hyperlinks, Access should automatically create a Hyperlink field; if you encounter problems, see "Importing Hyperlinks into Access" in the "Troubleshooting" section at the end of this chapter.

Setting a Primary Key

Every time you create a new table, Access prompts you to create a primary key. This step isn't mandatory, but it's highly recommended. For starters, a primary key is required if you ever want to create a relationship for the table. By definition, the primary key contains nonduplicate entries for each record; as a result, Access can use this unique identifier to positively identify each record, making searches easier and faster.

Access gives you three options for the primary key:

• AutoNumber primary keys are your safest choice. Under some circumstances, Access creates this type of primary key automatically. Using the AutoNumber data type guarantees that values are unique.

• A single-field primary key is a good choice when you're certain that the contents of the selected field will always be unique. Examples of useful single-field keys include unequivocal identification codes such as an employee badge number, Social Security number, part ID, or license plate number.

• Multiple-field primary keys are most common in junction tables used to link two tables in a many-to-many relationship. In an Invoices table, for example, each unique InvoiceID value might contain several ProductID values; likewise, each unique ProductID value in the Products table might be part of several invoices, each with its own InvoiceID number. By creating a third table (Order_Details) that combines these two values to create a primary key, you can be certain that the table will not contain any duplicate records and that you'll always find the record you need.

To define a single field as the primary key, click the Primary Key button on the toolbar. The primary key field is identified in Design view by a small key icon, displayed just to the left of the field name.

Speeding Up Sorts and Queries with Indexes

Indexes help make short work of searching and sorting. When you build an index for a field or combination of fields, Access creates a sorted data structure that it can search through to find unique values. Without an index, Access has to step through every record to complete a query or sort; with an index, Access can find a unique value and jump directly to the rows that contain that value. The performance difference can be astounding.

By default, the primary key in every table is indexed. As part of the process of defining field settings, you can also create an index for a specific field. In Design view, select the field and change the Indexed property to Yes from its default setting of No. In addition, you can choose the Yes (No Duplicates) setting to ensure that each new entry in the field is unique. By definition, this is the Indexed setting for a table's primary key.

You can also create an index that covers multiple fields. This technique is useful when you have a query that sorts and searches on a group of fields. Click the Indexes button and fill in the name of the index, the fields it should include (up to 10), and the sort order for each one. This same dialog box lets you view and edit or delete existing indexes.