Transpose rows to columns in Access.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have only 2 fields and want to change rows to column headings. My table
looks like this:

ColA ColB
N1 1
N2 2
N3 3
N4 4

The data changes each time access is open. Both columns are text. I want
it to look like this:

ColA N1 N2 N3 N4
ColB 1 2 3 4

Any possibility? Simple crosstab query states I do not have enough fields.

Thanks.
 
I have only 2 fields and want to change rows to column headings. My table
looks like this:

ColA ColB
N1 1
N2 2
N3 3
N4 4

The data changes each time access is open. Both columns are text. I want
it to look like this:

ColA N1 N2 N3 N4
ColB 1 2 3 4

Any possibility? Simple crosstab query states I do not have enough fields.
Click to expand...

Ummmmm... WHY?

The first table is properly normalized. The second IS NOT. It's simply a bad
design for a table.

What real-life task are you trying to accomplish?

John W. Vinson [MVP]
 
SAP2 said:
I have only 2 fields and want to change rows to column headings. My table
looks like this:

ColA ColB
N1 1
N2 2
N3 3
N4 4

The data changes each time access is open. Both columns are text. I want
it to look like this:

ColA N1 N2 N3 N4
ColB 1 2 3 4

Any possibility? Simple crosstab query states I do not have enough fields.

Thanks.
Click to expand...

SAP2,

Table:

CREATE TABLE YourTable_20070720_1
(ColA TEXT(2)
,ColB TEXT(1)
)


Sample Data:

As above.


Query:

TRANSFORM Max(YT1.ColB)
SELECT "ColB" AS ColA
FROM YourTable_20070720_1 AS YT1
GROUP BY "ColB"
PIVOT YT1.ColA


Results:

ColA N1 N2 N3 N4
ColB 1 2 3 4


Comments:

The above is meant as an example for you to work with. Although it does generate the
results you specified, I have the feeling that it isn't what you really wanted (I am just
unable to determine what it was that you really wanted).


Sincerely,

Chris O.
 
I have a similar problem, I think. I want to create a matrix so that I can change this:
Mary bus
Mary plane
Mary taxi
Jim plane
Tim bus
Tim plane

to

Bus Plane Taxi
Mary Mary Mary
Tim Tim

Thanks.

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com
 
Gene said:
I have a similar problem, I think. I want to create a matrix so that I can change this:
Mary bus
Mary plane
Mary taxi
Jim plane
Tim bus
Tim plane

to

Bus Plane Taxi
Mary Mary Mary
Tim Tim

Thanks.

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com
Click to expand...

You need a cross-tab query. Use the column that contains Bus, Plane,
Taxi as the "Value" column.
 
In case I have 3 rows and 3 columns (or more), I want to swap rows and columns in access, how can I do it?
Thanks
 
Back
Top