Creating and Saving a Table 4

Contents

Creating a Database 2

Step-by-Step 2.1 4

Creating and Saving a Table 4

EXTRA FOR EXPERTS 5

Step-by-Step 2.2 5

Table 6

Designing a Table 6

Understanding Data Types 7

Setting a Field's Data Type and Name in Datasheet View 8

EXTRA FOR EXPERTS 8

Step-by-Step 2.3 9

Entering Records in Datasheet View 10

Step-by-Step 2.4 10

Changing a Field's Data Type in Datasheet View 11

Step-by-Step 2.5 11

Working in Design View 12

EXTRA FOR EXPERTS 13

Adding, Deleting, Renaming, and Rearranging Fields in Design View 14

Step-by-Step 2.6 14

Description 15

Step-by-Step 2.7 16

Changing Field Properties in Design View 17

Changing the Field Size 18

EXTRA FOR EXPERTS 19

Step-by-Step 2.8 20

Setting a Field's Format 20

EXTRA FOR EXPERTS 21

Step-by-Step 2.9 21

Setting a Field's Default Value 22

EXTRA FOR EXPERTS 22

Using the Required Property 22

EXTRA FOR EXPERTS 23

Step-by-Step 2.10 23

Step-by-Step 2.11 24

End of Chapter Review 26

Lesson 2: Creating a Database: Summary 26

Lesson 2: Creating a Database: Vocabulary Review 26

Lesson 2: Creating a Database: Review Questions 27

TRUE / FALSE 27

WRITTEN QUESTIONS 27

FILL IN THE BLANK 27

Lesson 2: Creating a Database: Projects 28

PROJECT 2–1 28

PROJECT 2–2 29

PROJECT 2–3 29

Lesson 2: Creating a Database: Critical Thinking 30

ACTIVITY 2–1 30

ACTIVITY 2–2 30

Creating a Database

The first step in creating a database is to create the file that will store the database objects. You can choose to create a database using one of the many templates that are installed with Access. These templates contain objects that you can use to organize data about events, projects, tasks, and other categories of data. When you use a template to create a database, the template creates the database and one or more table, query, form, and report objects that you use to enter and view data. Another option is to use the Blank database template, which creates a database with an empty table in it.

To create a database, start Access. On the New tab in Backstage view, click the template you want to use or click the Blank database template to create a database that contains an empty table. Access asks you to specify a file name to use and a location in which to store the database, as shown in Figure 2–1.

FIGURE 2–1 Creating a new, blank database

After specifying the file name and the location in which to store the database, click the Create button to create the new database and open it in Access. When you create a blank database, Access opens an empty table in Datasheet view so that you can start entering data, as shown in Figure 2–2.

FIGURE 2–2 New, empty table created

Step-by-Step 2.1

1. With Windows running, click the Start button

on the taskbar. Click All Programs, click Microsoft Office, and then click Microsoft Access 2010.

2. On the New tab, click the Blank database template, if necessary. See Figure 2–1.

3. In the Blank database pane, click the Browse button

. The File New Database dialog box opens.

4. Navigate to the drive and folder where your Data Files are stored, and then open the Access Lesson 02 folder. Click OK.

5. In the Blank database pane, click in the File Name text box to select the default database name (Databasel.accdb), and then type Interviews followed by your initials.

6. Click the Create button. Access creates the Interviews database and opens it. Access also opens a new, empty table, as shown in Figure 2-2. Leave the table open for the next Step-by-Step.

TIP

Access might add the file name extension “accdb” to your file name automatically. You do not need to type it.

Creating and Saving a Table

When you create a blank database, Access creates the first table in the database for you and names it Table1. You can change this name when you save the table for the first time. To save a table, click the Save button on the Quick Access Toolbar. In the Save As dialog box, type the name of the table, and then click OK. The new table name appears on the tab for the table and also in the Navigation Pane, as shown in Figure 2–3. In many databases, data is stored in more than one table.

EXTRA FOR EXPERTS

You can also create a new table in a database by clicking the Create tab on the Ribbon, and then clicking the Table button in the Tables group.

FIGURE 2–3 Table after saving it

Step-by-Step 2.2

1. On the Quick Access Toolbar, click the Save button

. The Save As dialog box opens. The default name Table 1 is selected.

2. In the Table Name text box, type Applicant.

3. Click OK. The tab for the table now displays the table name Applicant. The Applicant table appears in the Navigation Pane, as shown in Figure 2–3. Leave the table open for the next Step-by-Step.

Table

Designing a Table

Understanding Data Types

Setting a Field's Data Type and Name in Datasheet View

Entering Records in Datasheet View

Changing a Field's Data Type in Datasheet View

After creating a table in a database, you need to tell Access which fields to include in the table. When you create a blank database, the table that Access creates for you contains one field named ID. Access sets the ID field as the table's primary key. In a table, the primary key is the field that contains a unique field value for each record in the table. In some tables, this field is called an AutoNumber because it automatically adds a unique number to the primary key field for each record in the table. You can tell that Access created an AutoNumber for the ID field because of the word (New) in the first record's field. When you add the first record to the table, Access will change (New) to a unique number.

In some tables, your data might already have a field that stores unique numbers for each record. This unique field might store student identification numbers, employee numbers, or Social Security numbers. These types of values are also good candidates to use as a table's primary key field. The advantage of setting a primary key is that Access does not let you enter duplicate values for this field in different records. In other words, if you enter the student ID 1001 in the record for a student named John Hooper, Access does not let you enter the same student ID number in a record for another student. You learn more about primary key fields later in this lesson.

Understanding Data Types

Before creating all the fields for your table, you must decide which data type to assign to the field based on the field values you will enter. A field's data type determines the kind of data that you can enter in the field, such as numbers or text, or a combination of numbers and text (also called alphanumeric data). Table 2–1 describes some common data types that you can use when you create a table.

TABLE 2–1 Common data types in Access /
DATA TYPE / DESCRIPTION /
Text / Accepts field values containing letters, numbers, spaces, and certain symbols such as an underscore (_). A Text field can store up to 255 characters and is used to store data such as names and addresses.
Number / Stores numbers. Number fields are usually values that will be used in calculations, such as multiplying the cost of an item by the number of items ordered to get a total. Number fields are sometimes used to restrict the entered field values to numbers.
Currency / Accepts monetary values and displays them with a dollar sign and decimal point.
Date/Time / Stores dates, times, or a combination of both.
Yes/No / Stores Yes/No, True/False, or On/Off values.
Lookup / Creates a field that lets you “look up” a value from another table or from a list of values entered by the user.
Memo / Accepts field values containing alphanumeric data, but can store field values containing up to 65,535 characters. Memo fields usually store long passages of text, such as detailed notes about a person or product.
Attachment / Stores graphics, sound, and other types of files as attachments.
Hyperlink / Stores a value that contains a hyperlink. Clicking the value activates the link and opens a Web page or other location, or addresses a message to an e-mail address.
Calculated / Opens the Expression Builder dialog box, which lets you specify fields and operators to use in calculations. The result of the calculation appears as the field's value, and determines the field's actual data type.
AutoNumber / Adds a unique numeric field value to each record in a table. AutoNumber fields are often used for primary key fields.

TABLE 2–1 Common data types in Access

Setting a Field's Data Type and Name in Datasheet View

When you create a table in Datasheet view, clicking the Click to Add field selector opens the list of data types show in Figure 2–4 and described in Table 2–1. After clicking the desired data type in the list, the list closes and the default field name is selected, so you can type the field name used in your table design. The field name is added to the field selector after you press Tab or Enter. After you have added all of the fields to your table, the last column in the table contains the Click to Add field selector in case you need to add another field later.

EXTRA FOR EXPERTS

You can also use a Quick Start selection to add commonly used fields to an existing table, such as the fields that store the street, city, state, and zip code for a person's address. In the Add & Delete group on the Fields tab, click the More Fields button, scroll down to the Quick Start section, and then click a Quick Start selection to add fields to the table.

FIGURE 2–4 Creating a new field

Step-by-Step 2.3

1. In the datasheet, click the Click to Add field selector. Figure 2–4 shows the list that opens.

2. In the list, click Text. The list closes, the field's data type is set to Text, and the default field name Field1 is selected in the field selector.

3. Type First Name.

4. Press Tab. The field name changes to First Name. The Click to Add list opens for the next field in the table.

5. In the list, click Text, type Last Name, and then press Tab. The third field's name and data type are set and the Click to Add list opens for the fourth field.

TIP

You can add field to a table quickly by typing the first letter of the data type name after the Click to Add list opens. For example, typing the letter “t” selects the Text data type, closes the list, and selects the field name.

6. In the list, click Text, type Phone, and then press Tab.

7. In the list for the fifth field, click Date & Time, type Appointment Date, and then press Tab.

8. In the list for the sixth field, click Number, type Job Number, and then press Tab.

9. In the list for the seventh field, click Text, type Notes, and then press the down key. Leave the table open for the next Step-by-Step.

Entering Records in Datasheet View

The First Name, Last Name, Phone, and Notes fields are Text fields that will contain alphanumeric data with less than 255 characters. The Appointment Date field is a Date/Time field that will store dates. To make sure that all job numbers contain only digits, the Job Numbers field has the Number data type. Figure 2–5 shows the table after creating all the fields.

FIGURE 2–5 Table after creating all the fields

When Access created the table, it created the ID field and assigned it the AutoNumber data type. When you enter a new record in the table, you do not need to type a value in this field. The value (New) appears in the ID field. After you enter a field value in the record, Access changes the value (New) to a unique value in the ID field automatically.

Step-by-Step 2.4

1. With the ID field for the first record active, press Tab.

2. In the First Name field, type Adam. Press Tab.

3. In the Last Name field, type Hoover. Press Tab.

4. In the Phone field, type 505-555-7844. Press Tab.

5. In the Appointment Date field, type 9/22/2013. Press Tab.

6. In the Job Number field, type 5492. Press Tab. The insertion point is in the Notes field. Leave the table open for the next Step-by-Step.

Changing a Field's Data Type in Datasheet View

When you need to change the data type for a selected field, you can do so by clicking the Data Type arrow in the Formatting group on the Fields tab. The Notes field has the Text data type, but it needs to use the Memo data type. You can click the Data Type arrow in the Formatting group to display a list of data types for fields, as shown in Figure 2–6. Clicking a data type in the list changes the data type for the current field and also closes the list.

FIGURE 2–6 Data Type list with the Notes field selected

Step-by-Step 2.5

1. With the Notes field for the first record active, click the Data Type arrow in the Formatting group on the Fields tab. Figure 2–6 shows the list that opens.

2. In the Data Type list, click Memo. The Notes field now uses the Memo data type.

3. Press Tab. The first record is complete, and the ID field for the second record is selected. The value “(New)” indicates that this field uses the AutoNumber data type.

4. Use Figure 2–7 to enter the remaining records in the table. Leave the table open for the next Step-by-Step.

EXTRA FOR EXPERTS