query question

[John Elway]

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.

[June7]

This will add $20 per guest in excess of 4.
Avg(Reservations.[Daily Rate] + IIf([No of Guests] > 4, ([No of Guests] - 4) * 20, 0)) AS [Daily Rate]

[John Elway]

I can not tell from the code but does this account the surge PER DAY. I can see you have added an if state which takes into consideration the $20 surcharge but is that for EACH day or a one time surcharge? I am looking for the $20 surcharge applied each day. So IE, if the there are 6 people to the room and they stay for 3 days, then it would be 2ppl*20 = 40, then 40 multiplied by 3 days = $120.

[June7]

That is adjusting the daily rate. Need to multiply by total nights. I was merely adjusting the Avg calc in the first query, which is averaging over total records, not total guests or total days. Adjust the calcs in other query in the same way.

[John Elway]

Where in the first queiry should I put your Avg(Reservations.[Daily Rate] + IIf([No of Guests] > 4, ([No of Guests] - 4) * 20, 0)) AS [Daily Rate] calculation?
Sorry this is over my head.

[June7]

In place of the last calc: Avg(Reservations.[Daily Rate]) AS [Daily Rate]

Unless you don't want to adjust the daily rate in that query.