multi concatenated textbox to an individual row

[BBBryan]

Hi,
Can someone help me.
I have imported a lot of data from excel into a table. There is in one of the columns some multi concatenate info I want to separate in it's own individual row.
I am not sure if there is a way to do this in a query or if there was an MsAccess2007 example somewhere. I looked on the internet but couldn't find anything.

the columns are like this now
123456 7RFE01, 7RFE03, 7MFE06, 7MFH04
237890 8RFE80, 8MFE03, 9GFE06, 5QFH06, 3QFY06

I want the columns to be like this in a query or new table.
123456 7RFE01
123456 7RFE03
123456 7MFE06
123456 7MFH04
237890 8RFE80
237890 8MFE03
237890 9GFE06
237890 5QFH06
237890 3QFY06


Thanks BBryan

 

[pbaldy]

If those are in separate fields, you can do it with a UNION query. If they're in a single field separated by commas, you're probably looking at a custom function to peel them out, using a recordset and the Split() function.

 

[BBBryan]

Thanks Paul,
I will have to research this function as I have never used it before and I am not sure how to do it.
I will see if there is a example for using the Split() function.


Thanks Bryan

 

[pbaldy]

No problem Bryan. Post back if you get stuck.

 

[BBBryan]

Hi Paul,
I researched this function And this is what I found and needed. I had to do a lot of digging to find this so i thought I would reply back to this post so others can benefit.
It appears that a lot of people are looking for this too.
I made a little database for this. It is not my code.

Thanks Bryan

 

[pbaldy]

Well done! Thanks for posting the solution, I'm sure it will help somebody. I've used the Split() quite few times, mainly for importing data from emails where a vendor sends in some sort of format that I can't pull in other ways.