Developer Forum

Hi :)
Hi i need somme help to resolve my Crosstable query . i want from my query to display me the state of each room (o or X),,, between the arrival date and the departure date .... it could be just Colors
i have two table : Room..and reservation
on my cross table i have on the X axes the room number , and on the Y axes the date (arrival +30 days)
i a looking for a way to put the sate (X or o) between the arrivale and the departure .
thre result should be like this :

days 1 2 3 4 5 6 7 ........31
101 O O O O
102 X X X
103
O -> Resrved
X--> Cheked

TRANSFORM First(IIf([Reservation]![StateRoom]="check in","O","")) AS Expr2
SELECT Room_Number.[Room Number]
FROM Room_Numbers LEFT JOIN Reservation ON Room_Number.[Room Number] = Reservation.RoomId
GROUP BY Room_Number.[Room Number]
ORDER BY Room_Numbers.[Room Number], DatePart("d",[Reservation]![D?partur])
PIVOT DatePart("d",[Reservation]![D?partLe]) In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);:what

*****************************************
* This message was posted via http://www.yqcomputer.com/
*
* Report SPAM or ABUSE by clicking the following URL:
* http://www.yqcomputer.com/
*****************************************

Assuming two table
tblNums
=============
Num integer values/records 0-30

Reservation
=================
RoomID
ArriveDate
DepartDate

Create a crosstab with the following query to display each reservation in a
separate row:

PARAMETERS [Enter Start Date] DateTime;
TRANSFORM Min("X") AS Expr1
SELECT Reservation.RoomID, Reservation.ArriveDate, Reservation.DepartDate
FROM Reservation, tblNums
WHERE (((DateAdd("d",[Num],[Enter Start Date])) Between [ArriveDate] And
[DepartDate]))
GROUP BY Reservation.RoomID, Reservation.ArriveDate, Reservation.DepartDate
ORDER BY Reservation.RoomID
PIVOT tblNums.Num;

To get one row per RoomID:
PARAMETERS [Enter Start Date] DateTime;
TRANSFORM Min("X") AS Expr1
SELECT Reservation.RoomID
FROM Reservation, tblNums
WHERE (((DateAdd("d",[Num],[Enter Start Date])) Between [ArriveDate] And
[DepartDate]))
GROUP BY Reservation.RoomID
ORDER BY Reservation.RoomID
PIVOT tblNums.Num;


--
Duane Hookom
MS Access MVP




query to display me the state of each room (o or X),,, between the arrival
date and the departure date .... it could be just Colors
the date (arrival +30 days)
the departure .
Reservation.RoomId
DatePart("d",[Reservation]![D?partur])
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,
29,30,31);:what
http://www.yqcomputer.com/

thank's men ....it's was verry helpful :)

*****************************************
* A copy of the whole thread can be found at:
* http://www.yqcomputer.com/
*
* Report SPAM or ABUSE by clicking the following URL:
* http://www.yqcomputer.com/
*****************************************