In this exercise, you will use database to analyze the reservation transactions for a hotel and use that information to fine-tune the hotels business strategy and marketing activities. This exercise encourages you to develop queries and reports in a database software to help you make better use of transaction data to understand your business. The attached file Hotel contains information about The Presidents Inn hotel in Cape May, NJ. At the Inn, 10 rooms overlook side streets, 10 rooms have bay windows with limited views of the ocean, and the remaining 10 rooms in the front of the hotel face the ocean. Room rates are based on room choice, length of stay, and number of guests per room. Room rates are the same for one to four guests. Fifth and sixth guests must pay an additional $20 per person per day. Guests staying for seven days or more receive a 10 percent discount on their daily room rates. Use the database to develop three reports on average length of stay per room type, average visitors per room type, and base revenue per room (i.e., length of visit multiplied by the daily rate) during the period of time in the database. You may use the query wizard and report wizard capabilities of Access. From analyzing these data, you should be able to find ways of generating more revenue. Write a brief report about the Inns current business situation and suggest future strategies in the Word document.

Query: SELECT Reservations.[Room Type], Avg(Reservations.[Departure Date]-Reservations.[Arrival Date]) AS stay FROM Reservations GROUP BY Reservations.[Room Type] ORDER BY 1;

Room Type stay Bay-window 3 Ocean 3.625 Side 5.5

? What is the average number of visitors per room type? Query: SELECT Reservations.[Room Type], Avg(Reservations.[No of Guests]) AS [Avg_Guests] FROM Reservations GROUP BY Reservations.[Room Type];

Room Type Avg_Guests Bay-window 1.8 Ocean 2.375 Side 3.25

? What is the base income per room (i.e., length of visit multiplied by the daily rate) during a specified period of time? Query: SELECT Reservations.[Room Type], round(Avg((Reservations.[Departure Date]-Reservations.[Arrival Date])*Reservations.[Daily Rate])) AS Avg_revenue FROM Reservations GROUP BY Reservations.[Room Type] ORDER BY Reservations.[Room Type]; Room Type Avg_revenue Bay-window 465 Ocean 874 Side 697

? What is the strongest customer base? Query: SELECT Reservations.[Room Type], Sum(Reservations.[No of Guests]) AS [SumOfGuests] FROM Reservations GROUP BY Reservations.[Room Type] ORDER BY Reservations.[Room Type];

Room Type SumOfGuests Bay-window 18 Ocean 38 Side 13

Put it in an access spreadsheet please

Sign in E Copy X R Table Tools Access File Home Create Esrtemal Data Database Tools Fields Table Tell me what you want to do X cut 2. Ascending Selection New Total ab Replace Aral - 11 Il Descending Advanced Save Speling View Go To Paste Filter Refresh Find Size to Switch Format Painter BIU A. Remove Sort y Toggle Filter SE A All-X Delete - More Fit Form Windows Views Clipboard Sort Fit Records Find Window Test Formatting Tables 101 Reservations Reservations Guest First Nami - Guest Last Nan Room Room Type - Arrival Da - Departure Da. No of Gues - Daily Ral - Click to Add- 1 Barry Lloyd Hayes Bay-window 12/1/2015 12/4/2015 2 $150.00 2 Michael Lunsford Cleveland Ocean 12/1/2015 12/9/2015 3 $112.50 Kiri Kyuong Coolidge Bay-window 12/4/2015 12/7/2015 1 $150.00 4 Edward Holt Washington Ocean 12/1/2015 12/3/2015 4 $325 00 5 Thomas Collins Lincoln Ocean 12/9/2015 12/13/2015 2 $300.00 6 Paul Bodkin Coolidge Bay-window 12/1/2015 12/3/2015 2 $150 00 7 Randall Battenburg Washington Ocean 12/4/2015 12/12/2015 2 $292.50 8 Calvin Nowotney Lincoln Ocean 12/2/2015 12/4/2015 1 $300.00 9 Homer Gonzalez Lincoln Ocean 12/5/2015 12/7/2015 5 $320.00 10 David Sanchez Jefferson Bay-window 12/5/2015 12/7/2015 2 $175.00 11 Buster Whisler Jackson Ocean 12/5/2015 12/8/2015 2 $250.00 12 Julia Martines Reagan Bay-window 12/10/2015 12/15/2015 $150.00 13 Samuel Kim Truman Side 12/20/2015 12/30/2015 3 $112.50 14 Arthur Gottfried Garfield Side 12/13/2015 12/15/2015 2 $125.00 15 Darlene Shore Arthur Ocean 12/24/2015 12/31/2015 5 $198.00 16 Carlyle Charleston Quincy Adams Bay-window 12/3/2015 12/6/2015 2 $150.00 17 Albert Goldstone Johnson Ocean 12/5/2015 12/7/2015 3 $250.00 18 Charlene Tilson Van Buren Bay-window 12/5/2015 12/7/2015 1 $150.00 19 Everett Chad Madison Ocean 12/10/2015 12/14/2015 2 $275.00 20 Gerald Pittsfield Roosevelt Ocean 12/5/2015 12/7/2015 2 $275 00 21 Jamal Smith Tyler Bay window 12/20/2015 12/23/2015 2 $150.00 22 Louis Paris Jackson Ocean 12/10/2015 12/14/2015 1 $250.00 23 Nigel Stratford Eisenhower Ocean 12/14/2015 12/16/2015 2 $200.00 24 Peter Willington Grant Ocean 12/19/2015 12/21/2015 1 $200.00 25 Ronald Jefferson Bay-window 12/24/2015 12/28/2015 4 $175.00 26 Trista Leven Eisenhower Ocean 12/17/2015 12/20/2015 $200.00 27 Valerie Snell Adams Bay-window 12/3/2015 12/6/2015 1 $150.00 28 Water Acton Polk Side 12/24/2015 12/31/2015 6 $148.50 20 Xavier Trezza McKinley Side 12/14/2015 12/17/2015 2 $125.00 30 Zachary Miler Washington Ocean 12/13/2015 12/16/2015 2 $325.00 * W) 1 1 $0.00 Record 1 of 30 No Filter Search PL Datasheet View Num Lock 227 PM 11/12/2019 Type here to search O AB 20 Sign in E Copy X R Table Tools Access File Home Create Esrtemal Data Database Tools Fields Table Tell me what you want to do X cut 2. Ascending Selection New Total ab Replace Aral - 11 Il Descending Advanced Save Speling View Go To Paste Filter Refresh Find Size to Switch Format Painter BIU A. Remove Sort y Toggle Filter SE A All-X Delete - More Fit Form Windows Views Clipboard Sort Fit Records Find Window Test Formatting Tables 101 Reservations Reservations Guest First Nami - Guest Last Nan Room Room Type - Arrival Da - Departure Da. No of Gues - Daily Ral - Click to Add- 1 Barry Lloyd Hayes Bay-window 12/1/2015 12/4/2015 2 $150.00 2 Michael Lunsford Cleveland Ocean 12/1/2015 12/9/2015 3 $112.50 Kiri Kyuong Coolidge Bay-window 12/4/2015 12/7/2015 1 $150.00 4 Edward Holt Washington Ocean 12/1/2015 12/3/2015 4 $325 00 5 Thomas Collins Lincoln Ocean 12/9/2015 12/13/2015 2 $300.00 6 Paul Bodkin Coolidge Bay-window 12/1/2015 12/3/2015 2 $150 00 7 Randall Battenburg Washington Ocean 12/4/2015 12/12/2015 2 $292.50 8 Calvin Nowotney Lincoln Ocean 12/2/2015 12/4/2015 1 $300.00 9 Homer Gonzalez Lincoln Ocean 12/5/2015 12/7/2015 5 $320.00 10 David Sanchez Jefferson Bay-window 12/5/2015 12/7/2015 2 $175.00 11 Buster Whisler Jackson Ocean 12/5/2015 12/8/2015 2 $250.00 12 Julia Martines Reagan Bay-window 12/10/2015 12/15/2015 $150.00 13 Samuel Kim Truman Side 12/20/2015 12/30/2015 3 $112.50 14 Arthur Gottfried Garfield Side 12/13/2015 12/15/2015 2 $125.00 15 Darlene Shore Arthur Ocean 12/24/2015 12/31/2015 5 $198.00 16 Carlyle Charleston Quincy Adams Bay-window 12/3/2015 12/6/2015 2 $150.00 17 Albert Goldstone Johnson Ocean 12/5/2015 12/7/2015 3 $250.00 18 Charlene Tilson Van Buren Bay-window 12/5/2015 12/7/2015 1 $150.00 19 Everett Chad Madison Ocean 12/10/2015 12/14/2015 2 $275.00 20 Gerald Pittsfield Roosevelt Ocean 12/5/2015 12/7/2015 2 $275 00 21 Jamal Smith Tyler Bay window 12/20/2015 12/23/2015 2 $150.00 22 Louis Paris Jackson Ocean 12/10/2015 12/14/2015 1 $250.00 23 Nigel Stratford Eisenhower Ocean 12/14/2015 12/16/2015 2 $200.00 24 Peter Willington Grant Ocean 12/19/2015 12/21/2015 1 $200.00 25 Ronald Jefferson Bay-window 12/24/2015 12/28/2015 4 $175.00 26 Trista Leven Eisenhower Ocean 12/17/2015 12/20/2015 $200.00 27 Valerie Snell Adams Bay-window 12/3/2015 12/6/2015 1 $150.00 28 Water Acton Polk Side 12/24/2015 12/31/2015 6 $148.50 20 Xavier Trezza McKinley Side 12/14/2015 12/17/2015 2 $125.00 30 Zachary Miler Washington Ocean 12/13/2015 12/16/2015 2 $325.00 * W) 1 1 $0.00 Record 1 of 30 No Filter Search PL Datasheet View Num Lock 227 PM 11/12/2019 Type here to search O AB 20