Fix aggregate column location in crosstab query

[GSan]

I have created a crosstab query that shows file types as the column headings and names as the rows. The aggregate function sums the files per type for each name. I know how to fix the column order for the file types using the PIVOT statement shown below:
..."PIVOT TotalFileCountByNameAndType.FileType In (""Excel"",""Word"",""PowerPoint"",""PDF"",""Email"",""Other"");"

However, on my screen the user can hide or show the column using checkboxes. When a column is hidden and then shown, the column is displayed at the end of the list. I am careful to recreate the PIVOT statement above to keep the order of the columns without the one that is to be hidden.

After hidding and then showing all of the columns, I discovered that it is the aggregate, 'sum' column that is the problem. It is not in the list so Access wants to make it the first column. Once it is the first column, the other columns remain in the correct order.

So is there any way to list the aggregate function column in the list with my file types so that it can be at the end of the list?

Thanks!

 

[TexanInParis]

GSan-

You would have to save the query as a QueryDef, then define and set the ColumnOrder property of that totals field to place it at the end of the display. In this case, it looks like you have one Row Heading column followed by the six application names. You would need to set ColumnOrder to 8 to get it to display at the end. Here's some sample code:

Public Sub SetColumnOrder()
    
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    
    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("<name of your query>")
    
    ' Call the procedure to set the ColumnOrder property.
    SetFieldProperty qdf.Fields("<name of your totals field>"), "ColumnOrder", dbLong, 8
    
    Set qdf = Nothing
    Set dbs = Nothing

End Sub

Private Sub SetFieldProperty(ByRef fld As DAO.Field, _
                             ByVal strPropertyName As String, _
                             ByVal intPropertyType As Integer, _
                             ByVal varPropertyValue As Variant)
    ' Set field property without producing nonrecoverable run-time error.

    Const conErrPropertyNotFound = 3270
    Dim prp As Property
    
    ' Turn off error handling.
    On Error Resume Next
    
    fld.Properties(strPropertyName) = varPropertyValue
    
    ' Check for errors in setting the property.
    If Err <> 0 Then
        If Err <> conErrPropertyNotFound Then
            On Error GoTo 0
            MsgBox "Couldn't set property '" & strPropertyName & _
                   "' on field '" & fld.Name & "'", vbCritical
        Else
            On Error GoTo 0
            Set prp = fld.CreateProperty(strPropertyName, intPropertyType, _
                      varPropertyValue)
            fld.Properties.Append prp
        End If
    End If
    
    Set prp = Nothing
    
End Sub

 

[GSan]

Thank you for replying...
I will try this

 

[GSan]

I have tried creating a property to define the column order but it is not working...the column is still second. It sets the column value to 8. But I don't understand how it will change the display. in the query, I use ...PIVOT In (list of all of the column headings) to determine the order.


Below is my code...

Private Sub SetQueryDefFieldProperty(columnValue As Integer)
' Set field property without producing nonrecoverable run-time error.

Const conErrPropertyNotFound = 3270
Dim prpNew As Property
Dim errLoop As Error
Dim LegalServiceDB As DAO.Database
Dim queryDefinition As queryDef
Set LegalServiceDB = CurrentDb

'Get the current version of the query that is used by the crosstab query
Set queryDefinition = LegalServiceDB.QueryDefs("TotalFileCountByNameAndType_Crosstab")

' Attempt to set the specified property.
On Error GoTo Err_Property
queryDefinition.Fields("File Totals").Properties("ColumnOrder").Value = columnValue
On Error GoTo 0
Exit Sub
Err_Property:
' Error 3270 means that the property was not found.
If DBEngine.Errors(0).Number = conErrPropertyNotFound Then
' Create property, set its value, and append it to the
' Properties collection.
Set prpNew = queryDefinition.Fields("File Totals").CreateProperty("ColumnOrder", _
dbInteger, columnValue)
queryDefinition.Fields("File Totals").Properties.Append prpNew
Resume Next
Else
' If different error has occurred, display message.
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End
End If
End Sub

 

