prevent double booking!! help!

[Jean-Daniel]

i'm doing a project on a database system for room reservation, in the booking table i have the booking id, room name and client id, and the date in and the date, out how would i do to ensure that when i'm booking a room on a certain date in and date out, the room is available and not booking?? can some one help?? please

[R_Badger]

Its hard to explain but it is doable. The first thing is break down your problem into the basic parts.

In your case.

You want to find rooms, where Bk_Date_in is not between date_in and date_out AND BK_date_out is not between date_in and date_out.

funnily enough that has not half built the SQL (query) you will need.

You will have to fill in the blanks yourself but, that should put you on the right stretch.

Don't be afraid to use vba, it really is your friend in the long run.

If you are still having problems, post back with more detail, including EXACT control names on the form where you are trying to make a booking, and your table structure, including table name and field names.

[Jean-Daniel]

(a)so i have the table booking, and the table booking_room and room table, i want that when i'm booking a room, it prevent double booking,
if the date in and date out is already taken, for that specified room.

(a) i'm trying to create a create a query so that in the booking_room table, where i have the client id, the room id and the date in and date out, to search to display for specific booking when i enter 2 date, i was trying with BETWEEN AND AND it does'n not work

[Jean-Daniel]

ok the query is ok but to prevent double booking i can't make it

[R_Badger]

You really should be doing that on a form.

Create a form for that, then tie the [please enter date in] to the form controls.

then you create a second query which joins the current query and your room table (do an unmatched query) join on roomid, showing all records in tbl_rooms and only the records from the qry_booked_rooms, which are equal. with a where [qry_booked_rooms].[room_id] is null.

[orange]

J-D,

Before jumping into queries and forms, you might want to consider building a data model; and identifying your business facts.

There is a free data model for hotel reservations at http://www.databaseanswers.org/data_...tels/index.htm
and related business rules/facts here http://www.databaseanswers.org/data_...tels/facts.htm
This isn't an answer to your questions, but it does show some of the concepts of building a model.

There is another model re Restaurant bookings at http://www.databaseanswers.org/data_...ings/index.htm

Here's a link to a point form approach to building a data base.
http://www.databaseanswers.org/approach2db_design.htm