Home > Sample chapters > Microsoft Office > Access

Create Databases and Simple Tables

Refining Table Structure

Although you can create the structure of a database in Datasheet view, some structural refinements can be carried out only in Design view. When you are familiar with tables, you might even want to create your tables from scratch in Design view, where you have more control over the fields. You can open a new table in Design view by clicking the Table Design button in the Tables group on the Create tab.

When you open an existing table in Design view, the tabbed page shows the underlying structure of the table.

httpatomoreillycomsourcemspimages1744871.jpg

The table design page.

This page has two parts. The top part consists of the following:

  • Selector. You can click the shaded box at the left end of a row to select the entire field. You can then insert a row above the selected one, delete the row (thereby deleting the field), or drag the row up or down to reposition its field in the table.

    The selector also identifies the primary key field of the table by displaying the Primary Key icon (a key with a right-pointing arrow).

  • Field Name column. This column contains the names you specified when you created the table. You can edit the names by using regular text-editing techniques. You can add a new field by typing its name in the first empty cell in this column.

  • Data Type column. This column specifies the type of data that the field can contain. By default, the ID field in a new table is assigned the AutoNumber data type, and all other fields are assigned the Text data type. With the exception of fields with the OLE Object and Attachment data types, you can change the type of any field by clicking its Data Type entry, clicking the arrow that appears, and clicking a new data type in the list.

    httpatomoreillycomsourcemspimages1744873.jpg

    The list of data types.

  • Description column. This column contains an optional description of the field.

The Field Properties area at the bottom of the design page displays the properties of the field selected in the top part. Different properties are associated with different data types. They can determine such things as the number of characters allowed in a field, the value inserted if the user doesn’t type an entry, and whether an entry is required. Properties can also assess whether an entry is valid and can force the user to select from a list of values rather than typing them (with the risk of errors).

All fields, no matter what their data type, can be assigned a Caption property that will appear in the place of the field name in tables or in other database objects. For example, you might want to use captions to display the names of fields with spaces, such as First Name for the FirstName field.

In this exercise, you’ll open a table in Design view, add and delete fields, change a data type, set field sizes, and add a caption.

  1. In the Navigation pane, right-click the Employees table, and then click Design View.

    Access opens the table with its structure displayed. Because you created this table by copying the Customers table, you need to make some structural changes.

  2. With CustomerID highlighted in the Field Name column, type EmployeeID, and then press the Tab key twice.

  3. In the Description column, type Unique identifying number.

  4. Click the Country field’s selector, and then on the Design contextual tab, in the Tools group, click the Delete Rows button.

    httpatomoreillycomsourcemspimages1744875.png
  5. In the empty row below the Phone field, click the Field Name cell, and type Birthdate. Then click the Data Type cell.

    Access assigns the default Text data type to the new field.

  6. Click the arrow at the right end of the Data Type cell, and in the list, click Date/Time.

  7. Repeat steps 5 and 6 to add another Date/Time field named DateHired.

  8. Select the ZIP field name, change it to PostalCode, and then change its data type to Text.

    The properties in the Field Properties area at the bottom of the design page change to those that are appropriate for this type of field.

    httpatomoreillycomsourcemspimages1744877.jpg

    The properties for the Text data type.

  9. In the box to the right of Field Size, double-click 255, and type 10.

    You are specifying that this field can contain no more than 10 characters.

  10. Change the Field Size property of the following fields as shown:

    FirstName

    50

    LastName

    50

    City

    50

    State

    20

    Phone

    30

  11. Click the State field. Then in the Field Properties area, click the Caption box, and type State or Region.

    The Field Name remains State, but in Datasheet view, the column heading will be State or Region.

    httpatomoreillycomsourcemspimages1744879.jpg

    You have changed the Field Size and Caption properties of the State field.

  12. On the Design tab, in the Views group, click the View button to switch to Datasheet view.

    httpatomoreillycomsourcemspimages1744881.jpg

    Access tells you that you must save the table before leaving Design view.

  13. In the message box, click Yes to save the table.

    Access saves the table and displays it in Datasheet view.

  14. With the table displayed in Datasheet view, click the LastName field name. Then on the Fields contextual tab, in the Add & Delete group, click the Text button.

    httpatomoreillycomsourcemspimages1744883.jpg

    A new field called Field1 that has the Text data type is inserted to the right of the LastName field.

  15. With Field1 selected, type Title, and press Enter.

  16. Click the Title field name. Then in the Properties group, in the Field Size box, click 255 to select it, type 50, and press Enter.

    httpatomoreillycomsourcemspimages1744885.jpg
  17. Type the following information in the first record:

    FirstName

    Karen

    LastName

    Berg

    Title

    Owner

    The Employees table is now ready for you to start entering data.

    httpatomoreillycomsourcemspimages1744887.jpg

    The first record of the Employees table.