How to Create a Simple Table

As you work with Access, you'll notice that there is usually more than one way to do things. The following are the three ways in which you can create a new table in Access:

• Start typing data into a blank table Access will create the fields in the table for you. If you're used to working with Microsoft Excel, this method might be the most comfortable one for you.

• Use the Table Wizard It guides you through the steps of building some common table types.

• Work directly in table design mode You specify the details of each field yourself. This is the most powerful and flexible way to design a new table.

In this section, you'll learn how to use each of these techniques.

Entering Data to Create Your Table

To begin with, we'll show you how to create the Types table, just by entering data. Table 5.1 shows the data this Access table will contain.

Table 5.1. Data for the Type Table

TypeID	Description
1	Decorative
2	Edible
3	Medicinal

To create the Types table, follow these steps:

1. Launch Access.

2. In the New File task pane, click the Blank Database link. Name the new database Plants.mdb , and click Create .

3. In the Database window, click the Tables shortcut.

4. Double-click the Create Table by Entering Data shortcut in the Database window to open a blank datasheet.

5. Click the mouse in the first row of the first column of the datasheet; then type the value 1 .

6. Press the Tab key to move to the next column, and type the value Decorative .

7. Use the mouse or arrow keys to move to the first column of the second row. Continue entering data by typing it into the table until the datasheet looks similar to Figure 5.2.

   Figure 5.2. Creating a table by entering data.

   

8. Right-click the header for the first column, where the text Field1 appears. Select Rename Column (see Figure 5.3) from the shortcut menu to highlight the current name of the field. Type the new name, TypeID , and press Enter .

   Figure 5.3. Change a field's name.

   

9. Here's another way to rename: Double-click the header for the second column, where the text Field2 appears. This highlights the current name of the field. Type the new name, Description , and press Enter .

10. Select File , Save to open the Save As dialog box. Change the Table Name in the dialog box to Types and click OK .

11. Access displays the warning dialog box shown in Figure 5.4 because you can't specify a primary key when you create a table by typing in data. For now, just click No .

    Figure 5.4. Access warns you that your table doesn't have a primary key.

    

12. The blank cells in the datasheet will vanish (except for one blank row, which is discussed later in the "Using Datasheets" section of the chapter), and the caption of the table will show its new name. Click the Close button to close the new table.

Note

In Access, column and field mean the same thing.

Congratulations! You've just created your first Access table.

Using the Table Wizard

Access also includes a Table Wizard that can streamline the job of creating new tables. The Table Wizard knows how to create fields for a variety of common situations. You'll use it to create the basic structure of the Catalogs table.

Note

The design you worked through in Chapter 4 defines the TypeID field in the Types table as an AutoNumber primary key field. However, that isn't what actually happened because Access automatically assigns the Number data type to the TypeID field when the contents are numerical values. This small deviation from the original plan is insignificant at this stage.

To build the initial Catalogs table, follow these steps:

1. Double-click the Create Table by Using Wizard link in the Tables section of the Database window. Like most wizards, this one presents information and asks for input in a series of panes. Each pane displays and fills the entire wizard window, and you can navigate from pane to pane by clicking Next or Back.

2. On the first pane of the Table Wizard, select the Business radio button and the Mailing List sample table. If you scroll down the list, you'll see that you have the choice of many sample tables here. You should start with a sample table that's relatively close to the one you want to build.

3. The next step is to move fields from the Sample Fields box to the Fields in My New Table box. You'll see this dual box interface at many places in Access. Figure 5.5 shows the functions of the four buttons between the boxes, which move items back and forth between the lists. Click the Select One button to select the OrganizationName, Address, City, State, PostalCode, Country/Region, and Notes fields.

   Figure 5.5. Controls to select items in the Table Wizard.

   

