Solved convert Row Fields From horizontall To vertical

[FahadTiger]

Hi EXPERT..
its possible in Excel..but I have no idea how can do it in access
I have this table

and I want it like this

im thinking about crosstab.. but no have idea how to do it
thanks for all

 

[plog]

Cross tab is correct and here's a good starting point:

Make summary data easier to read by using a crosstab query - Microsoft Support

Crosstab queries are useful for summarizing data, calculating statistics and then grouping the results.

support.microsoft.com

 

[plog]

Looking at your expected results closer, a crosstab will not work--you can't invert columns and rows with it. So, while you can achieve your horizontal values (Tomatto, Orange, Apple) you cannot get all 3 fields you want verticaly (Quantity, Price, Total). You can only get 1 of those values in there.

What you could do is 3 crosstabs--one for each vertical field. Then UNION those 3 queries together.

 

[FahadTiger]

thank you plog for your reply
this my simple database..
How can I do it plz

 

[June7]

First, build UNION query. Must type or copy/paste into SQL view window of query builder.

SELECT ID, ItemName, Total AS Data, "Total" AS Cat FROM Table1
UNION SELECT ID, ItemName, Price, "Price" FROM Table1
UNION SELECT ID, ItemName, Quantity, "Quantity" FROM Table1;

Now, build CROSSTAB using the UNION query as source.

TRANSFORM Sum(Query1.Data) AS SumOfData
SELECT Query1.Cat
FROM Query1
GROUP BY Query1.Cat
PIVOT Query1.ItemName;

 

[FahadTiger]

First, build UNION query. Must type or copy/paste into SQL view window of query builder.

SELECT ID, ItemName, Total AS Data, "Total" AS Cat FROM Table1
UNION SELECT ID, ItemName, Price, "Price" FROM Table1
UNION SELECT ID, ItemName, Quantity, "Quantity" FROM Table1;

Now, build CROSSTAB using the UNION query as source.

TRANSFORM Sum(Query1.Data) AS SumOfData
SELECT Query1.Cat
FROM Query1
GROUP BY Query1.Cat
PIVOT Query1.ItemName;

its perfect...thank you June7