The object of this project is to provide an existing customer with an extended solution to a system they already have in place.

Presently, they load 3 different classes of flights to the Caribbean into an Access.MDB and there are queries for each destination and class of flight. An asp is then used to pull the flight information and display the results in a web page. Examples can be seen by selecting 'Caribbean World' from the following URL and then use the drop down menu to view flights to the Caribbean:
http://www.FirstCallTravel.com

They now require to be able to add hotels and hotel rates into the mdb which will also need to have additional tables loaded for things like room types and board basis. The creation of tables is not an issue, it is the creation of queries that are able to carry out all the calculations and permitations for each hotel, room type and board basis (all by destination) that your help is needed.

I will list the logic, it is the SQL that is required in order that a holiday rate table can be displayed from a new asp rather than using static pages. Example of the static pages can be seen also on the link provided above.

I have been advised that it would be better for the query to take place in the mdb and then be called by the asp rather than the asp doing the work but I am open to suggestions.

This page is layed out in my logical thinking terms and is not necessarily the right method as far as programming is concerned but I feel it is enough to give you the right flavour about what we are trying to achieve.

There are around 12 queires that produce results for flights to the caribbean, at this stage we are just interested in the queries based on economy flights which is currently 4.

The example is based on Barbados Economy flights and everything to do with hotels at this stage can be changed as they have only been added to the mdb for the purpose of the project and are not used as part of the live system.

The url will be used to create a hyperlink on the asp. The Htl_MUPD is not used at the moment. The Hol_MUPD (Holiday Mark Up Per Day) amount is used to calculate how much to mark up a holiday. 7 times for weekly and 14 times for 2 weeks.

Tsf relates to transfers and whether they are included or not. Currency code links to the currency table to pick up the rate of exchange for the currency code.

This table shows there are rates from 02-Feb to 15-Feb in a Superior room on an All Inclusive meal plan at Barbados Hotel 1 and a Standard room on a Room Only meal plan at Barbados Hotel 2. There are also rates from 02-Feb to 08-Feb in a Standard room on a Half Board meal plan at Barbados Hotel 2.

The table is shown with each hotel, room type, occupancy level, date, price and board basis as I think this is the simplest way to control the data.

We could use season start and end dates but then the SQL would need to calculate how many dates in a particular season to calculate a total and this may increase the time and cost of the project. We would then need a stopsale table to note when hotels no longer have availability on a particular room type.

Have a board basis supplement table per hotel would also be easier but again it would require more SQL queries to work out if there was a meal plan upgrade available.

Go to the query for Economy Class Barbados flights rates.
Find all the departure dates where the 1Week and/or 2Weeks fields are greater than zero.
Add the mark up value to each result respectively.
The results will be all the dates for 1 and 2 weeks flights to Barbados. This is the TOTAL FLIGHT COST.

If the field for a 1Week rate is not greater than zero then it is ignored and likewise for the 2Weeks field. If the condition applies to both then there are no 1 or 2 week economy flights to Barbados available.

For any dates where the flight rate is not greater than zero, then the hotels do not need to be checked as there is no flight availablity. 7 and 14 nights have to be treated separately for this condition.

Go to the Barbaods hotels query/table.
For each date that a TOTAL FLIGHT COST has been recorded, search the query/table for a matching date.
Is the hotel rate greater than zero?
If not, then no 1 or 2 week holidays will be available as the first night is not available.
If the rate is greater than zero then is there a TOTAL FLIGHT COST for 1 week?
If yes then store the value and go to flight date plus 1.
Is the rate greater than zero for flight date plus 1?
If yes then store the hotel value for flight date plus 1 and go to flight date plus 2.

Repeat this until flight date plus 6. If all values are greater than zero then you have the total hotel price in the contract currency. If any of the hotel rates for flight date-flight date plus 6 are not greater than zero then there is no holiday price available for 1 week on that flight date.

The process needs to be repeated for 2 weeks. If there was no flight price for 7 nights then the calculation needs to start from the beggining. If there was a flight price for seven nights then the calculation process for the hotel for 1 week has already just been performed. If this failed to produce a rate then there is no point trying to calculate 2 weeks as you already no that one of the hotel dates rate is not greater than zero.

If there is no TOTAL FLIGHT COST for 1 week, the process of calculation still needs to take place if there is a TOTAL FLIGHT COST for 2 weeks.

A hotel can only be treated as having 6/13 consequtive nights if the hotel, room type, occupancy and board basis all match.

For each total hotel price in the contract currency;
Go to the hotel table and get the Hol_MUPD and the currency code.
Go to the currency table.
Get the Rate Of Exchange (ROE) for the currency code.
For each total hotel price in the contract currency divide it by the ROE.
The result will be the TOTAL STERLING HOTEL COST.
For each 1 week duration, take the Hol_MUPD amount, multiply by 7 and add to the TOTAL STERLING HOTEL COST to get the 1 WEEK HOLIDAY COST.
For each 2 week duration, take the Hol_MUPD amount, multiply by 14 and add to the TOTAL STERLING HOTEL COST to get the 2 WEEK HOLIDAY COST.

If possible, prices to be displayed to the nearest 5 or 9 integar (no decimals).

The system must match like for like so a hotel that has a price on day 1 for a room type on one board basis and a price the next day for the same room type but a different room type cannot be linked to create part of a holiday price.

Barbados Hotel 1
Departure	Airport	Room/View	Nights	Adult	Occ	Board	Tsf
02-Feb-04	LGW	Superior	7	£945	2	AI
			14	£1,889	2	AI

These are examples of display results based on the data as would be loaded from the above examples.
Price calculation for 7 nights:
Flight £200-£15=£185
Hotel $200 x 7 = $1400 / 1.60 = £875.0000
Hol MUPD = £10 x 7 = £70
Holiday Price for 7 nights is £875 + £70 = £945.00

Price calculation for 14 nights:
Flight £200-£15=£185
Hotel $200 x 14 = $2800 / 1.60 = £1,750.0000
Hol MUPD = £10 x 14 = £140
Holiday Price for 7 nights is £1,750 + £140 = £1,890 round to £1,889

The resulting displays would be correct based on the data displayed in the above example tables and queries.