• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 640
  • Last Modified:

Append comma-delimited field into one multi value field

We have imported an excel spreadsheet into an access table. The table looks like this:
ItemNo        CheckNumbers
1                123, 145, 555555, 1010101
2                 101010, 44444

I would like to append it into a new table and have it formated like:
ItemNo          CheckNumber
1                   123
1                   145
1                   5555555
2                   101010
2                    44444

I would like to be able to do this in a query since I do not know how to do code.
Thank you in advance for your help and advice!
0
DeniseOlson
Asked:
DeniseOlson
  • 14
  • 9
  • 7
2 Solutions
 
rockiroadsCommented:
Can do this in code maybe something like this

dim rs as dao.recordset
dim sNos() as string
dim i as integer

set rs=currentdb.openrecordset("select * from originaltable")
do while rs.eof = false

'Split by comma into a string array
    sNos = split(rs!CheckNumber, ",")

'Go thru array and popuate temp table
    for i = 0 to ubound(sNos)

'Insert into new table - assuming both are numeric
        currentdb.execute "INSERT INTO newtable (ItemNo, CheckNumber) VALUES (" & rs!ItemNo & "," & trim$(sNos(i)) & ")", dbFailOnError
    next i

'Check next record
    rs.movenext
loop

rs.close
set rs=nothing

0
 
DeniseOlsonAuthor Commented:
Hi rockiroads,

Can we do this via query not code? Code is way out of my knowledge and comfort zone.

Thanks,
Denise
0
 
rockiroadsCommented:
Ok, I know you said a query, but because of the variable length of checknumbers I cannot see how it would be possible, so I did it in code
What I forgot to add was a module name
So Ive updated that code

If you hit alt-f11 to go into vba code, in the project window, right click and insert a module

then paste the code attached

changed the tablnames accordingly (originaltable, newtable) then click anywhere in the code and hit f5



Public Sub SplitCheckNumbers()
 
    Dim rs As dao.Recordset
    Dim sNos() As String
    Dim i As Integer
    Dim sSql As String
    
    Set rs = CurrentDb.OpenRecordset("select * from originaltable")
    Do While rs.EOF = False
    
        'Split by comma into a string array
        sNos = Split(rs!CheckNumber, ",")
    
        'Go thru array and popuate temp table
        For i = 0 To UBound(sNos)
    
            sSql = "INSERT INTO newtable (ItemNo, CheckNumber) VALUES (" & rs!ItemNo & "," & Trim$(sNos(i)) & ")"
            Debug.Print sSql
            
            'Insert into new table - assuming both are numeric
            CurrentDb.Execute sSql, dbFailOnError
        Next i
    
        'Check next record
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
 
End Sub

Open in new window

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
rockiroadsCommented:
I just saw your reply. Ok, if you want it in a query then hopefully some other expert here knows a way. Otherwise Ive started to show you how to do in code, can continue if you so wish
0
 
Jim P.Commented:
No way to do it in a query.

It has to be code.
0
 
DeniseOlsonAuthor Commented:
What about a two step process?
If I can get the Memo field into separate fields:
ItemNo        CheckNumber1     CheckNumber2    CheckNumber3
1                123                         55555                  12345
2                 101010                  4444

Then move/append this data into a table like this:
ItemNo          CheckNumber
1                   123
1                   145
1                   5555555
2                   101010
2                    44444
???????
0
 
Jim P.Commented:
The issue is that there is no simple split command -- because the variability of the number of items in a given row and the CheckNumber length is not consistent there is no real way to break them out with a "simple" query.
0
 
DeniseOlsonAuthor Commented:
Can it be done in SQL, that is different than code......right?
0
 
Jim P.Commented:
SQL = Structured Query Language

SQL is the query. Have you even looked at Rocki's code? It is fairly simple.
0
 
DeniseOlsonAuthor Commented:
jimpen,

I think it is simple if you understand it.
Do I need to replace "CurrentDb" with the name of my database?
"original table", would that be the name of the existing table?
"new table", wold that be the new table name?
0
 
rockiroadsCommented:
Denise, what I said earlier

>> changed the tablnames accordingly (originaltable, newtable) then click anywhere in the code and hit f5 <<

and nothing else, assuming the fieldnames are the same ItemNo and CheckNumber

this assumes originaltable is ItemNo is numeric, CheckNumber is text
and newtable ItemNo is numeric and CheckNumer is numeric

if you have it as text then we wrap in quotes. I can show you only if you need to otherwise it might confuse matters

I have tried to post simple code with minimal changes for you to do
0
 
DeniseOlsonAuthor Commented:
Thanks for your help, will need to tackle this again on Monday.
Will work on the code and close out the ticket then.
Thanks for your patience!
Denise
0
 
DeniseOlsonAuthor Commented:
rockiroads,
 
