Displaying/grouping records correctly

[arodri]

Hello,

I played with this data for an entire day and still couldn't figure out how to get it the way I need it. Here is a sample of my data (table in sql server):

Site CustID Name PhoneNumber Code
TX01 101 Jane Smith 222-222-222 A1C357
TX01 103 John Key NULL A1C258
TX01 103 John Key NULL B999-tx
TX01 104 Jane Thomas NULL A1C987
Tx01 104 JAne Thomas NULL C78d-tx

-The CustID is unique for every customer
-There are 3 types of Codes
1. The ones that start with "A1C"
2. The ones that end with "-tx"
3. The ones that have a "+" in the code
-The same customer can have a record for one, two , or three of these codes (ex. John Key & Jane Thomas in sampe data above have A1C and -tx codes)


What I need to do with this data is somehow group it so that the data displays only ONE record per person and lists all of their codes in that record, like this:

Site CustID Name PhoneNumber Code-A1C Code-tx Code+
TX01 101 Jane Smith 222-222-222 A1C357 NULL NULL
TX01 103 John Key NULL A1C258 B999-tx NULL
TX01 104 Jane Thomas NULL A1C987 C78d-tx NULL

I have put this same post in the sql forums in case this can't be done in crystal, but I have tried all sorts of groupings in crystal and formulas and none of them do this.

I am able to get just one record per customer, but then for example, John Key (from sample data above) will have a code for A1C, but none for -tx, even though he clearly has a -tx code. For some reason that comes out blank in crystal when I group it.

Any ideas on how to accomplish this?? I REALLY need to figure something out and I ran out of ideas.

Thank you!

 

[CRCRCR]

Create a formula similar to this. Call "Code_Type"

If left({TABLE.Code},3) = "A1C" then "A1C" else
if right({Table_.Code},2) = "tx" then "tx" else
if mid({Table_.Code},2,1) = "+" then "+"
else "Other Code_Type"

(if the "+" isn't in a fixed position, you will have to use an 'in string' type funtion. (look up string functions inthe help, if required.

then create a crosstab.

Rows should be

Site
Cust_ID
Name
Phone_number


Columns should be the formula you created
i.e.
@Code_Type


the Summarised field shoudl be something like Max of {Table.Code}
Get rid of all the subtotals, and empty columns etc.. that Crystal seems to default with.

and heh presto, you shoudl get what you need.

Kai.