[AccessD] SQL Server Pivot

John,

    The requirement presented by you would involve crosstab flip flop. Generic sample subroutine named P_MakeAndShowQryFlipFlop(), as given below, transforms table contents from cross tab to normal and then back to new version of crosstab. The results are displayed in programmatically built query "Q_FlipFlop"

    The subroutine needs two arguments as follows:
    (a) Name of table
    (b) Ordinal position of first field storing age codes.
          (Imp: It is to be ensured that all fields thereafter are only those with age codes)

    For example, if the data table is named T_Age and AgeCodes are stored in second field onwards, the subroutine will be called as per following statement:

    P_MakeAndShowQryFlipFlop "T_Age", 2

    With this call, the query "Q_FlipFlop" will get built programmatically, duly taking into account the up-to-date contents of data table. It takes care of any variations in number of columns meant for holding AgeCodes and final results are in four column format (AgeGroup, Count of Code 1, Count of Code 2, Count of Code 3) as sought by you.

    Note - Suggested line of action is based upon tests conducted on Access 2003 desktop (Access 2000 file format). This has to be kept in view while adapting to SQL server.

Best wishes,
A.D.Tejpal
------------

' Crosstab FlipFlop Query (named Q_FlipFlop)
' Sample subroutine for building the query
' and displaying its results
'======================================
Sub P_MakeAndShowQryFlipFlop( _
                    TableName As String, _
                    PosOfFirstAgeGrpField As Long)
    ' Transforms table contents from cross tab
    ' to normal and then back to new version
    ' of crosstab. The results are displayed in
    ' prgrammatically built query "Q_FlipFlop"
    On Error Resume Next
    Dim db As DAO.Database
    Dim tdf As TableDef

    Dim Qst As String, Cnt As Long
    Dim Fnm As String

    Set db = CurrentDb
    Set tdf = db.TableDefs(TableName)
    
    Qst = "TRANSFORM Count(Q1.GrpCode) AS " & _
            "CodeCount " & _
            "SELECT Q1.AgeGrp FROM ["
            
    For Cnt = PosOfFirstAgeGrpField - 1 To tdf.Fields.Count - 1
        Qst = Qst & IIf(Cnt > PosOfFirstAgeGrpField - 1, _
                " UNION ALL ", "")
        Fnm = tdf.Fields(Cnt).Name
        Qst = Qst & "SELECT " & Chr(39) & Fnm & _
                Chr(39) & " AS AgeGrp, " & Fnm & _
                " AS GrpCode FROM " & TableName
    Next
    
    Qst = Qst & "]. AS Q1 " & _
            "WHERE Not IsNull(Q1.GrpCode) " & _
            "GROUP BY Q1.AgeGrp " & _
            "PIVOT 'C_' & Q1.[GrpCode];"
    
    ' Delete target query if already existing
    ' and then re-create the same as per above SQL
    DoCmd.DeleteObject acQuery, "Q_FlipFlop"
    db.CreateQueryDef "Q_FlipFlop", Qst
    db.QueryDefs.Refresh
    
    ' Display results
    DoCmd.OpenQuery "Q_FlipFlop"
    
    Set tdf = Nothing
    Set db = Nothing
    On Error GoTo 0
End Sub
'======================================

  ----- Original Message ----- 
  From: jwcolby 
  To: 'Access Developers discussion and problem solving' ; 'Discussion concerning MS SQL Server' 
  Sent: Saturday, February 02, 2008 22:17
  Subject: [AccessD] SQL Server Pivot


  I have a bunch of columns that break out ages into bands.  For example:

  Presence_of_adults_age_65_74_specific
  Presence_of_adults_age_55_64_specific
  Presence_of_adults_age_45_54_specific
  etc

  Each column has codes 1,2,3

  I need counts of each column, for each value

  column Cnt1 Cnt2 Cnt3
  65_74 45 3 23
  55_64 103 48 211
  45_54 20 1 49

  etc

  I think the Pivot statement is going to do that for me but I can't seem to
  wrap my mind around the syntax.  Can anyone point me to a web page that
  clearly explains this or simply write a SQL statement that does this?

  Thanks

  John W. Colby
  Colby Consulting
  www.ColbyConsulting.com