The Johnson Insurance Agency is a company with offices located across the country. You are the human resource

Instructions

The Johnson Insurance Agency is a company with offices located across the country. You are the human resource director for the company. Your office is located in the home office in Miami. Each employee receives an annual performance review. The review determines employee eligibility for salary increases and the performance bonus. The employee data are stored in an Access database. This database is used by the Human Resource department to monitor and maintain employee records.

Hint: 1) Create three tables. (Employee, Location and Title)

1. I have provided ten records for each table. You are to create five additional records for each tables. Include your name as one of the records to your database. You will have a total of fifteen records for each tables.

Employees Table
EmployeeID	LastName	FirstName	Gender	Salary	Evaluation	StartDate	Shift
001	Hill	Tom	M	$31,200	Excellent	3/3/2005	F
043	Williams	Susie	F	35,3200	Excellent	10/8/1099	S
767	Sutton	Mary	F	39,540	Good	5/21/2001	F
888	Ray	Jerry	M	36,767	Poor	3/22/2012	S
145	Madison	Whitney	F	50,198	Excellent	10/6/1998	F
203	Jackson	Harry	M	55,000	Good	11/2/9881	F
545	Roberts	Glen	M	46,980	Excellent	3/13/1980	F
322	Norton	Bobby	M	61,439	Poor	4/6/1979	S
654	Henderson	Carolyn	F	71,000	Excellent	6/2/1977	F
989	Pine	Frank	M	44,232	Good	9/3/1978	S

Field Name Data Type

Employee ID	Text
LastName	Text
FirstName	Text
Gender	Text
Salary	Currency
Evaluation	Text
StartDate	Date/Time
Shift	Text

Location Table
EmployeeID	LocationID	Address	City	State	Zip Code	OfficePhone
001	001Atlanta	450 Peachtree Road	Los Angeles	CA	30316	404) 333-5555
043	002Miami	3 Mills Avenue	Chesterfield	CA	55555	(222) 111-4444
767	003Chicago	54 Lemon Drive	Laurel	MD	20614	(111) 230-3444
888	005California	3 Mills Avenue	Minneapolis	MN	92122	(345) 222-5555
145	006Washington	333 Plains Field	Irvington	NJ	44444	(999) 234-2222
203	007NewYork	76 Taylor Road	Chester	VA	75454	(323) 432-5454
545	001Atlanta	90 Knox Court	Wilmington	NC	22222	(111) 777-9999
322	002Miami	10 Marlboro Road	Wilson	NC	88888	(333) 545-9854
654	005California	55 Brooks Drive	Chesterfield	CA	43211	(326) 065-1223
989	001Atlanta	700 Mega Lane	Hartford	CO	33434	(976) 222-1111

Field Name Data Type

Employee ID	Text
Location ID	Text
Address	Text
City	Text
State	Text
Zip Code	Number
Office Phone	Number

TitlesTable
EmployeeID	Title	MinimumSalary	MaximumSalary	2014Increase
001	Account Rep	$25,000	$75,000	3.00%
043	Human Resourced	40,000	90,000	2.5%
767	Sales/Marketing	30,000	77.000	1.5%
888	Marketing Director	40,000	80.000	5%
145	Executive Administration	30,000	60,000	2%
203	Claims	50,00	80,000	4.0%
545	Agent	40,000	75,00	3.3%
322	Customer Service	55,989	78,987	0%
654	Accounting	60,000	90,000	3.0%
989	Education Training	35,00	67,908	5.0%

Field Name Data Type

Employee ID	Text
Title	Text
Minimum Salary	Currency
Maximum Salary	Currency
2014 Increase	Number

Instructions

1) Sort the Employee Table alphabetically by the employee’s last name.

2) Create a query is to identify the employees who have an Evaluation rating of Excellent for the year. Include the fields from following table in the Query. Name the query Excellent.

First Name	Employee Table
Last Name	Employee Table
Evaluation	Employee Table
Address	Location Table
City	Location Table
Zip	Location Table

1. Create a query is to identify the employees whose salary is under $40,000 per year. The query should include Last Name, First Name and Salary. Name the query Salary.

2. In the Employee Table under Field Properties add an appropriate field size for Shift since values used will be either F or S (for First or Second Shift). Format the field so that even if the user enters the letter in lowercase that it will be converted to uppercase.

3. In the Employee Table under Field Properties add an appropriate field size for Gender since values used will be either M or F (Male or Female). Format the field so that even if the user enters the letter in lowercase that it will be converted to uppercase.

4. Create a Form from the Employees table. Name the form Employees. Include all the fields from the table. Format the table properly so that all the fields on the page will show and it will show one record per page.

Employee ID	Employee Table
Last Name	Employee Table
First Name	Employee Table
Gender	Employee Table
Salary	Employee Table
Evaluation	Employee Table
Start Date	Employee Table
Shift	Employee Table

8) Create a report and add the following fields from the tables below: Name the Report 2014 Increase. Format the table properly and all the information shows in full and is on one page.

First Name	Employee Table
Last Name	Employee Table
Gender	Employee Table
Start Date	Employee Table
Title	TitlesTable
2014 Increase	TitlesTable