Rate table (A2K SR3)

[TomGoodwin]

I have a database where the Rate information is coded into a Select Case (see below) and I want to modify the database to store the rate information in a table to facilitate changing the rates.

I have a frmCheck-In where the operator enters a room number. There is a tblRoom whose fields are RumberNumber and RoomTypeID. There is also a new tblRoomType whose fields are RoomTypeID, RoomType, WeeklyRate, MonthlyRate, DailyRate, and BusinessRate.

Question 1: Once the Room Number is entered, how can the RoomType be displayed?

The operator then selects the Pay Code (see below) from a drop down list and enters either 1 or 2 in the Occupants field (new field).

Question 2: How can the Room Rate to be displayed and stored in the RoomRate field in tblRental.

Examples of the various combinations for 1 room type:

If [RoomTypeID]=1 and [PayCodeID] =1 and [Occupants]=1 then[RoomRate]=[WeeklyRate]

If [RoomTypeID]=1 and [PayCodeID] =1 and [Occupants]=2 then [RoomRate]=[WeeklyRate]+20

If [RoomTypeID]=1 and [PayCodeID] =2 and [Occupants]=1 then [RoomRate]=[MonthlyRate]

If [RoomTypeID]=1 and [PayCodeID] =2 and [Occupants]=2 then [RoomRate]=[MonthlyRate]+60

If [RoomTypeID] =1 and [PayCodeID =3 and [Occupants]=1 then
[RoomRate]=[Daily]

If [RoomTypeID] =1 and [PayCodeID =3 and [Occupants]=2 then
[RoomRate]=[Daily]+5

If [RoomTypeID] =1 and [PayCodeID =5 and [Occupants]=1 then
[RoomRate]=[Business]

If [RoomTypeID] =1 and [PayCodeID =5 and [Occupants]=2 then
[RoomRate]=[Business]+10

PayCodeID 4 is Non-Room so If [RoomTypeID]=4 then [RoomRate]=0 regardless of the RoomType.

There are 7 Room Types and 5 Pay Codes so the If statements would contain all the various combinations of Room Types, Pay Codes and Number of Occupants.

Room Types are Standard, Mini, Double, Standard King, Executive, Jr. Executive and Handicap

Pay Codes are Weekly, Monthly, Daily, Non-Room and Business

The existing Select Case is attached as a word file

The operator has the ability to over ride the standard or rack rate. The room rate and rack rate are both saved so we can produce a report to show when the manager has charged something different from the rack rate.

The Business rate is available for only certain type of rooms so the manager get an error message if a business rate is entered for a room that in not entitled to the business rate.

Any assistance would be greatly appreciated.

Tom

[HansV]

1. You could add tblRoomType to the query that acts are Record Source of the form, linked to tblRoom on RoomTypeID (outer join, all records from tblRoom and only related records from tblRoomType). Add the RoomType and rate fields to the query. As soon as the operator selects a room, the type and rates will be available.

2. Will have to think about that; perhaps someone else will come up with something in the meantime.

[Support4John]

May find something here:

http://groups-beta.google.com/group/comp.d...arch+this+group

HTH, John

[HansV]

I wouldn't store the rates in tblRoomType, but in a separate table tblRoomRates:

<table border=1><td align=center>RoomTypeID</td><td align=center>PayCodeID</td><td align=center>Rate</td><td align=right>1</td><td align=right>1</td><td align=right>$ 249.00</td><td align=right>1</td><td align=right>2</td><td align=right>$ 969.00</td><td align=right>1</td><td align=right>3</td><td align=right>$ 59.00</td><tr><td align=right>1</td><td align=right>4</td><td align=right>

[TomGoodwin]

Thanks for the Web site. I have only hurriedly searched the site. Initially I was not able to find much helpful information. Will look again when I have a little more time.

Thanks

[TomGoodwin]

Many thanks for the table layouts. I will try to adapt them to my database this weekend and see if I can made the lookups work.

Tom

[TomGoodwin]

I am having a problem getting RentCalc0 to properly execute with the joins suggested. When I open qryRentCalc0, I am getting an error message

[HansV]

You have several expressions involving IIf and PayCodeID in which you don't specify from which table PayCodeID should be taken. It could be from tblRental or from tblPayCode; you want the one from tblPayCode. Replace [PayCodeID] with [tblRental].[PayCodeID].

[TomGoodwin]

I must be missing something. I did a Find and Replace on qryRentCalc0 and could find only one instance of PayCodeID.

Tom

[HansV]

Strange. I am attaching the database with the modified query, and including a part of the original SQL below.

..., IIf([PayCodeID]=5,[RoomRate]/3,IIf([PayCodeID]=2,[RoomRate]/[NumberofDays],IIf([PayCodeID]=1,[RoomRate]/7,IIf([PayCodeID]=3 Or [PayCodeID]=4 Or [RoomRate]="",[RoomRate],0)))) AS DailyRoomRate, ...

[TomGoodwin]

My error. I hit skip by mistake instead of no so it didn't find the other instances of PayCodeID in qryRentCalc0.

[TomGoodwin]

Prior to modifying the database as per your guidance, I had a report to show all the different room rates, rptRoomType. I have created a new qryRoomTypeandRates but as designed it selects 4 records for each room instead of one record for each room.

I have attached a stripped down copy of the database.

Your great assistance is always greatly appreciated.

Tom

[HansV]

Five, in fact: one record for each Room - PayCodeID combination. That's to be expected, since one room has different rates depending on the pay code. If you don't want this multiplication, you can remove tblRoomRates from the query, but of course you'll lose the rate information. What exactly do you want to accomplish?

[TomGoodwin]

What I am trying to accomplish is to print a report that would list each room number and beside it the various rates for that room so that the manager can conveniently see what rates have been loaded into the system. The idea would be like a rate board. The layout of the existing rptRoomType is what I am looking for.

Tom

[HansV]

See if the attached does what you want. I have used two crosstab queries - one for the single occupancy rates and another one for the double occupancy rates, and combined then in qryRoomTypeAndRates. The report has been adapted.