[AccessD] Transpose from columns to rows

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