[TexanInParis]

GSan-

Setting that property should define the order that the columns appear in the query Datasheet. It's the same as opening the query in Datasheet view, moving the column to the end, then saving. Next time you open, the column order should hold. I tested in queries that had a Column Headings property and that did not.

At what point in the process are you calling this code? Where are you seeing the column not in the right order?

 

[GSan]

I have to rebuild the query every time the user selects an option on the screen. But initially, I use the Form_Load method to display all of the data. After the initial load the following methods are called when each control is clicked.

So I build the query (Sub CreateTotalSQLString)
Then reset the crosstab query in the datasheet (Sub UpdateFileTotalsDatasheet). I set the column property in this method. After I reset the SourceObject, I call SetQueryDefFieldProperty with a parameter that sets the column value for the field.

The column value will change if the user unchecks a file type that he does not want to see but I still want the 'Totals' field to be at the end. The code is below:

Set LegalServiceDB = CurrentDb

'Get the current version of the query that is used by the crosstab query
Set queryDefinition = LegalServiceDB.QueryDefs("TotalFileCountByNameAndType_Crosstab")

'Set the new SQL string
queryDefinition.SQL = sqlQuery

Me.TotalFileCount_SubForm.SourceObject = ""
Me.TotalFileCount_SubForm.SourceObject = "Query.TotalFileCountByNameAndType_Crosstab"

' Call the procedure to set the ColumnOrder property.
Call SetQueryDefFieldProperty(totalFields)

queryDefinition.Close
LegalServiceDB.Close

Set queryDefinition = Nothing
Set LegalServiceDB = Nothing

 

[TexanInParis]

GSan-

Try setting the ColumnOrder property *before* you load the subform SourceObject. When you load the SourceObject first, it's picking up the default column sequence, and that won't change if you change the query after the load.

 

[GSan]

I have tried putting the SetQueryDefFieldProperty(totalFields) method before setting the SourceObject property but still no luck.
I have also tried calling the SetQueryDefFieldProperty(totalFields) before calling the UpdateFileTotalsDatasheet method that updates the sql query but no change in the order of the columns.

 

[TexanInParis]

That's very curious. If you open the query separately, does the total column show up in the right place?

 

[GSan]

No. It is in the 2nd of the 8 columns.
Stepping thru the code, the column order value is 8. Yet it has no affect on the order of the columns.

This is me being dense...but I don't understand how I can define a property for a field in a table and the query understands it has changing the order of the columns. All other properties are predefined internally...aren't they?

I think I am missing some part of the definition that tells Access it is to rearrange the columns.

 

[TexanInParis]

Gsan-

The ColumnOrder property is what determines the display order. What is the ColumnOrder property of the other fields?

 

[GSan]

The column order of the other fields is set in the query when I use PIVOT

..."PIVOT TotalFileCountByNameAndType.FileType In (""Excel"",""Word"",""PowerPoint"",""PDF"",""Email"",""Other"");"

Do I have to set the column order for all of the columns like I did with the File Totals column?

 

[TexanInParis]

Yes, that's correct. But your assignment of the ColumnOrder should push the total field to the end. As I look at your code again, I'm wondering what this is doing:

' Call the procedure to set the ColumnOrder property.
Call SetQueryDefFieldProperty(totalFields)

What is totalFields? The code expects an integer value, but I don't see where you set that. If you don't have that variable defined and set and haven't declared Option Explicit, VBA passes a variant to the Sub that probably assigns a value of 0 to the ColumnOrder or perhaps generates an error. What is the full SQL of the query? I assume you have an As [File Totals] assigned to the total.

 

[GSan]

totalFields is an integer variable that I declare in functions that call the SetQueryDefFieldProperty method. The column order for [File Totals] is being set correctly because I have a message box display the value after it is set and it is set to 8 during the Form_Load method.

Yes. [File Totals] is assigned to the total. Below is the query during the Form_Load method. I put in bold the 'File Totals' field so you can find it easier. Then the call to the query update method.


