Expand all comma separated values from a field into a new list

[DDS]

I have a bound field on a form where the user is prompted to enter distinguishing features about something and separate these features with commas.
These features may be single words or phrases and the number of different comma separated features entered would vary from record to record.


Is there is a way to expand all the comma separated values that have been entered into this field from every record and populate a list of all unique values?
This list of unique values (features) would then be used as the row source of a dropdown list which will function as a kind of 'keyword' or 'keyphrase' filter.
I'm fairly confident I'd be able to make this work with just a textbox that the user types a keyword into without any assistance, but it'd be great to make this a dropdown list showing all their options instead of just a textbox.

[DDS]

I'll pre-empt suggestions of creating a new table to list the features adding an identifier to link them to the parent record.
This is a last resort due to space restrictions on the data entry form.

[Minty]

If this is a one off requirement, create a temp table utilising a small function using the Split() Function to populate your temp table.
Then use a make table query to populate your new row source table with a distinct query on the temp table.

I certainly wouldn't try and populate the combo dynamically using the function - it will be painfully slow if you have more than few records.

[DDS]

The list will be added to constantly as new records are created so it won't be a one off.
About 500 records are created each year.
Another factor is this table is eventually going to be converted to a Sharepoint list.
If it's going to populate too slowly, perhaps I will need to add each feature as a separate record in a linked table.
Will do some testing with the few records I have.
Thanks.

[Minty]

If it's only 500 records a year it may not be too bad.

I think the temp table is still the way to go, as you can link it to the existing main data table to ensure you only update unique records.