Expert answer:just got through a few of these on this chapter bu
Expert answer:Please help me with this one, the problems are in the word doc and will need to be done on the Access doc.
pack_a03_grader_h1_hotel.zip
yo16_ac_bu03_grader_ps1_hw_instructions.docx
Unformatted Attachment Preview
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
…
Purchase answer to see full
attachment
How it works
- Paste your instructions in the instructions box. You can also attach an instructions file
- Select the writer category, deadline, education level and review the instructions
- Make a payment for the order to be assignment to a writer
- Download the paper after the writer uploads it
Will the writer plagiarize my essay?
You will get a plagiarism-free paper and you can get an originality report upon request.
Is this service safe?
All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades