Как создать запрос перекрестной таблицы Access с итогами для столбцов И строк?

ProgramBox

Как создать запрос перекрестной таблицы Access с итогами для столбцов И строк?

Post author:admin
Запись опубликована:14 мая, 2023
Post category:Вопросы по программированию

#sql #ms-access #crosstab

#sql #ms-access #перекрестная таблица

Вопрос:

Я хочу, чтобы результат моего запроса выглядел следующим образом:

           Person1 Person2 Person3 Person4    Total 
Status1         2       4       7       3      16
Status2         0       1       0       3      4
Status3         0       0       0       0      0
Status4         0       1       3       0      4
Total           2       6       10      6      24

Я могу получить все, кроме этой нижней строки, с помощью:

 TRANSFORM Count(personName) 
SELECT status, Count(status) AS Total
FROM table1 
GROUP BY status
PIVOT personName

Я нашел кое-что об использовании ОБЪЕДИНЕНИЯ для привязки к последней строке, но, похоже, я не совсем понимаю это правильно. Похоже, это должно быть обычным действием.

1. Можете ли вы объяснить результат, который вы ищете?

2. Результат, который я ищу, — это таблица, которую я разместил вверху. Приведенный ниже запрос не включает нижнюю строку.

Ответ №1:

По сути, вам пришлось бы запустить запрос дважды — один раз, чтобы получить данные, а затем второй раз, чтобы предоставить агрегированные данные. Если вы настроены на это, сделайте первый запрос для возврата данных своим собственным объектом. Затем выполните другой запрос для агрегирования первого с другим объектом. Создайте последний третий объект запроса, чтобы объединить два, используя UNION , как вы упомянули.

Хотя я должен сказать, что я действительно не рекомендую это. Похоже, вы пытаетесь заставить SQL сгенерировать что-то, что действительно является презентационной информацией (т. Е. не относится к тому же набору данных).

1. Я согласен, что объединение — это хакерский способ сделать это. Запрос перекрестной таблицы обеспечивает мне 90% выполнения, и если мне нужно было выполнить его только один раз, Access генерирует на основе этого красивый отчет. Однако это нужно будет запускать много раз, и я не хочу, чтобы пользователи создавали свои собственные отчеты с помощью мастера отчетов. Таким образом, вместо генерации меток и текстовых полей во время выполнения было бы достаточно представления таблицы данных.

2. Если это только для целей отчетности, почему бы просто не использовать нижний колонтитул сводки? Вы можете установить значение текстового поля в нижнем колонтитуле равным, например, =Sum(person1).

3. Это только для целей отчетности. Количество текстовых полей будет варьироваться в зависимости от того, как создается отчет. Мысль о разметке полей с метками во время выполнения заставляет меня съежиться.

4. 1 к пункту о соображениях уровня представления — это не относится к инструкции SQL.

5. @HuckIt, есть практическое ограничение на то, что вы можете выполнять динамически; хотя вы можете размещать текстовые поля по мере необходимости для столбцов person в вашем запросе, после определенного момента вы не сможете отображать их аккуратно, особенно если отчет должен быть распечатан. Если вы можете установить ограничение, то вам не нужно создавать метки и поля во время выполнения, просто создайте максимальное количество, которое вы будете отображать в режиме конструктора, и скрывать или показывать их по мере необходимости во время выполнения.

Ответ №2:

На самом деле существует простое решение этой проблемы. После того, как вы разработали запрос перекрестной таблицы, перейдите в режим конструктора в запросе и выберите «Итоги» в разделе «Записи» на вкладке «Главная». Затем вы можете выбрать сумму или количество и т.д….

Вот ссылка, в которой приведены шаги: http://office.microsoft.com/en-us/access-help/display-column-totals-in-a-datasheet-HA001233062.aspx

1. Может кто-нибудь показать пример того, как это сделать с помощью VBA? Я хочу управлять этим в коде, потому что количество столбцов, возвращаемых запросом перекрестной таблицы, является переменным и не всегда может быть настроено в конструкторе.

2. Это то, что нужно использовать для фиксированного количества столбцов. Спасибо за публикацию!

Ответ №3:

Я тоже искал решение. Также не удалось найти ни одного, кроме написания запроса на основе перекрестной таблицы, а затем суммирования этого и добавления в конец в запросе объединения. Поскольку я пытаюсь выполнять все инструкции SQL изнутри формы (более управляемой для развертывания) Мне не нравится этот подход: написание или повторное заполнение Querydef / view из кода и т.д.

Если вы отображаете результаты во вложенной форме в своей форме, вы можете выполнить следующее:

под подчиненной формой и еще одна подчиненная форма, достаточно короткая, чтобы содержать только 1 запись.

Привяжите элементы управления в форме к функции следующим образом:

 control1 = fnADOSum(yourCrosstabfield1, yourCrosstabSQL) 

Public Function fnADOSum(fldName As String, strInputSQL As String) As Double
    On Error GoTo ERRHANDLER

    Dim RS1 As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim StrSQL As String
    Dim dblRunTot As Double


    Set RS1 = New ADODB.Recordset
    RS1.CursorLocation = adUseServer
    Set cnn = CurrentProject.Connection

    dblRunTot = 0

    With RS1
    .Open strInputSQL, cnn, adOpenForwardOnly, adLockReadOnly
        If Not .EOF And Not .BOF Then
            .MoveFirst
            Do Until .EOF
            dblRunTot = dblRunTot   Nz(.Fields(fldName).Value, 0)
            .MoveNext
            Loop
        End If
    .Close
    End With

    fnADOSum = dblRunTot

    'CLEAN UP:
    cnn.Close
    Set RS1 = Nothing
    Set cnn = Nothing


    EXITHANDLER:
    Exit Function

    ERRHANDLER:
    '' your own error handling proc
    '' LogError err.Number, err.Description


End Function

Ответ №4:

Лидия писала: «На самом деле существует простое решение этой проблемы. После того, как вы разработали запрос перекрестной таблицы, перейдите в режим конструктора в запросе и выберите «Итоги» в разделе «Записи» на вкладке «Главная». Затем вы можете выбрать сумму или количество и т.д. …»

Переход в режим конструктора у меня не сработал:

Я выполнил запрос.
Затем перешел на вкладку Главная
Выбранные итоги в разделе Записей
Внизу результатов запроса перекрестной таблицы появилась метка «Итоги», но фактических итогов пока нет.
Щелкнул по пустой ячейке справа от метки итогов.
Появилась стрелка, и я выбрал «Сумма».

[Я использую Access 2013]

Ответ №5:

Найдено после долгих проб и ошибок…

Чтобы включить строки итогов при вводе формы или вложенной формы, вы можете добавить следующий VBA в код формы:

 Private Sub YourFormName_Enter()
    If Application.CommandBars.GetPressedMso("RecordsTotals") = False Then
        Application.CommandBars.ExecuteMso "RecordsTotals"
    End If
End Sub