Splitting Text

[adidashawn6]

I have a question about the data from a linked table from a SharePoint page. One of the fields in SharePoint is a "Choice" field where employees can place a check mark next to all options that apply. When i view this data in Access, when there are multiple selections made, it is put into this format...";#Stairstep HBO;#Stairstep Cinemax;#Stairstep Starz;#" . There is a semicolon and a hash tag before each selection. Now for my question, Is there any way to separate these selections and place them into individual columns in an access table when using a Make Table Query?

 

[JANR]

Is there any way to separate these selections and place them into individual columns in an access table

Definitivly NOT!

The individual choices are not fields (Columns) in a table but individual records (Rows) in a table. Access is not Excel where this schema would be more "correct".

I assume that you use Access 2010 since you are using sharepoint and that the records in your linked Sharepoint table looks something like this:

ID Choices
1 #Stairstep Cinemax;#Stairstep Starz
2 #Stairstep HBO;#Stairstep Starz

This is a multivalue field and to split it up in individual choices you can either use a custom function in VBA and use the Split() function in an array to seperate each choice and append the to a table using recordsets or just run a query like this:

SELECT [COLOR="Red"]SharepointTable.ID[/COLOR] AS [COLOR="Blue"]NewFK[/COLOR], Replace([[COLOR="red"]Choices[/COLOR]].Value,"#","") AS [COLOR="blue"]NewValue[/COLOR] INTO [COLOR="Blue"]MyNewTable[/COLOR]
FROM [COLOR="Red"]SharepointTable[/COLOR];

This will create a new table that is properly normalized in a RMDBS.

All the names marked in RED needs to match the names in your linked sharepoint table and the bits marked in BLUE will be your new fieldname and tablename.

The Replace() function is there to strip away the #, if you need it just remove this function call and just use:

..., [choices].Value AS NewValue...

Hope this helps.

JR