Results 1 to 5 of 5
  1. #1
    Baalu is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    7

    Lightbulb Divide the info into multiple rows based on a column

    Hi All,

    I am very new Access and this forum, kindly ignore if I do any mistakes.

    Question:

    I have a table "User_Groups" with 3 columns



    UserName UserID Groups
    Vinay A00123 TDBGRT
    Robert A05126 TDBMBT TDBSSC TDBONM
    David A04265 TTSOBD TTSBCD
    Abhi A04299 TDBMBT TDBSSC TDBOBD TDBGRT

    Now I have to right a query which shows the data in this way.

    UserName UserID Groups
    Vinay A00123 TDBGRT
    Robert A05126 TDBMBT
    Robert A05126 TDBSSC
    Robert A05126 TDBONM
    David A04265 TTSOBD
    David A04265 TTSBCD
    Abhi A04299 TDBMBT
    Abhi A04299 TDBSSC
    Abhi A04299 TDBOBD
    Abhi A04299 TDBGRT


    I don't know whether it is possible using only queries or to use some programming. I am pretty new to access. Please help me in building the solution.

    Thanks in Advance,
    Baalu

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    is your original GROUPS field a multi value field or is it just a text string?

    if it's a multi value field look into normalizing your data structure:

    Code:
    tblUsers
    User_AN  UserID  UserName
    1        A00123  Vinay
    2        A05126  Robert
    
    tblGroups
    Group_AN  GroupID  GroupName
    1         TBDGRT   Group X
    2         TDBMBT   Group Y
    3         TDBSSC   Group Z
    4         TDBONM  Group W
    
    tblUserGroup
    UG_ID  User_AN  Group_AN
    1      1        1
    2      2        2
    3      2        3
    4      2        4
    if it's a text string you would likely have to run some code to break it up as a query would likely not run this efficiently
    if you are tied to the structure you currently have and it IS a multi value field the code is a lot more complex

  3. #3
    Baalu is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    7

    Thumbs up Finally Did It with some coding and adding ID column to the User_Groups table.

    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

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Well you don't need to worry about dups if you set up your target table(s) correctly

    and I would break your string into an array, not try counting the number of spaces.

    you can split a string into an array if you have a recognizable break point, in your case a space

    Code:
    dim strArray() as string
    
    strarray = split(str, " ")
    for i = 0 to ubound(strarray)
        debug.print strarray(i)
    next i
    This is a lot more efficient than what you're doing

  5. #5
    Baalu is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    7
    I really don't know that it is this easy to split the string. I really helped me alot.

    Thanks Again.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 04-22-2014, 08:23 AM
  2. Replies: 1
    Last Post: 07-01-2013, 08:21 AM
  3. Divide a column by another Column in a Report?
    By taimysho0 in forum Reports
    Replies: 2
    Last Post: 01-06-2012, 06:25 PM
  4. Replies: 5
    Last Post: 12-01-2011, 05:38 PM
  5. Replies: 1
    Last Post: 04-09-2009, 09:18 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums