CODEWITHSUNDEEP
ms-accessms-access-2007
Deepanshu Goyal
Deepanshu Goyal

Reputation: 2813

Transpose a table using query in Access

I have the table1, i want to transpose it as shown in table1_1, can anyone tell me the query/function that can be used to do this

table 1

FileName    ObjectName  Column1 Column2 Column3 Column4 Column5

F1           Line1         A       B       C      D       E
F1           Line2         F       G       H      I       J
F1           Line3         K       L       M      N       O


table1_1


FileName    ObjectName       Column    Data
F1            Line1          Column1    A
F1            Line1          Column2    B
F1            Line1          Column3    C
F1            Line1          Column4    D
F1            Line1          Column5    E
F1            Line2          Column1    F
F1            Line2          Column2    G
F1            Line2          Column3    H
F1            Line2          Column4    I
F1            Line2          Column5    J
F1            Line3          Column1    K
F1            Line3          Column2    L
F1            Line3          Column3    M
F1            Line3          Column4    N
F1            Line3          Column5    O

Upvotes: 0

Views: 28202

Answers (1)

Fionnuala
Fionnuala

Reputation: 91326

Have a look at crosstab queries used with Min, Max and Last.

Transpose:

TRANSFORM First(Table1_1.Data) AS FirstOfData
SELECT Table1_1.FileName, Table1_1.ObjectName
FROM Table1_1
GROUP BY Table1_1.FileName, Table1_1.ObjectName
PIVOT Table1_1.Column;

Normalize:

SELECT FileName, ObjectName, "Column1" As ColName, Column1 As ColData  
FROM Table1 
UNION ALL
SELECT FileName, ObjectName, "Column2" As ColName, Column2 As ColData  
FROM Table1 
UNION ALL
SELECT FileName, ObjectName, "Column3" As ColName, Column3 As ColData  
FROM Table1 
UNION ALL
SELECT FileName, ObjectName, "Column4" As ColName, Column4 As ColData  
FROM Table1 
UNION ALL
SELECT FileName, ObjectName, "Column5" As ColName, Column5 As ColData  
FROM Table1

Upvotes: 3

Related Questions