Pivot Query in MS Access

Question

I have the following data in an Access table:

ID    Name      CAT
1     Bill      Red
1     Bill      Yellow
1     Bill      Green
1     Bill      Orange
2     Ted       Purple
2     Ted       White
3     Alice     Indigo
3     Alice     Violet
3     Alice     Red

And I would like to output it as follows:

ID    Cat1    Cat2    Cat3    Cat4
1     Red     Yellow  Green   Orange
2     Purple  White        
3     Indigo  Violet  Red    

Can I use pivot for this? If so, can someone suggest a suitable query? Many thanks.

Answer 1

Consider:

TRANSFORM First(Data.Cat) AS FirstOfCat
SELECT Data.ID, Data.Name
FROM Data
GROUP BY Data.ID, Data.Name
PIVOT "Cat" & DCount("*","Data","ID=" & [ID] & " AND Cat<'" & [Cat] & "'")+1;

Or if there is a unique record identifier field - autonumber should serve:

TRANSFORM First(Data.Cat) AS FirstOfCat
SELECT Data.ID, Data.Name
FROM Data
GROUP BY Data.ID, Data.Name
PIVOT "Cat" & DCount("*","Data","ID=" & [ID] & " AND ID_PK<" & [ID_PK])+1;