Add a field to a table

Tables, the basic building blocks of a database, are made up of records (sometimes called rows) and fields (sometimes called columns). For example, a contacts table would contain several records, and each record would contain several fields that store information, such as the last name, first name, telephone number, and city of each contact. This article explains why well-designed fields are important and shows you several basic ways to add fields to a table in Microsoft Office Access 2007.

What do you want to do?

Understand why fields are important

Every field has certain essential characteristics, such as a name that uniquely identifies the field within a table, a data type that defines the nature of the data, the operations that can be performed on the data, and how much storage space to set aside for each value.

A field also has properties that define the appearance or behavior of the data in the field. For example, the Format property defines how the data appears in a datasheet or form that contains that field.

It is important to plan how you might use the information stored in the fields. For example, it's usually best not to create a field to store a calculated value. Try to break information down to its smallest useful part per field. For instance, instead of storing a person's name in one field, you should store its components in separate fields for the first and last name. Generally speaking, if you need to report, sort, search, or calculate on an item, put that item in a field by itself.

Add an existing field from another table

If your database contains multiple tables, you can add a field from another table in the database by using the Field List. When you work with a table in Datasheet view, the Field List pane shows all of the other tables in your database, grouped into two categories:

Fields available in related tables This category lists all of the tables that have a relationship with the table you are currently working with.

Fields available in other tables This category lists all of the tables that do not have a relationship with the table you are currently working with.

Click the plus sign (+) next to a table name to display the list of fields in that table.

Drag the field that you want from the Field List pane to the table.

When the insertion line appears, drop the field in position.

The Lookup Wizard starts.

Follow the instructions to complete the Lookup Wizard.

The field appears in the table in Datasheet view.

Note: When you drag a field from an unrelated table and then complete the Lookup Wizard, a new one-to-many relationship is automatically created between the table in the Field List and the table that you are currently working with.

You can change the order in which the fields appear in the table by clicking the selector to the left of the field name. To move multiple contiguous fields, click the first field, hold down the SHIFT key, and then click the last field. Access automatically assigns a data type to each field that you add to the table, based on the data type of the field that you chose in the wizard. If you want to set the data type and format for a field, you can do so by using the commands in the Data Type & Formatting group on the Datasheet tab.

Note: In addition to determining the data type, Access may automatically set the Format property for a new field, depending on the value you type in that field. For example, if you enter 10:50 am, Access sets the data type to Date/Time and the Format property to Medium Time.

Add a field by using a field template

You can sometimes save time by choosing fields from a field template. A field template has predefined sets of definitions and properties for a field. A field template definition includes a field name, a data type, a setting for the field's Format property, and also a number of other field properties that, when taken together, form a model that serves as the basis for creating a new field.

Open the table in Datasheet view.

On the Datasheet tab, in the Fields & Columns group, click New Field.

The Field Templates pane appears.

Select one of more fields in the Field Templates pane and drag them to the table. When the insertion line appears, drop the fields in position.