Hi, Thanks for you inputs.
I actually get this data from other teams in excel format. And we usually do this work in excel manually. I'm trying to automate that.
Anyhow I could do it with little coding and creating an additional table. I added ID column to the table User_Groups.
Code I used:
Code:
Public Sub DivideData()
Dim str As String
Dim Token As String
Dim cnt, length, Spacecnt As Integer
Dim strSQL, strSQLIns As String
Dim rs As Recordset
Dim cn As Database
strSQL = "SELECT User_Groups.ID, User_Groups.Username, User_Groups.UserID, User_Groups.Groups FROM User_Groups"
Set cn = CurrentDb
Set rs = cn.OpenRecordset(strSQL, dbOpenDynaset)
DoCmd.SetWarnings (False)
DoCmd.RunSQL "Delete * from MakeDups;"
DoCmd.SetWarnings (True)
If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
Do Until rs.EOF = True
If Trim(rs.Fields(2).Value) <> "" Then
strSQLIns = "Insert into MakeDUPs (ID,UserID) Values (" & rs.Fields(0) & ", "
str = rs.Fields(0).Value
End If
cnt = 0
length = Len(str)
str = Trim(str)
Spacecnt = InStr(1, str, " ")
Do While Spacecnt > 0
cnt = cnt + 1
Token = Left(str, InStr(1, str, " ") - 1)
DoCmd.SetWarnings (False)
DoCmd.RunSQL strSQLIns & """" & Token & """" & ");"
DoCmd.SetWarnings (True)
'MsgBox strSQLIns & """" & Token & """" & ");"
str = Mid(str, InStr(1, str, " ") + 1)
Spacecnt = InStr(1, str, " ")
Loop
cnt = cnt + 1
DoCmd.SetWarnings (False)
DoCmd.RunSQL strSQLIns & """" & str & """" & ");"
DoCmd.SetWarnings (True)
'MsgBox strSQLIns & """" & str & """" & ");"
Token = ""
rs.MoveNext
Loop
End If
End Sub
I appreciate your suggestions and remarks on this.
Thanks in advance,
Baalu