Transpose rows to columns in Access.


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.
 
Ad

Advertisements

J

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]
 
C

Chris2

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.
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.
 
G

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
 
M

MGFoster

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
You need a cross-tab query. Use the column that contains Bus, Plane,
Taxi as the "Value" column.
 
Ad

Advertisements

Joined
Oct 17, 2020
Messages
1
Reaction score
0
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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top