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.

For more information about designing a database and choosing fields, see the article Database design basics.

Top of Page Top of Page

Add a new field

  1. Click the Microsoft Office Button Button image, and then click Open.
  1. In Open dialog box, select and open the database.
  2. In the Navigation Pane, double-click one of the available tables to open it.
  3. Type your data in the cell below the Add New Field column header.

Top of Page Top of Page

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.

Field List pane

  1. Click the plus sign (+) next to a table name to display the list of fields in that table.
  2. Drag the field that you want from the Field List pane to the table.
  3. When the insertion line appears, drop the field in position.

Insertion line for inserting a field from the Field List pane

The Lookup Wizard starts.

  1. 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 (one-to-many relationship: An association between two tables in which the primary key value of each record in the primary table corresponds to the value in the matching field or fields of many records in the related table.) 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.

For more information about table relationships, see the article Guide to table relationships. For more information about data types and field properties, see the article Introduction to data types and field properties.

Top of Page Top of Page

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.

  1. Open the table in Datasheet view.
  2. On the Datasheet tab, in the Fields & Columns group, click New Field.

Access Ribbon Image

The Field Templates pane appears.

field template

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

To create your own field template, see the article Create a custom field template for the Field Templates pane

Top of Page Top of Page

 
 
Applies to:
Access 2007