MS Access: A Simple Pivot Table
Posted by Derek Mang on November 20th, 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
January 16th, 2008 at 10:22 am
Hi,
All those   Â
make it difficult to interpret the VBA code.
I see a line that reads
If rs1.RecordCount > 0 Then
What's the meaning of it?
January 16th, 2008 at 10:46 am
I can see posting a reply changes that VBA line.
Here's how I actually see it in the code window of your post(remove the parentheses):
               If rs1.RecordCount (>) 0 Then
January 16th, 2008 at 10:48 am
One more try
               If rs1.RecordCount & g t ; 0 Then
January 21st, 2008 at 6:57 pm
Hi grovelli
The special character  is really a space.
The line in question is testing for any records returned in the recordset rs1. If > 0 then ...
dmang
January 31st, 2008 at 9:53 am
Thank you Derek, I think the blog owner should to something about that because you find those Â's everywhere, not just in code. Moreover you see in the code window
the greater > sign is substituted by & g t ;
All this contributes to difficulty in reading the blog.