Dynamic column

[Jay]

I have a table created using a make table query in ms access that is run by
my vb 6 application. The column generated in this table is not constant and
it changes depending on the room number setup by a hotel encoder.

An example of this is:

Date | 201 | 202 | 203 |
204
4/1/10 john matthew
4/2/10 peter
.
.
.
4/30/10 andrew

The most important here is on how I can setup the column in crystal reports.
Since the column is not constant.

BTW, I am using crystal reports 8.5 and VB 6.0.

Thanks in advance

[MikeD]

Post by Jay
I have a table created using a make table query in ms access that is run
by my vb 6 application. The column generated in this table is not constant
and it changes depending on the room number setup by a hotel encoder.
Date | 201 | 202 | 203 |
204
4/1/10 john matthew
4/2/10 peter
.
.
.
4/30/10 andrew
The most important here is on how I can setup the column in crystal
reports. Since the column is not constant.

A couple of things are a little unclear, to me at least. Are you saying the
actual number of columns in the table and even the column names can vary?
If so, that's a tremendously bad design. Without more information, I don't
know what would be best to suggest, but I know it's not that. I suppose
something with 2 or more tables and creating a relationship between them.
Afterall, that's why Access and SQL Server and Oracle (and others) are
called relational database management systems.

I don't think there's anything you can as far as Crystal goes with your
current design as Crystal "read" the columns when you're creating the
report. If you were using SQL Server or an RDMS that provided views and
stored procedures, I'd say *maybe* you could use either or both of those.

But I think the best answer is to redesign your database and not have its
architecture vary based on user input.

OTOH...if what I mentioned is not your database design (columns and names
*are* consistent), then I don't have any idea what your question/problem is.

[Jay]

Hi,

Thanks for the reply. Actually this is not my actual design. As what I have
mentioned that this table is based on a "make table query". I do this for
some first.

First I cannot get the crosstab query to work with crystal reports as shown
below:

TRANSFORM First([FirstName] & " " & [LastName] & " - " & [Status]) AS Name
SELECT qry_DateTemp.Date
FROM Customers RIGHT JOIN qry_DateTemp ON Customers.CustomerID =
qry_DateTemp.CustomerID
GROUP BY qry_DateTemp.Date
PIVOT qry_DateTemp.RoomNumber In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20);

As you can see the RoomNumber is hard coded in the PIVOT section. The
problem is the room number may change according to the hotel needs. What if
they use 201, 202, 203, etc in their room numbering? So this crosstab query
will be useless.

Instead I create a make table query based on the crosstab query above to
output the record in a temporary table. I do this because I can create a
code in visual basic that will pull out the Room Number from Rooms table
like the code below:

With rsRooms
.Open "SELECT RoomNumber FROM Rooms", CN, adOpenStatic,
adLockOptimistic

lvList.ColumnHeaders.Add , , "Date"

If .RecordCount > 0 Then
Do While Not .EOF
lvList.ColumnHeaders.Add , , .Fields("RoomNumber")

strRooms = strRooms & .Fields("RoomNumber")

.MoveNext

If Not .EOF Then strRooms = strRooms & ", "
Loop
End If
End With

The crosstab query 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
GROUP BY qry_DateTemp.Date
PIVOT qry_DateTemp.RoomNumber In (" & strRooms & ");

From there I can create a query named "qry_Inventory_View" and a make table
query like:

SELECT qry_Inventory_View.* INTO [Room Availability]
FROM qry_Inventory_View;

This will then insert all the records, that the qry_Inventory_View query
generated into "Room Availability" table.

If I can only create a crosstab query on crystal report I will not bother to
create this complicated process.

I hope I explained it well.

Thank you

Post by MikeD

Post by Jay
I have a table created using a make table query in ms access that is run
by my vb 6 application. The column generated in this table is not
constant and it changes depending on the room number setup by a hotel
encoder.
Date | 201 | 202 | 203 |
204
4/1/10 john matthew
4/2/10 peter
.
.
.
4/30/10 andrew
The most important here is on how I can setup the column in crystal
reports. Since the column is not constant.

A couple of things are a little unclear, to me at least. Are you saying
the actual number of columns in the table and even the column names can
vary? If so, that's a tremendously bad design. Without more information,
I don't know what would be best to suggest, but I know it's not that. I
suppose something with 2 or more tables and creating a relationship
between them. Afterall, that's why Access and SQL Server and Oracle (and
others) are called relational database management systems.
I don't think there's anything you can as far as Crystal goes with your
current design as Crystal "read" the columns when you're creating the
report. If you were using SQL Server or an RDMS that provided views and
stored procedures, I'd say *maybe* you could use either or both of those.
But I think the best answer is to redesign your database and not have its
architecture vary based on user input.
OTOH...if what I mentioned is not your database design (columns and names
*are* consistent), then I don't have any idea what your question/problem is.
--
Mike

[Phil Hunt]

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.

[Jay]

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.

Post by Phil Hunt
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.

[Phil Hunt]

I am not sure what you mean. Looking at the sample report in your first
post. CR xtab should be able to produce it comfortably.

Post by Jay
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.

Post by Phil Hunt
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.

