Normalise a CSV within a Field

M$ Access

[ukgimp]

Is it possible to normalise an access field that is made up of a bunch of CSV values

For example

“LANGUAGE”
1¦English, French,German
2¦English
3¦German,Italian

“LANGUAGES”
1¦English
2¦French
3¦German
4¦Italian

So that the language field reads:

1,2,3
1
3,4

Stripping out all the unique languages is easy and a make new table with unique values is easy too.

I thought about having an record_id / Lang_id field but I have around 40K records some with and some without.

So that I get

1¦1
1¦2
1¦3
2¦1

etc etc

any advice as ever apprecatiated

Cheers

[mattur]

This is a standard Many to Many relationship, relating zero or more languages to zero or more somethings.

So a normalised design will be 3 tables (as you suggest):

tblSmthng:
SmthngID ¦ Smthng

tblLang:
LangID ¦ LAnguage

tblSmthngLang:
SmthngID ¦ LangID

You could use the Split function to parse your unnormalised lang fields - it returns an array from a string of delimited values. Easiest way would probably be to write a vba script to step through each record, parsing the fieldas and appending new normalised records. HTH.

[aspdaddy]

Maybe this example explains it -

Person (#ID,Name)
Language (#ID,Name)
Speaks (#PersonID, #LangID)

[ukgimp]

thanks for the replies. I get that bit, it is just a slightly different beast due to the csv being within a feild.

As only 10% of the 40K actually contain a set of secondary langauges I was wondering if there was a benefit. It is unlikely ever to be heavily searched feild. ( A like may suffice when doing an advanced search.

Cheers