Access Transposing data in Access?

Joined
May 8, 2012
Messages
4
Reaction score
0
I have a table in which I need to make the column headers part of the data in the table as well as making the data, which is curretly listed horizontally, listed vertically instead. This is what the table looks like now:

RID Cheese Type 1 Cheese Type 2 Cheese Type 3 etc.....
200 1 5 7 etc.....
201 10 3 etc.....

But I need it to look like this:
RID Type Vol
200 Cheese Type 1 1
200 Cheese Type 2 5
200 Cheese Type 3 7
201 Cheese Type 2 10
201 Cheese Type 3 3

I would just do this manually, but there are 47 columns and 1500 rows of data so I'm hoping there's an easier way to do this. Any help would be appreciated!
 
Last edited:
Joined
Mar 20, 2012
Messages
764
Reaction score
4
I think the easiest way would be to Export the table to Excel, including headers. Then, in Excel, select all the data and Copy it. Next select the first cell on the sheet and right click, then choose Paste Special, and Check "Transpose" and hit OK. There are steps for this at the following link:
http://support.microsoft.com/kb/202176
 
Joined
Mar 20, 2012
Messages
764
Reaction score
4
The steps are all in the link, but I skipped the last part here. After doing the Copy and Paste Special > Transpose, you will need to re-import the data to Access.
Good Luck!
 
Joined
May 8, 2012
Messages
4
Reaction score
0
That works well to switch what I need it to, but I would still have to do a lot of editing afterward (the volumes end up in separate columns and I need them to be all under one column). Any ideas on fixing that easily?
 
Joined
Mar 20, 2012
Messages
764
Reaction score
4
You could Concatenate them by using the first blank column after the data. And say you have the Volumes in columns C, D, and E, go to Column F in Cell F1 and type =C1&D1&E1 and press enter. Then, fill the formula down the column, select the column and do a Copy and Paste Special > Values to get rid of the formula and delete columns C, D, and E. That should do it for you!
 
Joined
May 8, 2012
Messages
4
Reaction score
0
That does put them all under one column, but I need each number to have its own row as well. Any ideas?
 
Joined
Mar 20, 2012
Messages
764
Reaction score
4
Do you have a sample of what it looks like now and what you want it to look like?
 
Joined
May 8, 2012
Messages
4
Reaction score
0
Well right now it looks like this:
Type Vol Vol etc
Cheese Type 1 2 5 etc
Cheese Type 2 5 10 etc

And I want it to look like this:
Type Vol
Cheese Type 1 2
Cheese Type 1 5
Cheese Type 2 5
Cheese Type 2 10
 
Joined
Mar 20, 2012
Messages
764
Reaction score
4
That sounds like trying to reverse engineer a Pivot Table. I don't think you're going to find an easy answer on that one, at least, nothing comes to mind for me. I'll let you know if I find anything.
 

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