Results 1 to 3 of 3
  1. #1
    Chris802 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    2

    Query from CSV string in field

    Hello,
    I have a table (tbl_xTPS) with two columns of data (see attached "TPS_sourcedata.jpg" image w/ 5 rows) . The first column is 'Track_id' which contains a unique value, and the second is 'PSplit_ids' which contains a comma-separated string of related ids in another table (tbl_PartySplit). What i basically need to do is create a query that will return one record for each unique Track_id to PSplit_id relationship as in attached "TPS_desiredresult.jpg" (image w/ 16 rows).

    Here's what i've been trying so far but it's not working for me:

    SELECT tbl_xTPS.Track_id, tbl_PartySplit.PartySplit_id
    FROM tbl_PartySplit
    LEFT JOIN tbl_PartySplit
    WHERE tbl_PartySplit.PartySplit_id IN (tbl_xTPS.PSplit_ids)



    Any input on what i'm doing wrong would be helpful, thanks!
    Attached Thumbnails Attached Thumbnails TPS_sourcedata.jpg   TPS_desiredresult.jpg  

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    try:

    Code:
    Public Sub ParseData2Tbl()
    Dim vCode, vKey, vWord
    Dim sSql As String
    Dim rst
    Dim i As Integer
    
    DoCmd.SetWarnings False
    
    sSql = "select * from tbl_xTPS"
    Set rst = CurrentDb.OpenRecordset(sSql)
    With rst
      While Not .EOF
            vKey = .Fields("Track_ID").Value & ""
            vWord = .Fields("pSplit_IDs").Value & ""
            
            i = InStr(vWord, ",")
            While i > 0
               vCode = Left(vWord, i - 1)
               vWord = Mid(vWord, i + 1)
               
               GoSub Add1Rec
            Wend
            GoSub Add1Rec   'add last key
            
          .MoveNext
       Wend
    End With
    set rst = nothing
    DoCmd.SetWarnings True
    DoCmd.OpenTable "tbl_PartySplit"
    Exit Sub
    
    
    Add1Rec:
            sSql = "insert into tbl_PartySplit ([Track_ID], [pSplit_ID]) values (" & vKey & "," & vCode & ")"
            DoCmd.RunSQL sSql
    Return
    End Sub

  3. #3
    Chris802 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    2
    Hi Ranman, thanks for posting that it works~!

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

Similar Threads

  1. Replies: 1
    Last Post: 03-20-2017, 10:56 AM
  2. Replies: 1
    Last Post: 03-01-2016, 11:49 AM
  3. Replies: 7
    Last Post: 07-31-2014, 05:14 PM
  4. Need to use an update query on date string field
    By clawschieff in forum Queries
    Replies: 2
    Last Post: 01-28-2014, 02:07 PM
  5. Replies: 5
    Last Post: 03-10-2011, 02:19 PM

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