From: Phil Hunt on
Are you sure you are using the Room# from the Room table, and not from the
reservation table. Big difference.
If so, Put up the latest report, I will take a look later.


"Jay" <jpabs78(a)gmail.com> wrote in message
news:%23H3i3bi5KHA.5808(a)TK2MSFTNGP02.phx.gbl...
> Yes I tried everything but I can't display all the Room Number as column
> in crystal reports.
>
> "Phil Hunt" <aaa(a)aaa.com> wrote in message
> news:#mC4rYi5KHA.3804(a)TK2MSFTNGP05.phx.gbl...
>> I dont thank you can use a TRANSFORM query. Just do everything in CR.
>>
>>
>> "Jay" <jpabs78(a)gmail.com> wrote in message
>> news:egj4eSi5KHA.5548(a)TK2MSFTNGP04.phx.gbl...
>>> Hi,
>>>
>>> I think I am almost there. Yes I can create an outer join by adding the
>>> Rooms table. But this is in ms access database.
>>>
>>> How about in crystal reports? Where can I configure the sql that will
>>> look like this:
>>>
>>> TRANSFORM First([FirstName] & " " & [LastName] & " - " & [Status]) AS
>>> Name
>>> SELECT qry_DateTemp.Date
>>> FROM (Customers RIGHT JOIN qry_DateTemp ON Customers.CustomerID =
>>> qry_DateTemp.CustomerID) RIGHT JOIN Rooms ON qry_DateTemp.RoomNumber =
>>> Rooms.RoomNumber
>>> GROUP BY qry_DateTemp.Date
>>> PIVOT Rooms.RoomNumber;
>>>
>>> Thanks
>>>
>>> "Phil Hunt" <aaa(a)aaa.com> wrote in message
>>> news:#zpnx#h5KHA.1888(a)TK2MSFTNGP05.phx.gbl...
>>>> I could not open db becasuse of password. That's fine.
>>>> Looking the rpt, I think you are on the right track. You prob don't
>>>> need the temp, tho.
>>>> If you need to display all the rooms regardless, you need to have a
>>>> table that has all the rooms. You prob have that already. Do a outer
>>>> join from the room table and use room # from room table as columns.
>>>>
>>>> "Jay" <jpabs78(a)gmail.com> wrote in message
>>>> news:OlbPEwh5KHA.1424(a)TK2MSFTNGP04.phx.gbl...
>>>>> So I can explain it better please take a look at my reports. I also
>>>>> attached the database here:
>>>>>
>>>>> http://www.sourcecodester.com/files/hotel_db.zip
>>>>>
>>>>> In the report, all rooms must be displayed in the column. But because
>>>>> some rooms don't have data I am not able to display all the rooms in
>>>>> column. And noticed also at the Date february 27, the name should be
>>>>> andrew and not john smith.
>>>>>
>>>>> Thanks in advance for your help.
>>>>>
>>>>> "Phil Hunt" <aaa(a)aaa.com> wrote in message
>>>>> news:uXOOVnh5KHA.1888(a)TK2MSFTNGP05.phx.gbl...
>>>>>> OK, I know what you mean. I will take a look at some of the xtab
>>>>>> report I created and see if I can help further.
>>>>>>
>>>>>> "Jay" <jpabs78(a)gmail.com> wrote in message
>>>>>> news:e4Wxiah5KHA.420(a)TK2MSFTNGP02.phx.gbl...
>>>>>>> Yes but crystal reports crosstab cannot display all the columns
>>>>>>> (i.e. room number) when I create the report. and also the value must
>>>>>>> be the name of the guest checked in or have a reservation in the
>>>>>>> hotel. I notice that crystal reports does not support displaying
>>>>>>> name instead the value in dropdown list is count, sum, minimum,
>>>>>>> maximum, etc in the summary.
>>>>>>>
>>>>>>> I tried all this value but it gives a wrong name sometimes in the
>>>>>>> report.
>>>>>>>
>>>>>>> "Phil Hunt" <aaa(a)aaa.com> wrote in message
>>>>>>> news:eRruOTh5KHA.1888(a)TK2MSFTNGP05.phx.gbl...
>>>>>>>> Do not use cross tab query in crystal. Crystal report has a Cross
>>>>>>>> Tab report facility to do this kind of stuff.
>>>>>>>> All cross tab query are extension of DMBS, meaning it does not
>>>>>>>> adhere to any standard.
>>>>>>>>
>>>>>>>>
>>>>>>
>>>>>>
>>>>
>>>>
>>>>
>>
>>
>>


