Access Transposing data in Access?

K

khagan418

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:
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
 
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!
 
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?
 
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!
 
That does put them all under one column, but I need each number to have its own row as well. Any ideas?
 
Do you have a sample of what it looks like now and what you want it to look like?
 
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
 
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.
 
Back
Top