Query and report assistance

[POS120]

I have an exercise that asks the following questions for the attached database.

The database has the following fields left to right

Guest first name, guest last name, room, room type, arrival date, departure date, no of guests, daily rate

1. What is the average length of stay per room
2.What is the average number of visitors per room
3.What is the base income per room during a specified period
4.What is he strongest custoemr base?

Once these queries are done a report there is a request for a report to be generated which i believe is fairly straight forward with microsoft access by changing the query into a report form



I would kindly request assistance with how I can create the queries for these questions.

My experience thus far is very limited software skill when it comes to Microsoft access.

I just purchased microsoft access 2010 and have started to use library and microsoft tutorial resources however I find myself struggling to translate that information into a query design that can answer these questions.

what i know thus far is to use the query tab to start a query however i admittedly am at a loss as to how to write the formula

My first effort is something like this for

question 1. add room field, add field for difference between arrival and departure date= number of days per room, add field for average days per room ( number of days per room divided by number of stays)
question 2. add room type field, add no of guests field, add number of stays per room field= (?), add average number of visitors per room type field =(number of guests divided by number of stays)
question 3.add room field, add length of visit field =( difference between arrival and departure date),add daily rate field , add base income field =( length of visit muliply by daily rate)Note room rates are same for 1 to 4 guests 5th and 6th guest pay additional $20 per day, 7 day stay or moe receives 10% discount ** I ma completely uncertain how to incorporate this info into the query and how the formaul/parameter would look
question4.add number of guests field, add avg length of stay field, query formula would try to look at which size of guest party spends the most money at the hotel is it the 1 guest, 2 guest 3 guest, 4 guest or 4+ guest party

I would certainly appreciate any help that can focus my limited knowledge and experience into answering these questions

[June7]

The project you posted does not include any effort at attempting solutions. It is just the raw table. Attempt solutions and post your efforts for analysis.

Use queries to manipulate data. Also build report using Grouping & Sorting with aggregate calcs to manipulate data.

Learn date manipulation functions. You can simply subtract one date from another to return difference in days as day is the default unit. Use DateDiff to get difference in other time units. Note that a dep and arr of same day will result in 0 - not 1 day. But since you are actually counting nights of stay, this should work fine. Dep and Arr should never be same day (unless you rent by the hour).