Query

[lolo-001]

Hello everyone : I have some work to do via Microsoft Access and I have a table for a hotel and I want to do a query by Microsoft Access
I had some problems
I Wishes that someone can help me
the file is Attached


and the query on :

1• What is the average length of stay per room type?​

2• What is the average number of visitors per room type?​

3• What is the base income per room (i.e., length of visit multiplied by the daily rate) during a specified period of time?​

4• What is the strongest customer base?

​

 

[lolo-001]

please help me I need it today...

 

[raskew]

Hello -

I have some work to do via Microsoft Access

and

please help me I need it today...

Sound as if you have some homework to do.... Out of curiousity, what portion of it do you plan on doing?

Believe it would negate the learning experience if forum contributors did it all for you. Having said that, here are two sample queries that may help get you started. Copy each one, create a new query and, switching to SQL view, paste them in.

SELECT Reservations.[Room Type], Avg(DateDiff("d",[Arrival Date],[Departure Date])) AS LenStay, Avg(Reservations.[No of Guests]) AS AvgGuests, Avg(Reservations.[Daily Rate]) AS [AvgOfDaily Rate]
FROM Reservations
GROUP BY Reservations.[Room Type]
ORDER BY Reservations.[Room Type];

...and

SELECT qryLenStay.[Room Type], Format(Avg([Daily Rate]*[LenStay]),"Currency") AS AvgBill
FROM qryLenStay LEFT JOIN Reservations ON qryLenStay.[Room Type] = Reservations.[Room Type]
GROUP BY qryLenStay.[Room Type];

Bob

 

[lolo-001]

Thanx alot but this is not a homework I am learning with a group
Thanx .. i will try it

 

[lolo-001]

the second code not working there is error in the name of qryLenStay

 

[raskew]

Sorry -

Rename the first query to 'QryLenStay'.

Bob

 

[JohnElway]

Query question

Hi everyone.....

I have following SQL code.

SELECT Reservations.[Room Type], Avg(DateDiff("d",[Arrival Date],[Departure Date])) AS [Avg Length of Stay], Avg(Reservations.[No of Guests]) AS [Avg # of Visitors], Avg(Reservations.[Daily Rate]) AS [Daily Rate]
FROM Reservations
GROUP BY Reservations.[Room Type]
ORDER BY Reservations.[Room Type];

and

SELECT Avg_Length_of_Stay.[Room Type], Format(Avg([Daily Rate]*[Avg Length of Stay]),"Currency") AS AvgBill
FROM Avg_Length_of_Stay LEFT JOIN Reservations ON Avg_Length_of_Stay.[Room Type] = Reservations.[Room Type]
GROUP BY Avg_Length_of_Stay.[Room Type];

Now here is the dilemma. This code is for 1-4 guests per night. But if there is a fifth and sixth guest, they must pay an additional $20 EACH per day. How would I alter the code to make up for this point.