Lance-
I'll take a stab at the design of your "rates" table:
RateID
RateDescription
Rate (rate per day)
ValidFrom (would be 1/1/1900 for the "normal" rate)
ValidTo (would be 12/31/2099 for the "normal " rate)
MinimumNights (would be 1 for the "normal " rate)
FreeNights (number of nights free for each multiple of minimum - 0 for
"normal" rate)
Rows might look like:
1 Normal rate $199 1/1/1900 12/31/2099 1 0
2 Stay 4, get 5th free $199 1/15/2012 3/1/2012 4 1
3 Minimum stay special $185 6/1/2012 9/1/2012 5 0
I think that about covers it. Note that date ranges could overlap!
So, to stay 7 day days starting Feb 1, the price would be 6 * $199. To stay 10
days starting Feb 1, the price would be 8 * $199.
Rate calculation:
If FreeNights <> 0 Then
DaysFree = Int(TotalDays / FreeNights)
Else
DaysFree = 0
End If
TotalPrice = (TotalDays - DaysFree) * Rate
You could load the table into an array to avoid having to read through the
"rates" table, but you could do it with one pass of the "rates" table -
calculating the total price for each rate record that is valid for the booking
dates and saving the lowest total price along the way. The only tricky part
would be handling a reservation where some of the days fall outside of the valid
range of dates. You could decide to calculate the rate only if the entire stay
is within the valid range - but that would be a hotel policy decision.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Lance Gallant
Sent: Tuesday, October 11, 2011 12:09 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Not sure how to approach this
I am in the process of writing a "best available price" function for a
rooms booking system. I run through 4 possible special price scenarios
on a booking (normal price, stay for x and pay for y nights, minimum
stay, booking made between x and y date etc) and in each scenario set
3 variables. Not all bookings would qualify for all scenarios, so I
could get null values assigned.
Special_ID - Can be null
Description - text description of the special
Value - Calculated Value for the scenario
Where I am stuck is with taking my values and putting them into some
form which I can use in the following way:
I need to present the user with the available option (listbox?) so
that they may make a selection of which price they wish to
use. I wish to pre-select the lowest price option. Do I need to create
array, or should I be looking to create a temporary table. I am not
familiar with arrays in vba.
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar