Transpose rows to columns in Access.

[SAP2]

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.

 

[John W. Vinson]

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.

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]

 

[Chris2]

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.

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.

 

[Gene]

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

 

[MGFoster]

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

You need a cross-tab query. Use the column that contains Bus, Plane,
Taxi as the "Value" column.