Tried the codes and received an error on this line
  sSql = "INSERT INTO tblCheckNumbers (ClaimNumberSubmitted, ClientHCCheckNum1) VALUES (" & rs!ClaimNumberSumbitted & "," & Trim$(sNos(i)) & ")"
FYI, in my question I used more generic field names, the names in the vba code are actual. The table I am trying to populate with check numbers contains the same field names as the originating table.
Thanks,
Denise
0
 
Jim P.Commented:
"Trim$" is an unknown function in VBA. "Trim" on the other hand removes leading and trailing spaces in a field.
sSql = "INSERT INTO tblCheckNumbers (ClaimNumberSubmitted, ClientHCCheckNum1) VALUES (" & rs!ClaimNumberSumbitted & "," & Trim(sNos(i)) & ")"

Open in new window

0
 
DeniseOlsonAuthor Commented:
jimpen,

Removed the $ and still got the debug error
'Insert into new table - assuming both are numeric, is one of the notes in the code and this is not the case, ClaimNumber is numeric and Check number is text.
Does this change the code?
0
 
Jim P.Commented:
It does make a difference. Try this:
sSql = "INSERT INTO tblCheckNumbers " *& _ 
          "(ClaimNumberSubmitted, ClientHCCheckNum1) " & _
   "VALUES (" & rs!ClaimNumberSumbitted & ",'" & Trim(CStr(sNos(i))) & "')"

Open in new window

0
 
DeniseOlsonAuthor Commented:
It does not like the first &
sSql = "INSERT INTO tblCheckNumbers " *&
sSql = "INSERT INTO tblCheckNumbers " *& _ 
          "(ClaimNumberSubmitted, ClientHCCheckNum1) " & _
   "VALUES (" & rs!ClaimNumberSumbitted & ",'" & Trim(CStr(sNos(i))) & "')"

Open in new window

0
 
Jim P.Commented:
The ampersand and underscore " & _ "  is a the line continuation. Try this:
sSql = "INSERT INTO tblCheckNumbers " & _ 
          "(ClaimNumberSubmitted, ClientHCCheckNum1) " & _
   "VALUES (" & rs!ClaimNumberSumbitted & ",'" & Trim(CStr(sNos(i))) & "')"

Open in new window

0
 
DeniseOlsonAuthor Commented:
Thanks, now it does not like:
 CurrentDb.Execute sSql, dbFailOnError
0
 
Jim P.Commented:
What is the error message that you are receiving?
0
 
DeniseOlsonAuthor Commented:
Run-time error '3075':
Syntax error (missing operator) in query expression "'00002824522')'.
0
 
Jim P.Commented:
Can you post all the code as you have it now?
0
 
DeniseOlsonAuthor Commented:
Here is the code, the field names have been updated
Public Sub SplitCheckNumbers()
 
    Dim rs As dao.Recordset
    Dim sNos() As String
    Dim i As Integer
    Dim sSql As String
    
    Set rs = CurrentDb.OpenRecordset("select * from tblClientFeedback")
    Do While rs.EOF = False
    
        'Split by comma into a string array
        sNos = Split(rs!CheckNumber, ",")
    
        'Go thru array and popuate temp table
        For i = 0 To UBound(sNos)
    
            sSql = "INSERT INTO tblCheckNumbers " & _
          "(ClaimNumber, CheckNumber) " & _
   "VALUES (" & rs!ClaimNumber & ",'" & Trim(CStr(sNos(i))) & "')"
 
 
            Debug.Print sSql
            
            'Insert into new table - assuming both are numeric
            CurrentDb.Execute sSql, dbFailOnError
        Next i
    
        'Check next record
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
 
End Sub

Open in new window

0
 
rockiroadsCommented:
Hi Denise, just saw all your posts. Jim TRIM$ does indeed work, not sure why it doesnt work for you. The $ is a throwback from the old days but can still be used definitely in A2003.

Denise, what datatype is CheckNumber in your table? There is no reason to do CSTR, that is really optional.

If CheckNumber is defined as numeric then we need to drop the quotes

this should work just as well if its numeric

sSql = "INSERT INTO tblCheckNumbers (ClaimNo, CheckNumber) VALUES (" & rs!ClaimNo & "," & Trim$(sNos(i)) & ")"

I am going to tigten up validation. Can u run this and when it fails it will show the sql. Can u post that screenshot so we can look at the sql





