|
View Full Version : transpose field saman 05-25-2008, 05:09 AM 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 05-25-2008, 07:48 AM 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 05-25-2008, 07:49 AM 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 05-25-2008, 12:31 PM 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 05-26-2008, 01:23 AM 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. (http://msaccesshintsandtips.ning.com/profiles/blog/show?id=948619%3ABlogPost%3A7026) saman 05-27-2008, 05:41 AM 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. |