4. Select the OrganizationName field in the Fields in My New Table box. Then click the Rename Field button and enter Name in the Rename Field dialog box. Click OK to rename the field. Similarly, rename PostalCode to ZIP , Country/Region to Country , and Notes to Specialty . This matches the field names you designated in Chapter 4. (In Chapter 4, you worked with pen and paper lists and the table names contained spaces, which we've omitted in our actual table names .)

5. Click the Next button.

6. In the second pane of the Table Wizard, name the new table Catalogs . Click the radio button labeled No, I'll Set the Primary Key , as shown in Figure 5.6. Click Next .

   Figure 5.6. Naming a table in the Table Wizard.

   

7. In the third pane of the Table Wizard, specify the Name field as the field that will hold unique data. Select the radio button for Numbers and/or Letters I Enter and click Next .

8. The fourth pane of the Table Wizard asks how this table is related to the Types table you already created. You'll learn more about relationships in Chapter 6, "Tapping the Power of Relationships." For now, just accept the default (no relationship) and click Next .

9. Select the Enter Data Directly into the Table radio button and click Finish . This creates the table and opens it as a datasheet. Now you can type the data for the Catalogs table directly into the datasheet, as shown in Figure 5.7.

   Figure 5.7. Entering data in a new table from the Table Wizard.

   

When you're dealing with common tables, the Table Wizard can provide a fast way to get started. But sometimes your database needs a table that doesn't resemble any of the choices from the wizard. Those are the times when you'll need to use table design view.

Working in Design View

Design view gives you a way to look at all the nuts and bolts of a table and its fields. You can change anything about a table in design view: the names and numbers of the fields, the type of data they can hold, the primary key of the table, and more. This section introduces you to table design view, and you'll learn more about this view in Chapter 11, "Customizing Your Tables."

Tip

Although Access lets you include spaces in field names, we recommend that you avoid this. Using spaces makes working with some other parts of the product, such as queries, more difficult.

The plants table in the database isn't really like any of the sample data in the Table Wizard. So, in this section, you'll use table design view to create this table. To build the plants table, follow these steps:

1. Double-click the Create Table in Design View link in the Tables section of the Database window to open the design view of a new table.

2. Type CommonName in the first row of the Field Name column. Access automatically assigns Text as the data type for the field, as shown in Figure 5.8. You'll learn more about data types in a few pages.

   Figure 5.8. Creating a new table in design view.

   

3. Type LatinName in the second row of the Field Name column to create another field. Accept the default Text data type.

4. Type Notes in the third row of the Field Name column to create another field. For this field, click the drop-down arrow next to the Text data type and select Memo as the data type for this field.

5. Similarly, add three more fields to the table: Picture (data type should be OLE Object), TypeID (data type should be Number), and CatalogName (data type should be Text).

6. Right-click the CommonName field and select Primary Key . Access places a small key icon next to this field's name to indicate that it is the primary key.

7. Select File , Save As and name the new table Plants . Figure 5.9 shows the completed table in design view.

   Figure 5.9. A newly saved table in design view.

   

8. Click the Close button to close the newly created table.

You might have noticed the Field Properties area at the bottom of design view as you worked through the previous steps. As you select different fields, this area shows more information about each field. For example, the first property for the CommonName field is Field Size, and it has a default value of 50 . This tells Access that the CommonName field can hold up to 50 characters , but no more. Field properties are an important part of ensuring that only reasonable data is saved in your database. You'll learn more about field properties in Chapter 12, "Getting Down to Business with Queries."

Modifying an Existing Table

You can also use table design view to modify an existing table. For example, you'll recall that you didn't create a primary key for the Types table. You can use table design view to correct this oversight by following these steps:

1. Click the Types table in the Database window.

2. Click the Design button at the top of the Database window, in what's known as the Database Window toolbar, to open the table in design view.

3. Click the TypeID field to select it. You'll see a black triangle to the left of the field name when you do.

4. Click the Primary Key button on the Access toolbar, as shown in Figure 5.10, to set this field as the primary key of the table (another case of Access providing more than one way to do something).

   Figure 5.10. Setting the primary key for an existing table.

   

5. Select File , Save to save your changes.

6. Click the Close button to close the table.

You can change almost anything about a table in design view. In fact, you can change things you probably shouldn't. For example, what happens if you click in the data type column for the Description field and change the data type of the field from Text to Number? Well, the existing data isn't numbers, so if you save this change, Access will just throw your data away! Fortunately, it gives you a warning, as shown in Figure 5.11, before saving any such potentially disastrous changes.

Figure 5.11. Access warns you if you try to do something that will cause a loss of data.