How do I convert the fields in one row into columns?

Question

I have a table with columns such as:

          Cost     Rate

          Repair   12
          Repair   223
          Wear     1000    
          Wear     666
          Fuel     500
          Repair   600
          Fuel     450
          Wear     400

and I want this data as columns(Repair,Wear,Fuel) as:

         Repair    Wear   Fuel
           825     2066    950

How could I do this using an MS Access Query?

Answer 1

While there's a traditional SQL solution for this that is pretty kludgy, reading this page alerted me to the fact that MS Access has a TRANSFORM ... PIVOT statement you probably should look into and use to do this.

I can't be certain but it should look like:

TRANSFORM Sum([Items].[Rate]) AS SumOfRate 
SELECT [Items].[Costs] 
FROM Items 
GROUP BY [Items].[Costs] 
PIVOT Format([Items].[Costs]);

And it can get fancier than this. E.G.

PIVOT Format([Items].[month],"mmm") In ("Jan","Feb",...,"Nov","Dec");