LSP 121-405
Homework #2
Due: Thursday, October 1st, 2009 by 11:59 pm
Part A
You will create a database named Eval-Data with two tables to store employee performance evaluation information. The first table, named Employees, has the following fields and data.
ID Last Name First Name Address City Phone Date Hired
100 <your last> <your first> 1 E. Jackson Chicago 362-5000 9/10/09
101 Smith Sue 420 Oak River Forest 366-3333 2/20/93
102 Jones Bob 607 Park River Forest 366-2222 4/20/94
103 Hart Sally 815 Forest Oak Park 422-4444 7/10/95
104 Ford Joe 1020 Forest Oak Park 422-5566 8/3/92
105 Kingsfield Mary 27 Main River Forest 366-5555 8/19/92
106 Abba Bill 365 John Oak Park 395-3456 2/2/92
Open a new database, create the Employees table in Access and type in the data above. You should type your last name where it says “<your last>” and type your first name where it says “<your first>”. Make the following fields Index fields: Last Name, City, and Date Hired. Make the ID the primary key. Check your typing for accuracy and correct any errors you find. The Phone field should be Text and the Date Hired field should be Date/Time.
Open a Word document for your HW #2 answers and, while in Datasheet View, copy and paste the Employees table records into this Word document under Part A.
Create and enter data for the second table, named Evaluations, as shown here:
EmplID Evaluation Score Evaluation Date Evaluation Action
100 90 9/12/09 recommend
100 97 9/24/09 promote
101 82 3/4/04 none
101 87 3/9/06 review
101 90 2/28/03 none
101 91 3/5/06 recommend
102 94 3/5/06 recommend
103 84 2/27/03 none
103 87 3/5/06 none
104 74 3/5/06 review
105 98 2/28/03 recommend
105 99 3/7/06 promote
106 84 3/6/06 recommend
Once you have checked that your data is correctly entered, while in Datasheet View mode, copy and paste the records of the Evaluations table into your HW 2 answers document.
Set up a 1:M relationship between the Employees table and the Evaluations table with EmplID as the foreign key.
Part B
Use the above tables to perform each of the following six queries. For each query, tell me (in your answers document) what you entered for all Criteria fields in your query design, then go to Datasheet View and copy and paste the resulting records into your answers document. If any of these queries produce duplicate rows (i.e. the exact same row of values appears multiple time in the results), you should eliminate the duplicates by using a “Group By” value in the Totals line of the query design before copying to your answers.
1. Show First Name, Last Name, Address and Date Hired in Last Name ascending order for people living in River Forest.
2. Show ID, Last Name, and Date Hired for those living in River Forest and hired before 1/1/94.
3. Show Last Name and Phone in Last Name ascending order for those people living in River Forest or Oak Park.
4. Show Last Name, First Name, Date Hired, and Phone in ID-ascending order for people with a Last Name beginning with a letter greater than G.
5. Show the Last Name, First Name, Evaluation Date and Evaluation Action for those who received an Evaluation Action of “recommend” or “promote”.
6. Show the Last Name, First Name, and ID of those that were evaluated on or after 3/1/05
Part C
Create a form which looks, approximately, like the following for employee Bob Jones:
ID: 102
Last Name: Jones Date Hired: 4/20/94
First Name: Bob
Street Address: 607 Park SUBFORM
City: River Forest ID Eval Score Eval Date Action
State: IL 102 94 3/5/06 recommend
Phone: 366-2222
Note that State is not a field in the record, so, for the form you need to make this a simple Label field with the value State: IL
When finished, in Form View, go to the record containing your name (ID=100) and use Alt-Printscreen to paste a copy of the screen showing this form containing data for ID=100 into your homework file.
Part D
Create a report using query #5 from Part B above. Include all fields from the query. Group the records in the report based on their Evaluation Action. Within this grouping, sort records by ascending Evaluation Date. When the report is ready, go to Print Preview View and click “2 Pages” at the top to view 2 pages of your report, then use Alt-Printscreen to copy and paste this screen showing two pages of the finished report into your homework answer file. Save your homework answer file and submit it on the Course OnLine web site for HW #2.