From: Jay on
Yes I am using the Room Number from Rooms table.

Please download the report and the data.mdb again below:

http://www.sourcecodester.com/files/hotel_db.zip

I change the Date from February to April so it will not have conflict with
double reservation.

Thank you

"Phil Hunt" <aaa(a)aaa.com> wrote in message
news:ewhRCki5KHA.5016(a)TK2MSFTNGP02.phx.gbl...
> Are you sure you are using the Room# from the Room table, and not from the
> reservation table. Big difference.
> If so, Put up the latest report, I will take a look later.
>
>
> "Jay" <jpabs78(a)gmail.com> wrote in message
> news:%23H3i3bi5KHA.5808(a)TK2MSFTNGP02.phx.gbl...
>> Yes I tried everything but I can't display all the Room Number as column
>> in crystal reports.
>>
>> "Phil Hunt" <aaa(a)aaa.com> wrote in message
>> news:#mC4rYi5KHA.3804(a)TK2MSFTNGP05.phx.gbl...
>>> I dont thank you can use a TRANSFORM query. Just do everything in CR.
>>>
>>>
>>> "Jay" <jpabs78(a)gmail.com> wrote in message
>>> news:egj4eSi5KHA.5548(a)TK2MSFTNGP04.phx.gbl...
>>>> Hi,
>>>>
>>>> I think I am almost there. Yes I can create an outer join by adding the
>>>> Rooms table. But this is in ms access database.
>>>>
>>>> How about in crystal reports? Where can I configure the sql that will
>>>> look like this:
>>>>
>>>> TRANSFORM First([FirstName] & " " & [LastName] & " - " & [Status]) AS
>>>> Name
>>>> SELECT qry_DateTemp.Date
>>>> FROM (Customers RIGHT JOIN qry_DateTemp ON Customers.CustomerID =
>>>> qry_DateTemp.CustomerID) RIGHT JOIN Rooms ON qry_DateTemp.RoomNumber =
>>>> Rooms.RoomNumber
>>>> GROUP BY qry_DateTemp.Date
>>>> PIVOT Rooms.RoomNumber;
>>>>
>>>> Thanks
>>>>
>>>> "Phil Hunt" <aaa(a)aaa.com> wrote in message
>>>> news:#zpnx#h5KHA.1888(a)TK2MSFTNGP05.phx.gbl...
>>>>> I could not open db becasuse of password. That's fine.
>>>>> Looking the rpt, I think you are on the right track. You prob don't
>>>>> need the temp, tho.
>>>>> If you need to display all the rooms regardless, you need to have a
>>>>> table that has all the rooms. You prob have that already. Do a outer
>>>>> join from the room table and use room # from room table as columns.
>>>>>
>>>>> "Jay" <jpabs78(a)gmail.com> wrote in message
>>>>> news:OlbPEwh5KHA.1424(a)TK2MSFTNGP04.phx.gbl...
>>>>>> So I can explain it better please take a look at my reports. I also
>>>>>> attached the database here:
>>>>>>
>>>>>> http://www.sourcecodester.com/files/hotel_db.zip
>>>>>>
>>>>>> In the report, all rooms must be displayed in the column. But because
>>>>>> some rooms don't have data I am not able to display all the rooms in
>>>>>> column. And noticed also at the Date february 27, the name should be
>>>>>> andrew and not john smith.
>>>>>>
>>>>>> Thanks in advance for your help.
>>>>>>
>>>>>> "Phil Hunt" <aaa(a)aaa.com> wrote in message
>>>>>> news:uXOOVnh5KHA.1888(a)TK2MSFTNGP05.phx.gbl...
>>>>>>> OK, I know what you mean. I will take a look at some of the xtab
>>>>>>> report I created and see if I can help further.
>>>>>>>
>>>>>>> "Jay" <jpabs78(a)gmail.com> wrote in message
>>>>>>> news:e4Wxiah5KHA.420(a)TK2MSFTNGP02.phx.gbl...
>>>>>>>> Yes but crystal reports crosstab cannot display all the columns
>>>>>>>> (i.e. room number) when I create the report. and also the value
>>>>>>>> must be the name of the guest checked in or have a reservation in
>>>>>>>> the hotel. I notice that crystal reports does not support
>>>>>>>> displaying name instead the value in dropdown list is count, sum,
>>>>>>>> minimum, maximum, etc in the summary.
>>>>>>>>
>>>>>>>> I tried all this value but it gives a wrong name sometimes in the
>>>>>>>> report.
>>>>>>>>
>>>>>>>> "Phil Hunt" <aaa(a)aaa.com> wrote in message
>>>>>>>> news:eRruOTh5KHA.1888(a)TK2MSFTNGP05.phx.gbl...
>>>>>>>>> Do not use cross tab query in crystal. Crystal report has a Cross
>>>>>>>>> Tab report facility to do this kind of stuff.
>>>>>>>>> All cross tab query are extension of DMBS, meaning it does not
>>>>>>>>> adhere to any standard.
>>>>>>>>>
>>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>
>>>>>
>>>>>
>>>
>>>
>>>
>
>
From: Phil Hunt on
Your are actually using Room# from the qrt_datetemp which already cut down
the room # you can see.
"Jay" <jpabs78(a)gmail.com> wrote in message
news:OdI75Xo5KHA.2388(a)TK2MSFTNGP04.phx.gbl...
> Yes I am using the Room Number from Rooms table.
>
> Please download the report and the data.mdb again below:
>
> http://www.sourcecodester.com/files/hotel_db.zip
>
> I change the Date from February to April so it will not have conflict with
> double reservation.
>
> Thank you
>
> "Phil Hunt" <aaa(a)aaa.com> wrote in message
> news:ewhRCki5KHA.5016(a)TK2MSFTNGP02.phx.gbl...
>> Are you sure you are using the Room# from the Room table, and not from
>> the reservation table. Big difference.
>> If so, Put up the latest report, I will take a look later.
>>
>>
>> "Jay" <jpabs78(a)gmail.com> wrote in message
>> news:%23H3i3bi5KHA.5808(a)TK2MSFTNGP02.phx.gbl...
>>> Yes I tried everything but I can't display all the Room Number as column
>>> in crystal reports.
>>>
>>> "Phil Hunt" <aaa(a)aaa.com> wrote in message
>>> news:#mC4rYi5KHA.3804(a)TK2MSFTNGP05.phx.gbl...
>>>> I dont thank you can use a TRANSFORM query. Just do everything in CR.
>>>>
>>>>
>>>> "Jay" <jpabs78(a)gmail.com> wrote in message
>>>> news:egj4eSi5KHA.5548(a)TK2MSFTNGP04.phx.gbl...
>>>>> Hi,
>>>>>
>>>>> I think I am almost there. Yes I can create an outer join by adding
>>>>> the Rooms table. But this is in ms access database.
>>>>>
>>>>> How about in crystal reports? Where can I configure the sql that will
>>>>> look like this:
>>>>>
>>>>> TRANSFORM First([FirstName] & " " & [LastName] & " - " & [Status]) AS
>>>>> Name
>>>>> SELECT qry_DateTemp.Date
>>>>> FROM (Customers RIGHT JOIN qry_DateTemp ON Customers.CustomerID =
>>>>> qry_DateTemp.CustomerID) RIGHT JOIN Rooms ON qry_DateTemp.RoomNumber =
>>>>> Rooms.RoomNumber
>>>>> GROUP BY qry_DateTemp.Date
>>>>> PIVOT Rooms.RoomNumber;
>>>>>
>>>>> Thanks
>>>>>
>>>>> "Phil Hunt" <aaa(a)aaa.com> wrote in message
>>>>> news:#zpnx#h5KHA.1888(a)TK2MSFTNGP05.phx.gbl...
>>>>>> I could not open db becasuse of password. That's fine.
>>>>>> Looking the rpt, I think you are on the right track. You prob don't
>>>>>> need the temp, tho.
>>>>>> If you need to display all the rooms regardless, you need to have a
>>>>>> table that has all the rooms. You prob have that already. Do a outer
>>>>>> join from the room table and use room # from room table as columns.
>>>>>>
>>>>>> "Jay" <jpabs78(a)gmail.com> wrote in message
>>>>>> news:OlbPEwh5KHA.1424(a)TK2MSFTNGP04.phx.gbl...
>>>>>>> So I can explain it better please take a look at my reports. I also
>>>>>>> attached the database here:
>>>>>>>
>>>>>>> http://www.sourcecodester.com/files/hotel_db.zip
>>>>>>>
>>>>>>> In the report, all rooms must be displayed in the column. But
>>>>>>> because some rooms don't have data I am not able to display all the
>>>>>>> rooms in column. And noticed also at the Date february 27, the name
>>>>>>> should be andrew and not john smith.
>>>>>>>
>>>>>>> Thanks in advance for your help.
>>>>>>>
>>>>>>> "Phil Hunt" <aaa(a)aaa.com> wrote in message
>>>>>>> news:uXOOVnh5KHA.1888(a)TK2MSFTNGP05.phx.gbl...
>>>>>>>> OK, I know what you mean. I will take a look at some of the xtab
>>>>>>>> report I created and see if I can help further.
>>>>>>>>
>>>>>>>> "Jay" <jpabs78(a)gmail.com> wrote in message
>>>>>>>> news:e4Wxiah5KHA.420(a)TK2MSFTNGP02.phx.gbl...
>>>>>>>>> Yes but crystal reports crosstab cannot display all the columns
>>>>>>>>> (i.e. room number) when I create the report. and also the value
>>>>>>>>> must be the name of the guest checked in or have a reservation in
>>>>>>>>> the hotel. I notice that crystal reports does not support
>>>>>>>>> displaying name instead the value in dropdown list is count, sum,
>>>>>>>>> minimum, maximum, etc in the summary.
>>>>>>>>>
>>>>>>>>> I tried all this value but it gives a wrong name sometimes in the
>>>>>>>>> report.
>>>>>>>>>
>>>>>>>>> "Phil Hunt" <aaa(a)aaa.com> wrote in message
>>>>>>>>> news:eRruOTh5KHA.1888(a)TK2MSFTNGP05.phx.gbl...
>>>>>>>>>> Do not use cross tab query in crystal. Crystal report has a Cross
>>>>>>>>>> Tab report facility to do this kind of stuff.
>>>>>>>>>> All cross tab query are extension of DMBS, meaning it does not
>>>>>>>>>> adhere to any standard.
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>
>>>>
>>>>
>>
>>


