Question Basic Database Help - Double Booking

[alfiewi]

Hi there,
I'm new to the forum and a very basic user of databases. Recently I've attempted to create a database that allows people to book positions on a lake for fishing. Very similar structure to that of a hotel they book a location for a certain day. I have the basic structure crated already with tables and forms. My dilema at the moment is producing a list of available positions (positions are refered to as swims). MY thinking is to first produce a list of all the swims booked on a specific date then reverse the query as to recieve all booked swims. My knowledge is very basic.

Basically there are 4 tables: Address Table, Assign Table, Membership Details Table and Swim Details Table.


The whole thing works but the booking does not reserve the swim for every day during the guest's stay. It just contains the Begin Date as data and Duration as an integer. LeavingDate is worked out on the availability check form. The thing is I have no idea how to actually stop anyone else booking the swim during the duration of another guest's stay because it doesn't save the actual dates that the swim is booked for.

Can anyone look at my database and help me produce a query of swims booked where by when availability is requested a double booking can not occur as booked swims are shown.

I was going to put a macro button on the availability check form to peform the query for checking to see which swims are booked.

Thank you for your help
Alfie

 

[DavidAtWork]

I think you'd need to start by modifying your booking system so that when a booking is made ie 6 days at Swim1 starting on say March 1st, then you need to generate 6 x 1 day bookings for Swim1: Mar1, Mar2, Mar3, Mar4, Mar5, Mar6
Then you need a method of querying each of the Swims over a defined period which may be expressed as Date()+365 to see if there are any records exist from the above table. You could list all the bookings using:
SELECT tblBookings.*, tblBookings.SwimID FROM tblBookings
WHERE tblBookings.bookingDate BETWEEN Date() AND (Date()+365)
ORDER tblBookings.SwimID, tblBookings.bookingDate

This would list all bookings grouped by Swim in date order

To check if a booking has been made from a proposed booking is a little more tricky in that you have to check whether any of the new booking dates for (say a 5 day booking) for Swim1, a record exists in the bookings table for that Swim, for that date. You could use the DCount function:
For i = 1 to 5
If DCount("[bookingID]","[tblBookings]","[SwimID] = " & Form.SwimID & " And #" & [bookingDate] = Form.startDate & "#")>0 Then
MsgBox "Sorry this Swim has been booked",vbOK
Exit For
Exit Function/Sub
Else
Form.startDate= Form.startDate + 1
Next i

This would break from the above code if a booking was found in the proposed booking.
The field names and object names are just made up and you'd have to replace with your own, but will give you an idea. The code above will just check one Swim, but if you wanted to check all Swims then you'd need to wrap an outer loop around the loop above that would loop through all the Swims
I hope this makes sense, David

 

[alfiewi]

Hi David,
Apologise but this has gone way over my head. My skills are very basic and I have been trying to input your methods but don't know where to start. Would it be possible for anyone to simplyfy this down to a step by step guide as to find swims booked.
Thanks

 

[DavidAtWork]

What you're trying to do is complex and not straightforward. It may be eaiser for you if you just run a query to see what has been booked in the future for all Swims and then just do a visual check from what the query returns and see if it clashes with a proposed booking, sorry I can't be more simplistic only I've no idea what your table structure consists of. Do you think you are able to create a query that would show all future bookings?
David