Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    breezett93 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    53

    Convert values/string of a field into multiple records

    Prefacing that this is most definitely a poor design problem. In one table, I have a parent item that can have infinite child items. Here is an example:

    Parent | Child
    -------------
    1234 | 13, 27, 189, 210

    I'm looking for a two-part query. The first part is to turn that record into something like this:

    Parent | Child
    -------------
    1234 | 13


    1234 | 27
    1234 | 189
    1234 | 210

    Then, part two would be to dump that information into a different table.

    Thank you

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    where the source data is imported to tSrcTable,
    something like:

    Code:
    Public Sub ParseChilds()
    Dim rst, vQry
    Dim sSql As String
    Set rst = CurrentDb.OpenRecordset("select * from tSrcTable")
    DoCmd.SetWarnings False
    With rst
       While Not .EOF
            vPar = .Fields("parent").Value & ""
            vLine = .Fields("child").Value & ""
            
            i = InStr(vLine, ",")
            While i > 0
              vChild = Left(vLine, i - 1)
              vLine = Mid(vLine, i + 1)
              GoSub Add1Child
              
              i = InStr(vLine, ",")
            Wend
            vChild = vLine
            GoSub Add1Child
            
           .MoveNext
       Wend
    DoCmd.SetWarnings true
    Exit Sub
    
    Add1Child:
        sSql = "insert into tTargTable (PARENT,CHILD) VALUES ('" & vPar & "','" & vChild & "')"
        DoCmd.RunSQL sSql
    Return
    End With
    Set rst = Nothing
    Debug.Print "----done"
    End Sub
    


  3. #3
    breezett93 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    53
    Thanks for responding!

    The result in tTargTable looks like this:

    ID | Parent | Child
    -----------------
    1 |_______| 13
    2 |_______| 27

    I'm not sure why Parent is being left blank.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't see this as being possible in a query as you've asked, but maybe the split function might be a simpler approach. Split will give you an array, and you'd need to use the array UBound value as the upper value of a counter. That counter range will allow you to loop over the values and append them to your records.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Try this Sub.....

    **** Note that "Parent" is a reserved word in Access and shouldn't be used for object names. So I used "TheParent" and "TheChild".

    This uses the Split function as Micron suggested. You will have to change the table names and field names highlighted in BLUE to your table and field names:
    Code:
    Public Sub ParseRecord()
        Dim d As DAO.Database
        Dim r As DAO.Recordset
        Dim sSQL As String
        Dim vChild As String
        Dim ARR() As String       'Declare an array
        Dim i As Integer
        Dim vParent
    
        Set d = CurrentDb
        Set r = d.OpenRecordset("SELECT * FROM tblSource")
    
        With r
            If Not .BOF And Not .EOF Then
                While Not .EOF
    
                    vParent = .Fields("TheParent") & ""
                    vChild = .Fields("TheChild")
                    ARR = Split(vChild, ",")
    
                    For i = LBound(ARR) To UBound(ARR)
                        sSQL = "INSERT INTO tblTarget (TheParent,TheChild) VALUES ('" & vParent & "','" & ARR(i) & "')"
       '                Debug.Print sSQL
                        d.Execute sSQL, dbFailOnError     '<-- corrected line
                    Next
    
                    Erase ARR
    
                    .MoveNext
                Wend
            End If
        End With
    
        r.Close
        Set r = Nothing
        Set d = Nothing
    
    
        MsgBox "Done!"
    
    End Sub
    Last edited by ssanfu; 09-11-2020 at 02:11 PM. Reason: Corrected error in code...

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Hmm, 2 thoughts if I may
    1) CurrentDb.Execute - might as well use d, as it is declared? I would never use CurrentDb in a loop, but I forget exactly what the implications are and won't be able to find them at this time. OK, think I found it - it creates a new db object every time you call it. Not sure if that has any memory leakage issue since it only ever gets closed once at the end.

    2) have never used Erase, so do you really want to set array values to zls (for string arrays) rather than just kill and reset it? Could you end up with a subsequent array having 1 less element (because the record had one less value) plus a zls? I would think not since on the next loop the new array ought to be assigned to the variable. If so, it begs the question why Erase rather than just set the variable to a new array?
    Last edited by Micron; 09-10-2020 at 08:02 PM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    breezett93 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    53
    Run-time error 3463: Data type mismatch in criteria expression. on this line:
    Code:
    CurrentDb.Execute sSQL, dbFailOnError
    I'm guessing something with the Insert Into statement is just lightly wrong.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you have set the option to break on unhandled errors, then by turning the debug line on (uncomment it) and ensuring that you have already opened the immediate window (view on vb editor toolbar I believe) the code should break when it errors. At that point you should be able to see what the last printed sql statement was. Likely one or both of the variables are null or zls (zero length string). That could cause an issue with the sql itself or perhaps with the array.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    breezett93 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    53
    Thanks for sharing that immediate window. Never used that before!

    Parent was coming out as ' '. Child has the correct value. I made a minor adjustment to the Parent field, and now it's working properly.

    Thank you!

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You're welcome. Glad you got this solved!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Micron,
    1) Uhhhh, an oversight. I threw the code together and got it kinda working, then forgot to change the "Currentdb" to "d". I, too, knew about creating a new db object every time you call Currentdb.
    2) I wanted to clear the array (and I rarely use arrays), so I tried using "Erase". Seemed to do want I wanted.
    Thanks for catching the error and the array info/suggestion.

    @breezett93
    I didn't know your data types, so I used the String data type for the array and the Variant type for the "Parent" field (hope you changed the name) instead of Integer or Long.
    Glad you were able to fix it.

  12. #12
    breezett93 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    53
    I just noticed that every-time this query is called, it is adding every Child, not just NEW ones to the second table; so I'm getting lots of duplicates.

    Unless I'm mistaken, DISTINCT doesn't work with INSERT INTO; so what are my options for adjusting this query to only pull new Child numbers that aren't already in the second table?


    More background info to help with the "full picture":

    This query is triggered on a form after a user enters a Parent number in a text-box. Then all the Child numbers related to the Parent are displayed on the form.

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Perhaps you could expound a little more on your process. To me, it sounded like you have a table with multiple "child" values in one record and you wanted the child values in separate records in a different table.

    So are you entering a new record in the "Parent" (Source) table and entering multiple "Child record" values, then you want to split the child values into separate records into a different table for that one (new) record?
    If you want to add only a NEW record in the source table, you need to change the code, specifically the line
    Code:
    Set r = d.OpenRecordset("Select * from tblSource")
    to have a WHERE clause. The WHERE clause would use the "Parent" table PK value to select only that (new) record.

    Or you need some way to select all of the (new) records where you want the concatenated "Child" values split into separate records. And it depends on how/when you execute the query.

  14. #14
    breezett93 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    53
    Quote Originally Posted by ssanfu View Post
    To me, it sounded like you have a table with multiple "child" values in one record and you wanted the child values in separate records in a different table.
    That is correct.

    Quote Originally Posted by ssanfu View Post
    So are you entering a new record in the "Parent" (Source) table and entering multiple "Child record" values, then you want to split the child values into separate records into a different table for that one (new) record?
    Also yes. So using my example from before:

    Source Table -

    Parent | Child
    -------------
    1234 | 13, 27, 189, 210

    And the second/new table looks like this:

    Parent | Child
    -------------
    1234 | 13
    1234 | 27
    1234 | 189
    1234 | 210

    Perhaps that's what I had yesterday, but today a user enters a new child number (300) in the record associated with 1234. So, now the source table looks like this:

    Parent | Child
    -------------
    1234 | 13, 27, 189, 210

    Then they go to the form with the query that you wrote and enter 1234. The query splits the new child number AND the existing four that had already been split. So, in the second table, 1234 has two records of the first four children and one record of the new number. Second table now looks like this:

    Parent | Child
    -------------
    1234 | 13
    1234 | 27
    1234 | 189
    1234 | 210
    1234 | 13
    1234 | 27
    1234 | 189
    1234 | 210
    1234 | 300

    Does that make more sense?

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    IF it is such that you'd never want 2 identical values in the field, then this could be as simple as setting the target table field to be unique and then running sql or a query from vba. But you would have to turn off warnings, which needs to be done in conjunction with a properly written error handler to ensure they're turned back on. Then only the new value will be added and you will not raise a message about not being able to add x records. However IF there can be a 1234 | 13 and a 2468 | 13 then you're either talking a composite index (of the two fields) or a better designed query that ignores existing values that are in the target table (which would also have to consider what the 'parent' value is). I'll leave the query to ssanfu as he's much better at that than me.
    Last edited by Micron; 09-22-2020 at 12:03 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-28-2018, 11:16 AM
  2. Replies: 11
    Last Post: 08-10-2014, 06:13 PM
  3. Replies: 7
    Last Post: 05-09-2012, 01:55 PM
  4. Replies: 5
    Last Post: 08-29-2011, 05:17 PM
  5. Replies: 2
    Last Post: 04-07-2011, 10:15 AM

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