Spliting a field value

[tmashley]

Hello,

I have a table with a field that holds the users selections in.

This field can have on or many selections: e.g.

001 - Red, Green, Yellow
002 - Blue, Red
003 - Black
004 - Green, Black, Yellow
005 - Black

i need to show this data in a cross tab with the colours down the side and the numbers across the bottom like this;

Red - 2
Green - 2
Yellow - 2
Blue - 1
Black - 3

but all i can do is use the field and the infor down the side is like this;

Red, Green, Yellow - 1
Blue, Red - 1
Green, Black, Yellow - 1
Black - 2


Please can you help.
Thanks

 

[lbass]

I would use a series of formulas like the following:

//{@red}:
if instr({table.string},"Red") <> 0 then 1

//{@blue}:
if instr({table.string},"Blue") <> 0 then 1

etc.

Arrange these horizontally in the detail section, and then right click on each and insert a summary (sum). Then suppress the details section. This would give you a display like:

Red Blue Yellow //etc.
2 2 2

If you wanted to, you could rearrange these and add text boxes to give the display you showed in your post. You could implement this either at the group level (if you have a group) and/or at the report level.

-LB

 

[tmashley]

There is one problem with this process, the items in the field will increase over time and i do not control what items they are.

One thing is that the field that holds more than one item is ALWAYS seperated with ", " a comma then a space

could i do somthing with split & trim, i don't know !?!

 

[tmashley]

sorry to post again, but i do need some help

 

[tmashley]

anyone?