Parsing/Split a String Field and Appending to Table 1

[angler7]

OK, I know this is going to be easy for the gurus on this site.

I'm performing a merging of two outside databases. One database stores WorkOrder/Service Code activity one record per customer with the Service Code activity in one long field, the other database shows activity one record per Service Code. For my purposes, I'm trying to convert the former to the latter.

The tables show the type of activity for a customers Work Order, whether the customer Added a service (+) or subtracted a service (-). Each Svc code is 2 positions preceded by the +/-. (Some codes have a blank in the 1st position.) There can be dozens of codes in this field.

The Current Table is
AcctNumber SvcCode
1234 -G3-14-21-55-74
4568 +08+H1+ 5-07

The New Table I want is
AcctNumber SvcCode Action
1234 G3 -1
1234 14 -1
1234 21 -1
1234 21 -1
1234 55 -1
1234 74 -1
4568 08 1
4568 H1 1
4568 5 1
4568 07 -1

How do I go about this?

Thanks.
P.S. This is my first post after spending many hours of finding solutions/ideas on this site. Thanks to all contributors.

 

[CautionMP]

Here is a shell that will hopefully get you moving the right direction. If I have written it correctly it will cycle through the old table structure creating records in the new table based on every three (3) characters in CurrentTable.SvcCode.

Public Sub ConvertWorkOrder()
'Containers for the input records
Dim conInput As ADODB.Connection
Dim rstInput As New ADODB.Recordset
Dim sqlInput As String

'containers for the output records
Dim conOutput As ADODB.Connection
Dim rstOutput As New ADODB.Recordset
Dim sqlOutput As String

'Misc containers
Dim intSvcCodePos As Integer
Dim strSvcCode As String

'Establich appropriate connections
Set conInput = CurrentProject.Connection
Set conOutput = CurrentProject.Connection

'Define resordsets
sqlInput = "SELECT AcctNumber, SvcCode FROM CurrentTable;"
sqlOutput = "SELECT AcctNumberm, SvcCode, Action FROM NewTable;"

'Open Recordsets
rstInput.Open sqlInput, conInput, adOpenForwardOnly, adLockReadOnly
rstOutput.Open sqlOutput, conOutput, adOpenDynamic, adLockPessimistic

'Start the conversion
Do
  'Grab the old aggregate service code
  strSvcCode = rstInput.Fields("SvcCode")
  'cycle through the agregate service code
  For intSvcCodePos = 1 To Len(strSvcCode) Step 3
    With rstOutput
      .AddNew
      .Fields("AcctNumber") = rstInput.Fields("AcctNumber")
      .Fields("SvcCode") = Trim(Mid(strSvcCode, intSvcCodePos, 2))
      If Mid(strSvcCode, intSvcCodePos + 2, 1) = "-" Then
        .Fields("Action") = -1
      Else
        .Fields("Action") = 1
      End If
      .Update
    End With
  Next intSvcCodePos
  rstInput.MoveNext
Loop Until rstInput.EOF
'Done clean up
rstInput.Close
rstOutput.Close
Set rstInput = Nothing
Set rstOutput = Nothing
Set conInput = Nothing
Set conOutput = Nothing
End Sub

If you change the field names above (AcctNumber, SvcCode, Action) and Table names (CurrentTable, NewTable) to match your real data and call the sub from the immediate window the conversion should be performed.

Note: This is not fully (partially?) tested, it is built based on the data provided in oyour original post and will require moderate coding skills to finalize.

Instant programmer, just add coffee.

 

[angler7]

That worked, now I get to tweak and prod to fully incorporate into the app.

Thanks a bunch.

 

[CautionMP]

Glad ot hear it. A quick note, I botched the logic a little (I hope you caught it).

...
      '[s].Fields("SvcCode") = Trim(Mid(strSvcCode, intSvcCodePos, 2))[/s]
      .Fields("SvcCode") = Trim(Mid(strSvcCode, intSvcCodePos + 1, 2))
      '[s]If Mid(strSvcCode, intSvcCodePos + 2, 1) = "-" Then[/s]
      If Mid(strSvcCode, intSvcCodePos, 1) = "-" Then
        .Fields("Action") = -1
      Else
        .Fields("Action") = 1
      End If
...

Action is one character and comes before SvcCode.

CMP

Instant programmer, just add coffee.

 

[angler7]

Yes, I did. All part of the learning process.