Expert answer:I am working on chapter 9, but will need help with this ch.8 project filler. The problems are on word doc and will need to be done on the access doc.
pack_a04_grader_h1_hotelrevenue.zip
yo16_ac_bu04_grader_ps1_hw_instructions_dec_10.docx
Unformatted Attachment Preview
Office 2016 – myitlab:grader – Instructions
Your Office Access BU 04 Problem Solve 1
YO16_AC_BU04_GRADER_PS1_HW – Calculating Revenue and
Maintaining the Hotel Database
Project Description:
The area of Painted Paradise Golf Resort & Spa that generates the most revenue is the hotel. Guests may charge
anything from the resort to their room. Therefore, the hotel area must track all of the guests’ charges, such as
those from the spa, golf shop, gift shop, restaurants, movies, personal trainers, and sessions with golf
professionals. Hotel guests who use these services are eligible for a discount. You have been asked to create
useful information through the use of queries and have been given a scaled-down version of the hotel’s database
to use.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
Instructions
Points
Possible
1
Start Access. Open the downloaded Access file named a04_grader_h1_HotelRevenue.accdb.
Save the file with the name a04_grader_h1_HotelRevenue_LastFirst, using your last and
first name. If necessary, enable the content.
0
2
Create a query using the tblReservations table that calculates the total revenue for all room
reservations along with the total number of reservations grouped by room type.
• Add the RoomType field to the design grid.
• Create a calculated field named RoomRevenue that multiplies the RoomRate and the
NightsStay fields.
• Use the Total row to sum the RoomRevenue calculated field for each room type.
• Add the ReservationID field to the design grid and use the Total row to count the total
number of reservations for each room type.
• Rename the ReservationID field NumOfReservations.
• The query should include the following fields in the order listed: RoomType, RoomRevenue,
and NumOfReservations.
• Run the query, adjust the column widths as necessary, and then save the query as
qryRoomRev.
• Close the query.
10
3
Create a query that calculates gross sales revenue and total reservations, using the
tblReservations table.
• Create a calculated field named GrossRoomRev that multiplies the RoomRate and
NightsStay fields.
• Use the Total row to sum the GrossRoomRev calculated field.
• Include a new field named TotalReservations that counts the ReservationID field.
• If necessary, rearrange the order of the fields to be GrossRoomRev and then
TotalReservations.
• Run the query, adjust the column widths as necessary, and then save the query as
qryGrossRoomRev.
• Close the query.
10
4
Create a query that will use the fields in the qryRoomRev and qryGrossRoomRev queries to
display the room revenue and number of reservations along with the percentage of sales
revenue and percentage of reservations for each room type.
• Organize and name the fields so they appear as RoomType, RoomRevenue, %OfRoomRev,
Updated: 09/02/2016
1
10
YO16_AC_BU04_GRADER_PS1_HW_Instructions.docx
Office 2016 – myitlab:grader – Instructions
Step
Your Office Access BU 04 Problem Solve 1
Instructions
Points
Possible
NumOfReservations, and %OfReservations.
• Format the percent fields as Percent with 2 decimal places.
• Run the query, and adjust the column widths so that all data are visible.
• Add totals to sum the RoomRevenue, %OfRoomRev, NumOfReservations, and
%OfReservations.
• Save the query as qryRoomRevAnalysis, and then close the query.
5
6
Create an Append query that appends updated data from the accounting department, which
has been imported into the database and stored in tblNewRoomCharges.
• The query should append all records from the tblNewRoomCharges table to the
tblRoomCharges table.
• Run the query, save it as qryAppendRoomCharges, and then close the query.
Create a Delete query that deletes all the records in the tblNewRoomCharges table.
• Run the query, save it as qryDeleteRecords, and then close the query.
10
10
7
Create a Make Table query to store data about older reservations, which will eventually be
exported to a data warehouse.
• The new table should include the ReservationID, GuestID, CheckInDate, NightsStay,
NumberOfGuests, RoomRate, DiscountType, and EmployeeID fields from the tblReservations
table with check-in dates on or before 12/31/2017.
• Name the new table tblArchivedReservations.
• Run the query, save it as qryArchivedReservations, and then close the query.
10
8
Create an Update query that adds a $5 delivery charge to all room service orders in the
tblRoomCharges table.
• Run the query, and then save the query as qryUpdateRecords.
• Close the query.
10
9
Create an Update query that subtracts $25 from the room rate for VIP guest Paula Cote in the
tblReservations table.
• Run the query, save it as qryUpdateVIPRecord, and then close the query.
10
10
Create an outer join query that displays all fields from tblRoomCharges and the Purchase field
from tblChargeDetails to include all records from tblRoomCharges and only the records from
tblChargeDetails in which the joined fields are equal.
• Run the query, and adjust the column widths as necessary.
• In Datasheet view, add a total row at the bottom of the data set that totals the Amount field.
• Save the query as qryRoomCharges, and then close the query.
10
11
Create a query using SQL that will display the GuestID from tblRoomCharges and a new field
named TotalCharges that calculates the total room charges for each guest.
• Group by GuestID.
• Use the HAVING Clause to display only records in which the TotalCharges are greater than
$300.00.
• Sort the results in Descending order by TotalCharges.
• Run the query, and adjust the column widths as necessary.
• Save the query as qrySQLHighCharges, and then close the query.
Updated: 09/02/2016
2
10
YO16_AC_BU04_GRADER_PS1_HW_Instructions.docx
Office 2016 – myitlab:grader – Instructions
Step
12
Your Office Access BU 04 Problem Solve 1
Points
Possible
Instructions
Close all database objects. Close the database and then exit Access. Submit the database as
directed.
Total Points
Updated: 09/02/2016
3
0
100
YO16_AC_BU04_GRADER_PS1_HW_Instructions.docx
…
Purchase answer to see full
attachment
