Separating single values from a multi value field

[Chuck712]

I have a field in our data base that contains the response to a question of why applicants joined the organization. The vendor designed this field as a varchar data type and separated the choices with a comma. A list box determines the choices that data entry can input, however the results are stored with only a comma separating them. I need to count and quantify these results.

For example the fields returned will look like:
Discounts
Discounts,Social Activities
Dicounts, Member Privileges,Social Activites
Member Privileges
Social Activities,Travel
Discounts,Travel.

What I need is
Discounts 4
Member Privileges 2
Social Activities 3
Travel 2
ect.

So I need a count of each instance, but it is all stored as a text string separated by a comma.
Not the best design.
Any help would be greatly appreciated.

 

[gmmastros]

This question is very similar: thread183-1243232

The difference is that in the referenced thread, items were seperated with the space character. Your data is seperated with commas. Give this method a try, if you have any problems, post a followup question here.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Chuck712]

Thanks. This works great. I didn't think of the deconcatenate phrase when searching the post's, but this is exactly what it is.

 

[gmmastros]

I wouldn't have thought of 'deconcatenate' either, but I'm glad you found it helpful.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom