Hotel Reservations System - Vacancy

[Melanie S. via AccessMonster.com]

I hope I'm posting this in the right category.. any ideas on how to look for
room availability? I have already created forms for adding reservations for
both existing and new guest, and I would like to add a button on those forms
to check for vacancy between dates entered.. I have thought about a sort of
calendar maybe but I have absolutely no idea how to make one, any help please?


Plus, before looking for rooms availability, I think I have to delete all
unconfirmed reservations - I have made a delete query where criteria for:
confirmation = false
datein < date()
But this deletes only the records in the tblGuests and how to use this on a
form anyway? Or maybe I'm using the wrong method? Plz help



tblGuests
------------------
guestID (PK) (autonumber)
name
address
country
phone


tblBookings
------------------
guestID
roomno
datein
dateout
confirmation (yes/no)


tblRooms

 

[Tim Ferguson]

I would like to add
a button on those forms to check for vacancy between dates entered..

you could make a query that does something like this:

SELECT ALL RoomNumber
FROM Rooms
WHERE RoomNumber NOT IN
( SELECT ALL RoomNumber
FROM Bookings
WHERE DateIn <= RequiredDateEnd
AND RequiredDateStart <= DateOut
AND Confirmed = TRUE
)
ORDER BY RoomNumber

The inside select gathers all the rooms that are booked during the
requested period (I think! -- this is untested code...), and the outside
select will subtract that list from all the rooms. There is no allowance
here for rooms that are unavailable due to maintenance, the mother-in-law
staying etc etc. of course.

I
have thought about a sort of calendar maybe but I have absolutely no
idea how to make one, any help please?

I always get into trouble for saying this but... I still think Access is
not the best platform for this. You can buy active-X components that will
present diaries etc. If you need to roll your own, then a platform like
Excel is already built round a row-and-column user interface and is
ideally suited to calendaring.

Plus, before looking for rooms availability, I think I have to delete
all unconfirmed reservations - I have made a delete query where
criteria for: confirmation = false
datein < date()
But this deletes only the records in the tblGuests and how to use this
on a form anyway? Or maybe I'm using the wrong method? Plz help

The idea of flushing reservations seems about right, but you need to put
it in the context of the business processes. Waiting until the day of
intended arrival seems a bit late to give up on it, when you could have
let the room to someone who would have turned up. How would you undertake
this process in real life? Do bookers have a deadline to confirm by? Do
you look for these in advance? How do you make rooms available again?
What happens if you let an unconfirmed room and then the original booker
comes back and confirms late? Do you need a list of regular defaulters?

These are all the questions that you should be asking _way_ before
starting to type in tables and fields. Ideally, the database should mimic
or (occasionally) improve upon the business practices -- it should never
force unwelcome or improper procedures. Reckon on spending time on
systems analysis for at least two-thirds of the whole development period:
it's time well spent.

Hope that helps


Tim F

 

[Melanie S. via AccessMonster.com]

you could make a query that does something like this:

SELECT ALL RoomNumber
FROM Rooms
WHERE RoomNumber NOT IN
( SELECT ALL RoomNumber
FROM Bookings
WHERE DateIn <= RequiredDateEnd
AND RequiredDateStart <= DateOut
AND Confirmed = TRUE
)
ORDER BY RoomNumber

pardon the stupid newbie question but where do I put the code in the query?
and how does I make it to work?.. I mean, I'm thinking about perhaps a form
to input the two dates and room category, and click on a button to display
the rooms available between those dates.. how do I do this - the listing of
rooms available?

I always get into trouble for saying this but... I still think Access is
not the best platform for this. You can buy active-X components that will
present diaries etc. If you need to roll your own, then a platform like
Excel is already built round a row-and-column user interface and is
ideally suited to calendaring.

excel what? lol I have only very basic knowledge of excel ^^;

The idea of flushing reservations seems about right, but you need to put
it in the context of the business processes. Waiting until the day of
intended arrival seems a bit late to give up on it, when you could have
let the room to someone who would have turned up. How would you undertake
this process in real life? Do bookers have a deadline to confirm by? Do
you look for these in advance? How do you make rooms available again?
What happens if you let an unconfirmed room and then the original booker
comes back and confirms late? Do you need a list of regular defaulters?

These are all the questions that you should be asking _way_ before
starting to type in tables and fields. Ideally, the database should mimic
or (occasionally) improve upon the business practices -- it should never
force unwelcome or improper procedures. Reckon on spending time on
systems analysis for at least two-thirds of the whole development period:
it's time well spent.

