Использование фиксированных заголовков столбцов в перекрестных запросах


Для того чтобы в построенном запросе вывести столбцы в порядке календарной очередности месяцев, нужно задать фиксированные заголовки столбцов:

  1. Переключитесь в режим Конструктора и выведите на экран свойства запроса. Для этого нажмите кнопку Свойства (Properties) на панели инструментов и щелкните левой кнопкой мыши на свободном поле панели, в которой отображаются таблицы запроса. В диалоговом окне свойств запроса выводится свойство Заголовки столбцов (Column Headings), которое имеется только у перекрестных запросов.
  2. Введите краткие названия каждого месяца года в поле свойства Заголовки столбцов (рис. 8.16). Необходимо воспроизвести названия так, как они возвращаются функцией Format () и выглядят при стандартном отображении заголовков столбцов , в противном случае, данные для неправильно заданных месяцев не отображаются. Названия требуется заключить в кавычки и разделить символом точки с запятой. Пробелы не нужны. По завершении ввода названий всех 12 месяцев закройте окно свойств.
  3. Перейдите в режим просмотра SQL. Обратите внимание на использование выражений в вычисляемых полях и критерии запроса, а также ключевых слов TRANSFORM И PIVOT. Инструкция SQL содержит операцию TRANSFORM, в которой определяются данные, содержащиеся в таблице. В операции PIVOT задаются заголовки столбцов.

После ключевого слова IN указываются, если они есть, фиксированные названия столбцов.
Замечание
В ANSI SQL слова TRANSFORM и PIVOT не являются зарезервированными словами, а ключевое слово IN интерпретируется по-разному в Access и ANSI SQL. Обратите также внимание на формат даты в инструкции SQL: сначала пишется номер месяца, а потом число месяца, разделителем является косая черта, и все выражение содержится между значками #. Именно в таком формате должны быть всегда написаны даты в предложении SQL, хотя, как вы видели, в бланке запроса можно писать даты в привычном нам европейском формате. Нужное преобразование Конструктор выполняет автоматически.

  1. Нажмите кнопку Запуск (Run) для вывода результатов запроса. Теперь заголовки столбцов перекрестной таблицы соответствуют календарной последовательности (рис. 8.18).
  2. Выберите команду Файл, Сохранить как (File, Save As) и сохраните запрос под именем "Ежемесячная выручка от продаж в 1997 году".

 
Изменение уровня детализации в запросе
Для создания удобного для анализа представления или наглядного графика можно сократить число строк и столбцов в результатах построенного запроса. С этой целью создадим перекрестный запрос, который выводит ежеквартальную выручку от продаж по типам товаров. Чтобы создать такой запрос:

  1. Добавьте в уже созданный запрос таблицу "Типы" (Categories).
  2. Замените столбцы "КодТовара" и "Марка" в запросе столбцами "КодТипа" и "Категория" таблицы "Типы". Для этого сначала удалите лишние столбцы, а затем перенесите нужные столбцы в бланк заказа. В ячейках Перекрестная таблица (Crosstab) обоих столбцов выберите значение Заголовки строк (Row Heading) (рис. 8.19).
  3. Измените выражение в столбце Выражение! (Expl) Format([ДатаРазмещения];"""Квартал ""q") , которое определяет заголовки столбцов "Квартал 1", "Квартал 2", "Квартал 3", "Квартал 4". Несколько кавычек требуется для указания того, что слово Квартал и пробел рассматриваются как строка, a q — как формат.
  4. Откройте бланк свойств и очистите содержимое свойства Заголовки столбцов (Column Headings), а затем закройте бланк. Если не удалить фиксированные заголовки, то в перекрестной таблице не будет данных. Чтобы ускорить работу запроса, можно задать в поле свойства Заголовки столбцов четыре фиксированных названия: "Квартал 1", "Квартал 2", "Квартал 3", "Квартал 4".
  5. Нажмите кнопку Запуск (Run) для вывода результатов запроса.

 
Статистические функции для выполнения расчетов
Статистические функции используются в запросах главным образом для вычисления всевозможных итоговых значений, например для числового поля таблицы можно вычислить среднее значение или сумму значений для всех или отобранных записей, можно посчитать количество записей, возвращаемых запросом.
Статистические функции, которые можно использовать в запросах Access, входят в состав Access SQL. Описание этих функций, а также типов полей, к которым они могут быть применены, приведено в.

Функция

Типы полей

Описание

Avg()

Все типы полей, исключая Текстовый (Text), Поле Memo (Memo) и Поле объекта OLE (OLE Object).

Вычисляет арифметическое среднее набора чисел, содержащихся в указанном поле запроса

Count ( )

Все типы полей

Вычисляет количество непустых записей, возвращаемых запросом

First ( )

Все типы полей

Возвращает значение поля из первой записи результирующего набора

Last ()

Все типы полей

Возвращает значение поля из последней записи результирующего набора

Max()

Все типы полей, исключая Текстовый, Поле Memo и Поле объекта OLE

Возвращает максимальное значение из набора, содержащегося в указанном поле

Min()

Все типы полей, исключая Текстовый, Поле Memo и Поле объекта OLE

Возвращает минимальное значение из набора, содержащегося в указанном поле

StDev() StDevP()

Все типы полей, исключая Текстовый, Поле Memo и Поле объекта OLE

Возвращают смещенное и несмещенное значение среднеквадратичного отклонения, вычисляемого по набору значений, содержащихся в указанном поле

Sum( )

Все типы полей, исключая Текстовый, Поле Memo и Поле объекта OLE

Возвращает сумму набора значений, содержащихся в заданном поле

Var() VarP ( )

Все типы полей, исключая Текстовый, Поле Memo и Поле объекта OLE

Возвращают значение смещенной и не смещенной дисперсии, вычисляемой по набору значений, содержащихся в указанном поле

Перечисленных функций обычно достаточно для решения большинства задач. Тем не менее, при необходимости пользователь может самостоятельно написать функцию на языке VBA, которая будет выполнять сложные статистические, научные или инженерные расчеты над данными, и использовать эту функцию в запросах.
Замечание
Все статистические функции Access SQL поддерживаются стандартом ANSI SQL, но не все из них поддерживаются другими СУБД. Например, не все серверные базы данных поддерживают функции StDev(), StDevP(), Var () и VarP (). Поэтому, если вы планируете в дальнейшем перенести свою базу данных на сервер, прежде чем использовать эти функции, нужно убедиться, поддерживаются ли они сервером. Иначе потом придется изменять все объекты, в которых встречаются эти функции.

Использование статистических функций для расчета итоговых значений тесно связано с применением групповых операций в запросе. Групповые операции позволяют задать группы, для которых выполняются вычисления. Ниже мы приведем примеры таких расчетов как на всем множестве записей, так и на подмножествах, выбранных по условиям запроса.