Splitting field

M

Mike

I have a table that has part numbers in one field seperated by commas. I
need to make the comma items seperate records with the main number

How it looks now:

mainno partnumbers
1 E1234, 2345, 3456, 5678
2 S234, E3456, R5678


How I want it:

1 E1234
1 2345
1 3456
2 S234
2 E3456
 
M

Martin J

The simplest solution probably is write a function in code using the split
function use help if not familiar. However if your not comfortable using code
and more comfortable using queries. You will need probably 3 queries. The
first would be an append query to your new correct table. it will copy main#
and the first part#, it will be an iif instr(
part#,",")>0,left$(part#,instr(( part#,",")-1),part#). The second query will
update a copy of your original table (dont want to lose the original just in
case), taking out the first part# until and including the comma like iif
instr( part#,",")>0, right$(part#,instr(( part#,",")), ""). Then write a
delete query to delete records with empty part#. you will need to run these 3
queries in order until no more records are left in the original table.

HTH
Martin J
 
Top