Can I combine several fields in cross-tab result?

[meryls]

Hi!

I have a report that needs to create a cross-tab (or another method) to summarize quantities in several fields. It is compicated by the fact that this customer uses a data record which uses 4 different fields to store a similar type of entry and wants them summarized together.

Let me be specific:

Each record can hold four types of rental units and four corresponding quantities.

Record one can have:

field name value
rentalType1 = chair
Quantity1 = 8
rentalType2 = table
Quantity 2 = 2
rentalType3 = tablecloth
Quantity3 = 2
rentalType4 = chafing dish
Quantity4 = 1


Record two can have:
rentalType1 = tablecloth
Quantity1 = 4
rentalType2 = napkins
Quantity2 = 16
rentalType3 = chair
Quantity3 = 16


Of course, the types of rental units are not known in advance.

The user wants a report to summarize his rental units and quantities:

Rental Type Total
=========== =====

chafing dish 1
chair 24
napkins 16
table 2
tablecloth 6

Does anyone have an idea how to group these so they can be put into a single table as shown?

Thanks for the help!

Meryl

 

[synapsevampire]

The real problem is a bad database design, stab the dba in the toe with a dull letter opener.

I'd suggest creating a Union query to create something meaningful from the data, as in:

select rentalType1, Quantity1 from table
union
select rentalType2, Quantity2 from table
union
select rentalType3, Quantity3 from table
etc.

Of course the above is database and Crystal version dependent, please post technical information with subsequent reports.

Now you'll have an intelligent data source, and a dba which will be slightly hobbled and much easier to hit with sharpened paperclips from a rubber band sling.

-k

 

[meryls]

synapsevampire,

I'd love to take you up on your suggestion re: the dba. Unfortunately, I am not even doing the query. I am receving the database layout in a ttx file and the program is being invoked from VB 6. I am using Crystal 9.

If there is no strictly Crystal solution, I will have to go back to the dba with a large blunt object and see if I can get some help on his end. Unfortunately, this is not a new product so I cannot ask for a table re-design.

Thanks!
Meryl