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

Access Business Unit 3 Capstone Problem Solve 1 Student data file needed: a03Hotel

MS Access

Share With

---

Access Business Unit 3 Capstone
Problem Solve 1
Student data file needed:
a03Hotel.accdb

You will save your file as:
 a03Hotel_LastFirst.accdb

Using the Hotel Database for Advanced Querying
Finance & Accounting
The area of Painted Paradise Resort & Spa that generates the most revenue is the hotel. Guests can charge anything from the
resort to their room. Therefore, the hotel staff must track all of these 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
tor a discount. You have been asked to create a new table that tracks guest payments and to create queries that will help
management with decision making.
a. Open the Access file, a03Hotel. Save your file as a03Hotel_LastFirst using your last and first name. If necessary,
enable the content. Open tblGuests, and then replace YourName in record 23 with your last and first name.
Grader Heads Up
If you are using MyLab IT Grader, you will skip this step.
b. Create a new table named tbhlPayments that will track guest payments upon guest checkout. Add the following fields,
data types, and descriptions.
Field Name Data Type Description
PmtiDd Automatically assigned to each transaction (primary key)
ReservationiD Number An alphanumeric code unique to every reservation and matches a reservation in
tbiReservations (foreign key)

PmtAmount The amount the guest paid
PmtDate The date the guest made the payment
PmtMethod Shert Text How the customer paid (Cash, Check, MasterCard, Visa, American Express,
Discover)
AuthNumber Short Text The authorization number if paid by credit or debit card. Authorization numbers
can begin with the number zero.
c. Create the following tield properties.
Field Name Field Properties
PmtiD * Change the format to “PMTO"0
« Make this field the primary key.
« New values need te be sequential —Increment.
ReservationID « Change the format to “RBH”0
¢ Type RSVP ID as the caption.
¢ Make data in this field required for both new and existing records.
PmtAmourt * Type Amount as the captior.
* Payments can never be negative or zero. Create a rule te ersure it is a positive number greater
than zero. Ifa user erters a value that is negative, the user shoula get the following error:
Payment amount must be greater than zero

PmtDate ¢ This field should be formatted as “Medium Date” but should have an input mask of Short Date with
a four-digit year. Make certain that when users enter data, an underscore is displayed in the field
where users can enter digits.

¢ Type Date as the caption.

PmtMethod * Create a lookup field that lists the following valves:
Cash
Check
MasterCard
Visa
American Express
Discover
Limit the user's selectior to what is or the list. Allow multiple values.

* Type Method as the caption.

AuthNumber ¢ Set the maximum number of characters for the field to 6

¢ Type Auth # as the caption.
d. Enter the following data for the following fields in the tb]Payments table.

ia tetox-t ars} 000d) | BD PmtAmount PmtDate PmtMethod AuthNumber

° 122800 erent “ere

7 $483.23 03-Jan-21 MasterCard 877456

8 $346.50 03-Jan-21 Cash, Check

e. Open the Relationships window and add the table tblPayments. Create a one-to-many relationship between
ReservationID in the table tblReservations and the ReservationID in the table tblPayments. Enforce referential
integrity.

f. Use the data in tblReservations and tb]RoomCharges to create a query that calculates the attendant’s gratuity on a
room service order (20%) or spa treatment (25%). Your query results should display ReservationID, CheckInDate,
ChargeCategory, ChargeAmount, and Tip where Tip is a calculated field. If the ChargeCategory is not a spa
treatment or room service, a zero should be displayed in the field. Format the Tip field as Currency. Sort in
Ascending order by CheckInDate. Run the query, and then save it as qryTips Close the query.

g. Use the data in tblReservations and tblGuests to create a query that calculates the guest's total room charges. Your
query results should display GuestFirstName, GuestLastName, ReservationID, CheckInDate, NightsStay,
RoomRate, DiscountType, and TotalRoomRate where TotalRoomRate is a calculated field based on the number of
nights and the room rate. Subtract any discount from the total charges. In addition to the discount data in the
database, AARP and AAA members receive a 10% discount. Military personnel receive a 15% discount. Guests
without a discount should still have their total room charges calculated. Sort in Ascending order by ReservationID.
Format the calculated field as Currency. Run the query, and then save your query as qryTotalRoomRate Close the
query.

h. Use the data in tblGuests, tblChargeDetails, tblReservations, and tblRoomCharges to create a query that enables
you to enter the guest's full name—first and last name separated by a space—to find out what services the guest had
while staying at the resort. Concatenate the GuestFirstName and GuestLastName fields into a new field named Guest
Be sure to leave a space between the GuestFirstName and Guest-LastName fields. The parameter should ask the user
to Enter a Guest's First and Last Name Your query results should also list ChargeCategory, ChargeAmount,
RoomChargelID, and Purchase. Sort Ascending order by RoomChargeID. Combine the Not and Like operators to
ensure that your results do not include any Gifts/Sundries in the results. Test your query using Farrah Mcmahon Save
your query as qryParameter Close the query.

i. Use the data in tblReservations and tblPayments to create a query that finds all reservations between a check-in date
of 12/1/2020 and 12/31/2021. The query results should display ReservationID, CheckInDate, NightsStay,
NumberOfGuests, RoomType, RoomRate, PmtAmount, PmtDate, and PmtMethod. Show only the guests who paid
by credit card—MasterCard, Visa, American Express, and Discover. Sort in Ascending order by CheckInDate. Run the
query, and then save it as qryDates Close the query.

j. Use the data in tblReservations and tblGuests, to create a query to see in which months discounts were given for
AAA. Management is curious if there is a seasonal pattern. Your query results should display GuestFirstName,
GuestLastName, and ReservationMonth where ReservationMonth is a calculated field. ReservationMonth should
use the DatePart function to return the month of the checkin date for guests who received the AAA discount. Sort
Ascending by ReservationMonth. Run the query, and then save it as qryReservationMonth Close the query.

k. Use the data in tbIReservations and tblGuests to create a Top Values query that displays the 11 highest room rates
being charged to guests who have made a reservation. The query results should display GuestFirstName and
GuestLastName trom tblGuests and then CheckInDate, NightsStay, and RoomRate from tblReservations. Enter 11
in the Return (Top Values) box. (Hint: Do not forget to sort your data.) Run the query, and then save it as
qryTopRoomRaites Close the query.

1. Close the database, exit Access, and then submit the file as directed by your instructor.