Date Range Criteria

[terranauro via AccessMonster.com]

Dear All Access Expert

Hi,
I get stuck with this problem :

I have table with its field :

1. tblRoom : RoomID, RoomTypeID, RoomName, RoomBedID
2. tblRoomDetail : RoomID, RoomDetailID, seasonID, RoomSeasonStartDate,
RoomSeasonEndDate, RoomRate
This table is for determining how much room rate depending on the
season or period
3. tblAccomodation : AccomodationID, AccomodationStartDate,
AccomodationEndDate,RoomID
This table is for recording accomodation activity

the problem arise when I want to select the count the room rate,
the rate must be fit with current season. Therefore I must define
which rate is apply regarding the AccomodationStartDate and
AccomodationEndDate
I have try but it seems the result is not right.

Can somebody here help me ?

So many thanks in advance for your help


Sincerely Yours



Terran

 

[John Spencer]

Part of the problem is that you could have more than one rate apply for
any Accomodation record. Unless you base the rate just on the start date
or end Date of the accomodation.

So how do you handle that problem?
For instance, Accomodation for a specific room is for Jan 1 to Jan 10
Room Detail has two records for that period one for Jan 1 to Jan 3 and a
second for Jan 4 to May 30. Do you charge the first rate for the entire
period, the second rate for the entire period OR do you change the frist
rate for 3 days and the second rate for 7 days?

The first two options can be handled with your current structure. The
last option can be handled by adding another table - a calendar table
that includes all dates between the earliest date and latest date that
you have to calculate. OR you could change the tblRoomDetail table to
have one record for each room for each day (not the best solution).

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Allen Browne]

Can I suggest these fields for tblAccomodation:
- AccomodationID primary key
- ClientID who pays for this booking
- AccomodationStartDate Date/Time when this booking starts
- Days Number how many days
- DayRate Currency how much per day

Now create a query using this table, and type this into the Field row:
AccomodationEndDate: AccomodationStartDate + Days
In the next blank column, type:
Amount: Days * DayRate
Use this query as the source for any form or report where you need the
ending date, or the total amount.

Why include the DayRate? At some point in the future, you will change rates.
If you don't have the field in tblAccomodation, the amount for all your old
bookings with the go wrong.

When you enter a new accomodation booking, how do you get the DayRate into
the field? Use a DLookup() expression in the AfterUpdate event procedure of
the AccomdationStartDate, to look up the correct rate for this time of year,
and assign it to the DayRate field.

What happens if a booking sits astride the dates when the season changes?
For example, a booking for Nov 30 to Dec 3 would have 1 day in fall and 3
days in winter. Using the approach above, it would just charge for the rate
at the start of their booking. If you should handle a change of rate part
way through a booking (part at one rate, part at another), you would need
another table so that one booking can have muliple line items. The start
date, days and day rate would go into the related table.

If most of your bookings are short term (like a motel, not house leasing),
you might even consider creating a separate record for each day in the
related table. This simplifies some tasks (such as quickly determining which
rooms are booked when.)

HTH

 

[terranauro via AccessMonster.com]

Dear Mr. Allen Browne


First of all, I would like to say thank you for your response.
Please allow me to make some comment regarding your suggestion

Can I suggest these fields for tblAccomodation:
- AccomodationID primary key
- ClientID who pays for this booking
- AccomodationStartDate Date/Time when this booking starts
- Days Number how many days
- DayRate Currency how much per day

For tblAccomodation I do have already include the ClientID
For Accomodation and Days, don't you think that it would be better to enter
the date instead of how many days, because I believe it would be reduce
the user error, and the popular format for thing like accomodation would
be StartDate and EndDate
what field to connect between tblAccomodation and table which is store the
room rate sir ?

Now create a query using this table, and type this into the Field row:
AccomodationEndDate: AccomodationStartDate + Days
In the next blank column, type:
Amount: Days * DayRate
Use this query as the source for any form or report where you need the
ending date, or the total amount.

Why include the DayRate? At some point in the future, you will change rates.
If you don't have the field in tblAccomodation, the amount for all your old
bookings with the go wrong.

When you enter a new accomodation booking, how do you get the DayRate into
the field? Use a DLookup() expression in the AfterUpdate event procedure of
the AccomdationStartDate, to look up the correct rate for this time of year,
and assign it to the DayRate field.

It is actually my problem sir, I failed to get the correct rate for specified period. Is it have to be accomplished with some code ? Or just a simple expression sir ?

What happens if a booking sits astride the dates when the season changes?
For example, a booking for Nov 30 to Dec 3 would have 1 day in fall and 3
days in winter. Using the approach above, it would just charge for the rate
at the start of their booking. If you should handle a change of rate part
way through a booking (part at one rate, part at another), you would need
another table so that one booking can have muliple line items. The start
date, days and day rate would go into the related table.

Fortunately, in my case, it mostly would be short term (like 1 or2 day)

If most of your bookings are short term (like a motel, not house leasing),
you might even consider creating a separate record for each day in the
related table. This simplifies some tasks (such as quickly determining which
rooms are booked when.)

HTH

Dear All Access Expert

[quoted text clipped - 25 lines]

Terran

Thank you very much in advance for your suggestion. I am looking forward for
your reply

Sincerely yours



Terran

 

[Lou]

Dear Mr. Allen Browne

First of all, I would like to say thank you for your response.
Please allow me to make some comment regarding your suggestion

Can I suggest these fields for tblAccomodation:
- AccomodationID               primary key
- ClientID                              who pays for this booking
- AccomodationStartDate  Date/Time  when this booking starts
- Days                                   Number       how many days
- DayRate                            Currency      how much per day

For tblAccomodation I do have already include the ClientID
For Accomodation and Days, don't you think that it would be better to enter
the date instead of how many days, because I believe it would be reduce
the user error, and the popular format for thing like accomodation would
be StartDate and EndDate
what field to connect between tblAccomodation and table which is store the
room rate sir ?

Now create a query using this table, and type this into the Field row:
   AccomodationEndDate: AccomodationStartDate + Days
In the next blank column, type:
   Amount: Days * DayRate
Use this query as the source for any form or report where you need the
ending date, or the total amount.

Why include the DayRate? At some point in the future, you will change rates.
If you don't have the field in tblAccomodation, the amount for all your old
bookings with the go wrong.

When you enter a new accomodation booking, how do you get the DayRate into
the field? Use a DLookup() expression in the AfterUpdate event procedureof
the AccomdationStartDate, to look up the correct rate for this time of year,
and assign it to the DayRate field.
It is actually my problem sir, I failed to get the correct rate for specified period. Is it have to be accomplished with some code ? Or just a simple expression sir ?
What happens if a booking sits astride the dates when the season changes?
For example, a booking for Nov 30 to Dec 3 would have 1 day in fall and 3
days in winter. Using the approach above, it would just charge for the rate
at the start of their booking. If you should handle a change of rate part
way through a booking (part at one rate, part at another), you would need
another table so that one booking can have muliple line items. The start
date, days and day rate would go into the related table.

Fortunately, in my case, it mostly would be short term (like 1 or2 day)

If most of your bookings are short term (like a motel, not house leasing),
you might even consider creating a separate record for each day in the
related table. This simplifies some tasks (such as quickly determining which
rooms are booked when.)

[quoted text clipped - 25 lines]

Terran

Thank you very much in advance for your suggestion. I am looking forward for
your reply

Sincerely yours

Terran

Missing from this discussion is the topic of availability.

Not all rooms will be available every day. Some will be rented.
Others will be "off-line" for maintenance.

There needs to be tblAvailability, with one row for each room-day
combination.

create table tblAvailability
(
RoomID
AvailabilityDate
DailyRate
CommittedTo
)

This is a dynamic table. Each day, the current day's row is deleted
and a new row is added to extend the availability window.

When the room is booked, the field CommittedTo is populated by the
tblAccommodation.AccommodationID. If the room must be "taken off-
line", the CommittedTo is set to a value that blocks availability.

To calculate the cost of the accommodation, one queries

SELECT RoomID, sum( DailyRate )
from tblAvailability inner join tblAccommodation
on tblAvailability.CommittedTo = tblAccommodation.AccommodationID )

 

[Allen Browne]

The code you need will depend on things like how you determine the dates
when your 'season's change.

I'm not clear about exactly how you have things set up, but the code would
be someting like this:

Private Sub AccomodationStartDate_AfterUpdate
Dim strWhere As String
If Not IsNull(Me.AccomodationStartDate) Then
strWhere = Format(AccomodationStartDate, "\#mm\/dd\/yyyy\#") & _
" Between RoomSeasonStartDate And RoomSeasonEndDate"
Me.DayRate = DLookup("RoomRate", "tblRoomDetail", strWhere)
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.