Table Design

[cnother]

Being a newbie I have a general table design question which I would be grateful if someone could advise upon.

In Access 2000 I currently have 3 tables for use in a holiday cottage rental system:

tblCustomer (customer details)
name (text)
address (text)
telephone (text)
etc.....
customerID (autonum)


tblreservation (cottage reservation details)
cottage (text) - linked to tblCottage
customer (test) - linked to tblCustomer
start date (date)
number of nights (number)
Adults (number)
Price (currency)
etc.....
ReservationID (autonum)


tblCottage (list of cottages available for rental)
cottage (text)
CottageID (autonum)


I need to add a table structure which defines the price band for each cottage at several times throughout the year, in order to take account of peak holiday periods etc. The price field in the tblReservation table needs to refer to the correct price band based on the cottage and the start date of the reservation.

Any thoughts how best to approach this would be greatly appreciated.

Many thanks

Chris

 

[neileg]

You should be storing customerID and cottageID in tblreservation, not the text field.

No doubt someone else will be able to solve your seasonal rate issue, but I'm stumped!

 

[KurtRos]

Seasonal Rates Changes

Here is a suggestion based on you know some basic VB to.....

1. I agree with Neileg, you need ot record the customerID in the reservationtbl not the CustomerName. If you want to query it then you can query the CustomerID or Last Name or First or what ever your preferences are inthe Customertbl or you can query the customerID or date ranges or cottages in the reservationtbl.

2. As for the Rates.. I would make an excel spreadsheet and put your rates in column A and in column B put the month the rate changes (11=November) and the year (2007).... (if you want to get a little more specific divide the year up in weeks and use a week number 1 -52 and then you can break it down a little more.). Now you have hte capability of increasing rates by the month/week/year and not affecting rates for previous years by changing them.

3. Import this spreadsheet and place it into a table or copy and paste it (Importing is easier).

4. Now when you enter your date into the reservation date field on your form this would do an event (VBCODE) on update and look into the tbl_Seasonal_Rates and find the corresponding month or week depending on how you set it up and automatically insert that rate into the rate field.


Now I have been around Hotel/Motel's all my life and my family has owned over twenty of them over the past years so my next point to you is dont forget a tax table for hospitality rates, phone charges, food charges ordering movies etc whatever amenities you charge extra for on a one by one basis....

I know it sounds easier to make something but I would almost bet if you look on a torrent site like mininova.org you can find a free application that you can download that has all this functionality already in it since there are over 1200 hotels in each state minimum that someone is sharing......

Hope i helped and gave you some good ideas..

 

[Len Boorman]

Haven't read the detail above but alternative suggestion

tbl_Cottage_Price_Band
Fields
CottageID FromDate ToDate PriceBand_1


tbl_Price_Band
Fields
PriceBand (PK) Price £


When resertvation made check for start date between FromDate and ToDate along with Cottage ID to select PriceBand which refers to tbl_Prive_Band to get rate

L

 

[cnother]

Thanks to all that replied, and you all spotted the deliberate error in my transcript of the tables as I had actually stored customerID and cottageID as suggested. (this is where a pic of the table relationships would be so much easier than describing in words)
Len's suggestion looks quite sound on the face of it so will look into using this method further............
Chris

 

[Len Boorman]

but I'm stumped!

Not a chance... just having a quiet moment.

L