Public Sub SplitCheckNumbers()
 
    Dim rs As dao.Recordset
    Dim sNos() As String
    Dim i As Integer
    Dim sSql As String
    
    On Error Resume Next
    
    Set rs = CurrentDb.OpenRecordset("select * from tblClientFeedback")
    Do While rs.EOF = False
    
        'Split by comma into a string array
        sNos = Split(rs!CheckNumber, ",")
    
        'Go thru array and popuate temp table
        For i = 0 To UBound(sNos)
    
            If Trim$(sNos(i)) = "" Then
                MsgBox "Empty Number Encountered"
            Else
                sSql = "INSERT INTO tblCheckNumbers (ClaimNumber, CheckNumber) VALUES (" & rs!ClaimNumber & ", '" & Trim$(sNos(i)) & "')"
 
                Debug.Print sSql
            
                err.clear
                'Insert into new table - assuming both are numeric
                CurrentDb.Execute sSql, dbFailOnError
                If err.Number <> 0 Then
                    MsgBox "ERROR IN INSERTING:" & err.Description & vbCrLf & vbCrLf & sSql
                End If
            End If
        Next i
    
        'Check next record
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
 
End Sub

Open in new window

0
 
rockiroadsCommented:
Denise, is there any possibility that you can have records without a claim number in tblClientFeedback? If so then we need to handle that

Public Sub SplitCheckNumbers()
 
    Dim rs As dao.Recordset
    Dim sNos() As String
    Dim i As Integer
    Dim sSql As String
    
    On Error Resume Next
    
    Set rs = CurrentDb.OpenRecordset("select * from tblClientFeedback")
    Do While rs.EOF = False
    
        'Ensure claim number exists
        If IsNull(rs!ClaimNumber) = False Then
           
           'Split by comma into a string array
           sNos = Split(rs!CheckNumber, ",")
       
           'Go thru array and popuate temp table
           For i = 0 To UBound(sNos)
       
               If Trim$(sNos(i)) = "" Then
                   MsgBox "Empty Number Encountered"
               Else
                   sSql = "INSERT INTO tblCheckNumbers (ClaimNumber, CheckNumber) VALUES (" & rs!ClaimNumber & ", '" & Trim$(sNos(i)) & "')"
    
                   Debug.Print sSql
               
                   err.clear
                   'Insert into new table - assuming both are numeric
                   CurrentDb.Execute sSql, dbFailOnError
                   If err.Number <> 0 Then
                       MsgBox "ERROR IN INSERTING:" & err.Description & vbCrLf & vbCrLf & sSql
                   End If
               End If
           Next i
        End If
        
        'Check next record
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
 
End Sub

Open in new window

0
 
DeniseOlsonAuthor Commented:
Put in the latest code and still received an error. Attached is a screenshot.
ClaimNumber is a number field
CheckNumber is a text field
There are no blank ClaimNumbers in the table
Thanks!!

Error-Screenshot.xls
0
 
rockiroadsCommented:
It seems you have a single quote in that data. Is that expected?

To keep the single quote, if you replace the line that begins with

sSql = "INSERT INTO ...

with
                   sSql = "INSERT INTO tblCheckNumbers (ClaimNumber, CheckNumber) VALUES (" & rs!ClaimNumber & ", " & Chr$(34) & Trim$(sNos(i)) & Chr$(34) & ")"

then try it


Alternatively, to get rid of single quotes, use this line instead

                   sSql = "INSERT INTO tblCheckNumbers (ClaimNumber, CheckNumber) VALUES (" & rs!ClaimNumber & ", '" & Trim$(Replace(sNos(i), "'", "")) & "')"

0
 
DeniseOlsonAuthor Commented:
rockiroads, your code worked!! Thank you! Hope how the points are awared is ok.
jimpen, I awarded point to you also for your efforts, thanks!
Will now be posting more questions on how to save this module and get it into a macro
0
 
DeniseOlsonAuthor Commented:
There were random single quotes in the data. Attached is the final code used that worked.

Thanks for all of your support!!
Public Sub SplitCheckNumbers()
 
    Dim rs As dao.Recordset
    Dim sNos() As String
    Dim i As Integer
    Dim sSql As String
    
    On Error Resume Next
    
    Set rs = CurrentDb.OpenRecordset("select * from tblClientFeedback")
    Do While rs.EOF = False
    
        'Ensure claim number exists
        If IsNull(rs!ClaimNumber) = False Then
           
           'Split by comma into a string array
           sNos = Split(rs!CheckNumber, ",")
       
           'Go thru array and popuate temp table
           For i = 0 To UBound(sNos)
       
               If Trim$(sNos(i)) = "" Then
                   MsgBox "Empty Number Encountered"
               Else
                     sSql = "INSERT INTO tblCheckNumbers (ClaimNumber, CheckNumber) VALUES (" & rs!ClaimNumber & ", '" & Trim$(Replace(sNos(i), "'", "")) & "')"
 
    
                   Debug.Print sSql
               
                   Err.Clear
                   'Insert into new table - assuming both are numeric
                   CurrentDb.Execute sSql, dbFailOnError
                   If Err.Number <> 0 Then
                       MsgBox "ERROR IN INSERTING:" & Err.Description & vbCrLf & vbCrLf & sSql
                   End If
               End If
           Next i
        End If
        
        'Check next record
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
 
End Sub

Open in new window

0
 
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 14
  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now