Prev: Reading a large file that does not have newline characters
Next: Song BPM (Beats per minute) algorithm for VB6?
From: Jay on 27 Apr 2010 12:02
Hi,

Sorry I forgot to tell you the password. It's "jaypee".

And by the way I cannot create another outer join because I already used
outer join in Date to display all days in a month.

"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 27 Apr 2010 12:13
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 27 Apr 2010 12:22
Hi,

Sorry but when I use right join to Rooms table the Date from 1 to 30 or 31
is not included in row. If I use left join in favor of qry_DateTemp all the
date from 1 to 30 or 31 is there but the room number are gone again.

"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 27 Apr 2010 12:24
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 27 Apr 2010 12:30
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.
>>>>>>>
>>>>>>>
>>>>>
>>>>>
>>>
>>>
>>>
>
>
>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Reading a large file that does not have newline characters
Next: Song BPM (Beats per minute) algorithm for VB6?