Hotel database?

Question

I am doing a database for a hotel reservation system on access. I have a table called room category. All of the single rooms are numbers 100-103, all doubles 122-125, all suites 111-113, etc....

I need an IF formula in the room number field which says, if the room type field = single, then room numbers between 100-103, if souble then 122-125, etc......

Can anyone help please?

Thanks for that answer, but how do you put that into the table design, i'm assuming in the input mask.

Also, as it is one field, you need to link the IF statements

Thanks for your suggestions, but I cannot alter the design of the tables.

What I need is an if statement

Answer 1

I know this is going to sound a bit backwards and verbose, but you really need to setup the tables a bit differently. What you are in need of is what is called a "conjunction table" which you can then query to give you this exact information. This is how it works...

You have a table called "rooms" which lists all the room numbers for all types of rooms. Your room numbers are your primary key by the way. In this table you could also put fields like how big the room is, extra features, what services it has etc.

You then need a tabled called "type_of_rooms" where you have the fields typeid, typename. Here you list each type of room you have. A single, double, a suite, presidential sweet, jungle room etc. This table could also hold your base pricing for that type or room etc.

Now the third table is called "rooms_types" and contains two fields which are both keys. One goes to the rooms table (room number field) and the other goes to the type_of_room table (to the typeid field).

So what does this all get us? Well... this new rooms_types of table is going to allow you to do many things. First it allows you to associate multiple room types with a single room. So lets say you have the following data in the "type_of_rooms" table...

typeid, typename

1, single
2, double
3, suite
4, presidental
5, executive

Then you have the following data in the rooms table...

roomnumber, base_price

100, $20.00
122, $40.00
111, $60.00

Now your new table "room_types" can contain data like...

roomnumid, roomtypeid

100, 1
101, 1
123, 2
111, 3
111, 4

This data above shows that room number 111 is a suite, but is all a presidential room (or a presidential suite). Understand? Ok, so now to the query you need to understand this. I want to get all rooms which are singles...

Select roomnumberid from rooms_types where roomtypeid = 1

The records return are "100 and 101". So how you then display it on the page or program is completely up to you.

The advantage is to all this is flexibility. So lets say you decided to upgrade room 100 from a single to a new suite. You can go to the new "rooms_types" table and change number 100 to have a roomtypeid of 3 and thats it!

We run the query again and it will return the results room number 101. Lets say you want to change the base price for all single rooms, you go to the rooms table and change the base price to $35.00 and suddenly all your single rooms are now changed.

I know this is a bit longer than you had planned, but I hope you followed me here. This solves your immediate formula problem plus gives you the ability to change values quickly and easily and also help keep your database size down. It is a method called "database normalization".

Good luck with this project!

Answer 2

hotel databases available on http://www.ebusiness-indya.com

Answer 3

If (single)
for (i = 100, i < 103, i++)
blah, blah, blah
if (double)
for (i = 122, i < 125, i++)
blah, blah, blah

Is that what you are looking for?