Objective 11. Use the Access Help System

Mastery Assessments

Project 1I. Computer Inventory

Objectives: 3. Create a New Database; 4. Create a New Table; 5. Add Records to a Table; 7. Create Table Relationships; 9. Print a Table; 10. Close and Save a Database.

In the following Mastery Assessment, you will create a new database to track data related to computer inventory for Lake Michigan City College. The Vendors table will keep track of information about the vendors the college uses for computer purchases. The Inventory table will record the computers, their locations, when they were put into service, and the vendor number for the company from which the computer was purchased. Your completed tables will look like the ones shown in Figure 1.61.

Figure 1.61.

For Project 1I, you will need the following file:

New Access database

You will save your database as

1I_Computer_Inventory_Firstname_Lastname


[Page 957]

1.

Start Access. Create a new database and store it in your chapter folder as 1I_Computer_Inventory_Firstname_Lastname

2.

Use the following information to create a new table to store data about the vendors used by the college for computer purchases, and use 50 as the text field size unless otherwise specified.

Field Name

Data Type

Field Size

Vendor ID

Text

4

Vendor Name

Text

 

Street Address

Text

 

City

Text

20

State

Text

2

Postal Code

Text

5

Phone Number

Text

15

Contact Person

Text

40

3.

Set the Vendor ID as the primary key for this table. Save the table as 1I Vendors Firstname Lastname Switch to Datasheet view and add the following records to the table.

Vendor ID

Vendor Name

Street Address

City

State

Postal Code

Phone Number

Contact Person

1201

Computer Warehouse

11511 Stone Blvd.

Chicago

IL

60611

(312) 555-0748

Debbie Lucero

1202

Computers Online

783 Hobson Way

Arlington Heights

IL

60005

(847) 555-0123

Bill Franklin

1203

Computer World

3572 Rivas Lane

Orland Park

IL

60462

(708) 555-9852

Peggy Sharp

4.

Resize all of the columns to accommodate their data and column headings. If you are submitting printed pages, display the Page Setup dialog box, change the page orientation to Landscape, and then print the table. Close the table, saving the changes to the layout.

5.

Create a second table with the following fields to record the information about the computers that have been purchased by the college.


[Page 958]

Field Name

Data Type

Description

Field Size

Inventory#

Text

 

10

Description

Text

  

Location

Text

Building and room number

 

Install Date

Date/Time

Date item placed in service

 

Vendor ID

Text

 

4

Cost

Currency

  

6.

Set Inventory# as the primary key. Save the table as 1I Inventory Firstname Lastname

7.

Populate the table with the following records:

Inventory#

Description

Location

Install Date

Vendor ID

Cost

S10

2800 Server

R303

2/15/05

1201

8900

D533

GX280 Desktop Computer

D101

3/18/05

1202

1200

D544

GX280 Desktop Computer

D101

1/10/05

1202

1200

D555

4700 Desktop Computer

K430

1/15/04

1203

1600

D301

8400 Desktop Computer

K430

7/21/04

1203

2200

N56

HP zv6000 series Notebook Computer

A210

2/10/05

1201

1500

N57

HP zv6000 series Notebook Computer

C401

2/10/05

1201

1500

8.

Resize all of the column widths to accommodate their data and column headings. Switch to Design view, and then redisplay the Datasheet viewthis will sort the records by primary key. If you are submitting printed pages, preview and print the table. Close the table, saving the changes to the layout.

9.

Create a relationship between the two tables based on the Vendor ID field and enforce referential integrity. Place the field list with the one side of the relationship on the left, and then expand and arrange both field lists to display all the fields and the entire table name. Create the Relationships report. If you are submitting printed pages, print the Relationships report. Save the report with the default name. Save the layout of the Relationships window.

10.

Close the database and close Access.

End

You have completed Project 1I


[Page 959]

Project 1J. Students

Objectives: 1. Rename a Database; 2. Start Access, Open an Existing Database, and View Database Objects; 4. Create a New Table; 6. Modify the Table Design; 7. Create Table Relationships; 8. Find and Edit Records in a Table; 9. Print a Table; 10. Close and Save a Database.

In the following Mastery Assessment, you will open and edit an existing database that stores student information at Lake Michigan City College. Then you will add another table to store records about the classes taken by specific students. The Student ID number will be used to create a relationship between the Student table and the Classes table. By using two tables, you do not have to repeat all of the student information each time a class is completed. Your completed database objects will look like the ones shown in Figure 1.62.

Figure 1.62.

For Project 1J, you will need the following file:

a01J_Students

You will save your database as

1J_Students_Firstname_Lastname


[Page 960]

1.

Open My Computer, and then from the student files that accompany this textbook, locate the file a01J_Students and copy it to your chapter folder. Rename the file 1J_Students_Firstname_Lastname Start Access and open the file you just renamed.

2.

Point to the Students table, right-click, click Rename, type 1J Students Firstname Lastname and press . Then, make the following changes to the existing records:

Change the address for Danesha Aglipay to 100 E Ann St Apt 201

Delete the record for Student# 20765, Jason Lederer.

3.

In Design view, following the Last Name field, add Email Address as a new field and accept Text as the Data Type. Add the following email addresses:

Student#

Email Address

10157

ZMonteith57@lmcc.edu

10776

JLee76@lmcc.edu

20336

AMoore36@lmcc.edu

21005

JHo05@lmcc.edu

4.

Using your own name, add a new record, and for the email address, use the same pattern as the other student email addresses:

Student#

First Name

Last Name

Email Address

Address

City

State

Postal Code

Phone Number

21990

Firstname

Lastname

FLastname90@lmcc.edu

548 Trillium Lane

Chicago

IL

60611

(312) 555-0022

5.

Adjust the column widths to accommodate the longest entry. If you are submitting printed pages, change the print orientation to Landscape and then print the table. Save the changes to the table layout and then close it.

6.

Create a new table with the following fields that will store records for classes that students have taken.

Field Name

Data Type

Field Size

Record#

AutoNumber

 

Student#

Text

5

Class ID

Text

10

Credit Hours

Number

 

Grade

Text

2

Date Completed

Date/Time

 


[Page 961]
This table will record the grades for each class that a student takes. By arranging the data in two tables, the students name and address information is recorded only oncein the 1J Students table. Because a single student can take many courses, this information is stored in a separate tablethe 1J Classes table. In the 1J Students table, the Student# is the primary key field that uniquely identifies each student. In the 1J Classes table, the Student# appears many times and thus is the foreign key field.

7.

Set Record# as the primary key field and save the table with the name 1J Classes Firstname Lastname

8.

Create a one-to-many relationship between the tables based on the Student# field, and enforce referential integrity. Place the field list with the one side of the relationship on the left and then expand and arrange the field lists to display all of the fields and the full table name. Create the Relationships report, saving it with the default name. If you are submitting printed pages, print the Relationships report. Save the layout of the Relationships window.

9.

Populate your 1J Classes Firstname Lastname table with the following records.

Student#

Class ID

Credit Hours

Grade

Date Completed

10157

CHD 280

3

B+

4/30/05

10157

HST 290

3

B

12/21/06

10998

HAC 290

4

B-

4/30/05

10998

MTH 250

3

B+

4/30/05

11059

ACC 101

3

C+

12/21/06

11059

BUS 250

3

B

4/28/06

12025

MKT 120

3

B

4/30/05

12025

BUS 150

3

B-

4/28/06

20201

MKT 120

3

A-

4/30/05

20201

BUS 150

3

B

4/30/05

10.

Adjust the column widths to accommodate the widest entry. If you are submitting printed pages, print the table. Close your 1J Classes table, close the database, and then close Access.

End

You have completed Project 1J

[Page 962]

Категории

© amp.flylib.com,