ACC97: How to Create a Table in an Access Database (304450)


The information in this article applies to:

  • Microsoft Access 97
This article was previously published under Q304450
For a Microsoft Access 2000 version of this article, see 304262.

Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

SUMMARY

In the Microsoft Access user interface, you can create a table in a database by using any of the follow three methods:
  • You can create a table by using the Table Wizard.
  • You can create a table by entering data into a datasheet.
  • You can create a table in Design View.
This article shows you how to use each of these methods.

MORE INFORMATION

Creating a Table Using the Table Wizard

  1. Create a new, blank database.
  2. On the Tables tab, click New.
  3. Double-click Table Wizard.
  4. Follow the directions in the Table Wizard dialog boxes.
If you want to modify the table that the Table Wizard creates, open the table in Design view when you have finished using the Table Wizard.

Creating a Table by Entering Data in a Datasheet

In Microsoft Access, you can also create a table by just entering data into columns (fields) in a datasheet. If you enter data that is consistent in each column (for example, only names in one column, or only numbers in another column), Access will automatically assign a data type to the fields. To create a table by just entering data in a datasheet, follow these steps:
  1. Create a new, blank database.
  2. On the Tables tab, click New.
  3. In the New Table dialog box, double-click Datasheet View. A blank datasheet is displayed with default column names Field1, Field2, and so on.
  4. Rename each column that you want to use. To do so, double-click the column name, type a name for the column, and then press ENTER.

    You can insert additional columns at any time. To do so, click in the column to the right of where you want to insert a new column, and then on the Insert menu, click Column. Rename the column as described earlier.
  5. Enter your data in the datasheet. Enter each kind of data in its own column. For example, if you are entering names, enter the first name in its own column and the last name in a separate column. If you are entering dates, times, or numbers, enter them in a consistent format. If you enter data in a consistent manner, Microsoft Access can create an appropriate data type and display format for the column. For example, for a column in which you enter only names, Access will assign the Text data type; for a column in which you enter only numbers, Access will assign a Number data type. Any columns that you leave empty will be deleted when you save the datasheet.
  6. When you have added data to all the columns that you want to use, click Save on the File menu.
  7. Microsoft Access asks you if you want to create a primary key. If you have not entered data that can be used to uniquely identify each row in your table, such as part numbers or an ID numbers, it is recommended that you click Yes. If you have entered data that can uniquely identify each row, click No, and then specify the field that contains that data as your primary key in Design view after the table has been saved. To define a field as your primary key after the table has been saved, follow these steps:
    1. Open the table that Access created from the data that you entered in datasheet in Design view.
    2. Select the field or fields that you want to define as the primary key.

      To select one field, click the row selector for the desired field.

      To select multiple fields, hold down the CTRL key, and then click the row selector for each field.
    3. On the Edit menu, click Primary Key.

      If you want the order of the fields in a multiple-field primary key to be different from the order of those fields in the table, click Indexes on the toolbar to display the Indexes window, and then reorder the field names for the index named PrimaryKey.
    As mentioned earlier, Microsoft Access will assign data types to each field (column) based on the kind of data that you entered. If you want to customize a field's definition further--for example, to change a data type that Access automatically assigned, or to define a validation rule--open the table in Design view.

Creating a Table in Design View

If you want to create the basic table structure yourself and define all the field names and data types, you can create the table in Design view. To do so, follow these steps:
  1. Create a new, blank database.
  2. On the Tables tab, click New.
  3. In the New Table dialog box, double-click Design View.
  4. In the <Table Name>: Table dialog box, define each of the fields that you want to include in your table. To do so, follow these steps:
    1. Click in the Field Name column, and then type a unique name for the field.
    2. In the Data Type column, accept the default data type of Text that Access assigns or click in the Data Type column, click the arrow, and then select the data type that you want.
    3. In the Description column, type a description of the information that this field will contain. This description is displayed on the status bar when you are adding data to the field, and it is included in the Object Definition of the table. The description is optional.
    4. Once you have added some fields, you may need to insert a field between two other fields. To do so, click in the row below where you want to add the new field, and then on the Insert menu, click Rows. This creates a blank row in which you can add a new field.

      To add a field to the end of the table, click in the first blank row.
  5. After you have added all the fields, define a primary key field before saving your table. A primary key is one or more fields whose value or values uniquely identify each record in a table. To define a primary key, follow these steps:
    1. Select the field or fields that you want to define as the primary key.

      To select one field, click the row selector for the desired field.

      To select multiple fields, hold down the CTRL key, and then click the row selector for each field.
    2. On the Edit menu, click Primary Key.
  6. If you want the order of the fields in a multiple-field primary key to be different from the order of those fields in the table, click Indexes on the toolbar to display the Indexes dialog box, and then reorder the field names for the index named PrimaryKey.

    You do not have to define a primary key, but it is usually a good idea. If you do not define a primary key, Microsoft Access asks if you want Access to create one for you when you save the table.
  7. When you are ready to save your table, on the File menu, click Save, and then type a unique name for the table.

REFERENCES

For more information about creating tables in an Access database, click Microsoft Access Help on the Help menu, type creating tables in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.


For additional information working with data types and field properties in an Access table, click the article number below to view the article in the Microsoft Knowledge Base:

304445 ACC97: Explanation of Data Types and Field Properties in a Microsoft Access Database

Modification Type:MajorLast Reviewed:9/26/2003
Keywords:kbhowto KB304450
©2003 Microsoft Corporation. All rights reserved.