date query

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

Need a little help please

I'm currently designing a hotel reservation quote system
that has to check availability based on room types not
room numbers.

The query would need to be based on arrive and departure
date like this

SELECT tblRatesByDay.DateOfStay
FROM tblRatesByDay
WHERE (((tblRatesByDay.DateOfStay)>=[ArrivalDate] And
(tblRatesByDay.DateOfStay)<[DepartureDate]));

what i need is a query that would only report back a unit
type if UNITSAVAIL did not have any days with a 0 value
within the specified date range
in the case of this example - arriving on 9/1/3 and
leaving on 9/4/3 only types B & C would be reported back

=========================
tblRatesByDay

RoomType DateOfStay UnitsAvail
A 9/1/03 5
A 9/2/03 4
A 9/3/03 0
A 9/4/03 4
B 9/1/03 5
B 9/2/03 5
B 9/3/03 5
B 9/4/03 5
C 9/1/03 2
C 9/2/03 1
C 9/3/03 3
C 9/4/03 7

Any help appreciated

Thanks
 
SELECT tblRatesByDay.RoomType, Min(tblRatesByDay.UnitsAvailable) As
Available
FROM tblRatesByDay
WHERE (tblRatesByDay.DateOfStay >= [ArrivalDate]) And
(tblRatesByDay.DateOfStay < [DepartureDate])
GROUP BY tblRatesByDay.RoomType
HAVING Min(tblRatesByDay.UnitsAvailable) <> 0;


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Works great - thanks

-----Original Message-----
SELECT tblRatesByDay.RoomType, Min
Click to expand...
(tblRatesByDay.UnitsAvailable) As
Available
FROM tblRatesByDay
WHERE (tblRatesByDay.DateOfStay >= [ArrivalDate]) And
(tblRatesByDay.DateOfStay < [DepartureDate])
GROUP BY tblRatesByDay.RoomType
HAVING Min(tblRatesByDay.UnitsAvailable) <> 0;


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Brian said:
Need a little help please

I'm currently designing a hotel reservation quote system
that has to check availability based on room types not
room numbers.

The query would need to be based on arrive and departure
date like this

SELECT tblRatesByDay.DateOfStay
FROM tblRatesByDay
WHERE (((tblRatesByDay.DateOfStay)>=[ArrivalDate] And
(tblRatesByDay.DateOfStay)<[DepartureDate]));

what i need is a query that would only report back a unit
type if UNITSAVAIL did not have any days with a 0 value
within the specified date range
in the case of this example - arriving on 9/1/3 and
leaving on 9/4/3 only types B & C would be reported back

=========================
tblRatesByDay

RoomType DateOfStay UnitsAvail
A 9/1/03 5
A 9/2/03 4
A 9/3/03 0
A 9/4/03 4
B 9/1/03 5
B 9/2/03 5
B 9/3/03 5
B 9/4/03 5
C 9/1/03 2
C 9/2/03 1
C 9/3/03 3
C 9/4/03 7

Any help appreciated

Thanks
Click to expand...


.
Click to expand...
 
Back
Top