[AccessD] Transpose from columns to rows

Many, many thanks for that code. I've added it to the database and I now 
have exactly the output I need.

All I have to do now is to find out how to get hold of some other data I 
need but that's a WHOLE different problem !!

Chris



On 22/07/2011 12:46, A.D. Tejpal wrote:
> Chris,
>
>      Sample query Q_Codes as given below, should get you the desired output.
>
> Q_Codes (Crosstab Query)
> =============================
> TRANSFORM First(T_Codes.Code) AS FirstOfCode
> SELECT T_Codes.ID
> FROM T_Codes
> GROUP BY T_Codes.ID
> PIVOT "Code"&  Format(DCount("*","T_Codes","ID = "&  [ID]&  " AND Code<= '"&  [Code]&  "'"),"00");
> =============================
>
>      Note: Table T_Codes has fields ID (number type) and Code (text type).
>
> Best wishes,
> A.D. Tejpal
> ------------
>
>    ----- Original Message -----
>    From: Chris Swann
>    To: Access Developers discussion and problem solving
>    Sent: Friday, July 22, 2011 02:50
>    Subject: [AccessD] Transpose from columns to rows
>
>
>    Hi all,
>
>    Once again my skills need a little help.
>
>    I have a table which contains data in rows. I need to be able to take
>    the values for each record and, where there is more than one value per
>    record, put it into separate columns.
>
>    Here is what the data looks like (there are other fields but these are
>    the ones I need to move)
>
>    ID    CODE
>    1    A1
>    1    B1
>    1    C1
>    2
>    3    A1
>    4    Z1
>    4
>
>    etc etc
>
>    what I need is this
>
>    ID    CODE1    CODE2    CODE3
>    1        A1            B1            C1
>    2
>    3        A1
>    4        Z1
>
>    If anyone can point me in the right direction I would be really grateful.
>
>    Chris Swann