Split Records

[GlenLynam]

I have an excel spreadsheet that looks like the following...

ID Number Docs
1 2 1;2
2 3 1;2;3
3 1 1
4 3 1;2;3

the output in the access table needs to be...

ID Docs
1 1
1 2
2 1
2 2
2 3
3 1
4 1
4 2
4 3


How would i go about splitting the records in this way.

Thanks

Glen

 

[Golom]

Create a table called "Integers" with one field called "Num" (Type Long) and populate it with the integers from 0 to 9. (Actually, the maximum value needs to be 2 more than the length of the longest string in your "Docs" field.) Then

SELECT ID,
       Mid$("," & I1.Docs & ",", S1.num + 1 , S2.num - S1.num - 1) 
       AS [Documents]

FROM   myTable AS I1, Integers AS S1, Integers AS S2

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

"myTable" is the name of the table that contains your "ID" and "Docs" fields.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]