Splitting records in a table if a field has multiple values

Hello,

I am attempting to split a raw data table into a new table that has
split out a specific field in the raw data and created a new record
for each split but I have come to an impasse due to my limited
knowledge of sql.

The Raw data contains a number of columns with key columns similar to
Name, Product and Partcode. The issue is that the Partcode column in
the raw data can contain multiple values split by either a "," or a
"/" and in order to match with another source I need to split the
partcode and create a new record where each of the seperate columns
are the same except for the split value.

e.g Name Product Partcode
Columbus Fibre Optic cable PG234, PG456

needs to be converted to
Columbus Fibre Optic cable PG234
Columbus Fibre Optic cable PG456

I hope someone can help!

Thanks
tim

re: Splitting records in a table if a field has multiple values

SQL does not fix design flaws.


<mctime@hot-shot.comwrote in message
news:1174572380.195156.68310@e1g2000hsg.googlegrou ps.com...

re: Splitting records in a table if a field has multiple values

This is going to require some kind of programming to fix it. Probably have
to export it as a CSV file and use VB or something to rewrite the file and
then import it back into a table. My last post was a bit blunt.

This will require programming.


<mctime@hot-shot.comwrote in message
news:1174572380.195156.68310@e1g2000hsg.googlegrou ps.com...

re: Splitting records in a table if a field has multiple values

"Last Boy Scout" <Dummy@whitehouse.govwrote
Using multi-value fields is a design flaw only if your database is intended
to be relational. I use Access as a relational database, thus in one of my
databases, this would, indeed, be a design flaw.

But it is possible to use Access as a flat-file database if you do not want
all the relational advantages. Unfortunately, what the original poster
really needs is to convert his "flatfile" table into a relational table with
separate records for each of the multiple values in the multi-value field.

A loop, reading the Records from the existing table into a Recordset,
parsing the multi-value field (in recent versions, the SPLIT function can be
used), and writing a Record for each value to (the same) new Recordset. I
could put up some pseudo-code, but that often leads to someone trying to use
the pseudo-code "as-is" and being disappointed.

Note: "Name" (as shown in the example) is not a good choice for a
column-name in Access/Jet, because it is a reserved word and can lead to
confusion.

Larry Linson
Microsoft Access MVP

re: Splitting records in a table if a field has multiple values

Thanks Larry,

You have my requirement spot on, the multiple value field is from a
set of raw data that another system provides and I need to convert it
to a relational table.

Some Pseudo code would be great for me to start with as this is all
i'm after due to the fact that I have multiple raw data sets that are
all different and I just need a starting point.

p.s thanks for the note on "Name", I will make sure I don't use this
as a column name.

re: Splitting records in a table if a field has multiple values

<mctime@hot-shot.comwrote
Here's some real code, instead. I put it in the Click event of a command
button.

The raw input was in tblPartsRaw, and the normalized output was placed in
tblPartsNor. Each table had text fields named MfrName, Product, PartCode,
and OtherInfo. The PartCode Field of tblPartsRaw contained the multi-value,
comma-separated, values you described, which were "unpacked" into separate
Records in tblPartsNor, whose PartCode Field only contained one part code.

Larry Linson
Microsoft Access MVP

Private Sub cmdRunSplitCode_Click()
'---------------------------------------------------------------------------------------
' Procedure : cmdRunSplitCode_Click
' DateTime : 3/25/2007 15:10
' Author : LARRY LINSON
' Purpose : Runs "split-code" to generate multiple records
' from single record with multi-value field
'
'---------------------------------------------------------------------------------------
On Error GoTo PROC_Error
Dim db As DAO.Database
Dim rsRAW As DAO.Recordset
Dim rsNOR As DAO.Recordset
Dim aPCodes() As String
Dim i As Integer
Set db = CurrentDb
Set rsRAW = db.OpenRecordset("tblPartsRaw")
Set rsNOR = db.OpenRecordset("tblPartsNor")
If (Not rsRAW.BOF And Not rsRAW.EOF) Then
rsRAW.MoveFirst
Do Until rsRAW.EOF
aPCodes = Split(rsRAW!PartCode, ",", -1)
For i = LBound(aPCodes()) To UBound(aPCodes())
rsNOR.AddNew
rsNOR("MfrName") = rsRAW("MfrName")
rsNOR("Product") = rsRAW("Product")
rsNOR("PartCode") = aPCodes(i)
rsNOR("OtherInfo") = rsRAW("OtherInfo")
rsNOR.Update
Next i
rsRAW.MoveNext
Loop
Else
MsgBox "No Records in Input"
End If
rsRAW.Close
Set rsRAW = Nothing
rsNOR.Close
Set rsNOR = Nothing
Set db = Nothing

PROC_Exit:
Exit Sub

PROC_Error:
On Error GoTo 0
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
cmdRunSplitCode_Click of VBA Document Form_Form2"
Resume PROC_Exit:
End Sub

Private Sub cmdRunSplitCode_Click()
'---------------------------------------------------------------------------------------
' Procedure : cmdRunSplitCode_Click
' DateTime : 3/25/2007 15:10
' Author : LARRY LINSON
' Purpose : Runs "split-code" to generate multiple records
' from single record with multi-value field
'
'---------------------------------------------------------------------------------------
On Error GoTo PROC_Error
Dim db As DAO.Database
Dim rsRAW As DAO.Recordset
Dim rsNOR As DAO.Recordset
Dim aPCodes() As String
Dim i As Integer
Set db = CurrentDb
Set rsRAW = db.OpenRecordset("tblPartsRaw")
Set rsNOR = db.OpenRecordset("tblPartsNor")
If (Not rsRAW.BOF And Not rsRAW.EOF) Then
rsRAW.MoveFirst
Do Until rsRAW.EOF
aPCodes = Split(rsRAW!PartCode, ",", -1)
For i = LBound(aPCodes()) To UBound(aPCodes())
rsNOR.AddNew
rsNOR("MfrName") = rsRAW("MfrName")
rsNOR("Product") = rsRAW("Product")
rsNOR("PartCode") = aPCodes(i)
rsNOR("OtherInfo") = rsRAW("OtherInfo")
rsNOR.Update
Next i
rsRAW.MoveNext
Loop
Else
MsgBox "No Records in Input"
End If
rsRAW.Close
Set rsRAW = Nothing
rsNOR.Close
Set rsNOR = Nothing
Set db = Nothing

PROC_Exit:
Exit Sub

PROC_Error:
On Error GoTo 0
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
cmdRunSplitCode_Click of VBA Document Form_Form2"
Resume PROC_Exit:
End Sub