how to transpose multiple columns into rows?

[pemt]

Hi,

I have a query to make a table like this:
Item Num Values
A 1 10
A 2 22
A 3 78
B 1 32
B 2 40
B 3 87
C 1 34
C 2 76
C 3 98
actually each "Item" has more than a thousand of "Num".
how to "transpose" this "column table" into "row table"?
Like:
1 2 3 4 ....
A 10 22 78 ....
B 32 40 87 ...
C 34 76 98 ...

Thanks!

pemt

 

[KARL DEWEY]

Crosstab query --
TRANSFORM Sum([Values]) AS TotalOfValues
SELECT Item
FROM YourTable
GROUP BY Item
PIVOT Num;

 

[pemt]

Hi Karl,

thanks a lot.
It works well for "Num" with only 3 cells, but it doesn't work for "Num"
with over one thounsand cells. The error said "too many lines for crosstab".
For Excel 2007, there is over 60,000 columns. Maybe the crosstab doesn't have
so many columns?
How to fix it?
thanks again for your always help.

pemt3

Crosstab query --
TRANSFORM Sum([Values]) AS TotalOfValues
SELECT Item
FROM YourTable
GROUP BY Item
PIVOT Num;

--
Build a little, test a little.

Hi,

I have a query to make a table like this:
Item Num Values
A 1 10
A 2 22
A 3 78
B 1 32
B 2 40
B 3 87
C 1 34
C 2 76
C 3 98
actually each "Item" has more than a thousand of "Num".
how to "transpose" this "column table" into "row table"?
Like:
1 2 3 4 ....
A 10 22 78 ....
B 32 40 87 ...
C 34 76 98 ...

Thanks!

pemt

 

[pemt]

Hi Karl,

thanks a lot!

pemt

Crosstab query --
TRANSFORM Sum([Values]) AS TotalOfValues
SELECT Item
FROM YourTable
GROUP BY Item
PIVOT Num;

--
Build a little, test a little.

Hi,

I have a query to make a table like this:
Item Num Values
A 1 10
A 2 22
A 3 78
B 1 32
B 2 40
B 3 87
C 1 34
C 2 76
C 3 98
actually each "Item" has more than a thousand of "Num".
how to "transpose" this "column table" into "row table"?
Like:
1 2 3 4 ....
A 10 22 78 ....
B 32 40 87 ...
C 34 76 98 ...

Thanks!

pemt

 

[KARL DEWEY]

I think the limit for crosstab is 255.
I would suggest batches but that is a whole lot of batches.

Reverse Item and Num in the crosstab and then in Excel paste Transpose.

--
Build a little, test a little.

Hi Karl,

thanks a lot.
It works well for "Num" with only 3 cells, but it doesn't work for "Num"
with over one thounsand cells. The error said "too many lines for crosstab".
For Excel 2007, there is over 60,000 columns. Maybe the crosstab doesn't have
so many columns?
How to fix it?
thanks again for your always help.

pemt3

Crosstab query --
TRANSFORM Sum([Values]) AS TotalOfValues
SELECT Item
FROM YourTable
GROUP BY Item
PIVOT Num;

--
Build a little, test a little.

Hi,

I have a query to make a table like this:
Item Num Values
A 1 10
A 2 22
A 3 78
B 1 32
B 2 40
B 3 87
C 1 34
C 2 76
C 3 98
actually each "Item" has more than a thousand of "Num".
how to "transpose" this "column table" into "row table"?
Like:
1 2 3 4 ....
A 10 22 78 ....
B 32 40 87 ...
C 34 76 98 ...

Thanks!

pemt

 

[pemt]

Hi Karl,

how to reverse Item and Num in the crosstab?
I switch the columns Item and Num, but it shows like this:
Num 1 2 3 1 2 3 1 2 3
Item A A A B B B C C C
Value 10 22 78 32 40 87 34 79 98

Thanks,

pemt

I think the limit for crosstab is 255.
I would suggest batches but that is a whole lot of batches.

Reverse Item and Num in the crosstab and then in Excel paste Transpose.

--
Build a little, test a little.

Hi Karl,

thanks a lot.
It works well for "Num" with only 3 cells, but it doesn't work for "Num"
with over one thounsand cells. The error said "too many lines for crosstab".
For Excel 2007, there is over 60,000 columns. Maybe the crosstab doesn't have
so many columns?
How to fix it?
thanks again for your always help.

