Track capacity changes month by month

[Nils]

Hi guys,



I have a list of hotels that include their capacity (the number of hotelrooms they have), specified on a certain date. I need to do the following:

1. When I load the initial list of hotels, I must be able to say that from date X, this hotel has x rooms.

2. If I know that the capacity will change in the future (e.g., I know that the hotel will construct a new wing next year), I must be able to register that new capacity from that date, without affecting the original entry.

3. I must be able to pull a report by month that lists each hotel with capacity.


A sample report would look something like:

__________Jan 2011 ___Feb 2011 ____ Mar 2011
Hotel A________200 ________200 _________400
Hotel B _______ 200 ________200 _________200


Would I have to create one record per month for each hotel to get the capacity for each month?

Or is there a way to create one initial record with a start capacity, a second record with the new capacity on a future date, and then have the report know that the months in between the start date and change date should all have the initial capacity?


Any hints you can offer are greatly appreciated!

[jzwp11]

Welcome to the forum!

As to your table structure, I would have a table to hold the basic information about the hotel. To track changes in capacity, you would just track the change (i.e. enter a record only when there is a change). Since there can be many changes to the capacity over time, that describes a one-to-many relationship which requires a second table.

So you table to hold the basic info about each hotel

tblHotel
-pkHotelID primary key, autonumber
-txtHotelName
-Location
etc.


tblHotelCapacityTracking
-pkHotelCapTrackID primary key, autonumber
-fkHotelID foreign key to tblHotel
-capacity
-dteEffective (the effective date of the change in capacity)

You would add a new record in tblHotelCapacityTracking whenever there is a change to the capacity.

As to your report, there are a couple ways to do it. One way would be to have a table that just holds the periods of interest and then use a query that joins the tables to generate the report. However, I think that is not the best approach since you would have to add every possible period. I think a Visual Basic for Application (VBA) code solution would probably be better approach, but it would be a custom solution.

What else are you planning on doing with this database?