Queries: Fixed column name and crosstabs

   If someone wants to fix column names produced by a crosstab query, they can use Column Headings property of such a crosstab ( from its property sheet, in design view). You just put in it the name of the columns you want to get, like "JAN", "FEB", "MAC", "APR", ... and if you have typed MAC, you will NOT get a column MAR, for March, and you WILL get an column MAC, probably full of NULLs. Setting the Column Headings will force the XTab query to produce the columns names you give, even if a column is all empty, and it will produce ONLY those.

   Sometimes, it is not possible to use a Fixed Column Headings with the crosstab because the values in the source table, producing the column name in the crosstab, are continually changing. Unfortunately, some "fixed" names may be required for a form, a chart, a report... or for some other manipulation.

A possible solution, in that case, is to create a query between the form (chart, report) requiring fixed field name, and the crosstab. That query will just ALIAS (using the keyword As) the "variable" field name to a "constant" name schema:

SELECT XTab1 As F1, XTab2 As F2, ... FROM XTab;

where F1, F2, ... will be the constant names and XTab1 is a field name produced by the crosstab.

  If you assign that SQL string statement as a rowsource for a form, the form can then use the CONSTANT fields NAME:
F1, F2, ...
Plain and simple.

   The problem left is to build that SQL string. We cannot walk the fields collection of the crosstab, that may be too expensive to run the XTab query just for that purpose. Fortunately, the fields name that will be produced by the crosstab are, in fact, actual VALUES in a "pivot" field from a pivot table. Whatever PIVOT may bring to your mind, for now, it is enough to be just a "word" that appears in the text of the crosstab SQL statement; you should see both the table and the field after the keyword PIVOT, near the end of the query (in SQL view), like:

.... PIVOT TableName.FieldName;

Easy. If this is the case, you can easily get the string for the "cover"query,

ie: SELECT XTab1 As F1, XTab2 As F2, ...

from either one of the following program (the second function is only usable from Access 2000 ), which basically use some sort of concatenation like:

Debug.Print DAO_MakeSQLCoverQueryFor("FieldName", "TableName", "CrosstabName")

'   ******** Code Start ********
' This code was originally written by Michel Walsh. 
' It is not to be altered or distributed, 
' except as part of an application. 
' You are free to use it in any application,  
' provided the copyright notice is left unchanged.
'
' Code courtesy of
' Michel Walsh
'
Public Function DAO_MakeSQLCoverQueryFor(TableName As String, _
                            FieldName As String, _
                            XTableName As String) As String
'   XTableName = name of the crosstab query
'   TableName = name of the table supplying the pivot
'   FieldName = name of the field used as pivot
Dim W As String     ' the SQL string
Dim i As Long       ' keep the Field sequence
Dim db As DAO.Database
Dim rst As DAO.Recordset

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT DISTINCT " & FieldName _
                        & " FROM " & TableName & ";")
    rst.Move 0
    W = ""
    i = 1
    Do Until rst.EOF
        W = W & rst(FieldName) & " As F" & i & ", "
        i = i + 1
        rst.MoveNext
    Loop
    W = Left$(W, Len(W) - 2)
    ' trim last ", "
    W = "SELECT " & W & " FROM " & XTableName & ";"
    DAO_MakeSQLCoverQueryFor = W
End Function
'
'-------------------------------------------------
'
Public Function MakeSQLCoverQueryFor(TableName As String, _
                            FieldName As String, _
                            XTableName As String) As String
Dim rst As ADODB.Recordset
    Set rst = CurrentProject.Connection.Execute( _
                        "SELECT DISTINCT " & FieldName & " FROM " _
                        & TableName, , adCmdText)
    ' Firehose cursor is enough for this utilisation.
    rst.Move 0
    W = ""
    i = 1
    Do Until rst.EOF
        W = W & rst(FieldName) & " As F" & i & ", "
        i = i + 1
        rst.MoveNext
    Loop
    W = Left$(W, Len(W) - 2)
    ' trim last ", "
    W = "SELECT " & W & " FROM " & XTableName & ";"
    rst.Close
    Set rst = Nothing
    MakeSQLCoverQueryFor = W
End Function
'   ******** Code End ********

Now, if the SQL statement of your crosstab has a pivot clause different from the plain and simple....

PIVOT TableName.PivotName

you have to make the appropriate changes to the line of code:

"SELECT DISTINCT " & FieldName .

..As an example, if it is

...PIVOT TableName.Format(FieldName, "mmm")

then, in the program, use:
"SELECT DISTINCT " & Format(FieldName, "mmm") & .