Hope that helps

Tim F

well, bookers pay a deposit (usually cheque) and they say on which date they
will do it and once the receipt is obtained, confirmation for reservation is
consolidated but if they don't pay at the date due, the reservation is
cancelled..

 

[Tim Ferguson]

you could make a query that does something like this:

SELECT ALL RoomNumber

[snip]

pardon the stupid newbie question but where do I put the code in the
query? and how does I make it to work?..

It's not stupid at all, but the answer depends on your application.

I mean, I'm thinking about
perhaps a form to input the two dates and room category, and click on
a button to display the rooms available between those dates..

That makes sense: you could put the query into the rowsource for a list
box, and connect the RequiredDateStart and RequiredDateEnd to the text
boxes -- you can do this using VBA or using paramters in the query.

excel what? lol I have only very basic knowledge of excel ^^;

Excel uses the same VBA as Access does, so if you can program in Access
you can programme in Excel. You can even use the same DAO or ADO methods
taking the data from your original .mdb file so there is no duplication.

well, bookers pay a deposit (usually cheque) and they say on which
date they will do it and once the receipt is obtained, confirmation
for reservation is consolidated but if they don't pay at the date due,
the reservation is cancelled..

There is no mention in the design you mentioned above about payments or
ConfirmationDates etc: that is okay because it's better to present just
the problem you are asking about. Just as long as _you_ know what the
whole picture is and how you are going to solve it...

All the best

Tim F

 

[Melanie S. via AccessMonster.com]

That makes sense: you could put the query into the rowsource for a list

box, and connect the RequiredDateStart and RequiredDateEnd to the text
boxes -- you can do this using VBA or using paramters in the query.

the thing is I still don't know how to make the query.. I suppose I should
drag the roomno in the field below and put something in the criteria but what?
And what exactly must I do to connect the RequiredDateStart and
RequiredDateEnd to the text boxes on the form?

 

[Tim Ferguson]

the thing is I still don't know how to make the query.. I suppose I
should drag the roomno in the field below and put something in the
criteria but what?

The simplest way to create any query is usually in the query grid, but
sometimes you just have to work with the SQL pane itself. Select View ->
SQL and then paste in the query as I put it in the post:

Now you'll have to change the field and table names I made up for the
actual ones in your database: swap RoomNo for RoomNumber, tblRooms for
Rooms and so on.

And what exactly must I do to connect the
RequiredDateStart and RequiredDateEnd to the text boxes on the form?

Put these two lines at the start of the query (i.e. above the first
SELECT):-

PARAMETERS Forms!CheckBookingForm!txtRequestStartDate DATETIME,
Forms!CheckBookingForm!txtRequestEndDate DATETIME;

and don't forget the comma and the semicolon. Again, swap the actual name
of your form for my guess of CheckBookingForm and same for the two text
box controls.

Also replace the placeholders I guessed in the main part of the query
RequiredDateEnd and RequiredDateStart for the full Forms!etc!etc syntax
that you just used for the parameters above: they must match exactly.

If that all works, try changing the View -> Datasheet. You will be asked
for two dates: just type in test values for now. It it is still working
you are ready to go. Save the querydef with decent name
(qryAvailableRoomsByDate, for example).

Now, you need to connect this to the listbox. In the properties of the
list box, mark Enabled = False and RowSourceType = Query and RowSource =
qryAvailableRoomsByDate or whatever you just called it. You want the list
box disabled to start with, because when the form opens it won't have
legal values to query on and will produce an error.

The last thing is to use the command button to activate the list box. Use
the OnClick event to do something like

with lisAvailableRooms ' or whatever you call the list box
.Enabled = True ' wake it up
.Requery ' and make it fetch the data
End with

Actually, you need to check that it has valid date values in the text
boxes and so on, but you can put that in later.

It sounds more complex than it really is. The only hard part is getting
the SQL correct because the grid is not much help for generating nested
subqueries.

Let us know how you get on!
B Wishes


Tim F

 

[Melanie S. via AccessMonster.com]

The simplest way to create any query is usually in the query grid, but

sometimes you just have to work with the SQL pane itself. Select View ->
SQL and then paste in the query as I put it in the post:

oh ok, I never knew about the SQL pane..

Now you'll have to change the field and table names I made up for the
actual ones in your database: swap RoomNo for RoomNumber, tblRooms for
Rooms and so on.


