Design / Execution Question

[Bunky]

I have been asked to write an application that will produce the amount to
charge a person who wants to rent a room. I have already designed a rate
table that contains a resort number, From and To rate dates, room type, and
then 4 different daily rates to charge. My problem arises when a person
whats to stay at the resort over more than one From / To rate period. The
operator will enter the dates the person wants to stay, the resort, and the
room type but how do I only bring back the rates for the days wanted?

Example
Resort FromDt ToDt ARate BRate CRate DRate RmType
87 6/27 07/02 4 5 6 7
2br
87 7/03 07/04 9 12 15 8
2br
87 07/05 08/16 5 6 7 8
2br
87 08/17 09/03 4 6 6 6
2 br

Person wants to stay 07/01 to 07/07. How would I return the rates for all
three rows?

Any help is appreciated.
Thanks

 

[TedMi]

If you're writing SQL:
SELECT ARate, BRate, CRate, DRate FROM tblRate
WHERE Resort = x AND RmType = Y AND (StayFrom>=FromDt OR StayTo>FromDt)

The parens around the OR are essential!

If you're using the grid, in the FromDt column:
Criteria: <=[StayFrom]
or: <[StayTo]

Note that the ToDate does not enter into the criteria at all!
-TedMi

 

[ghetto_banjo]

I would use some VBA, which may or may not be best way to go about
it. If you are familiar with VBA code it won't be too bad though.

Here is a messy outline not knowing about how your forms/tables are
defined but hopefully helps you in right direction.


currentDate = FromDate


While currentDate <= EndDate

*Query here, or use DLookup
ARateValue = DLookup("[ARate]", "Rate Table Name", "FromDt <= #"
& currentDate & "# AND ToDt >= #" & currentDate &"# ")
BRateVallue = ....


*Do whatever with those rate values now, send to form/report,
store in array variables , etc

*increment date for loop
currentDate = DateAdd("d", 1, currentDate)

Wend

 

[Bunky]

Thank you for responding.

Unfortunately, that code brings back all the rows that are prior to the
ending date of the rate; not just the ones in the range I want. The range I
want may be only 3 rows ie 07/01/09 to 07/07/09 but it is bringing back
everything that meets the end date criteria.

If you're writing SQL:
SELECT ARate, BRate, CRate, DRate FROM tblRate
WHERE Resort = x AND RmType = Y AND (StayFrom>=FromDt OR StayTo>FromDt)

The parens around the OR are essential!

If you're using the grid, in the FromDt column:
Criteria: <=[StayFrom]
or: <[StayTo]

Note that the ToDate does not enter into the criteria at all!
-TedMi

I have been asked to write an application that will produce the amount to
charge a person who wants to rent a room. I have already designed a rate
table that contains a resort number, From and To rate dates, room type,
and
then 4 different daily rates to charge. My problem arises when a person
whats to stay at the resort over more than one From / To rate period. The
operator will enter the dates the person wants to stay, the resort, and
the
room type but how do I only bring back the rates for the days wanted?

Example
Resort FromDt ToDt ARate BRate CRate DRate RmType
87 6/27 07/02 4 5 6 7
2br
87 7/03 07/04 9 12 15 8
2br
87 07/05 08/16 5 6 7 8
2br
87 08/17 09/03 4 6 6 6
2 br

Person wants to stay 07/01 to 07/07. How would I return the rates for all
three rows?

Any help is appreciated.
Thanks

 

[Bunky]

Thank you for responding.

While I am not too good on VBA code, you did give me an idea to use the
DLookup. Maybe.

 

[TedMi]

Here's another approach that dosen't need VBA. It requires two queries:
Q1: SELECT ... FROM tblRate WHERE StartOfStay < ToDate
Q2: SELECT ... FROM Q1 WHERE EndOfStay>=FromDate

You can apply Resort and RoomType criteria to either of these queries, but
applying them to both is redundant.

Caveat: Your table design is based on the premise that each ToDate be one
day prior to the next record's FromDate. What if they're not?

-TedMi

Thank you for responding.

While I am not too good on VBA code, you did give me an idea to use the
DLookup. Maybe.

I would use some VBA, which may or may not be best way to go about
it. If you are familiar with VBA code it won't be too bad though.

Here is a messy outline not knowing about how your forms/tables are
defined but hopefully helps you in right direction.


currentDate = FromDate


While currentDate <= EndDate

*Query here, or use DLookup
ARateValue = DLookup("[ARate]", "Rate Table Name", "FromDt <= #"
& currentDate & "# AND ToDt >= #" & currentDate &"# ")
BRateVallue = ....


*Do whatever with those rate values now, send to form/report,
store in array variables , etc

*increment date for loop
currentDate = DateAdd("d", 1, currentDate)

Wend

 

[Michael Gramelspacher]

Thank you for responding.

Unfortunately, that code brings back all the rows that are prior to the
ending date of the rate; not just the ones in the range I want. The range I
want may be only 3 rows ie 07/01/09 to 07/07/09 but it is bringing back
everything that meets the end date criteria.

