Converting Rows to column

Question

Hi, let's assume 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?

Thanks in advance.

Answer 1

This will get the data you need:

select cost, sum(rate)

from mytable

group by cost

and then you can present it whichever way you like.

Answer 2

untested but

SELECT
(SELECT  sum(rate)  from mytable where cost = 'Repair') AS Repair ,
(SELECT  sum(rate) from mytable where cost = 'Wear') AS Wear,
(SELECT  sum(rate) from mytable wherecost = 'Fuel') AS Fuel

Answer 3

you can use this query

select 
sum(select rate from yourtable where cost = 'Repair') "Repair", 
sum(select rate from yourtable where cost = 'Wear') "Wear", 
sum(select rate from yourtable where cost = 'Fuel') "Fuel" 
from dual

if you are using Oracle

otherwise, if any other database engine, you can make a quick statement right before this one

create table dual (x char)

insert into dual values "x"

Answer 4

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");

Answer 5

You can do this with a Crosstab query. There is a wizard in the Access Queries window that will walk you through creating one. Just click the New Query button and select the Crosstab Query Wizard.