/en/access2007/thinking-about-database-design/content/
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.
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.
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
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.
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.
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, 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.
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.
Access 2007 allows you to add fields to tables when you are:
Either way, you need to know how to switch between the two 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
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.
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.
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:
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!
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:
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
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.
Open Access 2007 and build your own database. Then do the following:
/en/access2007/building-table-relationships/content/
