need help with query

[Philippe Berberat]

from this table:

C1 C2 C3 C4 C5
R1 a1 a2 a3 a4 a5
R2 b1 b2 b3 b4 b5
R3 c1 c2 c3 c4 c5
R4 d1 d2 d3 d4 d5
R5 e1 e2 e3 e4 e5

i need this:

R1 R2 R3 R4 R5
C1 a1 b1 c1 d1 e1
C2 a2 b2 c2 d2 e2
C3 a3 b3 c3 d3 e3
C4 a4 b4 c4 d4 e4
C5 a5 b5 c5 d5 e5

is someone have an sql to solve my problem

thanks, philippe

 

[doyapore]

I think a cross-tab query would fit the bill quite admirably.
But you should have a closer look at some of the other solutions that have
been on offer by the MVPs.
Thanks.

 

[Duane Hookom]

You would need to first create a union query like
SELECT "C1" as CField, [C1] TheValue, RField
FROM ThisTable
UNION ALL
SELECT "C2", [C2], RField
FROM ThisTable
UNION ALL
SELECT "C3", [C3], RField
FROM ThisTable
UNION ALL
SELECT "C4", [C4], RField
FROM ThisTable
UNION ALL
SELECT "C5", [C5], RField
FROM ThisTable;

Then create a crosstab query with RField as the Column Heading, CField as
the Row Heading, and TheValue as the Value.

 

[Philippe Berberat]

hi duane
thank you for your tip. it works great!

philippe

You would need to first create a union query like
SELECT "C1" as CField, [C1] TheValue, RField
FROM ThisTable
UNION ALL
SELECT "C2", [C2], RField
FROM ThisTable
UNION ALL
SELECT "C3", [C3], RField
FROM ThisTable
UNION ALL
SELECT "C4", [C4], RField
FROM ThisTable
UNION ALL
SELECT "C5", [C5], RField
FROM ThisTable;

Then create a crosstab query with RField as the Column Heading, CField as
the Row Heading, and TheValue as the Value.

--
Duane Hookom
MS Access MVP

from this table:

C1 C2 C3 C4 C5
R1 a1 a2 a3 a4 a5
R2 b1 b2 b3 b4 b5
R3 c1 c2 c3 c4 c5
R4 d1 d2 d3 d4 d5
R5 e1 e2 e3 e4 e5

i need this:

R1 R2 R3 R4 R5
C1 a1 b1 c1 d1 e1
C2 a2 b2 c2 d2 e2
C3 a3 b3 c3 d3 e3
C4 a4 b4 c4 d4 e4
C5 a5 b5 c5 d5 e5

is someone have an sql to solve my problem

thanks, philippe