Transform/Transpose rows to columns in MS Access

I am afraid what you want is not possible. You are trying to get results based on the order of the records in the record-list, and this cannot be done. You need to create a specific field that states the order, and then you can do it. The field that states the order would be something like:

ID  AT_cd  Order
01  BB01A  AT_cd1
01  IZ76N  AT_cd2
02  AC21B  AT_cd1
02  AX30A  AT_cd2
02  ZA98A  AT_cd3
03  AC21N  AT_cd1
03  ZA76M  AT_cd2
03  RT67T  AT_cd3
04  QS70P  AT_cd1
04  TR67A  AT_cd2
04  GB45L  AT_cd3
04  AC78M  AT_cd4

Once you do this, the following TRANSFORM produces your desired result:

TRANSFORM First(AT_cd)
SELECT ID
FROM Table_name
GROUP BY ID
ORDER BY ID
PIVOT Order

More information at LightningGuide.net.

With DCount you can get close using this query that also will sort the column values:

TRANSFORM 
    First(QueryQ.AT_cd)
SELECT 
    QueryQ.ID
FROM
    (SELECT 
        ID, 
        AT_cd, 
        "AT_cd" & CStr(DCount("*", "TableQ", "[ID] = '" & ID & "' And [AT_cd] <= '" & AT_cd & "'")) AS Sequence
    FROM 
        TableQ) As QueryQ 
GROUP BY 
    QueryQ.ID
PIVOT 
    QueryQ.Sequence;

Edit: From Duane Hookom I was suggested this alternative method using SQL only, thus perhaps faster:

TRANSFORM 
    First(QueryQ.AT_cd)
SELECT 
    QueryQ.ID
FROM 

    (SELECT TableQ.ID, TableQ.AT_cd, Count(TableQ.AT_cd) AS Sequence
    FROM TableQ 
    INNER JOIN TableQ AS TableQ_1 ON TableQ.ID = TableQ_1.ID
    WHERE (((TableQ.AT_cd)>=[TableQ_1].[AT_cd]))
    GROUP BY TableQ.ID, TableQ.AT_cd) As QueryQ

GROUP BY 
    QueryQ.ID
PIVOT 
    "AT_cd" & QueryQ.Sequence;