Hotel Database for Advanced Querying

Question Description

Please help me with this one, the problems are in the word doc and will need to be done on the Access doc.

Office 2016 – myitlab:grader – Instructions Your Office Access BU 03 Problem Solve 1 YO16_AC_BU03_GRADER_PS1_HW – Using the Hotel Database for Advanced Querying Project Description: The area of Painted Paradise Resort & Spa that generates the most revenue is the hotel. Guests may charge anything from the resort to their room. Thus, the hotel staff must track all of these charges, such as those from the spa, golf, gift shop, restaurants, movies, personal trainers, and sessions with golf professionals. These services are eligible for a discount. You have been asked to create a new table and create queries that will help management with decision making. Instructions: For the purpose of grading the project you are required to perform the following tasks: Step 1 Instructions Open the Access file, a03_grader_h1_Hotel.accdb. Save your file as a03_grader_h1_Hotel_LastFirst, using your last and first name. If necessary, enable the content. Points Possible 0 Create a new table with the following attributes: Field Name PmtID GuestID ReservationID PmtAmount PmtDate PmtMethod AuthNumber Data Type AutoNumber Number Number Currency Date/Time Short Text Short Text Enter the following descriptions for each field: PmtID: The payment ID automatically assigned to each transaction (primary key) 2 GuestID: An alphanumeric code unique to every guest matching a guest in the tblGuests (foreign key) 11 ReservationID: An alphanumeric code unique to every reservation and matches a reservation in tblReservations (foreign key) PmtAmount: The amount the guest paid PmtDate: The date the guest made the payment PmtMethod: How the customer paid (Cash, Check, MasterCard, Visa, American Express, Discover) AuthNumber: The authorization number if paid by credit or debit card. Authorization numbers can begin with the number zero. Save the new table as tblPayments. Create the following field properties in the tblPayments table: 3 7 PmtID • Change the format to “PMT0″0. Updated: 09/23/2016 1 YO16_AC_BU03_GRADER_PS1_HW_Instructions.docx Office 2016 – myitlab:grader – Instructions Step Your Office Access BU 03 Problem Solve 1 Instructions • Make this field the primary key. • New values need to be sequential—Increment. Points Possible GuestID • Create a Lookup field using the tblGuests table. Select GuestID, GuestLastName, and GuestFirstName. Sort in Ascending order by GuestID. Keep the key column hidden. • Type Guest as the caption. • Make data in this field required both for new and existing records. 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. PmtAmount • Type Amount as the caption. Continue creating the following field properties in the tblPayments table: PmtDate • This field should be formatted as “Medium Date”, but have an input mask of Short Date with a 4-digit year. Make certain when users enter data that an underscore is displayed in the field where users can enter digits. • Type Date as the caption. 4 5 PmtMethod • Create a lookup field that lists the following values: Cash, Check, MasterCard, Visa, American Express, Discover. Limit the user’s selection to what is on 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. Save your changes, and then switch to Datasheet view. Add the following data to the tblPayments: GuestID ReservationID PmtAmount PmtDate PmtMethod AuthNumber Bennett 5 $1,229.00 01-Jan-18 American Express 22597 Cote 6 $168.30 02-Jan-18 Cash Wong 7 $483.23 03-Jan-18 MasterCard 877456 Bridges 8 $346.50 03-Jan-18 Check Finch 10 $31.19 15-Feb-18 Cash Sharp 11 $395.80 15-Feb-18 Visa 01123 Woodward 12 $391.31 28-Feb-18 Visa 08556 Mcmahon 14 $137.62 02-Apr-18 Cash, Discover 22113 Wenner 16 $807.49 10-Apr-18 American Express 88945 Wagner 17 $1,682.71 06-Jul-18 Discover 612876 6 10 6 Open the Relationships window and add the table tblPayments. Create a one-to-many relationship between GuestID in the tblGuest and GuestID in the tblPayments. Enforce referential integrity. 3 7 Create a one-to-many relationship between ReservationID in the table tblReservations and the ReservationID in the table tblPayments. Enforce Referential integrity. 3 Updated: 09/23/2016 2 YO16_AC_BU03_GRADER_PS1_HW_Instructions.docx Office 2016 – myitlab:grader – Instructions Your Office Access BU 03 Problem Solve 1 Step Instructions Points Possible 8 Use the data in tblReservations and tblRoomCharges to create a query that calculates the attendants’ gratuity on a room service order (18%) or spa treatment (25%). Your query results should display ReservationID, CheckInDate, ChargeCategory, ChargeAmount, and a calculated field named Gratuity in that order. If the ChargeCategory is not a spa treatment or room service, a zero should be displayed in the field. 8 9 Format the Gratuity field as Currency. Sort in Ascending order by CheckInDate. Run the query, and then save it as qryTips. Close the query. 2 10 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 a calculated field named TotalRoomCharges in that order. 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 20% discount. Guests without a discount should still have their total room charges calculated. 8 11 Sort in Ascending order by ReservationID. Run the query, and then save your query as qryTotalRoomCharges. Close the query. 2 12 Use the data in tblGuests, tblChargeDetails, and tblRoomCharges to create a parameter query that enables you to enter the guest’s full name to find out what services they 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 GuestLastName fields. The Parameter should ask the user to Enter a Guest’s First and Last Name. Your query results should also list ChargeCategory, ChargeAmount, Purchase, and Guest. 7 13 Combine the Not and Like functions to ensure that your results do not include any Gifts/Sundries in the results. Test your query using your Cayden Wagner. Save your query as qryParameter. Close the query. 3 14 Use the data in tblReservations and tblPayments to create a query that finds all reservations between 6/1/2017 and 6/1/2018. The query results should display ReservationID, CheckInDate, NightsStay, NumberOfGuests, RoomType, RoomRate, PmtAmount, PmtDate, and PmtMethod in that order. 5 15 Only show the guests who paid by credit card—MasterCard, Visa, American Express, and Discover. Sort in Ascending order by PmtAmount. Run the query, and then save it as qryDates. Close the query. 5 16 Use the data in tblReservations, tblPayments, and qryTotalRoomCharges to create a query to see what the total charges will be for each guest. Your query results should display ReservationID, CheckInDate, NightsStay, RoomRate, and TotalRoomCharges from qryTotalRoomCharges, PmtAmount from tblPayments, and DiscountType from tblReservations. Create a new field named AmountDue that calculates whether an amount is due. (Hint: If the total room charges are more than the payment amount, then the guest owes money.) Create a new field named RefundDue that calculates whether a refund is owed to a guest. 8 17 Format the AmountDue and RefundDue fields as Currency. Run the query, and then save it as qryTotalCharges. Close the query 2 Updated: 09/23/2016 3 YO16_AC_BU03_GRADER_PS1_HW_Instructions.docx Office 2016 – myitlab:grader – Instructions Step Your Office Access BU 03 Problem Solve 1 Points Possible Instructions 18 Use the data in tblReservations 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 from tblGuests, and then CheckInDate, NightsStay, and RoomRate from tblReservations in that order. Enter 11 in the Return (Top Values) box. (Hint: Do not forget to sort your data.) Run the query, and then save it as qryTopValues. Close the query. 10 19 Close all database objects. Close the database and then exit Access. Submit the database as directed. 0 Total Points Updated: 09/23/2016 4 100 YO16_AC_BU03_GRADER_PS1_HW_Instructions.docx …

Do you have a similar assignment and would want someone to complete it for you? Click on the ORDER NOW option to get instant services. We assure you of a well written and plagiarism free papers delivered within your specified deadline.t assignment.