Query for month or week grouping

[Guest]

Hallo all,
I have two tables:
a table [Rooms] with the fields [room_id] and [Room number]
and
a table [reservations] with the fields
[reservation_id],[room_id],[startdate] and [enddate]

Now i want to know how many days all rooms together are occupied per month
(with the intention to calculate the occupation rate per month).

Can anyone give a clue how to solve this?

thanks,
RLi

 

[Allen Browne]

The dates have to come from somewhere, so you need a table of dates. You can
then add this table to your query.

Say the table is called tblDate, with one Date/Time field named TheDate, and
a record for each date in the ranges you need to query (and beyond if you
wish). You add tblDate to your query, with no line joining to your other
query in the upper pane of table design. Add tblDate.TheDate to the design
grid, and add this criteria under it:
Between Rooms.startdate and Rooms.enddate
The query will now give you a record for each date each room is booked.

Save that query. You can now create a Totals query that counts for any
period, the number of dates the room is occupied. By dividing that count
form the number of dates in the period, you can get occupancy rates.

 

[Guest]

Hi, Allen,
Thank you for your input. I 'think' i see how you propose to solve this.
(btw startdate and enddate are fields of the table Reservations, not Rooms;
but that does not change the usefulness of your suggestion, i think).
Unfortunatly i saw your post to late and tried another approach that seems
to work also:
I created a intermediate query called Numberofroomsquery:

SELECT Count(Rooms.[room_id]) AS Numberofrooms
FROM Rooms;

And than the next query gives me the occupation rate for each individual
record in Reservations:

SELECT Reservations.startdate,
DateDiff("d",startdate,IIf(Month(enddate)>Month(startdate),DateSerial(Year(DateAdd("m",1,startdate)),Month(DateAdd("m",1,startdate)),1),enddate))
AS Daysoccupied, Numberofrooms *
Day(DateAdd("d",-1,DateSerial(Year(DateAdd("m",1,startdate)),Month(DateAdd("m",1,startdate)),1)))
AS Daysinthemonth, Daysoccupied / Daysinthemonth AS Rate FROM Reservations,
Numberofroomsquery
ORDER BY Reservations.startdate;

Now, a simple grouping on the resulting startdate (with a SUM on Rate) gives
me the overall occupation rate. So you wrote 'the date have to come from
somewhere'...i use the startdate for this. It's rather elaborate (to find the
last day for each month), but seems to give the correct awnsers.


Thanks anyhow for your suggestion,

Rob

The dates have to come from somewhere, so you need a table of dates. You can
then add this table to your query.

Say the table is called tblDate, with one Date/Time field named TheDate, and
a record for each date in the ranges you need to query (and beyond if you
wish). You add tblDate to your query, with no line joining to your other
query in the upper pane of table design. Add tblDate.TheDate to the design
grid, and add this criteria under it:
Between Rooms.startdate and Rooms.enddate
The query will now give you a record for each date each room is booked.

Save that query. You can now create a Totals query that counts for any
period, the number of dates the room is occupied. By dividing that count
form the number of dates in the period, you can get occupancy rates.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hallo all,
I have two tables:
a table [Rooms] with the fields [room_id] and [Room number]
and
a table [reservations] with the fields
[reservation_id],[room_id],[startdate] and [enddate]

Now i want to know how many days all rooms together are occupied per month
(with the intention to calculate the occupation rate per month).

Can anyone give a clue how to solve this?

thanks,
RLi