select into all unique sub-strings within field

[dumbledown]

Hi,

I have a table, 'Table1', with a field labelled 'Tags'.
Within 'Tags' for multiple records are short substring descriptions with an ending ';'

e.g.

UK; Sport; Rugby;
USA; Politics;
UK; Sport; Football;

I want to run a query that will populate another table with the unique identifiers:

i.e.

UK
USA
Sport
Rugby
Politics
Football

I don't know how to do this, and am struggling to get going with it.



Any help would be awesome.
Thanks!

[CJ_London]

you'll need to write a vba routine to split the tag and update a table. Suggest the table is called tblTags with a field called TagName which is indexed, no duplicates. Depending on what you are doing with the data once extracted you may also want an autonumber primary key.

suggest something like this in a module

Public Function SplitTag(TagStr as string) As String
dim Tarr() as string
dim I as integer

if TagStr<>"" then
    Tarr=split(TagStr,";")
    SplitTag=ubound(Tarr) & " Tags found:- |"
   On Error Resume Next
   for I=0 to ubound(Tarr)-1
       currentdb.execute("INSERT INTO tblTags (TagName) Values('" & Tarr(I) & "')", dbfailonerror)
       SplitTag=SplitTag & err.number & "|"
   next I
else
    SplitTag="No Tags"
End if

End function

and your query would simply be something like

SELECT PrimaryKey, SplitTag(Tags) FROM Table1

It will return a string which tells you how many tags it found for each record and the result of inserting each one into the table

I haven't tested it but an error may be generated if it tries to insert a value that already exists so you may find a lot of 'value already exists errors

[dumbledown]

Thanks for your response Ajax
Unfortunately I think you've misunderstood my question.

Your function returns how many tags are in each record. I want to return the unique tags in the whole table (something very different).

so from:

UK; Sport; Rugby;
USA; Politics;
UK; Sport; Football;

You return:

3
2
3


I want:

UK
USA
Sport
Rugby
Politics
Football

Thanks again
Hugh

[CJ_London]

reread my post and code- it will return something like

1.. 3 Tabs found: |0|0|0|
2.. 2 Tabs found: |9|0|
3.. 4 Tabs found: |0|9|0|0|

which tells you how well the update has gone (0 means it has inserted the tag, 9 or whatever other number appears is an error code which means the value has not been inserted and if you google the code will tell you why not.

but the code has updated a table

for I=0 to ubound(Tarr)-1
       currentdb.execute("INSERT INTO tblTags (TagName) Values('" & Tarr(I) & "')", dbfailonerror)
       SplitTag=SplitTag & err.number & "|"
   next I

modify the sql to

SELECT PrimaryKey, Tags, SplitTag(Tags) FROM Table1

might be a bit clearer for you it will return

1..UK; Sport; Rugby;..3 Tabs found: |0|0|0|
2..USA; Politics;..2 Tabs found: |0|0|
3..UK; Sport; Football;Politics;..4 Tabs found: |9|9|0|9|

[dumbledown]

Thanks Ajax,
sorry for being slow.

I didn't realise it was updating a table that has to exist (tblTags), and this is where the info really is.

Thanks
Hugh