Adding a column with an incremental count based on multiple selections in another column.

That subject doesn't even make sense to me and I'm writing it...

Basically I have a database that has a stupid multi select field in it which means that it can store multiple values and I need to extract them to work out the split between the categories stored in it.

Probably easiest with an example.  Lets say the field looks like the following over a few rows:

,type1,type2,type3,

,type2,

,type3,type4,

,type4,type5,type6,type7

Now lets say that each type belongs to a different category, so:

Category 1 = type1, type3

Category 2 = type2, type5, type6

Category 3 = type4,type7

For each of the categories, I need to split an amount between them.  So, let's say I have $50,000 to share between the categories for row 1.  For the first row, you can see that there are two fields in Category 1 and one field in category 2, so I split the $50,000, with Category 1 getting 66% and Category 2 getting 33% of the amount?  --Does that make sense?

I figured I'd add a calculated column where I can check if the field contains the text for each type and do a +=1 to a count.  I could then use that count across the categories for a total so I can do cash_for_category = available_cash / total_category_entries * number_of_entries_for_this_category, i.e.

Category 1 Cash = (50,000 / 3) * 2

- Where 50,000 is the cash to share

- Where 3 = number of categories in the field

- Where 2 = number of fields in this category

Category 2 Cash = (50,000 / 3) * 1

Hopefully that all makes sense.

I suspect there is a much simpler way to ask this and hopefully a much simpler answer.

Many thanks for any assistance anyone can provide.

Regards,

D.