Порядок дат в перекрестном запросе — используйте отдельную таблицу для сортировки

У меня есть перекрестный запрос с датами в формате «ммм-гггг» для полей в столбцах.

Я использовал приведенный ниже дизайн для создания запроса.

Конструктор кросс-таблиц

Проблема, с которой я сталкиваюсь, заключается в том, что даты неправильно сортируются с 17 декабря по 16 июля в порядке убывания. Это будет динамический запрос с ежемесячной сменой месяцев, поэтому я хочу использовать дополнительную таблицу данных для сортировки (в отличие от ввода списка названий месяцев в окне свойств).

Таблица месячных заказов

Как мне исправить мой запрос, чтобы заставить его сделать это, пожалуйста?

Спасибо за вашу помощь


sql ms-access crosstab ms-access-2016 ms-access-2013
person JDT    schedule 16.01.2017    source источник
comment
Один из вариантов — отформатировать даты, используя формат, который будет правильно сортироваться, например: yyyy-mm.   -  person Gustav    schedule 16.01.2017

Ответы (1)


arrow_upward
1
arrow_downward

К сожалению, независимо от того, как сортируются объединенные таблицы, кросс-таблица по умолчанию будет сортировать столбцы в алфавитном порядке, поэтому апрель, декабрь, ... начинают порядок. Чтобы изменить или даже отфильтровать порядок столбцов в перекрестных таблицах, вы должны указать значения в предложении PIVOT Col IN () инструкции SQL.

Поскольку вам нужен динамический запрос, рассмотрите возможность создания querydef в VBA для обновления SQL за кросс-таблицей, где вы динамически обновляете предложение PIVOT Col IN (). Конечно, передайте даты начала и окончания по мере необходимости или по параметрам:

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
person Parfait    schedule 16.01.2017
comment
Спасибо, Парфе, сейчас попробую. - person JDT; 19.01.2017