How to transpose Field in in MS ACCESS

Question

I have a table with 52 columns now I need to transpose columns which contain Date

Input:

2009 2010 2011 2012

30    50  60   80

Output:

Year         Values

2009         30

2010         50

2011         60

2012         80

they contain numeric data one way is to do it via union but number of columns are very high here. — Rahul Sharma, Apr 16 '15 at 13:26
Do you want to have a query which will return the names of the columns of your table in one row or do you want to pivot the table in some way? If you want to pivot, can you, please, provide a small example with data inside the table and the result you want to see. — Guneli, Apr 16 '15 at 13:29
Thanks! Does each column contain only one row? Cause if they more than one, it is not clear how to change the data? To add a new column for each row? — Guneli, Apr 16 '15 at 13:56
You can Pivot the data using `TRANSFORM`. http://stackoverflow.com/questions/16546305/pivoting-data-in-ms-access — Raj More, Apr 16 '15 at 14:49
Here's an example for multiple column Transform: http://stackoverflow.com/questions/16691853/transform-and-pivot-in-access-2013-sql — Raj More, Apr 16 '15 at 14:50
Raj I want just opposite of what you are suggesting through transform and Pivot — Rahul Sharma, Apr 16 '15 at 15:25

score 3 · Accepted Answer · answered Apr 16 '15 at 16:44

Suggestion 1: Copy the data out, stick it in excel, then copy and Paste-Special>>Transpose. Then copy it back into a new table in Access

Suggestion 2: VBA. Assuming your table has a single record with many many many fields something like the following would work:

Sub transpose()
    Dim rs As Recordset
    Dim rsField As Field
    Dim db As Database
    Dim StrSQL As String

    Set db = CurrentDb

    Set rs = db.OpenRecordset("SELECT * FROM Table1", dbOpenTable)

    'Assuming that there is only 1 record
    For Each rsField In rs.Fields
        StrSQL = "INSERT INTO Table2 VALUES ('" & rsField.Name & "'," & rsField.Value & ");"
    Next rsField

End Sub

Table1 has the single record with many many fields. Table2 has two fields (YEAR and VALUE)

Suggestion 3: 1 Really really horrible UNION query:

SELECT "2009" as year, [2009] as value FROM Table1 
UNION ALL
SELECT "2010" as year, [2010] as value FROM Table1 
UNION ALL
SELECT "2011" as year, [2011] as value FROM Table1 
UNION ALL
SELECT "2012" as year, [2012] as value FROM Table1

Conclusion: All of these are terrible, but so is your data. The excel solution is very manual and excel might destroy your data (removing leading 0's, converting date formats, other awful things). Solution 2 means you have to write and maintain VBA... and Solution 3 is just a pain to write and isn't easily repeatable if you have other similar tables, but with different data that you need to unpivot.

Good suggestions. Despite the fact that the typical "un-pivot" solution is a UNION query in this case Option 2 (VBA) might be preferred simply because with a large number of source columns Option 1 (Excel transpose) would be tedious and Option 3 (ugly UNION) would probably puke with a "query too complex" error. — Gord Thompson, Apr 17 '15 at 00:04

How to transpose Field in in MS ACCESS

1 Answers1