Date - Criteria - Small but big problem for me - a novice

[arubikdoan]

Can you help me



:: What I have

A Hotel Reservation System

I have Arrival Date and Departure Date. Room availability does depend
on RoomNumber, ArrivalDate and DepartureDate.



:: What I want

Knowing which rooms are available in a specific duration (specified by
Arrival Date and Departure Date). When new guest come in, I want to know
which room is free for him in the duration he wish to stay



:: What I have tried

In Criteria:

Arrival Date field : <[Enter ArrivalDate:] and >[Enter Departure Date]

Depart DateField : <[Enter Arrival Date:] and >[Enter Departure Date]



:: What trouble I got



It did work only when there is a reservation on the room before that.
For example, I have 10 rooms, but have only 6 records for 6 rooms, the 4
rooms left have no records. Then the query does not list these 4 rooms.



:: What should I do?



Can you help me, please?



Thanks in advance



Arubik Doan

 

[Tom Ellison]

Dear Arubik:

I'm going to give bad news. You need to start with better table
structure if you are not going to make this unnecessarily difficult.

What I'd like to see is a table listing each room and attributes of
that room (smoking/no smoking, double/queen/king sized bed, etc) and a
table of guests.

Then you would have a table of transactions. Each transaction would
specify a room, a guest, a transaction type, and a date/time.

Transaction types would be: reservation arrival, reservation
departure, check-in, check-out.

This kind of structure will simultaneously track when/whether a guest
is in the hotel, and when/whether a room is reserved or filled. Other
types of transactions could track when a room must be out of service
for repairs or similar issues.

The "status" of a room on any given date would be determined by the
most recent transaction prior to or on that date. If that is a
reservation departure or a check-out, then it is available.

How you could track the current and future status of a room with one
record per room escapes me.

Can you help me



:: What I have

A Hotel Reservation System

I have Arrival Date and Departure Date. Room availability does depend
on RoomNumber, ArrivalDate and DepartureDate.



:: What I want

Knowing which rooms are available in a specific duration (specified by
Arrival Date and Departure Date). When new guest come in, I want to know
which room is free for him in the duration he wish to stay



:: What I have tried

In Criteria:

Arrival Date field : <[Enter ArrivalDate:] and >[Enter Departure Date]

Depart DateField : <[Enter Arrival Date:] and >[Enter Departure Date]



:: What trouble I got



It did work only when there is a reservation on the room before that.
For example, I have 10 rooms, but have only 6 records for 6 rooms, the 4
rooms left have no records. Then the query does not list these 4 rooms.



:: What should I do?



Can you help me, please?



Thanks in advance



Arubik Doan

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts