I'm wondering how others have solved this problem: My company has a number of "strategic goals" that we attach to various potentials as they come in. So, a potential can tick off none, one, or more than one of these as it moves through the pipeline. Let's call them by fruit names: Apple, Banana, Cherry, Durian. The data then appear as a comma separated list in the field for that question.
Potential Name Strategic Goal
Potential 1 Apple, Banana
Potential 2 Banana, Durian
Potential 3 Apple, Cherry
...
And so on. My question is how to group potentials in reports by whether they tick a given strategic goal. So, how did we do on Banana? How did we do on Durian? We're not worried about double-counting these, but the problem is that the database can't 'see' that Potential 1 and 2 both serve Banana. Is there some kind of SQL formula that deals with this? My alternative solution which is less desirable, is to make each strategic goal a checkbox instead of part of a multi-select.
Any advice from others who are reporting on this kind of data?
Andrew