CrossTable Query For Booking

[diafi sam via AccessMonster.com]

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.accessmonster.com
*
* Report SPAM or ABUSE by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abuse.aspx?aid=373026708ea046ada076d61ab3c43fcf
*****************************************

 

[Duane Hookom]

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

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.accessmonster.com
*
* Report SPAM or ABUSE by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abuse.aspx?aid=373026708ea046ada076d61ab3c43fcf
*****************************************

 

[diafi sam via AccessMonster.com]

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

*****************************************
* A copy of the whole thread can be found at:
* http://www.accessmonster.com/uwe/Forum.aspx/access-queries/15849
*
* Report SPAM or ABUSE by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abuse.aspx?aid=be9994e7945d437fbdba4e76e1b42b0d
*****************************************