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

QUESTION

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)
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

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.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.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.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..0pt;=””>

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

 

ANSWER:

REQUEST HELP FROM A TUTOR