Put these two lines at the start of the query (i.e. above the first
SELECT):-

PARAMETERS Forms!CheckBookingForm!txtRequestStartDate DATETIME,
Forms!CheckBookingForm!txtRequestEndDate DATETIME;

and don't forget the comma and the semicolon. Again, swap the actual name
of your form for my guess of CheckBookingForm and same for the two text
box controls.

Also replace the placeholders I guessed in the main part of the query
RequiredDateEnd and RequiredDateStart for the full Forms!etc!etc syntax
that you just used for the parameters above: they must match exactly.

If that all works, try changing the View -> Datasheet. You will be asked
for two dates: just type in test values for now. It it is still working
you are ready to go. Save the querydef with decent name
(qryAvailableRoomsByDate, for example).

Now, you need to connect this to the listbox. In the properties of the
list box, mark Enabled = False and RowSourceType = Query and RowSource =
qryAvailableRoomsByDate or whatever you just called it. You want the list
box disabled to start with, because when the form opens it won't have
legal values to query on and will produce an error.

The last thing is to use the command button to activate the list box. Use
the OnClick event to do something like

with lisAvailableRooms ' or whatever you call the list box
.Enabled = True ' wake it up
.Requery ' and make it fetch the data
End with

Actually, you need to check that it has valid date values in the text
boxes and so on, but you can put that in later.

It sounds more complex than it really is. The only hard part is getting
the SQL correct because the grid is not much help for generating nested
subqueries.

Let us know how you get on!
B Wishes

Tim F

hey, yeah it works fine! But just one little thing, not really important but
it just look a little bit weird.. when I click on the command button, the
list of rooms available appears alright but the textbox value entered for
'RequiredDateEnd" changes to 0:00

thank you for your help so far, much appreciated =)

 

[Melanie S. via AccessMonster.com]

Thank you again Tim! Thanks to you, I have learnt a lot and been able to make
many other coding and stuff for my other forms/queries but I'm getting a
problem with this last thing:

I want to delete all unconfirmed reservations when the user clicks the
command button of the frmCheckAvailability form, I added the code below in
the OnClick event but it said:

Run time error '3086':
Could not delete from specified tables

--------------- Code Added -------------------

DoCmd.RunSQL ("DELETE [tblGuests].* FROM [tblGuests] INNER JOIN tblBookings
ON tblGuests.guestID = tblBookings.guestID WHERE (((tblBookings.confirmation)
=False) AND ((tblBookings.confirmationdate)<Date()+1))")

 

[Melanie S. via AccessMonster.com]

Ah it's ok I found my mistake - should have just added DISTINCTROW after
DELETE!
And I turned off the warning "You are about to delete etc etc" too (DoCmd.
SetWarnings False)

DoCmd.RunSQL ("DELETE DISTINCTROW [tblGuests].* FROM [tblGuests] INNER JOIN
tblBookings ON tblGuests.guestID = tblBookings.guestID WHERE (((tblBookings.
confirmation)=False) AND ((tblBookings.confirmationdate)<Date()+1))")

 

[Tim Ferguson]

I want to delete all unconfirmed reservations when the user clicks the
command button of the frmCheckAvailability form, I added the code
below in the OnClick event but it said:

DoCmd.RunSQL "DELETE [tblGuests].* " & _
"FROM [tblGuests] INNER JOIN tblBookings " & _
" ON tblGuests.guestID = tblBookings.guestID " & _
"WHERE tblBookings.confirmation = False " & _
" AND tblBookings.confirmationdate < Date()+1"

This actually deletes the Guest from the Guests table, which is probably
not what you want because

(a) it will fail if there are other reservations waiting (or historical)
for the same Guest, and

(b) you might want the same Guest to book again in the future and it
looks so much better if you already know him or her

Another point: it might not be a good idea to do something destructive
like deleting records (remember: there is no Undo for this!!) as a side
effect of something innoccuous like checking for availability. I would
really recommend having a separate form to do cleaning tasks like
removing unconfirmed reservations and widowed guests (no -- I mean those
who don't have any reservation records to go with!)

Glad it's working though. All the best


Tim F

 

[Melanie S. via AccessMonster.com]

actually yeah I wanted to delete the guest but stupid me didn't think about a
guest who have several bookings... make another form just to delete
uncomfirmed reservations and widowed guests(lol)? but having to go to that
form first to delete, check rooms availability on another and then go to the
reservations form to add one reservation - it would be well more time
consuming .. ?

Thanks for your help =)