double booking reservations and "blacklist" question 2

[Gilbertjoe]

Question 1:

I have reservation database in access 2007 I have a cabin information table with:
cabinid (primarykey)
cabinname(text)
cabinlocation(text)

I have a customer table with:
customer id(primary key)
last name (text)
first name (text)
address(text)

I have a reservation table with:
reservationid (primary key)
customer (looks up to customer table)
cabin(looks up to cabin information table)
todays date (dd/mm/yyyy)
arrival date (dd/mm/yyyy)
departure date (dd/mm/yyyy)

Now this should be simple.. I need to prevent double booking a cabin reservation. I want the user to be able to enter an arrival date and departure date on a form, click a command button and have it open a reservation form with all cabins that are available between those dates. The user can then select a cabin(on another form?) and enter the reservation.
(also cabins can be rented out again on the departure day as well but since this is standard, no specific hourly times need to be added to tables).
This has been driving me crazy. I am having a brain freeze over something simple.

QUESTION 2:
I'd like to create a "blacklist" of customers so that a warning box or some bells and whistles appear to warn the user if the customer tries to rent a cabin again in the future (sometimes customers get kicked off the campground and then come back later and try to re-rent a cabin).
I'm not sure how to go about doing this.
Thanks in advance to all!

I was in the park wondering why frisbees got bigger as they got closer. Then it hit me.

 

[danvlas]

Select CabinID From tblReservations
Where (ArrivalDate>=[reqArrivalDate] and ArrivalDate<=reqDepartureDate) or ([departureDate]<=[reqArrivalDate] and [departuredate]<=[reqarrivaldate])

should get you all the cabins BOOKED for the requested period, thus unavailable.
reqArrivalDate and reqDepartureDate are supposed to be the new reservation dates.


For question 2, a simple 'Blacklist' yes/no field in the table might suffice.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Gilbertjoe]

I have this SQl In my available cabins query but it isn't giving me the results I want. Before I go further I want to at least get the results I'm looking for.
SELECT [Cabin Reservation Table].cabin
FROM [Cabin Reservation Table]
WHERE ((([Cabin Reservation Table].[Arrival Date])>=[Forms]![Cabin Reservation Form]![Arrival Date] And ([Cabin Reservation Table].[Arrival Date])<=[Forms]![Cabin Reservation Form]![Departure Date])) OR ((([Cabin Reservation Table].[Departure Date])<=[Forms]![Cabin Reservation Form]![Arrival Date] And ([Cabin Reservation Table].[Departure Date])<=[Forms]![Cabin Reservation Form]![Departure Date]))
GROUP BY [Cabin Reservation Table].cabin
ORDER BY [Cabin Reservation Table].cabin;





I want to prevent the user from double booking cabin reservations by having them enter and arrival date and departure date on a "available cabins" form and when a command button is clicked on that form the query will run and then open a "reservations form" with only the cabins that are available based on the dates given (with the ability to book the same cabin on the same date as the departure date) (renters depart in a.m and a cabin can be re-re-rented after 3pm on same day)For example. Customer 1 wants to reserve cabin 1 on 01/01/2007 and is departing on 01/04/2007 but someone already reserved that cabin for those days so the available cabin form would open with all the other cabins that are available. I hope this clears up what I'm trying to say.

I was in the park wondering why frisbees got bigger as they got closer. Then it hit me.

 

[PHV]

Wahat about this WHERE clause ?
WHERE ([Cabin Reservation Table].[Arrival Date]>=[Forms]![Cabin Reservation Form]![Departure Date]
OR [Cabin Reservation Table].[Departure Date]<=[Forms]![Cabin Reservation Form]![Arrival Date])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[danvlas]

What I posted is for BOOKED cabins.
You can left join the cabins table to this query to give you the result. What's in the table and is missing in the query is available.

PHV: I don't think so...Using 'Or' will give fake results.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[PHV]

For booked cabins:
WHERE ([Cabin Reservation Table].[Arrival Date]<[Forms]![Cabin Reservation Form]![Departure Date]
AND [Cabin Reservation Table].[Departure Date]>[Forms]![Cabin Reservation Form]![Arrival Date])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gilbertjoe]

ok. now I'm a little lost.
I need all cabins that are not booked for those dates unless there is some other way to prevent double booking.


I was in the park wondering why frisbees got bigger as they got closer. Then it hit me.

 

[PHV]

Have you tried my WHERE clause posted 13 Aug 07 13:14 ?

 

[Gilbertjoe]

yup. it returned booked cabins only. is there some other easier way to do this? I'm open for suggestions.


I was in the park wondering why frisbees got bigger as they got closer. Then it hit me.

 

[PHV]

it returned booked cabins only
???
Could you please post YOUR code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gilbertjoe]

oK.
Here is the code I put in my query.
SELECT [Cabin Reservation Table].Cabin
FROM [Cabin Reservation Table]
WHERE ([Cabin Reservation Table].[Arrival Date]>=Forms![Cabin Reservation Form]![Departure Date] Or [Cabin Reservation Table].[Departure Date]<=Forms![Cabin Reservation Form]![Arrival Date]);


I was in the park wondering why frisbees got bigger as they got closer. Then it hit me.

 

[Gilbertjoe]

Now I'm thinking my cabin information table should be somewhere in this query or statement. How can i return all cabins available if one or more cabins were never reserved?The cabin field in the cabin reservation table looks up to the cabin information table. I did put that in my initial post (didn't I)?? LOL


I was in the park wondering why frisbees got bigger as they got closer. Then it hit me.

 

[PHV]

SELECT I.Cabin
FROM [Cabin Information Table] AS I
LEFT JOIN [Cabin Reservation Table] AS R ON I.CabinID = R.Cabin
WHERE Nz(R.[Arrival Date],#2049-12-31#)>=Forms![Cabin Reservation Form]![Departure Date]
OR Nz(R.[Departure Date],#1900-01-01#)<=Forms![Cabin Reservation Form]![Arrival Date];

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gilbertjoe]

ohhh! SO CLOSE! but not quite.
It's now returing the data sort of correctly with two exceptions.
1. Example: john doe rents cabin 1 from 01/01/2007 to 01/05/2007 (he leaves on 01/05/2007). the cabin needs to become available again on the departure day (thusly it should show up on the query results). currently the code isnt doing that.

2. Example: john doe rents cabin 1 from 01/01/2007 to 01/05/2007 and then mary doe rents it from 01/05/2007 to 01/08/2007. the query then returns the available cabins but now cabin 1 is on the query results twice.



Thanks,I've had a good time. This wasn't one of them.

 

[PHV]

rents cabin 1 from 01/01/2007 to 01/05/2007
Which date is arrival and which is departure ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gilbertjoe]

arrival date would be 01/01/2007 (mm/dd/yyyy) format and departure date would be 01/05/2007 (mm/dd/yyyy) format.
should have stated the date format. sorry.


Thanks,I've had a good time. This wasn't one of them.

 

[Gilbertjoe]

anybody have any luck with this one yet?


Thanks,I've had a good time. This wasn't one of them.