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
43 Answers
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 SubThis 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 FunctionThis 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.
3Is 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
