Prev: Reading a large file that does not have newline characters
Next: Song BPM (Beats per minute) algorithm for VB6?
From: Jay on 26 Apr 2010 22:52
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

From: MikeD on 27 Apr 2010 07:03


"Jay" <jpabs78(a)gmail.com> wrote in message
news:#bt48Sb5KHA.3880(a)TK2MSFTNGP04.phx.gbl...
> 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.
>

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


From: Jay on 27 Apr 2010 08:03
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

"MikeD" <nobody(a)nowhere.edu> wrote in message
news:OUmV#kf5KHA.5476(a)TK2MSFTNGP06.phx.gbl...
>
>
> "Jay" <jpabs78(a)gmail.com> wrote in message
> news:#bt48Sb5KHA.3880(a)TK2MSFTNGP04.phx.gbl...
>> 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.
>>
>
> 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
>
>
>
From: Phil Hunt on 27 Apr 2010 10:20
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 10:33
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.
>
>
 |  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?