Split Up Data in One Field into Several Fields? boblarson solved a similar challenge

[RBusiness]

Hello folks,


I have a field in an Access 2003 table that has several image names in it separated by "; " (semi colon and space). The thing is, I need to split them up into their own fields. I found a similar challenge (link below) but it's not the same so I'm posting this as new.

My table name is "ONE BIG TABLE" (will be exporting data from one table for CSV)
My starting field name is "ALT_IMG".
An example of the contents of a record within "ALT_IMG" is

/AAG70260G05_2_1.JPG; AAG70260G05_3_1.JPG; AAG70260G05_5_1.JPG; AAG70260G05_6_1.JPG; AAG70260G05_7_1.JPG; AAG70260G05_8_1.JPG; AAG70260G05_4_1.JPG

What I need is to split these up into their own fields. I can create new fields to populate, I just need to get them in the fields and to remove them from the original ALT_IMG field after moved. There can be as many as 0 or 1 to 20 images in the ALT_IMG field.

Ultimately I was thinking about making new fields named "ALT_IMG_2", "ALT_IMG_3" (up tp 20) and then making an update query of sort to anything more than 1 image to the next field. Meaning if there are two images, then the first stays where it is and the second is moved to ALT_IMG_2. If there are three then the first stays where it is, the second goes to ALT_IMG_2 and the third goes to ALT_IMG_3.


Now I know that there are benefits of having multiple tables but I need this to end up in the same table. Please let me know what I can do.



I can go back and clean up the spaces and ; characters after with an update query but if it can be worked in, I'll take it.

Thank you for your much anticipated response!
RB



Similar solution by boblarson

https://www.accessforums.net/showthr...mber-of-fields

[orange]

Try using Bob's solution. You'll have to adjust the field name, and you want to split on "; "
There is more logic involved I'm sure, but I think that approach should work.

I'd suggest a new table with some identifier and separate records for each of these values.

Can you post the table mdb?

[RBusiness]

Hi Orange,

The table mdb is about 1GB so no can do. Bob's solution is confusing me but not because of Bob's post but because I'm not fluent in any of this. I've had great success and have made 50+ queries for updates, table joins and so many more things. This one though, which is my last stem is an all day event so far.

I think I just need a quick and dirty way. I got the data split up in Excel / NoteTab (ugh) and imported it to Access but the order of the fields was lost somehow!

So thank you but the Bob solution is beyond my skill level. I don't even know where to put it, what to change and so on. I'm just not good at this yet. I'm trying though...

[orange]

Well, using Bob's original as a base, I have modified the function and called it SplitCodesTwo. I created a new table (TableForInsertNameHere) to accept your companyid and the text field alt_img TableForInsertNameHere.

I have assumed your records in OneBigTable are companyId and alt_Img where alt_img contains the text you showed earlier.

The new function is

'---------------------------------------------------------------------------------------
' Procedure : SplitCodesTwo
' Author    : Jack
' Date      : 24/02/2012
' Purpose   : FROM: https://www.accessforums.net/showthread.php/14370-Access-2003-Split-Data-into-an-unknown-number-of-fields
'original from Bob Larson
'---------------------------------------------------------------------------------------
'
Function SplitCodesTwo()
Dim varSplit()  As String  'array to hold result of the Split function
Dim strSQL As String
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim lngCount As Long
 
   On Error GoTo SplitCodesTwo_Error

Set db = CurrentDb

'adjusted to deal with your table and field names
Set rst = db.OpenRecordset("Select [CompanyID], [alt_img] FROM OneBigTable")
 
With rst
 
Do Until .EOF
   varSplit = Split(![alt_img], "; ")  'Split at the "; "
   For lngCount = 0 To UBound(varSplit)

'this new table was defined separately before running this function
' each array element becomes a new record in the new table
'the companyid is from the original table
      strSQL = "INSERT INTO TableForInsertNameHere ( CompanyID, Alt_img) " & _
                   "VALUES (" & !CompanyID & ",'" & varSplit(lngCount) & "');"
                   Debug.Print strSQL 'Print the rendered sql before executing
      CurrentDb.Execute strSQL, dbFailOnError
   Next
   .MoveNext
 
Loop
 
End With
 
rst.Close
Set rst = Nothing

   On Error GoTo 0
   Exit Function

SplitCodesTwo_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure SplitCodesTwo "
End Function

This was the only record in OneBigTable

companyid	alt_img
1	AAG70260G05_2_1.JPG; AAG70260G05_3_1.JPG; AAG70260G05_5_1.JPG; AAG70260G05_6_1.JPG; AAG70260G05_7_1.JPG; AAG70260G05_8_1.JPG; AAG70260G05_4_1.JPG

This is the output into the new table

companyid	alt_img
1	AAG70260G05_2_1.JPG
1	AAG70260G05_3_1.JPG
1	AAG70260G05_5_1.JPG
1	AAG70260G05_6_1.JPG
1	AAG70260G05_7_1.JPG
1	AAG70260G05_8_1.JPG
1	AAG70260G05_4_1.JPG