Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rate table (A2K SR3)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rate table (A2K SR3)

    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.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rate table (A2K SR3)


  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rate table (A2K SR3)

    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>

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rate table (A2K SR3)

    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

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rate table (A2K SR3)

    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

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rate table (A2K SR3)

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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rate table (A2K SR3)

    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].

  9. #9
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rate table (A2K SR3)

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

    Tom

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rate table (A2K SR3)

    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, ...

  11. #11
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rate table (A2K SR3)

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

  12. #12
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rate table (A2K SR3)

    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

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rate table (A2K SR3)

    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?

  14. #14
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rate table (A2K SR3)

    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

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rate table (A2K SR3)

    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.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •