Move Multiple Values In Column To New Record

[KyleAK]

I am trying to write a query to take the following table:

Part Description
1001 BHJ, UI
1002 GU, SI, PQ
1003 YU, JU

And write it in the following format:

Part Description
1001 BHJ
1001 UI
1002 GU
1002 SI
1002 PQ
1003 YU
1003 JU

Basically, move each separate value, separated by a comma in the
description field, to a new record, but still tied to the original part
number.

I know I should be using a UNION query, but not sure exactly how to
formulate it. Any help would be greatly appreciated.

 

[John Nurick]

Hi Kyle,

Paste this little function into a module. It

Public Function SafeSplit(V As Variant, _
Delim As String, Item As Long) As Variant

On Error Resume Next 'to return Null if Item is out of range
SafeSplit = Split(V, Delim)(Item)
End Function

Then build a union query like this, with as many elements as needed:

SELECT Part AS PART,
SafeSplit([Description], ",", 0) AS Descr
FROM MyTable
UNION
SELECT Part AS PART,
SafeSplit([Description], ",", 1) AS Descr
FROM MyTable
UNION
SELECT Part AS PART,
SafeSplit([Description], ",", 2) AS Descr
FROM MyTable
...

When that's working, turn it into an append query to add the non-null
records to the other table:

INSERT INTO OtherTable (Part, Description)
SELECT Part, Descr
FROM (
SELECT Part AS PART,
SafeSplit([Description], ",", 0) AS Descr
FROM MyTable
UNION
SELECT Part AS PART,
SafeSplit([Description], ",", 1) AS Descr
FROM MyTable
...
)
WHERE Descr IS NOT NULL;