Split field text to new row

[verlorene]

I need to pull the data out of one field (seperated by semicolons) and place it into new rows with all of the other information in tact. This example only shows one client but there are many. The finished query will make a new table.

Here are some sample fields for each row:
Row
ClientID
Name
State
Product


Sample Data:
1 123 Bob Jones TX Lorem ipsum;dolor;sit, amet;consectetuer - adipiscing elit
2 123 Bob Jones CA
3 123 Bob Jones MI Sed; nonummy sapien eu tortor


Needed Row Output:
1 123 Bob Jones TX Lorem ipsum
2 123 Bob Jones TX dolor
3 123 Bob Jones TX sit, amet
4 123 Bob Jones TX consectetuer - adipiscing elit
5 123 Bob Jones CA
6 123 Bob Jones MI Sed
7 123 Bob Jones MI nonummy sapien eu tortor


Any help would be appreciated.
Thanks!

 

[Golom]

SELECT I1.Row, I1.ClientID, I1.[Name], I1.State,  
       Mid$(";" & I1.Product & ";", S1.num + 1 , S2.num - S1.num - 1) AS [SubProduct]

FROM   tblInput AS I1, integers AS S1, integers AS S2

WHERE S1.num <= Len(";" & I1.Product & ";") 
      AND S2.num <= Len(";" & I1.Product & ";") 
      AND Mid$(";" & I1.Product & ";" ,S1.num , 1 ) = ';'
      AND Mid$(";" & I1.Product & ";" ,S2.num , 1 ) = ';' 
      AND S1.num+1 < S2.num 
      AND instr(1,Mid$(";" & I1.Product & ";", S1.num + 1 , S2.num - S1.num - 1),";")=0

ORDER BY 1, 2, 3, 4, 5;

Where integers is a table with one field called num that contains the integers from 1 to 2 more than the maximum string length in tblInput.Product.

 

[verlorene]

One other note: there can be any number of states or products (products can even be blank)

 

[Golom]

What I gave you will still work as long as you have enough integers to handle the full length of the text string plus 2.