Convert Row to Column

[albert_leung]

I need to convert the following dataset from:

ClmSys Code
1111 V1
1111 V2
1111 V3
2222 V2
2222 H4
2222 H4
2222 P8
3333 JV
4444 AA
4444 V3
4444 V2


4444 TT
....

TO
ClmSys Cod1 Cod2 Cod3 Cod4 ...
1111 V1 V2 V3
2222 V2 H4 H4 P8
3333 JV
4444 AA V3 V2 TT

Please help me for that. Any idea like SQL or any way I can do that? Thanks.
Albert

[June7]

This tutorial shows how to do something like that. It concatenates data of multiple records to a single value. http://allenbrowne.com/func-concat.html

To keep the data separate is a step beyond. You would have to write data to a table.

[albert_leung]

Yeah, I need to separate the data as individual column. which is ClmSys, Cod1, Cod2, Cod3, and Cod4 are all individual column. Anyway I can do it without VBA?

ClmSys Cod1 Cod2 Cod3 Cod4 ... 
1111    V1	  V2	  V3
2222    V2    H4	  H4	  P8
3333    JV
4444    AA	  V3	  V2	  TT

Thanks a lot.
Albert

[alansidman]

CrossPost solution:

http://www.access-programmers.co.uk/...d.php?t=216982

[June7]

Looks like the question was throughly addressed in other thread. No, I see no way to do without VBA.