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

Giganews Newsgroups
Subject: Splitting a fields data into one or more new recordset(s)
Posted by:  noodnutt (noodnu…@gmail.com)
Date: Sat, 5 May 2018

Hi Team

I was hoping someone could assist me with a possible Access equivalent of t=
he ( 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 ne=
wly 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 co=
pied 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 deter=
mined by how many PONos are in the target field ( txfPONo ).

The (single ) number digit fields do not require replicating as they will b=
e 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 w=
ill 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 sin=
gle container. And in as much as utilising the existing excel process as th=
e springboard for converting these orders would be better, then importing t=
he 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 s=
omeone can point me in the right direction.

Kind regards
TIA
Mark

Excel Splitting code:

Sub SplitInbounds()

For col =3D 1 To 14

Select Case Sheets("Inbound").Cells(4, col).Value
Case "Driver"
Drv =3D col
Case "Stacks"
pltSpace =3D col
Case "Woods"
woods =3D col
Case "Weight"
wght =3D col
End Select

Next col

st =3D 5
For i =3D 5 To 350

cDrv =3D 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:=3DxlDown
Selection.Interior.ColorIndex =3D 37
Selection.Font.Bold =3D True

plts =3D plts + Cells(i, "E").Value
wds =3D wds + Cells(i, "F").Value
wg =3D wg + Cells(i, "H").Value

Cells(i + 1, "E") =3D plts
Cells(i + 1, "F").Value =3D wds
Cells(i + 1, "H").Value =3D wg

st =3D i + 1
en =3D i + 1
plts =3D 0
wds =3D 0
wg =3D 0

i =3D i + 1

ElseIf cDrv =3D Cells(i, "I").Value Then

plts =3D plts + Cells(i, "E").Value
wds =3D wds + Cells(i, "F").Value
wg =3D wg + Cells(i, "H").Value

End If

Next i

End Sub

Replies