I want to transpose rows into columns in ms-access

Question

I need to transpose rows into columns in MS Access database, VBA, SQL both the codes are welcome.

Table

    | Name | Ticker | ID | Innovation | Quality | Year |
    | XYZ  |  PQR   | 11 | 1          | 1       | 2009 | 
    | XYZ  |  PQR   | 11 | 0          | 1       | 2010 | 
    | XYZ  |  PQR   | 11 | 1          | 0       | 2011 | 
    | XYZ  |  PQR   | 11 | 1          | 1       | 2012 |

Desired Table

    | Year        | 2009 | 2010 | 2011 | 2012 |
    | Name        | XYZ  | XYZ  |  XYZ |  XYZ |
    | Ticker      | PQR  | PQR  | PQR  | PQR  |
    | ID          | 11   | 11   | 11   | 11   |
    | Innovation  | 1    | 0    | 1    | 1    |
    | Quality     | 1    | 1    | 0    | 1    |

As you can see from the desired table, I am trying to have the Year row as Column and list all the columns apart from Year as my rows. I have tried using Tranform and Pivot function in MS Access but it only Pivots one variable. Let me know your thoughts on it.

The below code failed in transposing all the variables.

    TRANSFORM Max([Quality])
    SELECT Ticker
    FROM Table
    Where Ticker = "XYZ"
    GROUP BY Ticker
    PIVOT Year;

Also, if possible I want to publish it as PDF document.

Thanks in advance, RVG

A clumsy workaround is to copy it to Excel, use copy/paste transpose and then either publish the pdf from Excel or copy the transposed table back into Access. Also, I don't think I understand the desired table - what would the next 4 rows, ie for Name="ABC", look like? Would that add new rows or new columns to the desired table? — Don George, Nov 14 '16 at 17:20
So I would just display one company which I will be selecting from list box for which I would updated the code as Ticker = [Forms]![ComSearch_frm]![Search_lbx].Value So my desired table would be to publish just one company information Year wise. — rvg6991, Nov 14 '16 at 17:35
In this case Excel is not a clumsy workaround but really the best option. — Andre, Nov 14 '16 at 18:27
Your desired result is no longer a viable database structure (either table or query) as you break the integrity of each column (i.e., inconsistent data types, column names). This is purely for presentation purposes. Consider exporting to an external end-use app like Excel for data wrangling, like `TRANSPOSE`. — Parfait, Nov 15 '16 at 03:59

score 1 · Answer 1 · edited May 23 '17 at 12:16

1

Access TRANSFORM is not really intuitive and easy to use and I do not think you can use it that way. Each result row is supposed to be an aggregate of your table. I know of no way to get the previous field names into a new column.

See a working example: TRANSFORM and PIVOT in Access 2013 SQL

What you really seem to want is just a new presentation to the existing data.

Excel might help.

I have never exported pdf from Access but from Excel is easy. Here is an example:

Sub ExportPdf(path As String, Optional openAfter As Boolean)

''calculate best range for print area
Dim lastCol As Integer
Dim firstRow As Integer
Dim lastRow As Integer

lastCol = pt.TableRange2.Columns(pt.TableRange2.Columns.Count).Column
firstRow = pt.TableRange2.Rows(1).Row
lastRow = ms.Cells(pt.TableRange2.Rows.Count * 3, 1).End(xlUp).Row

Worksheets(ContextSheet).PageSetup.PrintArea = Range(Cells(firstRow, 1), Cells(lastRow, lastCol)).Address

Worksheets(ContextSheet).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    path & "\Area " & getPivotTablePageFilters(getPivotTable()) & ".pdf", Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=openAfter
End Sub

edited May 23 '17 at 12:16

Community

1
1

answered Nov 14 '16 at 17:45

user6788933

285
2
10

The Crosstab wizard is there for a reason, and it's fairly intuitive. But as you say, it won't work for OP's requirements - one can PIVOT on **one** column (the "column header"), not all of them. – Andre Nov 14 '16 at 18:25
If you really want to show it in Access you can also commit into showing a fixed number of years like the last 5. Using Year(Date)....Year(Date)-5 for your labels and aggregates. – user6788933 Nov 14 '16 at 18:42
Yes the years are fixed from 2005 to 2015 last 10 years and even the parameters are fixed. – rvg6991 Nov 14 '16 at 20:28
@user6788933 Yes the 83 variables are fixed and so are the Years from 2005 to 2015. Any idea on how we can do it in Access. – rvg6991 Nov 14 '16 at 20:34
Is it possible for me to write vba code on command click function in Access Form that could transpose the table into desired one and export it to excel. This way also, I can help excel document showing all the company data vertically. – rvg6991 Nov 14 '16 at 20:48
1

