I'm trying to get a headcount of the number of clients that have items separated by an "&" in a particular field. I need to have the headcount by each of the values. I'm using an interface to a proprietary system which the Crystal Report will be inserted so I can't create tables. Using the Split command on the report allows me to create an individual row for each item but they are not records that can be sorted on or grouped on.
For Example with the 2 fields "Name" and "List of Fruits":
Name ===> List of Fruits
Joe =====> &Banana&Apple&Pear&
Bob =====>&Cherry&Pear&
Cindy ====>&Blueberry&Apple&
I would like to get the Output:
Fruit =====>Count
Apple =====>2
Banana ===>1
Blueberry ==>1
Cherry ====>1
Pear =====>2
Any help would be appreciated.....