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.
Activity 1.10. Adding Fields to a Table
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.
[Page 890]
1.
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.
Figure 1.24.
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.
2.
In the first Field Name box, type Pledge# and then press to move the insertion point to the Data Type column.
After you name a field, you must designate a data type for the field. The data type defines the type of data that can be entered in a fieldtext, numbers, dates, monetary values, and so on. Text is the default data type. With Text as the data type, any characterstext or numberscan be entered into the field.
[Page 891]
At the right end of the box, an arrow displays. This arrow does not display until you click in this box. Some Access features become available in this mannerwhen a specific location is selected.
3.
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.
Figure 1.25.
Figure 1.26. Data Types
(This item is displayed on page 892 in the print version)
Data Type Name
Type of Data That Can Be Entered in the Field
Text
Text, or combinations of text and numbers, up to 255 characters
Memo
Lengthy text, or combinations of text and numbers; up to 65,536 characters or limited by the size of the database
Number
Numerical values used in calculations
Date/Time
Date and time values for the years 100 through 9999
Currency
Monetary values
AutoNumber
A unique sequential or random number assigned by Access as each record is entered
Yes/No
Fields that contain only one of two values (Yes/No, True/False, or On/Off)
OLE Object
An object such as a graphic file, Microsoft Word document, or Microsoft Excel worksheet that is embedded in an Access TableOLE is an abbreviation for object linking and embedding, a technology for transferring and sharing information among applications
Hyperlink
Text, or combinations of text and numbers stored as text, used as a hyperlink address that can connect to an e-mail address or Web page
Lookup Wizard
A field from which you can choose a value from a list or another table
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.
[Page 892]
4.
From the displayed Data Type list, click AutoNumber, and then press to move the insertion point to the Description column.
A data type of AutoNumber will assign a unique sequential numbereach number incremented by oneto each record as it is entered in the table. The AutoNumber can also be set to assign random numbers. You can probably think of some things that already have unique numbers. For example, individuals have Social Security numbersno two people have the same Social Security number. In instances where there is no unique number that is part of the data you are collecting, the AutoNumber data type is a useful way to automatically create a unique number and thus ensure that each record is uniquely identified.
[Page 893]
Descriptions for fields in a table are not required. Include a description if the field name does not provide an obvious description of the field. Information typed in the description area displays in the status bar of the Datasheet view when that field is active. It is used to provide additional direction to people entering data. In this instance, the field name Pledge# is self-explanatory, so no additional description is necessary.
5.
Press again to move the insertion point down to the next row. In the second Field Name box, type Student# and then press twice.
The next field name is entered and Text is accepted as the data type. The insertion point is in the Description column. For each pledge record, this field will contain the student number of the student who received the pledge. The student number contains only numbersno letters or charactersbut because the numbers are used only as a way to identify students and are not used for mathematical calculations, it is customary to define such a number as Text rather than Number.
6.
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.
Figure 1.27.
(This item is displayed on page 894 in the print version)
This action sets the size of the Student# field to no more than 5 characters. Field properties are characteristics of a field that control how the field will display and how the data can be entered in the field. Using this portion of the screen, you can define properties for each field that you name.
The default field size for a text field is 50. By limiting the field size property to 5 you ensure that only five characters can be entered for each student number. One of the goals of any database is to ensure the accuracy of the data that is entered. Setting the proper data type for the field and limiting the field size are two ways to help to reduce errors.
[Page 894]
7.
Using the techniques you have just practiced, name and define the third, through sixth fields:
Field Name
Data Type
Description
Student Last Name
Text
Enter last name only
Donor First Name
Text
Donor Last Name
Text
Donor Phone
Text
8.
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.
Each data type has its own set of properties. For example, you can set the field size property for text fields, but not for currency or memo fields.
9.
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
[Page 895]
You can quickly select a data type by typing the first letter of the data type name. In this manner, you can keep your hands on the keyboard rather than using the mouse to display the list of data types.
10.
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.
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.
Activity 1.11. Setting a Primary Key and Saving a Table
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.
[Page 896]
When you save a table, Access will prompt you to designate one of the fields as the primary key field.
1.
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.
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.
2.
Click OK, and then compare your screen with Figure 1.30.
Figure 1.30.
(This item is displayed on page 897 in the print version)
A message box displays advising you that a primary key has not been defined. Even though you can save a table without a primary key, good database design dictates that you establish a primary key to ensure that you do not enter the same record more than once. The function of a primary key is to prevent duplicate records within the same table.
[Page 897]
If you choose Yes, Access will designate the AutoNumber fieldPledge#as the primary key field. If your table does not include an AutoNumber field, Access will add a field named ID as an AutoNumber field, designate it as the primary key, and then save the table. This may not be what you want. If you choose No, the table will be saved without a primary key. You can designate a field as a primary key at a later time or designate the primary key field when the table is created, which is usually preferable.
3.
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.
4.
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.
Figure 1.31.
(This item is displayed on page 898 in the print version)
[Page 898]
Alert!: Does Your Screen Differ?
If you attached the primary key to the wrong field name, click the Pledge# field and then click the Primary Key button again. The primary key image will move to the correct field name.
5.
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.
The table designthe selection of field names with defined propertiesis saved and the new name displays in the Table title bar. Having your name in the title of the table will assist in identifying your printouts in a classroom or lab where you share a printer with others or when sending electronic files to your instructor.
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.