MS Access: A Simple Pivot Table
Posted by Derek Mang on 20th November 2007
One of my clients recently had the need for a management report that presented project information. These data included project milestone dates, along with some basic project data. The client's database maintained the main project data and milestone dates in separate tables, where the milestones were in a many-to-one relationship. The objective was to create a new table with one row per project containing a combination of project descriptive data and milestones.
The client is fairly accomplished with Access, developing forms, queries, etc.., but could not get this one to work as desired. When asked to help out, I was also at a loss. I knew what the result should be, but could not use Access functionality alone to make it happen.
I did a little research and wound up with sample SQL that would create a PIVOT table.  Unfortunately, I could not combine this with additional SQL to achieve the desired result.
Since time was not in great supply (there's a shock!!), it was VBA to the rescue (another shock!). The code is found below, and here's what happens:
1. A milestones "Pivot" recordset is created using the TRANSFORM
2. A new table is dynamically created. This table includes the project ID, name, and description fields, and then however many date columns returned in the "PIVOT" recordset.  (Note that the new table is deleted first if already within the set of table) definitions.
3. The new "tabledef" is appended to the database, and the db is refreshed.
4. The new table is opened, and new rows are added based on the projects returned in the original "Pivot" recordset.
I am sharing this mainly because of the elegance of the "TRANSFORM" SQL. This really made the solution pretty simple, and I now know something new for dealing with similar situations. Hopefully, others will be able to benefit from my experience.
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim rs1 As DAO.Recordset
   Dim tbldef As DAO.TableDef
   Dim t As Integer
   Dim rsDates As DAO.Recordset
  Â
   Const PROJECTINFO As String = "PROJECTINFO"
  Â
   strSQL = "TRANSFORM MAX(dbo_ProjectDates.dDateForecast) " _
           & "SELECT dbo_ProjectDates.dID " _
           & "FROM dbo_ProjectDates " _
           & "GROUP BY dbo_ProjectDates.dID " _
           & "PIVOT dbo_ProjectDates.dMilestone;"
  Â
   Set db = CurrentDb
   For t = 0 To db.TableDefs.Count - 1
       If db.TableDefs(t).Name = PROJECTINFO Then
           db.TableDefs.Delete PROJECTINFO                                                  Exit For
       End If
   Next t
   Set rs = db.OpenRecordset(strSQL)
   If rs.RecordCount > 0 Then
       With rs
           .MoveFirst
           Set tbldef = db.CreateTableDef(PROJECTINFO)
           With tbldef
               .Fields.Append .CreateField("cID", dbText, 20)
               .Fields.Append .CreateField("cName", dbText, 255)
               .Fields.Append .CreateField("cDesc", dbMemo)
               For t = 1 To rs.Fields.Count - 1
                   .Fields.Append .CreateField(rs.Fields(t).Name, dbText, 10)
               Next t
           End With
           db.TableDefs.Append tbldef
           db.TableDefs.Refresh
           Set rsDates = db.OpenRecordset(PROJECTINFO)
           While Not .EOF = True
               Set rs1 = db.OpenRecordset("SELECT pname, pdescription FROM dbo_Projectstatus " _
                   & "WHERE pID = '" + .Fields(0) + "'")
               If rs1.RecordCount > 0 Then
                   rs1.MoveFirst
                   rsDates.AddNew
                   rsDates.Fields(0) = .Fields(0)
                   rsDates.Fields(1) = rs1.Fields("pname")
                   rsDates.Fields(2) = rs1.Fields("pdescription")
                   For t = 3 To .Fields.Count - 1
                       rsDates.Fields(t) = .Fields(t - 2)
                   Next t
                   rsDates.Update
               End If
               rs1.Close
               .MoveNext
           Wend
       End With
   End If
Posted in Office (All) | 5 Comments »