You are here:

Using MS Access/Avoiding clashes in a table

Advertisement


Question
Hi Scott,

Following our previous correspondence I can happily add bookings to TblBookings.

The problem lies in the situation I have which is in no way efficient, in that TblBookings contains an entry for every room for every day, for the next year, with the GuestID 999999 - that's over 25,000 entries sitting doing nothing!!.

As it works at the moment, if when I check the dates for a room number, and the GuestID 999999 is true, then the room is 'available'

I want to be able to code a ComboBox to look at the TblBookings with only valid genuine bookings (and hence strip out the 25,000 idle records), see if a room type is available between two dates, and return the list of room numbers for that room type if no clashes exist.

I know it's a possible scenario, it's just I don't know how to structure the code behind the combo button, or whether I would need any queries to filter the records.   I can happily build queries to find records, I just don't know how to build queries to exclude existing records.

Tables involved:

TblBookings
RmCode
StayDate
DepartDate -1 (as the room is available on the depart date for the next guest)

TblRooms
RmCode
RmNumber

So, if a Room Type ([RmCode]) is SRGT (ten rooms in total, 201 thru 210), and there are bookings between 10-Jan-2013 and 12-Jan-2013 in TblBookings for some of thiose rooms but not all, I'd like the rowsource for the Combo Box to be a list of all the available rooms.   Phew!

Which direction should I head first please?

many thanks in anticipation

Pete

Answer
You have picked one of the hardest applications to develop. A hotel bookings database is not an easy task.

The way I would do this is to have a record for each room for each day of a reservation. So if a room is booked for 5 days, you create 5 records. This can easily be automated so you aren't actually entering 5 records. So I would have 2 tables TblBookings which has the basic info about a booking and TblBookedDates whihc has a record for each date a room is booked.

Once you have this, you can find all rooms free on a date using a subquery. Something like:

SELECT RmNumber FROM TBLRooms
WHERE RmNumber NOT IN(SELECT RmNumber FROM tblBookedDates WHERE StayDate = x);


Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.