LSP 121

Homework #3

Due: June 25th

Part A

Create a database with two tables. The first table has the following fields and data:

IDLast NameFirst NameAddressCityPhoneDate Hired

101SmithSue420 OakRiver Forest366-33332/20/93

102JonesBob607 ParkRiver Forest366-22224/20/94

103HartSally815 ForestOak Park422-44447/10/95

104FordJoe1020 ForestOak Park422-55668/3/92

105KingsfieldMary27 MainRiver Forest366-55558/19/92

106AbbaBill365 JohnOak Park395-34562/2/92

Make the following fields Index fields: Last Name, City, and Date Hired. Make the ID the primary key.

Make sure there are no typos in the entered records. If there are, edit the records appropriately.

The Phone field should be Text and the Date Hired field should be Date/Time.

While in Datasheet View mode, copy and paste the records into a Word document.

The second table has the following fields and data:

IDEvaluation ScoreEvaluation DateEvaluation Action

101823/4/04none

101873/9/06review

101902/28/03none

101913/5/06recommend

102943/5/06recommend

103842/27/03none

103873/5/06none

104743/5/06review

105982/28/03recommend

105993/7/06promote

106843/6/06recommend

Make the field ID your foreign key.

While in Datasheet View mode, copy and paste the records into a Word document.

Part B

Using the above table perform each of the following queries. For each query, copy and paste the results into your Word document:

1. Print First Name, Last Name, Address and Date Hired in Last Name ascending order for people living in RiverForest.

2. Print ID, Last Name, and Date Hired for those living in RiverForest and hired before 1/1/94.

3. Print Last Name and Phone in Last Name ascending order for those people living in RiverForest or Oak Park.

4. Print Last Name, Date Hired, and Phone in Last Name ascending order for people living in RiverForest and with a Last Name beginning with a letter greater than M.

5. Print the Last Name and First Name for those who received an Evaluation Action of “recommend” or “promote”. (Save this query as you will need it in part D.)

6. Print the Last Name, First Name, and ID of those that were evaluated on or after 3/1/04.

Part C

Create a form which has the approximate following layout:

ID: 101

Last Name:SmithDate Hired: 2/20/93

FirstName:Sue

Street Address:420 OakSUBFORM

City:RiverForestID Eval ScoreEval Date Action

State:IL

Phone:(708)366-3333

Note that State is not a field in the record. Thus, for the form you need to make this a simple Labelfield with the value: State: IL

Using the Snipping Tool, copy your form with any record showing in the data fields.

Part D

Create a report using the fifth query from Part B. Include all fields from the query. Group the records in the report based on their promotion. Within the promotion grouping, sort each record alphabetically by Last Name. Include a copy of the finished report along with this homework.