Preventing Double Booking of a Room in form Based on multiple criteria.

[dexterfichuk]

Hi there, first time posting, and I'm sorry if this is worded strange, I'm new to microsoft access and trying to build a database for work and have spent all day researching this with no luck.

As of now, I have Table for rooms called Rooms, and the data in the table is roomNumber which is in the format Letter and two Digit number, so A01 would be dorm building A and room building 1, and I then have a seperate row named roomType that is either VIP or Semi Private.

Now I am creating a form where a worker will put in there scheduledCheckin date and scheduledCheckout date and it will be written to the Bookings table. I would like this form however to take the dates they have put in, as well as room type (Semi-Private or VIP, and assign them an available room that isn't booked at all in that range) or list all available rooms for that range of time and they could then just select the room. I would rather it automatically assigns an available room based on room type though because this check in system is going to have about 500 rooms.

I've attatched a link for what I have so far, and i've been struggling to get this figured out. I know how to make a query to list anyone who has booked rooms over that date, but need one for just preventing booking the same room.
drive.google. com/file/d/0B0uM5E5iAtd6Q2djS0gxdlhJeDQ/view?

 

[spikepl]

If you want somebody to look at your db you need to post it here, not on some gone-in-5-minutes site. Compact & repair, then zip it, and you can attach it here, even with less than 10 posts.

For finding overlaps in bookings, check this: http://www.baldyweb.com/OverLap.htm

 

[dexterfichuk]

Sorry my apologies! I've attached what I have so far and jsut reading over the link you've posted. As you'll see in my db that my NEW Booking Clashes query is just finding bookings with the same date, rather than room number. Also, I am unsure how i can create a form where the users puts in dates, and then i create a button to trigger a query using the entered dates.

 

[jdraw]

see this sample database