[Phil Hunt]

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.

Post by Jay
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.

Post by Phil Hunt
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.

[Jay]

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.

Post by Phil Hunt
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.

Post by Jay
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.

Post by Phil Hunt
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.

[Phil Hunt]

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.

Post by Jay
So I can explain it better please take a look at my reports. I also
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.

Post by Phil Hunt
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.

Post by Jay
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.

Post by Phil Hunt
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.

[Jay]

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.

Post by Phil Hunt
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.

Post by Jay
So I can explain it better please take a look at my reports. I also
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.

Post by Phil Hunt
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.

Post by Jay
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.

Post by Phil Hunt
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.

[Jay]

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

Post by Phil Hunt
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.

Post by Jay
So I can explain it better please take a look at my reports. I also
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.

Post by Phil Hunt
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.

Post by Jay
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.

Post by Phil Hunt
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.

[Jay]

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.

Post by Jay
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
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

Post by Phil Hunt
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.

Post by Jay
So I can explain it better please take a look at my reports. I also
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.

Post by Phil Hunt
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.

Post by Jay
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.

Post by Phil Hunt
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.

[Phil Hunt]

I dont thank you can use a TRANSFORM query. Just do everything in CR.

Post by Jay
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
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

Post by Phil Hunt
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.

Post by Jay
So I can explain it better please take a look at my reports. I also
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.

Post by Phil Hunt
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.

Post by Jay
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.

Post by Phil Hunt
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.

[Jay]

Yes I tried everything but I can't display all the Room Number as column in
crystal reports.

Post by Phil Hunt
I dont thank you can use a TRANSFORM query. Just do everything in CR.

Post by Jay
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
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

Post by Phil Hunt
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.

Post by Jay
So I can explain it better please take a look at my reports. I also
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.

Post by Phil Hunt
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.

Post by Jay
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.

Post by Phil Hunt
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.

[Phil Hunt]

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.

Post by Jay
Yes I tried everything but I can't display all the Room Number as column
in crystal reports.

Post by Phil Hunt
I dont thank you can use a TRANSFORM query. Just do everything in CR.

Post by Jay
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
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

Post by Phil Hunt
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.

Post by Jay
So I can explain it better please take a look at my reports. I also
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.

Post by Phil Hunt
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.

Post by Jay
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.

Post by Phil Hunt
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.

[Jay]

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

Post by Phil Hunt
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.

Post by Jay
Yes I tried everything but I can't display all the Room Number as column
in crystal reports.

Post by Phil Hunt
I dont thank you can use a TRANSFORM query. Just do everything in CR.

Post by Jay
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
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

Post by Phil Hunt
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.

Post by Jay
So I can explain it better please take a look at my reports. I also
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.

Post by Phil Hunt
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.

Post by Jay
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.

Post by Phil Hunt
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.

[Phil Hunt]

Your are actually using Room# from the qrt_datetemp which already cut down
the room # you can see.

Post by Jay
Yes I am using the Room Number from Rooms table.
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

Post by Phil Hunt
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.

Post by Jay
Yes I tried everything but I can't display all the Room Number as column
in crystal reports.

Post by Phil Hunt
I dont thank you can use a TRANSFORM query. Just do everything in CR.

Post by Jay
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
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

Post by Phil Hunt
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.

Post by Jay
So I can explain it better please take a look at my reports. I also
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.

Post by Phil Hunt
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.

Post by Jay
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.

Post by Phil Hunt
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.

[Jay]

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.

Post by Phil Hunt
Your are actually using Room# from the qrt_datetemp which already cut down
the room # you can see.

Post by Jay
Yes I am using the Room Number from Rooms table.
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

Post by Phil Hunt
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.

Post by Jay
Yes I tried everything but I can't display all the Room Number as
column in crystal reports.

Post by Phil Hunt
I dont thank you can use a TRANSFORM query. Just do everything in CR.

Post by Jay
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
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

Post by Phil Hunt
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.

Post by Jay
So I can explain it better please take a look at my reports. I also
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.

Post by Phil Hunt
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.

Post by Jay
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.

Post by Phil Hunt
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.

[Phil Hunt]

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#

Post by Jay
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.

Post by Phil Hunt
Your are actually using Room# from the qrt_datetemp which already cut
down the room # you can see.

Post by Jay
Yes I am using the Room Number from Rooms table.
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

Post by Phil Hunt
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.

Post by Jay
Yes I tried everything but I can't display all the Room Number as
column in crystal reports.

Post by Phil Hunt
I dont thank you can use a TRANSFORM query. Just do everything in CR.

Post by Jay
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
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

Post by Phil Hunt
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.

Post by Jay
So I can explain it better please take a look at my reports. I
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.

Post by Phil Hunt
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.

Post by Jay
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.

Post by Phil Hunt
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.

[Phil Hunt]

Both John and Andrew reserved on feb 27. The max of John and Andrew will be
John. Change it to min and u see Andrew. You need to add another break in
your report or simply don't allow 2 reservation

Post by Jay
So I can explain it better please take a look at my reports. I also
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.

Post by Phil Hunt
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.

Post by Jay
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.

Post by Phil Hunt
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.