Transform/Transpose rows to columns in MS Access

Question

I have a table in Access database, I would like to transform AT_cd column into rows using SQL query in MS Access (as below result table) but I am unable to transform it correctly. I tried to transform using the below query but it is transforming the AT_cd into 200+ columns. I am not sure how to correct my query. Below is the sample table and desired result table provided. Any help much appreciated.

Table:

ID  AT_cd
01  BB01A
01  IZ76N
02  AC21B
02  AX30A
02  ZA98A
03  AC21N
03  ZA76M
03  RT67T
04  QS70P
04  TR67A
04  GB45L
04  AC78M

Desired result:

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

Code:

TRANSFORM FIRST(AT_cd)
SELECT [ID]
FROM Table1
GROUP BY [ID]
PIVOT AT_cd

Answer 1

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;