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.

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

  TIP

  If you don’t want a table to have a primary key (for example, if none of the fields will contain a unique value for every record), select the field designated as the primary key, and on the Design contextual tab, in the Tools group, click the Primary Key button to toggle it off. If you want to designate a different field as the primary key, select the new field, and click the Primary Key button to toggle it on. (You don’t have to remove the primary key from the current field first; it will happen automatically.)

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

  

  The list of data types.

  SEE ALSO

  For more information about data types, see "Restricting the Type of Data" in Chapter 6.

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

   

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.

   TIP

   If you use only five-digit ZIP codes, the Number data type is fine. But setting it to Text allows you to enter ZIP+4 codes or the letter-number postal codes used in Canada and other countries.

   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.

   

   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

    TIP

    Sometimes changing the field properties of a table that already contains data can produce unanticipated results. If you make a change to a field property that might cause data to be lost (for example, if you make the Field Size property smaller than one of the field’s existing values), Access warns you of this problem when you attempt to save the table. For more information, see Chapter 6.

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.

    

    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.

    

    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.

    

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

    TIP

    You can also create a new field with a specific data type by clicking the Click To Add label to the right of the last field in the field name row. Then in the list that appears, you can click the data type you want.

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.

    

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.

    

    The first record of the Employees table.

    Clean Up

    Close the Employees table. Retain the MyTables database for use in the last exercise.