Introduction

Now that we have a handle on what the database should look like, we need to work on setting up our tables and the fields within these tables.

In this lesson, we'll walk you through the steps of setting up a database in Access 2007 . You'll be setting up tables according to the plan for our bookstore scenario. You'll also be setting up the fields for each table, including establishing what data types can be entered in a given field.

Setting up tables and fields

Creating a new database with Access

When you launch Access 2007, you will see the Getting Started window.

In the left pane, the template categories —including the featured local templates —are listed, as well as the categories on Office Online. Templates are prebuilt databases focused on a specific task that you can download and use immediately.

You will also see the New Blank Database option, which allows you to build your own database from scratch.

Getting Started Window

When you choose the New Blank Database option at the top of the window, you will be prompted to rename the database from the default name, which is Database1.accdb . Rename the database whatever you want. In the example below, we named the database Ready2Read because it's the name of the store in our scenario.

Create a New Blank Database

Click Create to finish naming the database.

Setting up tables

The new database opens with one table showing as a default. It also defaults to naming this table Table1 in both the navigation pane and the Table tab itself. You will want to name your tables based on your database design plan.

Default Table Name

Naming a table

To give the table a unique name, you must first click on the Microsoft Office button in the upper-left corner of the application.

Naming Table1

Next, select Save from the menu. The Save As dialog box will appear to let you save the table whatever name you want.

Save As Dialog Box

The new table names appear in both the navigation pane and the Table tab itself, as you can see in the picture below.

New Table Name

TIP: Give your tables logical, easy-to-understand names.

To rename a table:

With the table closed, right-click the Table you want to rename in the navigation pane . Select the Rename option that appears in the menu.

Rename Table from Navigation Pane

The table name will be highlighted and a cursor will appear, which means you can now type the new name right there. Left-click anywhere outside of the table name to make the change.

To close a table:

There are several ways to close an active table. You can right-click the Table tab and choose Close from the menu.

Close Table from Table Tab

A more common method is to click the X that appears in the top-right corner of the active database object window.

Close Table from Document Window

To open a table:

To open a table, right-click the Table name of the table you want to open in the navigation pane , then choose Open from the menu.

Open Table from Navigation Pane

A more common method is to double-click the table name in the navigation pane . The selected table will open in the active database object window.

Adding more tables to the database

By default, Access 2007 starts out with one table. To add more tables to the database, click the Create tab on the Ribbon .

Create New Table

Next, select Table from the Tables command group . A new table will open in the active database object window. You must name your table using the Save command from Microsoft Office menu.

TIP: You can tell which table you are currently in by seeing which table tab is highlighted.

Adding fields to a table

Access 2007 allows you to add fields to tables when you are:

• Working in Datasheet view , which looks like a spreadsheet
• Working in Design view , where you are able to set more controls for your fields

Either way, you need to know how to switch between the two views.

To switch views:

Select the Views command group from either the Home tab (seen below) or the Datasheet tab on the Ribbon . Select the view option you want from the menu.

Switching Views

Adding fields in Datasheet view

By default, Access 2007 creates one field in each new table: the ID field. This field auto-numbers to give each record in the table a unique number identifier. Recall that records are the rows in a table.

TIP: You may want to rename the ID field with a unique name because the ID field appears automatically in every table you create. While this is not necessary, it may help avoid confusion when setting up table relationships.

Automatic ID Field

To add more fields to a table in Datasheet view , double-click the Add New Field header.

Add New Field

The Add New Field text will disappear from the header. Name the field by typing the name directly into the header. Press the Tab key on your keyboard to move to the next field.

Adding fields in Design view

In Design view , the field names are along the lefthand column instead of across the top like in Datasheet view, as seen below.

Fields in Design View

To add a new field to a table in Design view , click in the cell where you want the new field and type the field name . When you switch back to Datasheet view, your new field appears as its own column, as seen below.

Datasheet View of Added Field

In Design view, you have several field property options you can set to ensure data can only be entered in certain formats. Setting these options is a good idea if you want to make sure the data you have in your database is good, strong data.

Moving fields

If you want to rearrange the order in which your fields appear in a table, Access 2007 lets you easily move them around. To move a field in Datasheet view , drag and drop the field to the location you want. To do this:

• Click the field header for the field you want to move.
• Move the mouse in the area of the header.
• When the cross with arrows appears, hold down your left mouse button.
• With the left mouse button still held down, move the cursor to where you want the field to appear.
• Release the mouse button, and the field appears in its new location.

Deleting fields

To delete a field in Datasheet view , click the field header , then select Delete from the Field & Column command group. This is found on the Datasheet tab on the Ribbon.

WARNING: You should not delete any field in a table without first knowing what impact the deletion may have on the rest of the database!

Setting the data type for a field

Access 2007 lets you control how data can be entered in each table field within your database. This is done using Data Type . The default data type is text for every field after the ID field, which was set to auto-number. You can change the Data Type setting in two ways:

• From the Data Type & Formatting command group on the Ribbon
• From the Data Type column in Design view

To change data type from the Ribbon:

Click the field header, then select the data type you want from the drop-down menu next to the Date Type command.

Setting Data Type via Ribbon

To change data type in Design view:

Click the field name, then press the Tab key on your keyboard. From the drop-down menu under the Data Type column, select the format you want.

Setting Data Type in Design View

The data type is more noticeable when the database is being populated with records. It will cause data to be formatted for currency, text, number, and date and time just like it does in Microsoft Excel.

Challenge!

Open Access 2007 and build your own database. Then do the following:

• Give Table1 a new name.
• Add a new table.
• Add fields to a table.
• Set the data type for a field in a table.
• Open and close the tables.
• Move a field in a table.
• Delete a field in a table.