'Set the file count crosstab query to include all of the data in the database
sqlTotalCountString = "TRANSFORM Nz(Sum(TotalFileCountByNameAndType.[File Type Totals]),0) AS [SumOfFile Type Totals] " & _
"SELECT TotalFileCountByNameAndType.Name, Nz(Sum(TotalFileCountByNameAndType.[File Type Totals]),0) AS [File Totals] " & _
"FROM TotalFileCountByNameAndType " & _
"GROUP BY TotalFileCountByNameAndType.Name, TotalFileCountByNameAndType.LastName, TotalFileCountByNameAndType.FirstName " & _
"ORDER BY TotalFileCountByNameAndType.LastName, TotalFileCountByNameAndType.FirstName " & _
"PIVOT TotalFileCountByNameAndType.FileType In (""Excel"",""Word"",""PowerPoint"",""PDF"",""Email"",""Other"");"

'This method updates the query definition and sets the field property.
Call UpdateFileTotalsDatasheet(sqlTotalCountString, totalFields)

'this message box displays '8' when I run the form
MsgBox "Col order " & CurrentDb.QueryDefs("TotalFileCountByNameAndType_Crosstab").Fields("File Totals").Properties("ColumnOrder")

This is the 'UpdateFileTotalsDatasheet':

Private Sub UpdateFileTotalsDatasheet(sqlQuery As String, totalFields As Integer)
'Using DAO objects
Dim LegalServiceDB As DAO.Database
Dim queryDefinition As queryDef
Dim sqlString As String

On Error GoTo ErrorHandler

Set LegalServiceDB = CurrentDb

'Get the current version of the query that is used by the crosstab query
Set queryDefinition = LegalServiceDB.QueryDefs("TotalFileCountByNameAndType_Crosstab")

' Call the procedure to set the ColumnOrder property.
Call SetFieldProperty(queryDefinition.Fields("File Totals"), "ColumnOrder", dbLong, totalFields)


'Set the new SQL string
queryDefinition.SQL = sqlQuery

Me.TotalFileCount_SubForm.SourceObject = ""
Me.TotalFileCount_SubForm.SourceObject = "Query.TotalFileCountByNameAndType_Crosstab"

queryDefinition.Close
LegalServiceDB.Close

Set queryDefinition = Nothing
Set LegalServiceDB = Nothing

Exit Sub

ErrorHandler:
'Display error message
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

End Sub

 

[TexanInParis]

GSan-

A couple of comments on your code:

Set LegalServiceDB = CurrentDb

'Get the current version of the query that is used by the crosstab query
Set queryDefinition = LegalServiceDB.QueryDefs("TotalFileCountByNameAndT ype_Crosstab")  ' [COLOR="Red"]<=[/COLOR] Is this just a copy error? I see a space in the name.

' Call the procedure to set the ColumnOrder property.
Call SetFieldProperty(queryDefinition.Fields("File Totals"), "ColumnOrder", dbLong, totalFields)


'Set the new SQL string
queryDefinition.SQL = sqlQuery  ' [COLOR="Red"]<=[/COLOR] You're changing the SQL AFTER you set the column order.  Try setting the SQL first, then do the column order.

Me.TotalFileCount_SubForm.SourceObject = ""
Me.TotalFileCount_SubForm.SourceObject = "Query.TotalFileCountByNameAndType_Crosstab"

queryDefinition.Close
LegalServiceDB.Close

Set queryDefinition = Nothing
Set LegalServiceDB = Nothing

Also, you haven't told me what you see if you open the query by itself outside the form after you've run this code.

 

[GSan]

When I run the query by itself, the 'File Totals' column is the second column in the display which is the same order if I run it using the Form.

The typo defining the name of the crosstab query is a copy error.
There are no errors when I run the code. It just displays the column in the wrong position.

I will move the setProperty method after the SQL statement but before setting the SourceObject and see if that fixes the problem.

Thanks for all of your attention!

 

[GSan]

