Microsoft Access Projects Worksheet

PhoneAreaID FirstName
1 Meda
(505) 555-1812
1 Christy
(505) 555-4231
(505) 555-7810
1 Jason
(505) 555-6110
4 Paul
4 Juanita
(505) 555-2837
5 Susan
(505) 555-5215
5 Jay
(505) 555-8343
5 Kate
(505) 555-7421
4 Gabriel
(505) 555-8499
LastName
Rodate
Istas
Niese
Medina
Gomez
Brock
Groth
Munson
Jones
StreetAddress
4215 Lupita Rd
17324 Camino Dr
1542E Abeja Ct
1200 Three Trees Rd
5 Park Rd
923 Clark St
622 E. High St
4476 Wilson Blvd
1452 S. Bainbridge Dr
City
Santa Fe
Santa Fe
Santa Fe
Santa Fe
Santa Fe
Santa Fe
Santa Fe
Santa Fe
Santa Fe
State
NM
NM
NM
NM
NM
NM
NM
NM
NM
ZipCode
87505
87501
87509
87508
87500
87501
87511
87513
87513
HireDate
3/14/17
8/12/18
8/12/18
2/1/17
9/30/18
5/15/17
3/16/18
6/4/18
3/10/18
JobTitle
Spa Manager
Therapist
Massage Therapist
Coffee Shop Manager
Barista
Gift Shop Manager
Sales Associate
Sales Associate
Barista
ScheduleDay
1/2/2018
1/2/2018
1/3/2018
1/3/2018
1/2/2018
1/2/2018
1/2/2018
1/3/2018
1/3/2018
1/2/2018
1/2/2018
StartTime
8am
6am
8am
6am
8am
8am
10am
8am
10am
8am
2pm
HoursScheduled EmployeeID
8,0
4
8,0
5
8,0
4
8,0
5
8,0
1
8,0
2
8,0
3
8,0
2
8,0
3
8,0
7
8,0
8
AreaName
Silver Moon Lounge
Indigo5
Terra Cotta Brew
Painted Treasures
Event Planning
Red Bluff Golf Club
Grader – Instructions
Access 2016 Project
YO16_AC_BU01_GRADER_PS4_AS – Hotel Staff Database
Project Description:
The hotel general manager needs a human resource database to store information on employees, the areas they work in, and
the hours they are scheduled to work. The database will have three new tables: tblHotelAreas, tblEmployee, and tblSchedule.
Steps to Perform:
Points
Possible
Step
Instructions
1
Start Access. Open the downloaded file named a01_grader_a2_HotelStaff.accdb. Save the file
with the name a01_grader_a2_HotelStaff_LastFirst, using your last and first name. If
necessary, enable the content.
0
2
Create a new table in Design view. This table will store employees. Add the following fields
and descriptions.
6
Field Name
Data Type
AutoNumber
A unique identifier
for the employee (primary key)
AreaID
Number
The area ID from tblHotelAreas
(foreign key)
EmployeeID
Description
3
Add the following nine fields to the table (in this order): FirstName, LastName,
StreetAddress, City, State, ZipCode, Phone, HireDate, and JobTitle. Change the data
type for the HireDate field to Date/Time. Accept Short Text as the data type for the remaining
fields. Change the field sizes of the FirstName, LastName, City, and JobTitle fields to 30;
change the field size of StreetAddress to 40; change the field size of State to 2; change the
ZipCode field size to 5; change the field size of Phone to 14; and change the field format of
HireDate to Short Date. Assign the best field as the primary key and then save the new table
as tblEmployee.
10
4
Define an input mask for the phone number. Use a mask that will show phone numbers as
(555) 555-5555 with a placeholder of “_” and save with the symbols in the mask. Save and
close the table.
4
5
Import the downloaded workbook a01_grader_a2_HotelEmp.xlsx from Excel using the
Employee worksheet, appending it to tblEmployee. Do not save the import steps.
6
6
Import the tblHotelAreas table from the downloaded a01_grader_a2_HotelAreas.accdb Access
database and create a new table. Close the table.
6
7
Import the Area worksheet in the downloaded Excel file a01_grader_a2_HotelEmp.xlsx,
appending it to tblHotelsAreas. Do not save the import steps.
6
8
Create a new table in Design view. This table will store information about an employee’s
schedule. Add ScheduleID as the first field in the table, set the data type to AutoNumber.
Add the following four fields to the table (in this order): ScheduleDay, StartTime,
HoursScheduled, and EmployeeID. Change the data types for the ScheduleDay and
StartTime fields to Date/Time. Change the data types for the HoursScheduled and
EmployeeID fields to Number.
5
Created On: 07/05/2019
1
YO16_AC_BU01_GRADER_PS4_AS – Hotel Staff Database
Grader – Instructions
Step
Access 2016 Project
Points
Possible
Instructions
9
Change the format for ScheduleDay to Short Date; change the format for StartTime to
Medium Time; and change the field size for HoursScheduled to Integer. Assign the best field
as the primary key, and then save the new table as tblSchedule. Close the table.
5
10
Import the Schedule worksheet in the downloaded Excel file a01_grader_a2_HotelEmp.xlsx,
appending it to tblSchedule. Do not save the import steps.
4
11
Open the Relationships window. Add tblSchedule, tblEmployee, and tblHotelAreas to the
window. Create a one-to-many relationship between EmployeeID in tblEmployee and
EmployeeID in tblSchedule. Enforce referential integrity. Do not cascade update or cascade
delete.
8
12
Create a one-to-many relationship between AreaID in tblHotelAreas and AreaID in
tblEmployees. Enforce referential integrity. Do not cascade update or cascade delete. Save the
changes. Create a relationship report accepting the default name. Close the report and close
the Relationships window.
8
13
Use the Form tool to create a form for tblEmployee, and then save it as
frmEmployeeSchedule. View the form in Form View. Locate the record for the employee
named Gabriel Jones. Add a new record to work on 1/3/2018, starting at 8 AM and working
for 8 hours. Close the form.
7
14
Use the Simple Query Wizard to create a query. The query results should list AreaID and
AreaName from tblHotelAreas, FirstName and LastName from tblEmployee, and ScheduleDay,
StartTime, and HoursScheduled from tblSchedule (in that order). Save your query as
qryCoffeeShopSchedule and then view the query results.
7
15
Switch to Design view, and then enter criteria that will only return records with AreaID 4 in
the results. Run, and then save the query. Close the query.
4
16
Create a report using the Report Wizard using fields from qryCoffeeShopSchedule. Add the
fields FirstName, LastName, ScheduleDay, StartTime, and HoursScheduled (in that order).
View by tblSchedule, and group by ScheduleDay. Click Grouping Options and change Group
intervals to Day. Sort in ascending order first by StartTime and then by LastName, and change
to Landscape orientation. Name your report rptCoffeeShopSchedule and then finish the
wizard.
10
17
View the report in Layout view. Modify the report title to be Coffee Shop Schedule. Save,
and then close the report.
4
18
Close the database, and then exit Access. Submit the database as directed.
0
Total Points
Created On: 07/05/2019
2
100
YO16_AC_BU01_GRADER_PS4_AS – Hotel Staff Database
Grader – Instructions
Access 2016 Project
YO16_AC_BU02_GRADER_PS2_HW – Painted Treasures
Project Description:
The Painted Treasures Gift Shop sells many products for the resort patrons including jewelry, clothing, and spa products. A
database has been started to keep track of the customers, purchases, and products. There are no reports, forms, or queries
built yet, so the staff feels like the database is not easy to use. You will create reports, forms and queries to help the staff
better manage the data in the database.
Steps to Perform:
Points
Possible
Step
Instructions
1
Start Access. Open the downloaded file named a02_grader_h2_Giftshop.accdb. Save the file
with the name a02_grader_h2_Giftshop_LastFirst, replacing LastFirst with your name. In
the Security Warning bar, click Enable Content.
0
2
Use the Report Wizard to create a report showing customers and their purchases. Include
LastName, FirstName, PurchaseDate, Quantity, ProductDescription, Category, and Price.
Accept the default view and add no additional grouping. Sort by Category and then by
ProductDescription, both in ascending order. Save the report as
rptCustomerPurchases_aStudent.
5
3
Change to Layout view, click on the Purchase Date label and textbox and open the Format tab
on the Property Sheet. Type 1.1″ in the Width, and 2.5” in the Left. Close the Property
Sheet. Change the report title to Customer Purchases. Apply the downloaded theme
a02_grader_h2_Giftshop.thmx to all objects in the database. Add Created by a Student to
the report footer. Save and close the report.
8
4
Use the Form Wizard to create a form for inputting customers. Select all fields from
tblCustomer and accept all defaults. Name the form frmCustomerInput_aStudent. Add
Created by a Student to the form footer. Change the form title to Customer Input.
5
5
Change to Layout view. Click the label and textbox for CustomerID and delete them. Adjust
the right margin of LastName to be as narrow as FirstName (Width = 1.5″ in the Property
Sheet). Adjust the right margin of StreetAddress to be as narrow as City. (Width = 1.8646″
in the Property Sheet.) Change the Shape Fill for the LastName and FirstName textboxes to be
Red, Accent 2 Lighter 80%.
8
6
Insert the downloaded a02_grader_h2_Giftshop.jpg into the form. In the Property Sheet, on
the Format tab, change the Width to 2.5″ and the Height to 1.1″. Move the image to the
right of the customer fields so that it aligns with the top of LastName. Save the form.
5
7
In Form view, change the first record to have a LastName of Student and a FirstName of
Access. Close the form.
3
8
Use the appropriate query wizard to create a query to see which customers made multiple
purchases using all the fields in tblPurchase (hint: this is a Find Duplicates Query). Show all
fields available. Save the query as qryMultiplePurchases_aStudent. In Design view, sort
in ascending order on CustomerID and PurchaseID. Save and close the query.
6
9
Use the appropriate query wizard to create a query to see if any customers have made no
purchases. Show LastName, FirstName and ResortHotelRoom in that order. Save the query as
qryCustomerWithoutPurchase_aStudent. Close the query.
5
Created On: 07/05/2019
1
YO16_AC_BU02_GRADER_PS2_HW – Painted Treasures
Grader – Instructions
Access 2016 Project
Points
Possible
Step
Instructions
10
Use Query Design to create a query to find customers who made purchases but have no
ResortHotelRoom. Include LastName, FirstName, PurchaseDate, and ResortHotelRoom in that
order. Do not show ResortHotelRoom in the results. Sort in ascending order by LastName and
FirstName. In Datasheet view Autofit all column widths. Save the query as
qryNonGuestPurchases_aStudent. Close the query.
8
11
Use Query Design to create a query to show customers that have purchased Indigo5 or Spa
category products in January 2018. Include LastName, FirstName, PurchaseDate, and
Category in that order. In Design view, sort by LastName and FirstName, both in ascending
order. Autofit the fields. Save the query as qrySpaIndigoJanuary_aStudent. Close the
query.
10
12
Use Query Design to create a query to calculate the extended amount for each product
purchased. Include PurchaseID, PurchaseDate, PurchaseLine, Quantity, ProductDescription,
and Price in that order. Save the query as qryExtendedAmount_aStudent. Add a new
calculated field to the query to calculate each product’s extended amount due based on the
quantity and the price. Name the new field ExtendedAmount. Sort the query by PurchaseID
in ascending order. In Datasheet view Autofit all column widths. Save the changes and close
the query.
12
13
Use the Report Wizard to create a report showing all complete purchases. Include all the fields
from qryExtendedAmount_aStudent. Group by PurchaseID. Sort by PurchaseLine in ascending
order. Change Orientation to Landscape, accept all other default options, and name the report
rptPurchases_aStudent. Add Created by a Student to the report footer.
10
14
Change to Layout view. Change the report title to Purchase Report. Change the width of the
PurchaseID to 1″. Change the width of the Purchase Line column header and textbox in the
Property Sheet to 1″ and the left to 2.0″. Change the width of the PurchaseDate column
header and textbox to 1″.
5
15
Click ExtendedAmount and add a sum subtotal. Open the Property Sheet and change the
Format of the new control to Currency. Add a caption and change it to Total Purchase.
Repeat these revisions for the grand total control and change the caption to Grand Total
Purchases.
5
16
Change the subtotal Total Purchase caption and text box to bold with a Shape Fill of Red,
Accent 2 Lighter 80%. Change the grand total caption and text box to bold with a Shape Fill
of Red, Accent 2, Lighter 60%. Save and close the report.
5
17
Close all database objects. Close the database and then exit Access. Submit the database as
directed.
0
Total Points
Created On: 07/05/2019
2
100
YO16_AC_BU02_GRADER_PS2_HW – Painted Treasures
Painted Paradise
Painted Treasures Gift Shop