Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #13105

Re: Splitting a fields data into one or more new recordset(s)

Show all headers | View raw

On Sunday, May 6, 2018 at 2:11:30 AM UTC-4, noodnutt wrote:
> Hi Team
> 
> I was hoping someone could assist me with a possible Access equivalent of the ( Excel code at the foot of this post ) in which Excel splits a cell of data, then inserts a new row,  copying certain cell information into the newly created row based on how many orders are included in the target cell. .
> 
> Essentially, once orders have been imported via csv file entitled tblImport_Orders, I need it to do the following please.
> 
> From this:
> txfLoadID.....txfPONo.....................................txfVendor
> 3006119.......0057536485,0057536571,0057536709,0057550314.XYZ Sales
> 
> To This: ( removing the leading zeros ).
> txfLoadID.....txfPONo......txfVendor
> 3006119.......57536485.....XYZ Sales
> 3006119.......57536571.....XYZ Sales
> 3006119.......57536709.....XYZ Sales
> 3006119.......57550314.....XYZ Sales
> 
> In total their is 14 fields:
> txfLoadID*
> txfPONo*
> txfVendorID*
> txfVendor*
> txfSuburb*
> txfDest*
> snfStacks
> snfWeight
> snfWoods
> txfRoute*
> txfDriver*
> txfLoad*
> dtfVenArr*
> dtfDCArr*
> 
> Of the 14 fields above, only 11* of the fields need to be replicated and copied into the new recordset(s), if! there is more than one PONo attached to a LoadID. The overall number of newly generated recordset(s) will be determined by how many PONos are in the target field ( txfPONo ).
> 
> The (single ) number digit fields do not require replicating as they will be populated via another process that looks at the individual PONo(s) volume as opposed to the entire LoadId volume which is the cumulative amount. I will be looking to use a Lookup based on the LoadID and the individual PONo ( which is provided in a different import file from our customers Planning Team ) for this step.
> 
> The company wants to transition away from individual Excel files ( one for every day of operation, totally thousands of individual files ), into a single container. And in as much as utilising the existing excel process as the springboard for converting these orders would be better, then importing the conversions, they ( and I suppose, I ) would like to explore the option of doing it within the same environment, if possible that is!
> 
> Fairly tall order for me, and i'm guessing many others, but! I am hopeful someone can point me in the right direction.
> 
> Kind regards
> TIA
> Mark
> 
> Excel Splitting code:
> 
> Sub SplitInbounds()
> 
> For col = 1 To 14
> 
> Select Case Sheets("Inbound").Cells(4, col).Value
> Case "Driver"
> Drv = col
> Case "Stacks"
> pltSpace = col
> Case "Woods"
> woods = col
> Case "Weight"
> wght = col
> End Select
> 
> Next col
> 
> st = 5
> For i = 5 To 350
> 
> cDrv = Cells(i, "I").Value
> 
> If cDrv <> Cells(i + 1, "I").Value Then
> 
> Range(Cells(i + 1, 1), Cells(i + 1, getUsedColumns)).Select
> Selection.Insert Shift:=xlDown
> Selection.Interior.ColorIndex = 37
> Selection.Font.Bold = True
> 
> plts = plts + Cells(i, "E").Value
> wds = wds + Cells(i, "F").Value
> wg = wg + Cells(i, "H").Value
> 
> Cells(i + 1, "E") = plts
> Cells(i + 1, "F").Value = wds
> Cells(i + 1, "H").Value = wg
> 
> st = i + 1
> en = i + 1
> plts = 0
> wds = 0
> wg = 0
> 
> i = i + 1
> 
> ElseIf cDrv = Cells(i, "I").Value Then
> 
> plts = plts + Cells(i, "E").Value
> wds = wds + Cells(i, "F").Value
> wg = wg + Cells(i, "H").Value
> 
> End If
> 
> Next i
> 
> End Sub

Here's some code to get you started
Public Sub E()
  Dim intPeriod As Integer
  Dim s As String
   
  s = "3006119.......0057536485,0057536571,0057536709,0057550314.XYZ Sales"
  intPeriod = InStrRev(s, ".")    'get last period
  Debug.Print Mid(s, intPeriod + 1) 'prints XYZ Sales
  s = Left(s, intPeriod - 1)
  intPeriod = InStrRev(s, ".")  'last period before orders
   Debug.Print UBound(Split(s, ",")) + 1  '# of recorods to create
  
     
End Sub

This prints out  XYZ Sales and the number 4 for the number of records to create.

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-05 23:11 -0700
  Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-05 23:50 -0700
  Re: Splitting a fields data into one or more new recordset(s) Patrick Finucane <patrickfinucanetx@gmail.com> - 2018-05-06 06:32 -0700
  Re: Splitting a fields data into one or more new recordset(s) Ron Weiner <rw@domain.com> - 2018-05-06 10:19 -0400
  Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-07 05:38 -0700
    Re: Splitting a fields data into one or more new recordset(s) Ron Weiner <rw@domain.com> - 2018-05-07 10:20 -0400
      Re: Splitting a fields data into one or more new recordset(s) Ron Weiner <rw@domain.com> - 2018-05-07 10:41 -0400
        Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-08 05:26 -0700
          Re: Splitting a fields data into one or more new recordset(s) Ron Weiner <rw@domain.com> - 2018-05-08 10:04 -0400
            Re: Splitting a fields data into one or more new recordset(s) Ron Paii <ron81pai@gmail.com> - 2018-05-08 10:57 -0700
              Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-10 05:55 -0700
                Re: Splitting a fields data into one or more new recordset(s) Ron Paii <ron81pai@gmail.com> - 2018-05-10 06:58 -0700
                Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-11 05:54 -0700
                Re: Splitting a fields data into one or more new recordset(s) Ron Paii <ron81pai@gmail.com> - 2018-05-11 10:57 -0700
                Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-11 19:49 -0700
                Re: Splitting a fields data into one or more new recordset(s) Ron Paii <ron81pai@gmail.com> - 2018-05-14 05:17 -0700
                Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-18 05:35 -0700
                Re: Splitting a fields data into one or more new recordset(s) Ron Weiner <rw@domain.com> - 2018-05-10 09:58 -0400
                Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-11 06:17 -0700
                Re: Splitting a fields data into one or more new recordset(s) Patrick Finucane <patrickfinucanetx@gmail.com> - 2018-05-11 06:40 -0700
                Re: Splitting a fields data into one or more new recordset(s) Ron Weiner <rw@domain.com> - 2018-05-11 15:26 -0400
                Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-11 19:51 -0700

csiph-web