If you're sending it to Excel anyway, do the transpose there – Don George Nov 14 '16 at 21:49
At Don George, how does your function work? As I know, usually, these things are run through a query, but I can't see how your solution will work. I think I need instructions to follow? Thanks! Also, At user6788933, can you please give me step-by-step instructions to follow to make your Sub work? This looks intersting, but I'm not sure how to implement it. Thanks! Ryan-- – ASH Dec 28 '16 at 03:11
pt is the pivot table in question and ms the sheet where it is contained. – user6788933 Dec 29 '16 at 08:34

score 1 · Answer 2 · answered Nov 14 '16 at 22:41

This is a vb script that takes the data from TableSource and transposes it into TableTranspose. The 2nd table has to be set up with a column named FName to take the field names, and columns for each year in the source table.

Function Transpose()
Set db = CurrentDb()
db.Execute ("delete * from TableTranspose")
Set RS = db.OpenRecordset("TableSource")
Set YrList = db.OpenRecordset("select distinct [Yr] from [TableSource] Group by [Yr]")

For Each F In RS.Fields
    FN = F.Name
    INS = "Insert Into TableTranspose (FName"
    SQL = "Select '" & FN & "'"
    YrList.MoveFirst
    Do While Not YrList.EOF
        YR = YrList.Fields("YR").Value
        INS = INS & ",[" & YR & "]"
        SQL = SQL & ",max(iif(YR=" & YR & ",[" & FN & "])) AS [" & YR & "]"
        YrList.MoveNext
        Loop
    SQL = SQL & " From TableSource"
    db.Execute (INS & ") " & SQL)

Next F
MsgBox ("Done")
End Function

This works by processing one field at a time to match the layout of the desired output, and looping through each year of TableSource to find the data to make up the row in TableTranspose. It shouldn't matter how many fields there are or what they are named.

It will create a row in the output for the Year, which will be redundant - you can delete it, or add logic to skip that field if necessary.

This seems to work fine with the 4 years of data in your sample, and should extend OK to more years. It's possible that you will hit a limit on SQL command length if there are too many years in the data, but I think not.

If you are filtering the records from TableSource, you can add the WHERE clause on the line just from the db.execute near the bottom.

One final comment - in the output table, each column can only have one datatype, so I assumed all the source data is text and that the fields in TableTranspose will all be text. You may need to insert some quotes (single quotes) in the buildup of the SQL command if there is numeric data in TableSource other than the year. — Don George, Nov 14 '16 at 23:36
Thank you very much. But I am receiving the error Too few parameters. Expected 1. if we try to debug it takes me to db.Execute (INS & ") " & SQL) Thanks in advance. — rvg6991, Nov 15 '16 at 15:51
Probably because I needed to change the TableSource field name to "Yr", since "Year" is a reserved word — Don George, Nov 15 '16 at 16:09

Don George · Answer 3 · 2016-11-14T18:36:37.957

0

Is the data always the same set of field names and years? If so, you might be able to use a UNION query, something like:

Select "Name" as [FName],  max(iif(year="2009",name))as [2009], max(iif(year="2010"
,name)) as [2010], max(iif(year="2011",name)) as [2011], max(iif(year="2012", name)) as [2012] from Table group by FName

Union all Select "Ticker",  max(iif(year="2009",ticker)), max(iif(year="2010"
,ticker)), max(iif(year="2011",ticker)), max(iif(year=-"2012",ticker)) from Table group by FName

Union all Select "ID",  max(iif(year="2009",id)), max(iif(year="2010"
,id)), max(iif(year="2011",is)), max(iif(year="2012",id)) from Table group by FName

Union all Select "Innovation",  max(iif(year="2009",innovation)), max(iif(year="2010"
,innovation)), max(iif(year="2011",innovation)), max(iif(year=-"2012",innovation)) from Table group by FName

 Union all Select "Quality",  max(iif(year="2009",quality)), max(iif(year="2010"
,quality)), max(iif(year="2011",quality)), max(iif(year=-"2012",quality)) from Table group by FName

edited Nov 14 '16 at 18:36

answered Nov 14 '16 at 18:18

Don George

1,328
1
11
18

Yes the column names are fixed and even the years from 2005 to 2015. I have total of 83 parameters, I guess MS Access have UNION operator limitations. Can I do it with UNION? – rvg6991 Nov 14 '16 at 20:29
For that many fields, I'd do it in vba - I'll think about it and post another response if I can. I don't know if UNION can handle that many sources, but the query will be ugly. What did you think about the Excel solution I noted in the comments and that Andre supported? – Don George Nov 14 '16 at 20:40
I am getting the error 'Data type Mismatch in criteria expression'. Please help me resolve the error. – rvg6991 Nov 14 '16 at 20:42
I assumed the fields were text so I put the criteria in "" - if they are nu7mberic, take out the quotes. – Don George Nov 14 '16 at 20:52

I want to transpose rows into columns in ms-access

3 Answers3

Linked