pemt3

Crosstab query --
TRANSFORM Sum([Values]) AS TotalOfValues
SELECT Item
FROM YourTable
GROUP BY Item
PIVOT Num;

--
Build a little, test a little.


:

Hi,

I have a query to make a table like this:
Item Num Values
A 1 10
A 2 22
A 3 78
B 1 32
B 2 40
B 3 87
C 1 34
C 2 76
C 3 98
actually each "Item" has more than a thousand of "Num".
how to "transpose" this "column table" into "row table"?
Like:
1 2 3 4 ....
A 10 22 78 ....
B 32 40 87 ...
C 34 76 98 ...

Thanks!

pemt

 

[KARL DEWEY]

Crosstab query --
TRANSFORM Sum([Values]) AS TotalOfValues
SELECT Num
FROM YourTable
GROUP BY Num
PIVOT Item;

The results should be like this --
A B C
1 10 32 34
2 22 40 76
3 78 87 98
4 .... ... ...

Then Paste Transpose in Excel.
--
Build a little, test a little.

Hi Karl,

how to reverse Item and Num in the crosstab?
I switch the columns Item and Num, but it shows like this:
Num 1 2 3 1 2 3 1 2 3
Item A A A B B B C C C
Value 10 22 78 32 40 87 34 79 98

Thanks,

pemt

I think the limit for crosstab is 255.
I would suggest batches but that is a whole lot of batches.

Reverse Item and Num in the crosstab and then in Excel paste Transpose.

--
Build a little, test a little.

Hi Karl,

thanks a lot.
It works well for "Num" with only 3 cells, but it doesn't work for "Num"
with over one thounsand cells. The error said "too many lines for crosstab".
For Excel 2007, there is over 60,000 columns. Maybe the crosstab doesn't have
so many columns?
How to fix it?
thanks again for your always help.

pemt3

:

Crosstab query --
TRANSFORM Sum([Values]) AS TotalOfValues
SELECT Item
FROM YourTable
GROUP BY Item
PIVOT Num;

--
Build a little, test a little.


:

Hi,

I have a query to make a table like this:
Item Num Values
A 1 10
A 2 22
A 3 78
B 1 32
B 2 40
B 3 87
C 1 34
C 2 76
C 3 98
actually each "Item" has more than a thousand of "Num".
how to "transpose" this "column table" into "row table"?
Like:
1 2 3 4 ....
A 10 22 78 ....
B 32 40 87 ...
C 34 76 98 ...

Thanks!

pemt

 

[robhmac]

>Then Paste Transpose in Excel.

Okay, that's all fine and dandy, but isn't how to do this in Access the real question? I have a similar problem that I'm trying to solve. I can "view" groups in a Pivot Table, but I haven't been able to figure out how to run a query on it, since (I guess) it doesn't create any new object/table, just lets you look at it differently and use it to create a report.

This is what I have:

Class (up to 6 per group) Student (unique)
English Betty
English Freddie
English John
English Quinn
English Corin
Math Henry
Math Richard
Math Jeremy
Math Shawn
Math Andrew
Science David
Science Charles
Science George
Science Michael
Science Peter
Science Thomas
Journalism Zeke
Journalism Ian
Journalism Sasha
Journalism Victor
Journalism Daniel
Geography Jesse
Geography Nicholas
Geography Xavier
Geography Edward
Geography Kyle
Geography Patrick
History Walter
History Jason
History Stewart
History Tyler

I want to "merge" the groups into one row:

Class Stud 1 Stud 2 Stud 3 Stud 4 Stud 5 Stud 6
English Betty Freddie John Quinn Corin
Math Henry Richard Jeremy Shawn Andrew
Science David Charles George Michael Peter Thomas
Geography Jesse Nicholas Xavier Edward Kyle Patrick
History Walter Jason Stewart Tyler

(This is a gross over-simplification of a temp table that i want to use to create an Update Query).
I've also tried using a Crosstab Query, but I also have the same problem, too many lines to create headers.

I found a post on another forum (still looking for it) that showed how to use a query to "Group by" then Total: First then Total: Last, but it only worked for 3 columns.

I am clueless with SQL. I tried to loop through with VBA, but I don't know how to reference more than 1 record at time to deal with the grouped rows.

I also tried searching on how to work with duplicate records, but they explain how to delete them.

I'm banging my head against a wall on this one. Thank you so much!