Calculating prices over a date range

[Elephant]

I am developing a database for a travel company.

I have a form called "Orders" with general details about customer and a "OrderID". A sub-form to this is "Order Details" listing the various itmes of the order, ie hotel stays.

In the "Order Details" sub-form I want to put the Supplier name (ie, hotel name), the room type (eg, single, double etc) and then put in the Dates of stay (Arrival date, Departure date or Arrival date and number of nights stayed) - then I want the price to be automatically calculated and displayed.

One important issue is that over a given period of stay (eg June 4th, 4 nights) some nights can be "Price X" and some "Price Y" depending on the day of the week so the solution must take this into account.

Any ideas how to make this work?

Thanks
Steve

 

[Tay]

How are the dates/prices stored? It might be best to have a separate table which has 'date' and 'price'. So june 4th - £50, june 5th - £55, june 6th = £56. Maybe then you could calculate the correct price for the stay.
HTH

 

[ColinEssex]

Or you could have a masterfile table where you enter the day number and the price. (Day 1 equates to Sunday in Access) So your table would be Day 1 £50 Day 2 £40 etc.

Then if someone stayed for the weekend (Fri/Sat/Sun) that would be dayNumber 6,7 and 1

Col

 

[Elephant]

Thanks Col for the reply.

Most hotels I am using have 2 prices, a High Season and a Low Season. Some Tuesdays could be Low season and some Tuesdays could be High Season. Can Access take into account days through the whole year, rather than a week?

I have the prices stored in a "Hotel Prices Table" ie, the hotel name, the room type, the price, and then price range "RangeStart" (eg 01 June) and "RangeEnd" (eg 20 June). Actual dates are more important than actual days of the week.

So, Price X (eg 50 GBP) is valid start "June 20" End "June 22" and Price Y (75 GBP) is valid start "June 23" and end "June 24".

I want to input in the form that a person stays "June 20 x 4 nights", so the costs are 50+50+50+75, and the form gives me the result of 225 ...

 

[Elephant]

Thanks Tay for your reply.

If I made a table with a date for each day of the year (ie 365 days) and next to it a price for that day, what formula could I use to show the total price for any given period

eg, I would have a Order Details sub-form with Hotel name, arrival date, deaprture date, and then a "total price" which would include the calculation formula ...

Thanks
Steve