The Johnson Insurance Agency is a company with offices located across the country

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