Papa_
EX19_AC_CH08_GRADER_CAP_AS
.zip
Exp19_Access_Ch08_Cap – Performance 1.0
Exp19_Access_Ch08_Cap – Performance 1.0
Access Chapter 8 Capstone – Performance
EX19_AC_CH08_GRADER_CAP_AS
Project Description:
You have been asked to modify an Employees database that tracks employee data, company locations, and performance and bonus information.You will import a spreadsheet from Excel and a text file into the database.You will add a Hyperlink field that will store a URL for each city where the company has a location.You will also add an Attachment field that will store employee photos.You will then use imported data to enhance a query in the database, create a report, and then export it to a PDF document.
Start Access. Open the downloaded Access file named Exp19_Access_Ch8_Cap_Performance.accdb. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files.
Create a new table in the database by importing the downloaded workbook named a08c2Location.xlsx. Use the first row of the worksheet as column headings, set LocationID as the primary key, and import the table as Location. Accept all other default options. Do not save the import steps.
Create a table in the database by importing the downloaded text file named a08c2Titles.txt. Use the first row of the file as field names, TitleID as the primary key, and name the table Titles. Accept all other default options. Do not save the import steps.
In the Employees table, add an Attachment field named Photo as the last field. Save the table.
Add the downloaded image file KFleming for EmployeeID 3 to the Employees table.
Add the downloaded image file PBrumbaugh for EmployeeID 17 to the table. Close the table.
In the Location table, add a Hyperlink field named CityInfo as the last field. Save the table.
Add the URL http://en.wikipedia.org/wiki/Atlanta as the CityInfo value for Atlanta in the Location table.
Add the URL http://en.wikipedia.org/wiki/Pittsburgh as the CityInfo value for Pittsburgh. Close the table.
Add the Location and Titles tables to the Relationships window. Join Location and Employees by their common field, enforcing referential integrity. Join Titles and Employees by their common field, enforcing referential integrity.
Open the Performance query in Design view (right-click the query in the Navigation Pane, and select Design View). Add the newly imported Titles table to the query window. Drag and drop the Title field from the Titles table over the Performance field in the query design grid so that it is placed to the left of the Performance field. The query calculates raises for the employees based on data in the Titles table.
Add the Photo field from the Employees table to the last position in the query. Run the query.
Set the criteria in the query so that only employees on probation will display and then run the query. Note that these employees will receive no bonus. Modify the criteria to display employees who are not on probation. Employees whose performance is good or excellent will receive a bonus. Run, save, and then close the query.
Create a simple report based on the Performance query. In Layout view, delete the Probation and PercentIncrease fields. Adjust the column widths so that they all fit within the page, but the data is still visible.
In Design view, move the page numbering control to the left so that its right edge is just inside the 7.5-inch mark on the horizontal ruler. Drag the right edge of the report to the left so that it is just inside the 8-inch mark on the ruler. View the report in Print Preview; the report should consist of two pages.
Modify the report title as Performance Reviews and delete the logo to the left of the title.
Save the report as Employees in Good Standing and close the report. Export the report as a PDF document using the same name and publish the report. Open the report in your reader program and close the report. Do not save the export steps.
Open the Performance query in Design view. Add the newly imported Location table to the query window. Drag and drop the Location field from the Location table over the LastName field in the query design grid so that it is in the first field position. Sort the records by Location in ascending order. Run, save, and then close the query.
Close all database objects. Close the database and then exit Access. Submit the database as directed.
EX19_AC_CH08_GRADER_CAP_AS_Instructions x
Grader – Instructions Access 2019 Project
Exp19_Access_Ch08_Cap – Performance 1.0
Project Description:
You have been asked to modify an Employees database that tracks employee data, company locations, and performance and bonus information.You will import a spreadsheet from Excel and a text file into the database.You will add a Hyperlink field that will store a URL for each city where the company has a location.You will also add an Attachment field that will store employee photos.You will then use imported data to enhance a query in the database, create a report, and then export it to a PDF document.
Steps to Perform:
Step
Instructions
Points Possible
1
Start Access. Open the downloaded Access file named
Exp19_Access_Ch8_Cap_Performance.accdb. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files.
0
2
Create a new table in the database by importing the downloaded workbook named
a08c2Location.xlsx. Use the first row of the worksheet as column headings, set LocationID as the primary key, and import the table as
Location. Accept all other default options. Do not save the import steps.
8
3
Create a table in the database by importing the downloaded text file named
a08c2Titles.txt. Use the first row of the file as field names, TitleID as the primary key, and name the table
Titles. Accept all other default options. Do not save the import steps.
8
4
In the Employees table, add an Attachment field named
Photo as the last field. Save the table.
8
5
Add the downloaded image file
KFleming for EmployeeID 3 to the Employees table.
4
6
Add the downloaded image file
PBrumbaugh for EmployeeID 17 to the table. Close the table.
4
7
In the Location table, add a Hyperlink field named
CityInfo as the last field. Save the table.
8
8
Add the URL
http://en.wikipedia.org/wiki/Atlanta as the CityInfo value for Atlanta in the Location table.
4
9
Add the URL
http://en.wikipedia.org/wiki/Pittsburgh as the CityInfo value for Pittsburgh. Close the table.
4
10
Add the Location and Titles tables to the Relationships window. Join Location and Employees by their common field, enforcing referential integrity. Join Titles and Employees by their common field, enforcing referential integrity.
6
11
Open the Performance query in Design view (right-click the query in the Navigation Pane, and select Design View). Add the newly imported Titles table to the query window. Drag and drop the Title field from the Titles table over the Performance field in the query design grid so that it is placed to the left of the Performance field. The query calculates raises for the employees based on data in the Titles table.
6
12
Add the Photo field from the Employees table to the last position in the query. Run the query.
6
13
Set the criteria in the query so that only employees on probation will display and then run the query. Note that these employees will receive no bonus. Modify the criteria to display employees who are not on probation. Employees whose performance is good or excellent will receive a bonus. Run, save, and then close the query.
6
14
Create a simple report based on the Performance query. In Layout view, delete the Probation and PercentIncrease fields. Adjust the column widths so that they all fit within the page, but the data is still visible.
8
15
In Design view, move the page numbering control to the left so that its right edge is just inside the 7.5-inch mark on the horizontal ruler. Drag the right edge of the report to the left so that it is just inside the 8-inch mark on the ruler. View the report in Print Preview; the report should consist of two pages.
4
16
Modify the report title as
Performance Reviews and delete the logo to the left of the title.
4
17
Save the report as
Employees in Good Standing and close the report. Export the report as a PDF document using the same name and publish the report. Open the report in your reader program and close the report. Do not save the export steps.
4
18
Open the Performance query in Design view. Add the newly imported Location table to the query window. Drag and drop the Location field from the Location table over the LastName field in the query design grid so that it is in the first field position. Sort the records by Location in ascending order. Run, save, and then close the query.
8
19
Close all database objects. Close the database and then exit Access. Submit the database as directed.
0
Total Points
100
Created On: 07/11/2019 1 Exp19_Access_Ch08_Cap – Performance 1.0
Papa_Exp19_Access_Ch8_Cap_Performance.accdb
EmployeeID
LastName
FirstName
Gender
Title
Salary
Location
Performance
HireDate
Probation
3
Fleming
Karem
F
T01
¤ 41,100.00
L01
Average
2016-12-12
false
9
Titley
David
M
T01
¤ 40,200.00
L01
Good
2019-01-18
false
17
Brumbaugh
Paige
F
T02
¤ 57,500.00
L01
Average
2016-12-22
false
19
Harrison
Jenifer
F
T01
¤ 44,800.00
L01
Excellent
2018-10-16
false
21
Sell
Mike
M
T01
¤ 43,500.00
L01
Excellent
2013-10-06
false
27
Barnes
Jeb
M
T02
¤ 56,900.00
L02
Excellent
2018-09-05
false
28
Michael
Bea
F
T01
¤ 32,000.00
L02
Poor
2019-03-23
true
36
Dill
Marjorie
F
T01
¤ 40,600.00
L02
Excellent
2016-02-05
false
38
Ronald
Margaret
F
T01
¤ 38,900.00
L02
Excellent
2016-09-24
false
40
Freyre
Julianne
F
T01
¤ 44,000.00
L02
Average
2018-09-04
false
73
Payne
Reggie
M
T01
¤ 40,700.00
L04
Good
2015-04-21
false
76
Felsher
Laura
F
T01
¤ 35,750.00
L04
Excellent
2017-01-13
false
79
Ray
Clay
M
T01
¤ 39,500.00
L04
Excellent
2018-06-30
false
82
Guiterrez
Joan
F
T01
¤ 39,000.00
L04
Average
2013-11-08
false
132
Smith
Johnny
M
T01
¤ 46,600.00
L07
Average
2016-04-16
false
133
Schultz
Judge
M
T02
¤ 50,750.00
L07
Poor
2019-11-11
true
134
Jefferson
Debbie
F
T01
¤ 36,700.00
L07
Good
2019-03-15
false
135
Everett
Kyle
M
T01
¤ 39,300.00
L07
Excellent
2018-07-06
false
140
Munroe
Alexandra
F
T01
¤ 41,400.00
L07
Average
2017-09-09
false
142
Thomas
Kenny
M
T01
¤ 42,400.00
L07
Excellent
2013-09-29
false
290
Sotolongo
Cristi
F
T03
¤ 24,000.00
L04
Average
2018-06-02
false
ID
mSysRowId
1
SELECT Employees.LastName, Employees.FirstName, Employees.Performance, Employees.Salary, [Salary]*[Raise]+[Salary] AS NewSalary, IIf([Performance]=”Excellent”,0.05*[Salary],IIf([Performance]=”Good”,0.025*[Salary],0)) AS Bonus, Employees.Probation, (([NewSalary]+[Bonus])-[Salary])/[Salary] AS PercentIncrease, Employees.HireDate
FROM Employees;
a08c2Titles.txt
TitleID,Title,Description,EducationRequired,MinimumSalary,MaximumSalary,Raise
T01,Account Rep,Marketing,Four-year degree,$25000,$75000,.03
T02,Manager,Supervisory,Four-year degree,$50000,$150000,.04
T03,Trainee,Entry-level,Two-year degree,$18000,$25000,.035
T04,SeniorRep,Sales Manager,Four-Year Degree,$45000,$150000,.04
KFleming
PBrumbaugh
a08c2Location.xlsx
Location
LocationID
Location
Address
State
Zipcode
OfficePhone
CostOfLiving
L01
Atlanta
450 Peachtree Road
GA
30316
(404) 333-5555
97.40
L02
Boston
3 Commons Blvd
MA
02190
(617) 123-4444
135.40
L03
Pittsburgh
15 Forbes Avenue
PA
15219
(412) 555-1111
93.00
L04
Kansas City
3750 River Road
MO
64101
(816) 555-9998
94.20
L05
New York
3495 Fifth Avenue, Suite 33998
NY
10029
(212) 555-6654
204.30
L06
San Francisco
44 Montgomery Street
CA
94102
(415) 555-3737
171.40
L07
Chicago
500 Loop Highway
IL
60620
(312) 444-6666
117.40
L08
Miami
1417 NE 136th St
FL
33018
(305) 555-0098
116.20
L09
Phoenix
4535 N. 23rd Ave
AZ
85001
(602) 555-0098
100.40
L10
Orlando
1000 Kirkman Road
FL
32801
(407) 555-5555
107.30
L11
Los Angeles
1000 Rodeo Drive
CA
90210
(213) 666-6666
159.00
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
