Help Required in Query

HI. I tried to open that attachment but all I'm getting are re-directs to other E-E pages.

Could you also try to open it? (I've tried 2 browsers btw)
If it's re-directing for you also could you try adding the file again perhaps?
Book2.xlsx

Adding again....
Example-DB-Rooms.xlsx

thanks, it can be accessed now. I have questions however:
can we have the real table definition (and field names) please?
are there more fields in that table not shown but needed here?
If the table does not contain a date is it to be generated by the query?
     (see line 44; 28/07/2013 isn't in the table)
Why does the value in [reserved rooms] go into "expected arrival" for 18/07/2013
    (line 3 and 17)
Why do the values in [reserved rooms] go into "In-House" for 19/07/2013+
    (line 4-11 and 18-25)
Why does the value in [reserved rooms] go into "expected departure" for 27/07/2013
     (line 12 and 26)

Database                    
Date          Reserved Rooms          
18/07/2013          2          
19/07/2013          2          
20/07/2013          2          
21/07/2013          2          
22/07/2013          2          
23/07/2013          2          
24/07/2013          2          
25/07/2013          2          
26/07/2013          2          
27/07/2013          2          

Output if date range is between 18/07/2013 and 27/07/2013                   
Date          Expected                    Expected
              Arrival          In-House   Departure
18/07/2013          2          0          0
19/07/2013          0          2          0
20/07/2013          0          2          0
21/07/2013          0          2          0
22/07/2013          0          2          0
23/07/2013          0          2          0
24/07/2013          0          2          0
25/07/2013          0          2          0
26/07/2013          0          2          0
27/07/2013          0          0          2

Output if date range is between 20/07/2013 and 27/07/2013                               
Date          Expected                    Expected
              Arrival          In-House   Departure
20/07/2013          0          2          0
21/07/2013          0          2          0
22/07/2013          0          2          0
23/07/2013          0          2          0
24/07/2013          0          2          0
25/07/2013          0          2          0
26/07/2013          0          2          0
27/07/2013          0          0          2
                              
Output if date range is between 27/07/2013 and greater                              
Date          Expected                    Expected
              Arrival          In-House   Departure
27/07/2013          0          0          2
28/07/2013          0          0          0

Select allOpen in new window

an we have the real table definition (and field names) please?
Ans: Table Name: RoomReservation
Columns:
Checkindate - Date
Checkoutdate - Date
Rooms - Numeric

are there more fields in that table not shown but needed here?
Ans: As above

If the table does not contain a date is it to be generated by the query?
(see line 44; 28/07/2013 isn't in the table)
Ans: What I meant was the user input date range to get the result.

Why does the value in [reserved rooms] go into "expected arrival" for 18/07/2013
(line 3 and 17)
Ans: Because the person is expected to arrive in the room on 18/7

Why do the values in [reserved rooms] go into "In-House" for 19/07/2013+
(line 4-11 and 18-25)
Ans: Because person has already occupied the room

Why does the value in [reserved rooms] go into "expected departure" for 27/07/2013
(line 12 and 26)
Ans: Becausae the person is expected to vacate the room on that day.

See the attached file for actual DB struc with data
Example-DB-Rooms.xlsx

mmmm, I'm a little confused

what happens if there were 20 records of
18/7/2013, 27/02/2013, 2

i.e. check In 2*20, & In-House 2*20, & Departure 2*20

is there no restriction on availability of rooms?
nb: if 20 is too small make it 200

& similar to this what happens if there are many records in the table, some of which overlap a given start and end (but don't have the same start/end)?

are your samples adequate?
do you need to provide more representative data?
and from that data, expected results?
is there a limit of availability that needs to be factored in?
where does total availability come from?

If I'm reading too much into it, just say, no: "what you see it what I need" and I'll stop pestering you.

what happens if there were 20 records of
18/7/2013, 27/02/2013, 2

i.e. check In 2*20, & In-House 2*20, & Departure 2*20

is there no restriction on availability of rooms?
nb: if 20 is too small make it 200
ANS: Your assumption is right. availability rooms is taken care of in my business logic.

where does total availability come from?
ANS: Comes from business logic which will not allow over booking