transpose field

[saman]

hi
I want transpose 4 fields in table1 into a field(fld1) in table2 as Excel.

Table1
A B C D
10 2 2 8


Table2
fld1 fld2 fld3
A 10 a
B 2 b
C 2 c
D 8 d

How can i transpose table1(horzintal) to table2 (vertical)
Thank you

 

[jal]

hi
I want transpose 4 fields in table1 into a field(fld1) in table2 as Excel.

Table1
A B C D
10 2 2 8


Table2
fld1 fld2 fld3
A 10 a
B 2 b
C 2 c
D 8 d

How can i transpose table1(horzintal) to table2 (vertical)
Thank you

You say you want to do this in Excel, so this sounds like an Excel question to me, not an access question. Here is what I have in my Excel notes (never actually tried it though).

Do a Copy > Paste Special (checkmark the Transpose box) to convert (transpose) this horizontal table to the standard vertical layout. You will have to redo the formula fields (if any), however.

 

[jal]

You could probably get similar results using pivot tables, although it takes a few minutes to learn how to use them, if you are new to them.

 

[Pat Hartman]

If the transpose in Exel doesn't do it for you, you can do it with queries in Access. If this is really only four columns, I would create one query to select column A and append it as you want to the new table. Then modify the query three times to create the other three rows. If you have many columns, you'll probably want to write a code routine that loops through the columns and modifies and runs the append query for you.

 

[Uncle Gizmo]

I haven't got to time to look myself at the moment but I believe you may be able to adapt this code here to do what you want.

 

[saman]

If the transpose in Exel doesn't do it for you, you can do it with queries in Access. If this is really only four columns, I would create one query to select column A and append it as you want to the new table. Then modify the query three times to create the other three rows. If you have many columns, you'll probably want to write a code routine that loops through the columns and modifies and runs the append query for you.

hi Pat Hartman
how can it create one query?please give me sample.