After you have saved and named the database, the next step is to plan and create the tables in which to record your data. Tables are the foundation of your database because the actual data is stored there.
Limit the data in each table to one subject. For example, think of all the data at your college; there is likely one table for student information, another table for course information, and another table for room information, and so on.
Within each table, create fields that are broken down to the smallest usable part. For example, instead of a complete address in one field, break the address down into a field for the street address, a field for the city, a field for the state, and a field for the postal code. With small usable parts, you could, for example, find all of the people who live in a particular city or state or postal code.
To answer all the questions you want the database to answer, in this project you will create a database with two tables. One table will list the dollar amounts pledged and collected from donors, and another table will list the names of student club members who are participating in the fundraising event. Donations are made to students and the organization each student represents.
Recall that fieldsthe columns in your tableare categories that describe each piece of data stored in the table. Your tables design refers to the number of fields, the names of fields, and the format of the content within a field. In this activity, you will create a table to record the amounts of money pledged and collected by each club member. To do this, you will use the table Design view.
In the Database window, double-click the command Create table in Design view, and then compare your screen with Figure 1.24. Alternatively, on the Database window toolbar, click Design; or, right-click the command and click Open.
The Design view for the new table displays. Here you name the fields in your table. Because you have not yet named or saved this table, the title bar indicates the default name Table1. The insertion point is blinking in the first Field Name box.
In the first Field Name box, type Pledge# and then press to move the insertion point to the Data Type column.
Click the Data Type arrow to display a list of data types as shown in Figure 1.25, and then take a moment to study the table in Figure 1.26 that describes the different data types.
Each pledge that a club member receives is assigned a pledge number. The first field will contain this pledge number for each record. The number must be unique so that each pledge of money has an identifier that no other pledge has.
From the displayed Data Type list, click AutoNumber, and then press to move the insertion point to the Description column.
Press again to move the insertion point down to the next row. In the second Field Name box, type Student# and then press twice.
In the lower portion of the screen, under Field Properties, click in the Field Size box, select the value displayed, type 5 and then compare your screen with Figure 1.27.
Using the techniques you have just practiced, name and define the third, through sixth fields:
In the next empty Field Name box, type Pledge Amount and then press . Click the Data Type arrow, click Currency, and then press to move to the Description column. Under Field Properties, notice the properties available for the Currency data type.
Press to move to the next row, type Date Collected and then press . Type D to select the Date/Time data type, and then press . In the Description column, type Date the pledged money was collected from the donor
Under Field Properties, click in the Format box, and then click the arrow that displays. From the displayed list, click Short Date (6/19/94), and then compare your screen with Figure 1.28.
Setting the format to short date controls how the dates that are entered will display in the table.
Another Way: To Create a New Table in a Database
There are two additional ways to begin a new table. You can create a new table using the wizard, which walks you step by step through the process of creating a new table. Or, you can create a table by typing data directly into an empty table in the Datasheet view, and then name the fields in the Design view after the data has been entered.
A primary key is the field that uniquely identifies a record in a table. For example, in a college registration system, your student number uniquely identifies youno other student at the college has your exact student number. Two students at your college could have the exact same name, for example, David Michaels, but each would have a different and unique student number.
When you save a table, Access will prompt you to designate one of the fields as the primary key field.
On the Table Design toolbar, click the Save button . In the Save As dialog box, using your own name, type 1B Pledges Firstname Lastname and then compare your screen with Figure 1.29.
When you save objects within a database, it is not necessary to use underscores. Your name is included as part of the object name so you and your instructor will be able to identify your printouts and electronic files.
Click OK, and then compare your screen with Figure 1.30.
Click Cancel to return to the table Design view so you can set a primary key, and notice that the table is not savedTable1 still displays in the title bar.
Click anywhere in the Pledge# field to make it the active field. On the Table Design toolbar click the Primary Key button , and then compare your screen with Figure 1.31.
On the Table Design toolbar, click the Save button . In the Save As dialog box, use your own first and last name and type 1B Pledges Firstname Lastname and then click OK.
More Knowledge: Changing the Table Name
If you type the table name incorrectly, or need to change the name of a table, in the Database window, right-click the table name, and then from the displayed shortcut menu click Rename. This changes the table name to edit mode and you can type the new name, or edit it as you would any selected text.
GO! with Microsoft Office 2003 Brief (2nd Edition)