I have moved the SetProperty method at different parts of the code and it does not change anything.

'Set the new SQL string
queryDefinition.SQL = sqlQuery

' Call the procedure to set the ColumnOrder property.
Call SetFieldProperty(queryDefinition.Fields("File Totals"), "ColumnOrder", dbLong, totalFields)

Me.TotalFileCount_SubForm.SourceObject = ""

'I have also put the method after resetting the source object and there is no change.

Me.TotalFileCount_SubForm.SourceObject = "Query.TotalFileCountByNameAndType_Crosstab"

I have also tried closing the form and opening the query to see if that result is the same and it is. The 'Total Files' column is always in the second position.

In case this matters...I am using Access 2007.

 

[TexanInParis]

GSan-

I'm using 2007 also. I did some more dinking around and I found that you must set the ColumnOrder property for ALL the columns. This apparently what Access does internally if you move a field position in Datasheet view in the UI. If you just set ColumnOrder for that totals field, it still leaves all the pivot fields with no ColumnOrder.

So, I wrote a generic set column sub like this:

Sub SetColumnOrder(strQueryName, strFieldName, intOrder)
Const conErrPropertyNotFound = 3270
Dim prpNew As Property
Dim db As DAO.Database, qd As DAO.QueryDef

    ' Point to this database
    Set db = CurrentDb
    ' Get the query
    Set qd = db.QueryDefs(strQueryName)
    ' Set an error trap
    On Error GoTo ErrProperty
    ' Attempt to set the ColumnOrder directly
    qd.Fields(strFieldName).Properties("ColumnOrder") = intOrder
ExitSet:
    ' Clear the trap
    On Error GoTo 0
    ' Clear the objects
    Set qd = Nothing
    Set db = Nothing
    ' Done
    Exit Sub
    
ErrProperty:
    ' See if property not found
    If Err = conErrPropertyNotFound Then
        ' Create property, set its value, and append it to the
        ' Properties collection.
        Set prpNew = qd.Fields(strFieldName).CreateProperty("ColumnOrder", _
          dbInteger, intOrder)
        qd.Fields(strFieldName).Properties.Append prpNew
    Else
        MsgBox "Error: " & Err & ", " & Error
    End If
    Resume ExitSet
End Sub

Next, I created a crosstab query in Northwind 2007 that looks like:

In Datasheet view, the results are as expected, with the "SumOfQuantity1" in the wrong place:

Next, I ran this procedure that loops through all the fields and sets the Column Order sequentially, but skips the totals column and puts it at the end:

Sub FixCrosstabOrder()
Dim db As DAO.Database, qd As DAO.QueryDef, fld As DAO.Field
Dim intI As Integer

    ' Point to this database
    Set db = CurrentDb
    ' Get the querydef
    Set qd = db.QueryDefs("Crosstab with column totals")
    ' Initialize the column order
    intI = 1
    ' Loop through all the fields
    For Each fld In qd.Fields
        ' If it's not the final total field
        If fld.Name <> "SumOfQuantity1" Then
            ' Set the column order
            SetColumnOrder qd.Name, fld.Name, intI
            ' Increment
            intI = intI + 1
        End If
    Next fld
    ' Now push the final total to the end
    SetColumnOrder qd.Name, "SumOfQuantity1", intI
    ' Clear objects
    Set fld = Nothing
    Set qd = Nothing
    Set db = Nothing
    
End Sub

And here's the query in Datasheet view after fixing ALL the columns:

 

[GSan]

Where do you call the FixCrosstabOrder?

I have put it after setting the SQL statement but before setting the SourceObject.
I have put it after setting the SourceObject.

But STILL the wrong order change!

I have stepped thru the code and all of the 8 columns are being set with the correct order value.

 

[TexanInParis]

I just called it from the Immediate Window, then opened the query in Datasheet view. I would set the new SQL, close the QueryDef, then call the FixCrosstabOrder, then set the SourceObject. After you run FixCrosstabOrder, what do you see when you open the query? It worked for me.