Objective 11. Use the Access Help System | Chapter One. Getting Started with Access Databases and Tables

Table of contents:

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]

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

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

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

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.

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

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

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

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.

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]

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.

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.

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

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

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.

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.

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

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.

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.