Hotel registration 1

[HuginMunin]

I used to be quite competent with Access but it's a long time ago and I forget easily..

Just wondering if anyone could help me with this simple hotel registration database. I need two tables: customers and rooms. In customers I have entered fields such as name, address etc. I have also entered a field called room number. In the other table: rooms, I have entered all the room numbers of the hotel. What I want is a drop down list in the form I have created, so that you can select a room. My problem here is that the form allows double-booking (i.e. several registrations on the same room). How do I deal with this?

Also, the relations are a bit confusing. I've made an ID field with autonumber to be the primary key in the customers-table and I only have one field in the rooms table, namely Room Number. This is the primary key in this table and it's connected to the room number field in the customers table with a one-to-many relations going FROM the room table. Is this the best way of doing it?

Furthermore I am to create two reports: current room availability and customers checking out within 48 hours. I have completely forgotten about queries, so can someone please enlighten me?

Appreciate the help!

 

[Golom]

You appear to need a "Bookings" table of the form
[tt]
tblBookings
CustomerID (PK)
RoomNo (PK)
CheckInDate (PK)
CheckOutDate
QuotedRate
... Other Fields As Needed ...
[/tt]
If you then enter a request to book a room for a specified date range then a query of the form

Select RoomNo

From   Rooms R 

Where R.RoomNo NOT IN
      (Select RoomNo From Bookings
       Where (CheckInDate <= [Date To Check In] 
              AND CheckOutDate >= [Date To Check In])
          OR (CheckInDate < [Date To Check Out] AND
              CheckOutDate > [Date To Check Out])
      )

Here [Date To Check In] and [Date To Check Out] are the dates that you want to book. You would probably use a control (such as a calandar control) on the form as the source rather than an input box as shown here.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]