Homework answers / question archive / Access Business Unit 4 Capstone Problem Solve 1 Student data file needed: a04HotelRevenue

Access Business Unit 4 Capstone Problem Solve 1 Student data file needed: a04HotelRevenue

MS Access

Share With

---

Access Business Unit 4 Capstone
Problem Solve 1

Student data file needed:
a04HotelRevenue.accdb

You will save your file as:
a04HotelRevenue_LastFirst.accdb

Calculating Revenue and Maintaining the Hotel Database
Finance & Accounting
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 scaleddown version of the hotel's database to use.
a. Open the Access database a04HotelRevenue. Save your file as a04HotelRevenue_LastFirst using your last and first
name. If necessary, enable the content.
b. Create a query that calculates the total revenue from all room reservations along with the total number of
reservations grouped by room type.
¢ Include a calculated field named RoomRevenue that multiplies the RoomRate and NightsStay fields.
¢ Use the Total row to sum the RoomRevenue calculated field.
¢ Include a calculated field named ReservationVolume that counts the number of reservations.
¢ Group the results by RoomType.
e Ifnecessary, rearrange the order of the fields to be RoomType, RoomRevenue, and ReservationVolume.
¢ Run the query, adjust the column widths so that all data are visible, and then save the query as qryRoomRev
¢ Close the query.
c. Create a query that calculates gross sales revenue and gross sales volume.
¢ Create a calculated field named GrossRoomRev that multiplies the RoomRate and NightsStay fields.
¢ Use the Total row to sum the GrossRoomRev calculated field.
e Include a calculated field named GrossVolume that counts the number of reservations.
e Ifnecessary, rearrange the order of the fields to be GrossRoomRev and then GrossVolume.
¢ Run the query, adjust the column widths so that all data are visible, and then save the query as qryGrossRevVol
¢ Close the query.

d. Create a query that will use the fields in the qryRoomRev and qryGrossRevVol queries to display the room revenue
and reservation volume along with the percentage of sales revenue and percentage of sales volume for each room
type.

Organize and name the fields so they appear as
RoomType
RoomRevenue
% OfRoomRev
ReservationVolume
% 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.
e Add totals to sum the RoomRevenue, %OfRoomRev, ReservationVolume, and %OfReservations.
¢ Save the query as qryRevenueVolAnalysis and then close the query.

e. 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 thINewRoomCharges table to the thlRoomCharges table.
¢ Run the query, save it as qryAppendRoomCharges and then close the query.

f. Create a Delete query that deletes all the records in the thlNewRoomCharges table.

¢ Run the query, save it as qryDeleteRecords and then close the query.
g. 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/2019

¢ Name the new table tblArchivedReservations

¢ Run the query, save it as qryArchivedReservations and then close the query.

h. 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.

i, Create an Update query that subtracts $25 trom the room rate for VIP guest Paula Cote in the tblReservations table.

¢ Run the query, save it as qryUpdateVIPRecord and then close the query.

j. Create an outer join query that displays all fields from thlRoomCharges and the Purchase field from
tblChargeDetails to include all records from tbIRoomCharges and only the records from tblChargeDetails in which
the joined fields are equal.

¢ Run the query, and adjust the column widths so that all data are visible.
¢ 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.

k. Create a query using SQL that will display the GuestID from tblIRoomCharges 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 $150.00.

¢ Sort the results in Descending order by TotalCharges.

¢ Run the query, and adjust the column widths so that all data are visible.

¢ Save the query as qrySQLHighCharges and then close the query.
1. Close the database, exit Access, and then submit your file as directed by your instructor.
Critical Thinking
Which room type had the highest revenue and volume? What assumptions can you make about the guests of the
hotel based on this room type? Which room type has the biggest impact on revenue with the smallest change in reservations?