Date Order in Cross Tab Query - use Separate Table to Sort

Question

I have a cross tab query with 'mmm-yyyy' formatted dates for Fields in the Columns.

I have used the below Design to create the query.

Cross Tab Design View

The problem I am having is the dates are not sorting correctly from Dec-17 down to Jul-16 in descending order. This is going to be a dynamic query with months changing every month so I want to use an additional table of data to do the sorting (as opposed to entering a list of month names in the Properties window).

How would I fix my query to get it to do this please?

Thanks for your help

Answer 1

Unfortunately, no matter how joined tables are sorted, crosstab will sort columns by default in alphabetical order, hence Apr, Dec, ... begins the order. To change or even filter column order in crosstabs, you would specify values in PIVOT Col IN () clause of SQL statement.

Since you need a dynamic query consider creating a querydef in VBA to update the SQL behind the crosstab where you dynamically update the PIVOT Col IN () clause. Of course, pass begin and end dates as needed or by parameters:

Public Sub BuildCrossTab() 
  Dim db As Database 
  Dim qdef As QueryDef 
  Dim strSQL As String, dates As String
  Dim i As Integer, monthsDiff As Integer

  Set db = CurrentDb

  ' DELETE PREVIOUS SAVED QUERY
  For Each qdef in db.QueryDefs
     If qdef.Name = "AccuralsCrosstabQ" Then
        db.Execute "DROP Table " & qdef.Name, dbFailOnError
     End If
  Next qdef

  ' LOOP THROUGH ALL MONTHS BACKWARDS 
  dates = "("
  monthsDiff = DateDiff("m", #7/1/2016#, #12/1/2016#)
  For i = monthsDiff To 0 Step -1
     dates = dates & " '" & Format(DateAdd("m", i, #7/1/2016#), "mmm-yyyy") & "',"
  Next i
  dates = dates & ")"
  dates = Replace(dates, ",)", ")")

  ' PREPARE SQL STRING 
  strSQL = "TRANSFORM SUM(a.[Amount $]) AS SumAmount" _ 
       & " SELECT a.Company, a.[Accrual ID], SUM(a.[Amount $]) As [Total Amount $]" _ 
       & " FROM [Accruals Raw Data] a " _ 
       & " GROUP BY a.Company, a.[Accrual ID]" _
       & " PIVOT Format(a.[Posted Date], ""mmm-yyyy"")" _
       & " IN " & dates

  ' CREATE QUERY
  Set qdef = db.CreateQueryDef("AccuralsCrosstabQ", strSQL)    

  Set qdef = Nothing 
  Set db = Nothing 
End Sub