From: Jay on
Hi,

I double check the crosstab and I selected Room Number from rooms table.
Kindly open the report and right click the crosstab and click format
crosstab. There you will see that I am using Room Number from rooms table.

"Phil Hunt" <aaa(a)aaa.com> wrote in message
news:#5ISFSu5KHA.4888(a)TK2MSFTNGP06.phx.gbl...
> Your are actually using Room# from the qrt_datetemp which already cut down
> the room # you can see.
> "Jay" <jpabs78(a)gmail.com> wrote in message
> news:OdI75Xo5KHA.2388(a)TK2MSFTNGP04.phx.gbl...
>> Yes I am using the Room Number from Rooms table.
>>
>> Please download the report and the data.mdb again below:
>>
>> http://www.sourcecodester.com/files/hotel_db.zip
>>
>> I change the Date from February to April so it will not have conflict
>> with double reservation.
>>
>> Thank you
>>
>> "Phil Hunt" <aaa(a)aaa.com> wrote in message
>> news:ewhRCki5KHA.5016(a)TK2MSFTNGP02.phx.gbl...
>>> Are you sure you are using the Room# from the Room table, and not from
>>> the reservation table. Big difference.
>>> If so, Put up the latest report, I will take a look later.
>>>
>>>
>>> "Jay" <jpabs78(a)gmail.com> wrote in message
>>> news:%23H3i3bi5KHA.5808(a)TK2MSFTNGP02.phx.gbl...
>>>> Yes I tried everything but I can't display all the Room Number as
>>>> column in crystal reports.
>>>>
>>>> "Phil Hunt" <aaa(a)aaa.com> wrote in message
>>>> news:#mC4rYi5KHA.3804(a)TK2MSFTNGP05.phx.gbl...
>>>>> I dont thank you can use a TRANSFORM query. Just do everything in CR.
>>>>>
>>>>>
>>>>> "Jay" <jpabs78(a)gmail.com> wrote in message
>>>>> news:egj4eSi5KHA.5548(a)TK2MSFTNGP04.phx.gbl...
>>>>>> Hi,
>>>>>>
>>>>>> I think I am almost there. Yes I can create an outer join by adding
>>>>>> the Rooms table. But this is in ms access database.
>>>>>>
>>>>>> How about in crystal reports? Where can I configure the sql that will
>>>>>> look like this:
>>>>>>
>>>>>> TRANSFORM First([FirstName] & " " & [LastName] & " - " & [Status]) AS
>>>>>> Name
>>>>>> SELECT qry_DateTemp.Date
>>>>>> FROM (Customers RIGHT JOIN qry_DateTemp ON Customers.CustomerID =
>>>>>> qry_DateTemp.CustomerID) RIGHT JOIN Rooms ON qry_DateTemp.RoomNumber
>>>>>> = Rooms.RoomNumber
>>>>>> GROUP BY qry_DateTemp.Date
>>>>>> PIVOT Rooms.RoomNumber;
>>>>>>
>>>>>> Thanks
>>>>>>
>>>>>> "Phil Hunt" <aaa(a)aaa.com> wrote in message
>>>>>> news:#zpnx#h5KHA.1888(a)TK2MSFTNGP05.phx.gbl...
>>>>>>> I could not open db becasuse of password. That's fine.
>>>>>>> Looking the rpt, I think you are on the right track. You prob don't
>>>>>>> need the temp, tho.
>>>>>>> If you need to display all the rooms regardless, you need to have a
>>>>>>> table that has all the rooms. You prob have that already. Do a outer
>>>>>>> join from the room table and use room # from room table as columns.
>>>>>>>
>>>>>>> "Jay" <jpabs78(a)gmail.com> wrote in message
>>>>>>> news:OlbPEwh5KHA.1424(a)TK2MSFTNGP04.phx.gbl...
>>>>>>>> So I can explain it better please take a look at my reports. I also
>>>>>>>> attached the database here:
>>>>>>>>
>>>>>>>> http://www.sourcecodester.com/files/hotel_db.zip
>>>>>>>>
>>>>>>>> In the report, all rooms must be displayed in the column. But
>>>>>>>> because some rooms don't have data I am not able to display all the
>>>>>>>> rooms in column. And noticed also at the Date february 27, the name
>>>>>>>> should be andrew and not john smith.
>>>>>>>>
>>>>>>>> Thanks in advance for your help.
>>>>>>>>
>>>>>>>> "Phil Hunt" <aaa(a)aaa.com> wrote in message
>>>>>>>> news:uXOOVnh5KHA.1888(a)TK2MSFTNGP05.phx.gbl...
>>>>>>>>> OK, I know what you mean. I will take a look at some of the xtab
>>>>>>>>> report I created and see if I can help further.
>>>>>>>>>
>>>>>>>>> "Jay" <jpabs78(a)gmail.com> wrote in message
>>>>>>>>> news:e4Wxiah5KHA.420(a)TK2MSFTNGP02.phx.gbl...
>>>>>>>>>> Yes but crystal reports crosstab cannot display all the columns
>>>>>>>>>> (i.e. room number) when I create the report. and also the value
>>>>>>>>>> must be the name of the guest checked in or have a reservation in
>>>>>>>>>> the hotel. I notice that crystal reports does not support
>>>>>>>>>> displaying name instead the value in dropdown list is count, sum,
>>>>>>>>>> minimum, maximum, etc in the summary.
>>>>>>>>>>
>>>>>>>>>> I tried all this value but it gives a wrong name sometimes in the
>>>>>>>>>> report.
>>>>>>>>>>
>>>>>>>>>> "Phil Hunt" <aaa(a)aaa.com> wrote in message
>>>>>>>>>> news:eRruOTh5KHA.1888(a)TK2MSFTNGP05.phx.gbl...
>>>>>>>>>>> Do not use cross tab query in crystal. Crystal report has a
>>>>>>>>>>> Cross Tab report facility to do this kind of stuff.
>>>>>>>>>>> All cross tab query are extension of DMBS, meaning it does not
>>>>>>>>>>> adhere to any standard.
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>
>>>>>
>>>>>
>>>
>>>
>
>
From: Phil Hunt on
I did open the rpt. It comes from the qry_Datetemp. Click on the
Database/Visual table .. You see the Rooms is not included. It was included
in the qry. But the qry already cut out some room#
"Jay" <jpabs78(a)gmail.com> wrote in message
news:OXfbdgu5KHA.5808(a)TK2MSFTNGP02.phx.gbl...
> Hi,
>
> I double check the crosstab and I selected Room Number from rooms table.
> Kindly open the report and right click the crosstab and click format
> crosstab. There you will see that I am using Room Number from rooms table.
>
> "Phil Hunt" <aaa(a)aaa.com> wrote in message
> news:#5ISFSu5KHA.4888(a)TK2MSFTNGP06.phx.gbl...
>> Your are actually using Room# from the qrt_datetemp which already cut
>> down the room # you can see.
>> "Jay" <jpabs78(a)gmail.com> wrote in message
>> news:OdI75Xo5KHA.2388(a)TK2MSFTNGP04.phx.gbl...
>>> Yes I am using the Room Number from Rooms table.
>>>
>>> Please download the report and the data.mdb again below:
>>>
>>> http://www.sourcecodester.com/files/hotel_db.zip
>>>
>>> I change the Date from February to April so it will not have conflict
>>> with double reservation.
>>>
>>> Thank you
>>>
>>> "Phil Hunt" <aaa(a)aaa.com> wrote in message
>>> news:ewhRCki5KHA.5016(a)TK2MSFTNGP02.phx.gbl...
>>>> Are you sure you are using the Room# from the Room table, and not from
>>>> the reservation table. Big difference.
>>>> If so, Put up the latest report, I will take a look later.
>>>>
>>>>
>>>> "Jay" <jpabs78(a)gmail.com> wrote in message
>>>> news:%23H3i3bi5KHA.5808(a)TK2MSFTNGP02.phx.gbl...
>>>>> Yes I tried everything but I can't display all the Room Number as
>>>>> column in crystal reports.
>>>>>
>>>>> "Phil Hunt" <aaa(a)aaa.com> wrote in message
>>>>> news:#mC4rYi5KHA.3804(a)TK2MSFTNGP05.phx.gbl...
>>>>>> I dont thank you can use a TRANSFORM query. Just do everything in CR.
>>>>>>
>>>>>>
>>>>>> "Jay" <jpabs78(a)gmail.com> wrote in message
>>>>>> news:egj4eSi5KHA.5548(a)TK2MSFTNGP04.phx.gbl...
>>>>>>> Hi,
>>>>>>>
>>>>>>> I think I am almost there. Yes I can create an outer join by adding
>>>>>>> the Rooms table. But this is in ms access database.
>>>>>>>
>>>>>>> How about in crystal reports? Where can I configure the sql that
>>>>>>> will look like this:
>>>>>>>
>>>>>>> TRANSFORM First([FirstName] & " " & [LastName] & " - " & [Status])
>>>>>>> AS Name
>>>>>>> SELECT qry_DateTemp.Date
>>>>>>> FROM (Customers RIGHT JOIN qry_DateTemp ON Customers.CustomerID =
>>>>>>> qry_DateTemp.CustomerID) RIGHT JOIN Rooms ON qry_DateTemp.RoomNumber
>>>>>>> = Rooms.RoomNumber
>>>>>>> GROUP BY qry_DateTemp.Date
>>>>>>> PIVOT Rooms.RoomNumber;
>>>>>>>
>>>>>>> Thanks
>>>>>>>
>>>>>>> "Phil Hunt" <aaa(a)aaa.com> wrote in message
>>>>>>> news:#zpnx#h5KHA.1888(a)TK2MSFTNGP05.phx.gbl...
>>>>>>>> I could not open db becasuse of password. That's fine.
>>>>>>>> Looking the rpt, I think you are on the right track. You prob don't
>>>>>>>> need the temp, tho.
>>>>>>>> If you need to display all the rooms regardless, you need to have a
>>>>>>>> table that has all the rooms. You prob have that already. Do a
>>>>>>>> outer join from the room table and use room # from room table as
>>>>>>>> columns.
>>>>>>>>
>>>>>>>> "Jay" <jpabs78(a)gmail.com> wrote in message
>>>>>>>> news:OlbPEwh5KHA.1424(a)TK2MSFTNGP04.phx.gbl...
>>>>>>>>> So I can explain it better please take a look at my reports. I
>>>>>>>>> also attached the database here:
>>>>>>>>>
>>>>>>>>> http://www.sourcecodester.com/files/hotel_db.zip
>>>>>>>>>
>>>>>>>>> In the report, all rooms must be displayed in the column. But
>>>>>>>>> because some rooms don't have data I am not able to display all
>>>>>>>>> the rooms in column. And noticed also at the Date february 27, the
>>>>>>>>> name should be andrew and not john smith.
>>>>>>>>>
>>>>>>>>> Thanks in advance for your help.
>>>>>>>>>
>>>>>>>>> "Phil Hunt" <aaa(a)aaa.com> wrote in message
>>>>>>>>> news:uXOOVnh5KHA.1888(a)TK2MSFTNGP05.phx.gbl...
>>>>>>>>>> OK, I know what you mean. I will take a look at some of the xtab
>>>>>>>>>> report I created and see if I can help further.
>>>>>>>>>>
>>>>>>>>>> "Jay" <jpabs78(a)gmail.com> wrote in message
>>>>>>>>>> news:e4Wxiah5KHA.420(a)TK2MSFTNGP02.phx.gbl...
>>>>>>>>>>> Yes but crystal reports crosstab cannot display all the columns
>>>>>>>>>>> (i.e. room number) when I create the report. and also the value
>>>>>>>>>>> must be the name of the guest checked in or have a reservation
>>>>>>>>>>> in the hotel. I notice that crystal reports does not support
>>>>>>>>>>> displaying name instead the value in dropdown list is count,
>>>>>>>>>>> sum, minimum, maximum, etc in the summary.
>>>>>>>>>>>
>>>>>>>>>>> I tried all this value but it gives a wrong name sometimes in
>>>>>>>>>>> the report.
>>>>>>>>>>>
>>>>>>>>>>> "Phil Hunt" <aaa(a)aaa.com> wrote in message
>>>>>>>>>>> news:eRruOTh5KHA.1888(a)TK2MSFTNGP05.phx.gbl...
>>>>>>>>>>>> Do not use cross tab query in crystal. Crystal report has a
>>>>>>>>>>>> Cross Tab report facility to do this kind of stuff.
>>>>>>>>>>>> All cross tab query are extension of DMBS, meaning it does not
>>>>>>>>>>>> adhere to any standard.
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>
>>>>
>>
>>