Maybe you should have a Calendar table with every date for the next 10 years.

CREATE TABLE [Calendar] (
[Calendar_Date] DATETIME,
CONSTRAINT pk_calendar PRIMARY KEY ([Calendar_Date])
);

Maybe a Rates table like this.

CREATE TABLE [Rates] (
[Rate_Code] CHARACTER(1) NOT NULL,
[Previous_Date] DATETIME NOT NULL,
[Start_Date] DATETIME NOT NULL,
[End_Date] DATETIME,
[Rate_Amount] DECIMAL(6, 2) NOT NULL,
CONSTRAINT pk_rates PRIMARY KEY ([Rate_Code], [Start_Date], [Rate_Amount])
);

Daily Rates are then:

SELECT Calendar.Calendar_Date, Rates.Rate_Amount
FROM Calendar, Rates
WHERE (((Calendar.Calendar_Date) Between [Rates].[Start_Date] And [Rates].[End_date]));

 

[Bunky]

TedMi,

Now that should work; I will try that soon. I just got pulled off that for
another fire so I will test later. Thanks a bunch for the response. I will
let you know when I get a chance to test it. Gotta go put out another fire!

Kent

Here's another approach that dosen't need VBA. It requires two queries:
Q1: SELECT ... FROM tblRate WHERE StartOfStay < ToDate
Q2: SELECT ... FROM Q1 WHERE EndOfStay>=FromDate

You can apply Resort and RoomType criteria to either of these queries, but
applying them to both is redundant.

Caveat: Your table design is based on the premise that each ToDate be one
day prior to the next record's FromDate. What if they're not?

-TedMi

Thank you for responding.

While I am not too good on VBA code, you did give me an idea to use the
DLookup. Maybe.

I would use some VBA, which may or may not be best way to go about
it. If you are familiar with VBA code it won't be too bad though.

Here is a messy outline not knowing about how your forms/tables are
defined but hopefully helps you in right direction.


currentDate = FromDate


While currentDate <= EndDate

*Query here, or use DLookup
ARateValue = DLookup("[ARate]", "Rate Table Name", "FromDt <= #"
& currentDate & "# AND ToDt >= #" & currentDate &"# ")
BRateVallue = ....


*Do whatever with those rate values now, send to form/report,
store in array variables , etc

*increment date for loop
currentDate = DateAdd("d", 1, currentDate)

Wend

 

[Bunky]

Michael,

Thank you for responding.

Unfortunately, I do not think that would be a viable solution because I did
not tell you we have over 65 resorts that will all have rate tables. It
might be worth a shot but I think I am going to try and stick with queries at
this point. But I will keep this solution in my hip pocket if I have trouble
with the queries.

Thank you for your time and knowledge,
Kent

Thank you for responding.

Unfortunately, that code brings back all the rows that are prior to the
ending date of the rate; not just the ones in the range I want. The range I
want may be only 3 rows ie 07/01/09 to 07/07/09 but it is bringing back
everything that meets the end date criteria.

Maybe you should have a Calendar table with every date for the next 10 years.

CREATE TABLE [Calendar] (
[Calendar_Date] DATETIME,
CONSTRAINT pk_calendar PRIMARY KEY ([Calendar_Date])
);

Maybe a Rates table like this.

CREATE TABLE [Rates] (
[Rate_Code] CHARACTER(1) NOT NULL,
[Previous_Date] DATETIME NOT NULL,
[Start_Date] DATETIME NOT NULL,
[End_Date] DATETIME,
[Rate_Amount] DECIMAL(6, 2) NOT NULL,
CONSTRAINT pk_rates PRIMARY KEY ([Rate_Code], [Start_Date], [Rate_Amount])
);

Daily Rates are then:

SELECT Calendar.Calendar_Date, Rates.Rate_Amount
FROM Calendar, Rates
WHERE (((Calendar.Calendar_Date) Between [Rates].[Start_Date] And [Rates].[End_date]));

 

[John Spencer MVP]

A query like this should work

SELECT Rates.*
FROM Rates
WHERE [StayStart] <= Rates.ToDt and [StayEnd]>=Rates.FromDt
And Resort = 87 And RmType=8

That should return the rows you are interested in. It won't tell you which
rate to charge for each day. To get the relevant rates for each day, I would
add a Calendar table with a date field with one record for each day in the
period that you are doing business - basically the earliest Rates.FromDt to
the latest Rates.ToDt

If you can add that table then you can get the daily rates with a query that
looks like the following.

SELECT Calendar.TheDate
, Rates.*
FROM Rates INNER JOIN Calendar
ON Calendar.TheDate >= Rates.FromDt and Calendar.TheDate <=Rates.ToDt
WHERE [StayStart] <= Rates.ToDt
and [StayEnd]>=Rates.FromDt
And Resort = 87 And RmType=8


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