yakdog
11-03-2007, 01:41 PM
It's a Bed and Breakfast. There are two Access Tables in the query. No
spreadsheet. The Reservations table has RoomNumbers (some of which repeat because different customers rent the room during different time periods), CustomerNumbers, Check-inDate and Check-out Dates. The Rooms table has RoomNumber, FloorNumber, PricePerNight fields. The calculated field that I have developed to figure the money generated by each room is Total:([Rooms].[PricePerNight])*([Reservation].[Check-outDate]-[Reservations].[Check-inDate]).
This makes my result look like:
Room Number Total
3 $960.00
7 $540.00
3 $480.00
1 $300.00
4 $280.00
4 $280.00
3 $240.00
2 $225.00
2 $150.00
4 $140.00
1 $ 75.00
6 $ 60.00
How do I use a Summation function to aggregate the amount of money brought in for each room, so that the result looks like
Room Number Total
3 $1,680.00
4 $700.00
7 $540.00
2 $375.00
1 $375.00
6 $60.00
Any help would be appreciated.
Thank you
spreadsheet. The Reservations table has RoomNumbers (some of which repeat because different customers rent the room during different time periods), CustomerNumbers, Check-inDate and Check-out Dates. The Rooms table has RoomNumber, FloorNumber, PricePerNight fields. The calculated field that I have developed to figure the money generated by each room is Total:([Rooms].[PricePerNight])*([Reservation].[Check-outDate]-[Reservations].[Check-inDate]).
This makes my result look like:
Room Number Total
3 $960.00
7 $540.00
3 $480.00
1 $300.00
4 $280.00
4 $280.00
3 $240.00
2 $225.00
2 $150.00
4 $140.00
1 $ 75.00
6 $ 60.00
How do I use a Summation function to aggregate the amount of money brought in for each room, so that the result looks like
Room Number Total
3 $1,680.00
4 $700.00
7 $540.00
2 $375.00
1 $375.00
6 $60.00
Any help would be appreciated.
Thank you