Группировка в запросах. Определение пяти самых дорогих товаров

Изменение уровня детализации в запросе

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

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

2.1. Переключитесь в режим Конструктора и выведите на экран свойства запроса. Для этого щелкните левой кнопкой мыши на свободном поле панелии в меню выберите Свойства. В диалоговом окне свойств запроса выводится свойство Заголовки столбцов, которое имеется только у перекрестных запросов.

Внимание! Если окно открылось не полностью, щёлкните мышкой ещё раз.

2.2. Введите краткие названия каждого месяца года в поле свойства Заголовки столбцов. Необходимо воспроизвести названия так, как они возвращаются функцией Format () и выглядят при стандартном отображении заголовков столбцов (первые три буквы названия месяца), в противном случае, данные для неправильно заданных месяцев не отображаются. Названия требуется заключить в кавычки и разделить символом точки с запятой. Пробелы не нужны. По завершении ввода названий всех 12 месяцев закройте окно свойств (рис. 15).

Рис. 15. Перекрёстный запрос.

2.3. Перейдите в режим просмотра SQL: Режим/Режим SQL. Инструкция SQL содержит операцию TRANSFORM, в которой определяются данные, содержащиеся в таблице. В операции PIVOT задаются заголовки столбцов. После ключевого слова IN указываются, если они есть, фиксированные названия столбцов (рис. 16).

Рис. 16. Окно режима SQL

2.4. Вернитесь в режим Конструктора и нажмите кнопку Выполнить для вывода результатов запроса. Теперь заголовки столбцов перекрестной таблицы соответствуют календарной последовательности.

2.5. Нажмите главную кнопку окна (левый верхний угол), выберите Сохранить как и сохраните запрос под именем "Ежемесячная выручка".

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

3.1. В режиме Конструктора добавьте в уже созданный перекрёстный запрос «Ежемесячная выручка» таблицу "Типы".

3.2. Замените столбцы «КодТовара» и «Марка» в запросе столбцами «КодТипа» и «Категория» таблицы «Типы». Для этого сначала замените имя таблицы, а затем имя поля. В строке Перекрестная таблица обоих столбцов выберите значение Заголовки строк.

3.3. Измените выражение в столбце «Выражение»: Format([Заказы]! [ДатаРазмещения];"""Квартал ""q"), которое определяет заголовки столбцов "Квартал 1", "Квартал 2", "Квартал 3", "Квартал 4". Несколько кавычек требуется для указания того, что слово Квартал и пробел рассматриваются как строка, a q — как формат.

3.4. Откройте бланк свойств и очистите содержимое свойства Заголовки столбцов, а затем закройте бланк. Если не удалить фиксированные заголовки (названия месяцев), то в перекрестной таблице данные не будут отражаться.

3.5. Сохраните запрос Сохранить как под именем «По кварталам». Нажмите кнопку Выполнить для вывода результатов запроса.

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

Основные функции обобщения (групповые операции):

Ø SUM - вычисляет сумму всех значений заданного поля (для числовых полей), отобранных запросом;

Ø AVG - вычисляет среднее значение среди записей, отобранных запросом (для числовых и денежных типов данных);

Ø MIN, MAX - выбирает, соответственно, минимальное и максимальное значение в записях поля, отобранных запросом;

Ø COUNT - вычисляет количество записей, имеющих ненулевое значение и отобранных запросом;

Ø FIRST, LAST - определяет, соответственно, первое и последнее значение в указанном поле записей.

4.1. Создайте запрос в режиме Конструктора по таблице «Товары». В нижнюю панель окна выберите поля «Марка» и «Цена».

4.2. На вкладке Конструктор в разделе Показать или скрыть выберите кнопку Итоги. В появившейся строке Групповые операции в поле «Цена»выберите Мах, а в строке Сортировка - По убыванию.

4.3. Щелкните правой кнопкой мыши по пустому месту в окне Конструктор и в контекстном меню выберите Свойства. В окне Свойства запроса в строке Вывод всех полей выберите Нет (рис.17). В строке Набор значений из раскрывающегося списка выберите 5.

Рис. 17. Окно свойств запроса.

4.4. Закройте окно свойств запроса. Выполните запрос и сохраните его под именем «Самые дорогие товары».