Hotel Database - Vacant room check

[shauneyd]

Hello Guys,

This is my first post. I am a newbie to Access and I am having difficulty with a query I am trying to do.

I have a one-to-many relational hotel database set up with 5 tables:

Bookings
Customers
Employees
Rooms
Room Types

What I am trying to do is to run a query where I can check to see when a room is vacant. For the date range I am using a parameter Start Date and End Date.

I think I might be close to this but the problem I am having is that when I run the query every single room shows as vacant when I know that these rooms are definitely not vacant.

Here is the SQL code I am working with at the moment:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT R.*
FROM ROOMS AS R LEFT JOIN BOOKINGS AS B ON R.RoomNo = B.RoomNo
WHERE (((B.RoomNo) Is Null)) OR ((([End Date])<.[ArrivDate])) OR ((([Start Date])>(.[ArrivDate]+.[DurStay])));

I would greatly appreciate it if you guys could cast your eye over this to see where I am going wrong. I have spent many, many hours on this and can't seem to figure it out for myself.

Greatly appreciated,

shauneyd

 

[stopher]

Can you give an example where it fail i.e. a start and end date and a booking ArrieDate and DurStay

Thanks

 

[shauneyd]

Can you give an example where it fail i.e. a start and end date and a booking ArrieDate and DurStay

Thanks

Hi Stopher,

Many thanks for your prompt reply.

An example is as follows:

Room number 2 is booked ArrivDate = 04/08/2014 and DurStay is 7 days.
When I run the query to find vacant rooms using parameter Start Date 02/08/2014 and End Date 08/08/2014 it says that Room number 2 is vacant during those dates when it shouldn't be available.

Hope this makes sense. Let me know if you require any more information.

I really appreciate your help with this.

Shauneyd

 

[sumox]

I think fault is in your table making system. Not Fully (There are many ways of
doing a thing)

May be this way out
Tables :
Rooms Table :
Room No. Room Location RoomType Facilities Booked to
ex-data
212 firstfloor Executive All Mr. Andy(combobox lookup from Bookings table)

Bookings Table :
Favour to , StartDate, EndDate, Contact Details
->Lookup 3.1.2014 10.1.2014 ->Lookup from Customer Table
from Customer
Table

Now Create a relationship between Bookings and Rooms table

Now Create a Join Query where all fields are there of both tables
Simple Add Critiera to "startdate" and "enddate" fields as
"=Forms!SearchforRooms!StartDate" and "=Forms!SearchforRooms!EndDate"
respectively

and also create a form nicely looking with a good photo of your hotel
and two textboxes insert from Toolbox
Rename them as "StartDate" and "EndDate" (now how to rename , is right-clik-->properties-->Other--> Name : Type here the name and press tab. it will auto-save)

Now Create a Report based on your query
Now Create a command clik button on form which shud open a report based on
Query "EmptyRooms" or whatever name you decide

i've made many databases for SOHO Organisations. I am not Expert
You can AWF search or Google for this "access hotel database" for easy approach

Thanks buddy

 

[stopher]

I've used exactly the same query with exactly the same dates and it seems to work fine i.e. I don't get that room returned

I've attached my db so maybe you can spot some difference somewhere.

 

[sumox]

oh sorry to forget !

 

[sumox]

You should create a Blank Record in Customer table
which data is supplied to "Rooms" table and "bookings" table

The Blank record shud be :
Example :
Customer name : Vacant
Customer Age : 0
Customer ID: no
Customer Address : no
....

Now in the Join query stated above, you should also set a filter Criteria
in "Booked to" field as "Vacant" ya
Only Text "Vacant", so all records booked to "Vacant" will be drawn by query