date query

[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

 

[John Viescas]

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)

 

[Brian]

Works great - thanks

-----Original Message-----
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)

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

.