No double bookings...

[csmager]

I am in the process of making a database for booking rooms. There are for, and for arguments sake I will call them room1, room2, room3, and room4.

When somebody books a room, the details of the person and the date they have booked is stored in one table, and the room they have been allocated is stored in another.

The problem I have is that at the present moment, any number of people can book the same room on the same date - I'm sure there is a very simple solution to this problem that is just staring me in the face!

Thanks for any help!

 

[allanon]

I'm sure you already have a table that holds all of the room bookings. Before you update the table just run a query to see if the room is booked at that time.

strSQL = "SELECT * FROM bookings WHERE bookings.RoomNo = '" & _
me.txtRoom.value & "' and ((#" & me.txtFromDate.value & _
"# between bookings.fromDate and bookings.ToDate) or " & _
"(#" & me.txtToDate.value & "# between bookings.fromDate and bookings.ToDate))

set rs = currentdb.openrecordset(strSQL, dbopensnapshot)

if rs.recordcount > 0 then
You have a booking so cancel the current attempt at booking.

Anyhow, it could go something like that.

 

[csmager]

I don't mean to appear completely stupid, but I am fairly new to databases....

Could you explain the query you have written in reasonable english as well as in SQL syntax?

Also could you, say, highlight all the parts I am supposed to replace?

Thanks (and sorry!)

 

[allanon2]

Ok, lets see now. Since you have not posted the table structures I will have to make up my own.

people table
key people date_booked_start date_booked_end
1 people1 1/1/02 1/1/02
2 people1 2/2/02 2/4/02
3 people2 1/1/02 1/14/02
4 people2 2/3/02 2/15/02

rooms
key people room
1 people1 room1
2 people2 room2

This table structure of cource assumes that one party can only ever have one room. I assume that there is more to your tables than this.

next part is to create a query:
SELECT people.people, rooms.room, people.date_booked_start, people.date_booked_end
FROM people INNER JOIN rooms ON people.people = rooms.people;

I just did this in the query builder thing. I now get:
bookings (name of my query)
people room date_booked_start date_booked_end
people1 room1 1/1/02 1/1/02
people1 room1 2/2/02 2/4/02
people2 room2 1/1/02 1/14/02
people2 room2 2/3/02 2/15/02

so now I know who has booked what room and when.

Assume the name of your form fields are:

txtRoom, txtStartDate, txtEndDate

On the before insert event for your update you could do something like:

Dim strSQL as string
Dim rs as recordset

strSQL = "SELECT * FROM bookings WHERE bookings.room = '" & _
me.txtRoom.value & "' and ((#" & me.txtFromDate.value & _
"# between bookings.fromDate and bookings.ToDate) or " & _
"(#" & me.txtToDate.value & "# between bookings.fromDate and bookings.ToDate)) or ((#" & me.txtFromDate.value & _
&quot;# < bookings.fromDate) and (# & me.txtToDate.value & _
&quot;# > bookings.ToDate))&quot;

set rs = currentdb.openrecordset(strSQL, dbopensnapshot)
(executes the query)

What this SQL is doing is selecting a record that where there is some overlap of a room booking date with the dates that you entered.

Then you just check to see if you got a result. with this:
if rs.recordcount > 0 (what you want here is a 0)

I hope this helps you a bit. If you are still having trouble post the table structures and some dummy data and I will debug the sql.

 

[csmager]

Sorry to be a pain, but I can't seem to get my head around applying this to my table structures - I should have posted them earlier! I have two tables:

data table
key people date_booked date_party
1 people1 1/1/02 1/1/03
2 people1 2/2/02 2/2/03
3 people2 1/1/02 1/1/03
4 people2 2/3/02 2/3/03

rooms
key room_day room_eve
1 room1 room1
2 room2 room3

The bookings are accepted in date_booked order. In other words first come first served

The people can book a different room in the evening to during the day - although this is essentially unimportant to the 'no double bookings' query!

If there's anything you don't understand about the table structure (I'm sure I've forgotten to mention something!) then please ask...

Thanks for all your help!

 

[allanon2]

Ok, I'm missing something here. With this table structure you will never succeed. You have no way to associate people with rooms. In the first table you have booking dates but no rooms so you don't know which rooms are booked at those times. In the second table you have rooms....but nothing else... I hope I am not being too difficult here but I think there is a little problem with your tables...you have no way to identify when a room is booked.

If you are not opposed to it, I will give you my email and you can send me the database and I will set it up a bit so that you can make the needed queries.

If this does not work for you then what I suggest you do is create a couple of tables more like this:

Table 1: Rooms

key autonumber
RoomId text
Description text

(* Description could be any number of fields that contain data about the room*)

Table 2: People

key autonumber
PeopleId text
Description text

(* Description could be any number of fields that contain data about the people *)

Table 3: Bookings

key autonumber
PeopleId text
RoomId text
BookStart datetime
BookEnd datetime

With this table structure all of your problems go away. When you want to enter a booking you do the stuff that I posted before except you would change the following line:

strSQL = &quot;SELECT * FROM bookings WHERE bookings.RoomId = '&quot; & _
me.txtRoom.value & &quot;' and ((#&quot; & me.txtFromDate.value & _
&quot;# between bookings.BookStart and bookings.BookEnd) or &quot; & _
&quot;(#&quot; & me.txtToDate.value & &quot;# between bookings.BookStart and bookings.BookEnd)) or ((#&quot; & me.txtFromDate.value & _
&quot;# < bookings.BookStart) and (# & me.txtToDate.value & _
&quot;# > bookings.BookEnd))&quot;

One other change; you would no longer need that intermediate query with this solution.

I hope that this helps you some. If you would like me to look at the database you can let me know and I will give you my email address.

 

[csmager]

Have you already tested this?? If you have, I think it would be a lot easier if you emailed it to:

charlie@csmager.co.uk

I'll have a look at it your posts again later on this morning - but I've only got a minute or two now!

 

[csmager]

Thinking about this again, I will email you what I have done so far, as this may enable you to actually find out what's going on...

Please bear in mind that I've only just started this, so you can change pretty much whatever you want!!

There is also a query built in to only let you choose a room if the capacity is more than the amount of guests coming - you'll see what I mean when I send it to you! I only mention this as if you can see a better way of doing that, then by all means change it!!

As I said, I'm quite new to access - or at least, using it properly anyway!

Thanks for all your help so far - and I'm sorry if I'm taking up too much of your time!

 

[csmager]

Any Joy? I haven't heard anything from you for days! Let me know if you've got anywhere!

Thanks

 

[allanon]

Sorry. My computer died. It is in the shop right now. I had a working solution. I thought I emailed it to you. I will check when I get it back. I should have it back tomorrow. I actually had it working so please be patient. My bios chip fried.

 

[csmager]

One of the only problems you can't really fix yourself! That's unfortunate! I was just getting a bit concerned, as I emailed you and had no reply....

Anyway, thanks for replying - my email address is still charlie@csmager.co.uk - not 9mager@uppingham.co.uk.

Thanks again

 

[allanon2]

I sent you database back. Let me know if you still did not get it.