By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,154 Members | 2,313 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,154 IT Pros & Developers. It's quick & easy.

double booking reservations and "blacklist" question

P: 2
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 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 they try to rent a cabin again.
I'm not sure how to go about doing this.
Thanks in advance to all!
Aug 13 '07 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,383
You'll need two queries. One will return all CabinIDs where the arrival and departure dates overlap. The other will return all unmatched Cabin IDs.

As for the blacklist. Create an extra field in the customers table as a yes/no field. Then you can DLookup that field to see if they've been blacklisted.
Aug 13 '07 #2

P: 2
You'll need two queries. One will return all CabinIDs where the arrival and departure dates overlap. The other will return all unmatched Cabin IDs.

As for the blacklist. Create an extra field in the customers table as a yes/no field. Then you can DLookup that field to see if they've been blacklisted.
I know there is the old "give a fish/teach to fish" thing but I'd really like to know in more detail how to create these queries in that manner.
Aug 13 '07 #3

Rabbit
Expert Mod 10K+
P: 12,383
Table1
CabinID
1
2
3


Table2
CabinID
ADate; Arrival Date
DDate; Departure Date
2, 1/1/2007, 1/31/2007

Query1
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS ArrDate DateTime, DepDate DateTime;
  2. SELECT Table2.CabinID
  3. FROM Table2
  4. WHERE ((([ArrDate]) Between [ADate] And [DDate])) OR ((([DepDate]) Between [ADate] And [DDate]));
  5.  
Query2
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.CabinID
  2. FROM Query1 RIGHT JOIN Table1 ON Query1.CabinID = Table1.CabinID
  3. WHERE (((Query1.CabinID) Is Null));
  4.  
Results - ArrDate = 1/3/2007 AND DepDate = 3/1/2007
CabinID
1
3
Aug 13 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.