>

Групповые операции в запросах Access. Запрос с группировкой Запросы с вычислениями

Запрос позволяет для данных, относящихся к одинаковым группам, подводить итоги: подсчитывать их количество, сумму, среднее, максимальное и минимальное значение.

Задание : создать запрос, который будет подсчитывать общее количество наименований и суммарный объем товаров, поступивших по каждой накладной.

Рассмотрим данные таблицы "Поступление товаров" (рис.13). В таблице одинаковые значения столбца Номер накладной могут повторяться многократно (в зависимости от того, сколько товаров поступило по накладной). Необходимо создать запрос, который будет для накладных с одинаковым номером суммировать количество товара.

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

Рис. 13. Таблица "Поступление товаров"

Порядок работы:

1. В Окне навигации выбирается Категория объекта - Тип объекта , а в разделе Фильтр по группам устанавливается переключатель Запросы . На ленте в разделе Создание в группе Запросы нажимается кнопка Конструктор Запросов .

2. В диалоговом окне Добавление таблиц , выделяются таблицы, данные из которых требуется отобразить в запросе ("Поступление товаров" ) и нажимается кнопка Добавить .

3. На экране отобразится окно конструктора запроса, состоящее из двух частей: в верхней части отображены макеты таблиц, а в нижней - раздел для определения параметров запроса.

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

5. Для реализации запроса в режиме Конструктора добавляется дополнительная строка Групповые операции (рис.14) нажатием на панели инструментов кнопки.

Под полем № накладной в строке Групповые операции выбирается команда Группировка (накладные с одинаковым номером группируются), под полем Код товара в строке Групповые операции выбирается команда Count (количество), а под полем Количество - команда Sum (количество суммируется). Операции, используемые в запросе на группировку, описаны в табл.2.

Результат выполнения запроса представлен на рис. 15. (сравните результат с данными рис. 13).


Таблица 2. Групповые операции

Название операции

Значение

Суммирование

Среднее значение

Минимальное значение

Максимальное значение

Количество элементов в столбце

Последний элемент

Первый элемент

Условие

Указывает на логическое выражение

Выражение

Указывает на то, что поле вычисляемое

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

Выберите нужное действие

Общее представление о способах суммирования данных

Просуммировать числовой столбец в запросе можно с помощью агрегатной функции. Агрегатные функции выполняют вычисления со столбцами данных и возвращают единственное значение. В Access существует множество агрегатных функций, включая Sum , Count , Avg (для вычисления среднего значения), Min и Max . Суммирование данных производится путем добавления в запрос функции Sum , подсчет данных - путем использования функции Count и т. д.

Кроме того, в Access предусмотрено несколько способов добавления функции Sum и других агрегатных функций в запрос. Вы можете:

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

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

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

Город

Краснодар

Санкт-Петербург

Москва

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

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

В следующих разделах описано, как добавить строку итогов, использовать итоговый запрос для суммирования данных и перекрестный запрос, вычисляющий промежуточные итоги по группам и интервалам времени. Помните, что многие агрегатные функции работают только с данными в полях, имеющих определенный тип данных. Например, функция SUM работает только с типами данных "Число", "Действительное" и "Денежный". Дополнительные сведения о типах данных, требуемых для каждой функции, см. ниже в разделе .

Общие сведения о типах данных см. в статье Изменение типа данных для поля .

Подготовка примера данных

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

Сделать это в Access можно несколькими способами. Вы можете ввести данные вручную, скопировать каждую таблицу в редактор электронных таблиц (такой как Excel) и импортировать листы в Access или же вставить данные в текстовый редактор, например Блокнот, и импортировать их из созданных текстовых файлов.

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

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

Таблицы "Товары"

Наименование товара

Цена

Фигурка программиста

Игры и головоломки

Схема реляционной базы данных

Картины и рамы

Игры и головоломки

Доступ! Игра!

Игры и головоломки

Видеоигры

DVD-диски и фильмы

Неуловимая летающая пицца

Спортивное снаряжение

Модели для сборки, хобби

Видеоигры

Собери клавиатуру

Модели для сборки, хобби

Таблица Заказы

Дата заказа

Дата отгрузки

Город назначения

Стоимость доставки

Jakarta (Джакарта)

Jakarta (Джакарта)

Новосибирск

Владивосток

Краснодар

Санкт-Петербург

Таблица Сведения о заказах

Идентификатор заказа

Наименование товара

Код товара

Цена за единицу

Количество

Скидка

Собери клавиатуру

Недвижущаяся фигурка бюрократа

Приступить к вашему компьютеру! С DVD-диска!

Волшебная микросхема

Компьютерные маньяки и мифические животные

Доступ! Игра!

Фигурка программиста

Неуловимая летающая пицца

Внешний дисковод гибких дисков 5.25"" (масштаб 1:4)

Схема реляционной базы данных

Схема реляционной базы данных

Примечание: Помните, что в типичной базе данных таблица "Сведения о заказах" будет содержать только поле "Код товара" (без поля "Наименование товара"). В данном примере поле "Наименование товара" используется для упрощения восприятия данных.

Ввод примеров данных вручную

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

Создание листов с примерами данных

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

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

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

    Повторяйте шаги 2 и 3, чтобы скопировать каждый пример таблицы на пустой лист и переименовать этот лист.

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

    Сохраните книгу в нужной папке на компьютере или в сети и переходите к следующей процедуре.

Создание таблиц базы данных на основе листов

    На вкладке Внешние данные в группе Импорт щелкните Excel .

    Нажмите кнопку Дополнительно , а затем выберите редактор электронных таблиц из списка.

    Откроется диалоговое окно Внешние данные - лист <имя программы> .

    Нажмите кнопку Обзор , откройте файл электронной таблицы, созданный на предыдущих этапах, и нажмите кнопку ОК .

    Откроется окно мастера импорта электронных таблиц.

    По умолчанию мастер выбирает первый лист в книге (в этом примере - лист Клиенты ), и данные из этого листа появляются в нижней части страницы мастера. Нажмите кнопку Далее .

    На следующей странице мастера выберите Первая строка содержит названия столбцов , а затем нажмите кнопку Далее .

    Если нужно, вы можете изменить имена полей и типы данных или пропустить некоторые поля, воспользовавшись текстовыми полями и списками в группе Параметры поля . В противном случае нажмите кнопку Далее .

    Оставьте параметр автоматически создать ключ выбранным и нажмите кнопку Далее .

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

    Повторите шаги с 1 по 7 для каждого листа книги Excel, чтобы создать для него таблицу.

Переименование полей первичного ключа

Примечание: При импорте листов Access автоматически добавляет в каждую таблицу столбец первичного ключа и по умолчанию присваивает ему имя "Код" и тип данных "Счетчик". В этом разделе даны инструкции по переименованию полей первичного ключа. Это позволяет четко определить все поля в запросе.

    В области навигации щелкните правой кнопкой мыши каждую таблицу, созданную на предыдущем шаге, и выберите команду Конструктор .

    Для каждой таблицы найдите поле первичного ключа. По умолчанию Access присваивает каждому полю имя Код .

    В столбце Имя поля каждого поля первичного ключа добавьте имя таблицы.

    Например, вы должны переименовать поле ID таблицы Categories на "код категории", а поле для таблицы Orders - на "код заказа". В таблице "сведения о заказе" переименуйте поле на "Detail ID". В таблице Products (продукты) переименуйте поле на "код продукта".

    Сохраните изменения.

Суммирования данных с помощью строки итогов

Чтобы добавить в запрос строку итогов, откройте его в режиме таблицы, добавьте строку, а затем выберите нужную агрегатную функцию, например Sum , Min , Max или Avg . В этом разделе объясняется, как создать простой запрос на выборку и добавить строку итогов. Не обязательно использовать примеры таблиц, представленные в предыдущем разделе.

Создание простого запроса на выборку

Добавление строки итогов

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

    Дважды щелкните запрос в области навигации. Запрос будет выполнен, а его результаты будут загружены в таблицу.

    На вкладке Главная в группе Записи нажмите кнопку Итоги .

    В таблице появится новая строка Итог .

    В строке Итог щелкните ячейку в поле, по которому вы хотите вычислить сумму, и выберите в списке функцию Sum .

Скрытие строки итогов

    На вкладке Главная в группе Записи нажмите кнопку Итоги .

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

Вычисление общих итогов с помощью запроса

Общие итоги - это сумма по всем значениям столбца. Можно вычислять нескольких типов общих итогов, включая:

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

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

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

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

Таблица "Заказы"

Идентификатор заказа

Дата заказа

Дата отгрузки

Город назначения

Стоимость доставки

Jakarta (Джакарта)

Санкт-Петербург

Краснодар

Новосибирск

Владивосток

Краснодар

Санкт-Петербург

Таблица "Сведения о заказах"

Код сведений

Идентификатор заказа

Наименование товара

Код товара

Цена за единицу

Количество

Скидка

Собери клавиатуру

Недвижущаяся фигурка бюрократа

Приступить к вашему компьютеру! С DVD-диска!

четырехпроцессорном

Волшебная микросхема

Компьютерные маньяки и мифические животные

Доступ! Игра!

Фигурка программиста

четырехпроцессорном

Неуловимая летающая пицца

Внешний дисковод гибких дисков 5.25"" (масштаб 1:4)

Схема реляционной базы данных

Схема реляционной базы данных

Вычисление простого общего итога

Вычисление общего итога за исключением нескольких записей

Вычисление итогов по группе с помощью итогового запроса

В этом разделе описано, как создать итоговый запрос для вычисления промежуточных итогов по группам данных. Помните, что по умолчанию итоговый запрос может включать только поле или поля, содержащие данные, по которым выполняется группировка, например поле "Категории", а также поля со значениями, которые вы хотите просуммировать, например поле "Продажи". Итоговые запросы не могут включать другие поля, описывающие значения внутри категории. Если вы хотите отобразить эти описательные данные, создайте второй запрос на выборку, объединяющий поля из итогового запроса с полями, содержащими дополнительные данные.

В этом разделе описано, как создавать итоговые запросы и запросы на выборку, если требуется определить объем продаж для каждого товара. В качестве примера используются следующие таблицы:

Таблицы "Товары"

Код товара

Наименование товара

Цена

Фигурка программиста

Эксперименты с C# (игра для всей семьи)

Игры и головоломки

Схема реляционной базы данных

Картины и рамы

Волшебная микросхема (500 деталей)

Картины и рамы

Доступ! Игра!

Игры и головоломки

Компьютерные маньяки и мифические животные

Видеоигры

Приступить к вашему компьютеру! С DVD-диска!

DVD-диски и фильмы

Неуловимая летающая пицца

Спортивное снаряжение

Внешний дисковод гибких дисков 5.25"" (масштаб 1:4)

Модели для сборки, хобби

Недвижущаяся фигурка бюрократа

Видеоигры

Собери клавиатуру

Модели для сборки, хобби

Таблица "Сведения о заказах"

Код сведений

Идентификатор заказа

Наименование товара

Код товара

Цена за единицу

Количество

Скидка

Собери клавиатуру

Недвижущаяся фигурка бюрократа

Приступить к вашему компьютеру! С DVD-диска!

Волшебная микросхема

Компьютерные маньяки и мифические животные

Доступ! Игра!

Фигурка программиста

четырехпроцессорном

Неуловимая летающая пицца

Внешний дисковод гибких дисков 5.25"" (масштаб 1:4)

Схема реляционной базы данных

Схема реляционной базы данных

Следующие действия также предполагают наличие отношения "один-ко-многим" между полями "Код товара" в таблицах "Заказы" и "Сведения о заказах" с таблицей "Заказы" на стороне "один" данного отношения.

Создание итогового запроса

    На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов .

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

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

    При использовании примеров таблиц, указанных выше, добавьте таблицы "Товары" и "Сведения о заказах".

    Дважды щелкните поля таблицы, которые вы хотите использовать в запросе.

    Как правило, в запрос добавляется только поле группы и поле значения. Однако вместо поля значения можно использовать вычисление, а далее пошагово объяснить, как это сделать.

    1. Создайте столбец, вычисляющий объемы продаж для каждой транзакции, введя во второй столбец бланка запроса следующее выражение:

      Общее значение продаж: (1-[Сведения о заказах].[Скидка]/100)*([Сведения о заказах].[Цена за единицу]*[Сведения о заказах].[Количество])

      Убедитесь, что поля, на которые ссылается выражение, имеют типы данных "Число" или "Денежный". Если оно ссылается на поля с другими типами данных, то при попытке переключения в режим таблицы появится сообщение об ошибке Несоответствие типов данных в выражении условия отбора .

      На вкладке Конструктор в группе Показать или скрыть нажмите кнопку Итоги .

      В бланке появится строка Итог , в первом и втором столбцах которой будет указано Группировка .

      Во втором столбце измените значение в строке Итог строки на Sum . Функция Sum суммирует отдельные показатели продаж.

      Выполнить .

      Оставьте запрос открытым, чтобы использовать его в следующем разделе.

      Использование условий в итоговом запросе

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

      Если вам нужно исключить некоторые записи, можно добавить условия в запрос. Например, вы можете пропустить транзакции с суммой менее 100 ₽ или вычислить итоги только по некоторым категориям товаров. В этом разделе описано использование трех типов условий:

      Условия, игнорирующие некоторые группы при вычислении итогов. Например, можно вычислить итоги только для категорий товаров "Видеоигры", "Картины и рамы" и "Спортивное снаряжение".

      Условия, скрывающие некоторые итоговые значения после их вычисления. Например, можно отобразить только итоговые значения свыше 150 000 ₽.

      Условия, исключающие некоторые записи при вычислении итогового значения. Например, можно исключить отдельные транзакции, в которых значение (Цена за единицу * Количество) меньше 100 ₽.

      На примере следующих действий объясняется, как добавить условия одно за другим и как это повлияет на результаты запроса.

      Добавление условий в запрос

      Откройте запрос из предыдущего раздела в Конструкторе. Для этого щелкните правой кнопкой мыши вкладку документа для запроса и выберите команду Конструктор .

      Щелкните правой кнопкой мыши запрос в области навигации и выберите Конструктор .

      В строке Условия отбора столбца "Код категории" введите =Куклы Or Спортивное снаряжение or Картины и рамы .

      Чтобы выполнить запрос и отобразить результаты в режиме таблицы, нажмите кнопку Выполнить .

      Вернитесь в Конструктор и в строке Условия отбора столбца "Общее значение продаж" введите >100 .

      Выполните запрос, чтобы просмотреть результаты, а затем переключитесь в Конструктор.

      Теперь добавьте условия для исключения отдельных транзакций, сумма которых меньше 100 ₽. Для этого необходимо добавить еще один столбец.

      Примечание: Невозможно указать третье условие для столбца "Общее значение продаж". Любое условие, указанное для этого столбца, будет применено к итоговому значению, а не к отдельным значениям.

      Скопируйте выражение из второго столбца в третий столбец.

      В строке Итог нового столбца выберите Условие , а в строке Условия отбора введите >20 .

      Выполните запрос, чтобы просмотреть результаты, а затем сохраните его.

      Примечание: При следующем открытии запроса в Конструкторе можно заметить небольшие изменения в бланке. Во втором столбце выражение в строке Поле будет заключено в функцию Sum , а в строке Итоги будет указано Выражение вместо функции Sum .

      Общее значение продаж: Sum((1-[Сведения о заказах].Скидка/100)*([Сведения о заказах].Цена*[ Сведения о заказах].Количество))

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

Суммирование данных из нескольких групп с помощью перекрестного запроса

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

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

При создании перекрестного запроса обычно включаются данные из нескольких таблиц, и всегда включаются три типа данных: данные, используемые в качестве заголовков строк, данные, используемые в качестве заголовков столбцов и значения, которые вы хотите просуммировать или с которыми необходимо произвести другие вычисления.

Инструкции в данном разделе предполагают использование следующих примеров таблиц:

Таблица "Заказы"

Дата заказа

Дата отгрузки

Город назначения

Стоимость доставки

Jakarta (Джакарта)

Санкт-Петербург

Краснодар

Новосибирск

Владивосток

Краснодар

Санкт-Петербург

Таблица "Сведения о заказах"

Идентификатор заказа

Наименование товара

Код товара

Цена за единицу

Количество

Скидка

Собери клавиатуру

Недвижущаяся фигурка бюрократа

Приступить к вашему компьютеру! С DVD-диска!

Волшебная микросхема

Компьютерные маньяки и мифические животные

Доступ! Игра!

Фигурка программиста

Неуловимая летающая пицца

Внешний дисковод гибких дисков 5.25"" (масштаб 1:4)

Схема реляционной базы данных

Схема реляционной базы данных

Ниже объясняется, как создавать перекрестный запрос, группирующий итоги продаж по городам. Запрос использует два выражения для возврата форматированной даты и общего объема продаж.

Создание перекрестного запроса

    На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов .

    В диалоговом окне Отображение таблиц дважды щелкните таблицы, которые вы хотите использовать в запросе, а затем нажмите кнопку Закрыть .

    Таблица появится в виде окна в верхней части конструктора запросов.

    При использовании примеров таблиц дважды щелкните таблицы "Заказы" и "Сведения о заказах".

    Дважды щелкните поля, которые вы хотите использовать в запросе.

    Каждое имя поля появится в пустой ячейке в строке Поле на бланке.

    При использовании примеров таблиц добавьте поля "Город назначения" и "Дата отгрузки" из таблицы "Заказы".

    В следующую пустую ячейку строки Поле скопируйте и вставьте или введите следующее выражение: Итоги продаж: Sum(CCur([Сведения о заказах].[Цена за едницу]*[Количество]*(1-[Скидка])/100)*100)

    На вкладке Конструктор в группе Тип запроса щелкните элемент Перекрестная таблица .

    В бланке запроса будут отображены строки Итог и Перекрестный .

    Щелкните ячейку в строке Итог в поле "Город назначения" и выберите Группировка . Выполните те же действия для поля "Дата отгрузки". Измените значение в ячейке Итоги поля "Итоги продаж" на Выражение .

    В строке Перекрестный присвойте ячейке в поле "Город назначения" значение Заголовки строк , полю "Дата отгрузки" - значение Заголовки столбцов , а полю "Итоги продаж" - Значение .

    На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить .

    Результаты запроса будут отображены в режиме таблицы.

Справочные сведения об агрегатных функциях

В следующей таблице перечислены и отписаны агрегатные функции Access, которые можно использовать в строке итогов и в запросах. Помните, что в Access предусмотрено больше агрегатных функций для запросов, чем для строки итогов. Кроме того, при работе с проектом Access (внешней базой данных Access, которая подключается к базе данных Microsoft SQL Server) можно использовать расширенный набор агрегатных функций, предоставляемый SQL Server. Дополнительные сведения о них см. в электронной документации Microsoft SQL Server.

Функция

Описание

Поддерживаемые типы данных

Вычисляет среднее значение для столбца. Столбец должен содержать числовые или денежные величины или значения даты или времени. Функция игнорирует пустые значения.

Подсчитывает число элементов в столбце.

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

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

Максимум

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

"Число", "Денежный", "Дата/время"

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

"Число", "Денежный", "Дата/время"

Standard Deviation

Показывает, насколько значения отклоняются от среднего.

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

"Число", "Денежный"

Суммирует элементы в столбце. Подходит только для числовых и денежных данных.

"Число", "Денежный"

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

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

"Число", "Денежный"

Microsoft® Office Access 2007. Как воспользоваться базой данных. Что лежит в основе структуры ленты. Знакомство с шаблонами. Цель этого курса. Загрузка базы данных. Команду СОХРАНИТЬ КАК можно использовать для создания копии. Что из следующего верно в отношении формата файлов Access 200. Кнопка Microsoft Office. Параметры макета отчета с тремя вкладками. Создайте базу данных. Быстрое освоение программы.

«Access» - Типы запросов. Создание запросов. Создание таблиц. Создание отчетов. Создание новой базы данных Access. Редактирование запросов. Структуризация данных. Пример информационно-логической схемы. Обслуживание и защита БД. Этапы построения базы данных. Создание и редактирование форм. Автоматизированное рабочее место (АРМ) в Microsoft Access. Создание схемы данных. Базы данных: понятие, основные элементы.

«Формы в Access» - Поле в форме, по которому должна быть фильтрация. Как делать формы в ACCESS. Переходы по записям. Отбор записей в форме. Переход на заданную запись. Собаки, которые принадлежат владельцу. Выполнение макросов. Напишите макрос mcFilter. Быстрый просмотр отчета. Сортировка записей. Напишите макрос. Переход по подчиненным формам. Выберите макрокоманду. Выбираете макрокоманду. Просмотр подробных данных.

«База данных Access 2007» - Режимы просмотра отчета. Свойства элементов. Группировкой. Создание таблиц (шаблоны). Виды запросов. Таблица. Формы. Конструктор макросов. Группы макросов. Макрос – это набор макрокоманд. Режим SQL. Установка/снятие пароля. Работа с таблицами. Проектирование таблиц. Отчеты. Создание запроса, включающего все данные. Макросы. Добавление новых полей. Сложная фильтрация. Свойства формы и ее элементов.

«База данных MS Access» - Примечание группы. Запрос на выборку. Разделы отчета. Внедренные макросы. Этапы создания интерфейса. Многотабличные запросы. Представления. Назначение и виды запросов. Конструирование макроса. Вычисляемые поля. Мастер запросов. MS Access. Назначение каждого из разделов. Групповые операции в запросах. Макет. Отчеты. Виды обработки данных. Конструктор запросов. Связная целостность. Форма. Запросы на обновление.

«Access 2010» - Добавление поля подстановки в таблицу. Добавление и сохранение данных. Цели курса. Создание таблиц для новой базы данных. Создание таблицы в режиме таблицы. Способы создания таблиц. Экономия времени с помощью полей из раздела «Краткое руководство». Выберите синтаксически правильный вариант списка значений из предложенных ниже. Создание таблиц для базы данных. Процесс в режиме таблицы. Общие сведения.

Как правило, данные проще анализировать, если они разделены на группы. Например, отчет с группировкой продаж по регионам может помочь выявить тенденции, которые иначе могли бы остаться незамеченными. К тому же, поместив итоговые значения (например, суммы или средние значения) в конце каждой группы своего отчета, вы тем самым замените множество операций, выполняемых на калькуляторе.

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

Примечание: Статья неприменима к веб-приложениям Access - новому типу баз данных, которые создаются в Access и публикуются в Интернете.

В этой статье

Создание краткого отчета с группировкой или сортировкой

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

Создание отчета с группировкой при помощи мастера отчетов

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

Запуск мастера отчетов

Группировка записей при помощи мастера отчетов

Группировка позволяет упорядочить записи по группам (например, по регионам или продавцам). Вложенные группы дают возможность легко определить отношения между группами и быстро найти нужные данные. С помощью группировки вы также можете получить сводные данные, такие как итоговые значения или проценты.

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


Сортировка записей и подведение итогов по ним

Вы можете сортировать записи в порядке возрастания или убывания по 1–4 полям.


С помощью кнопок навигации в нижней части области просмотра вы можете последовательно просматривать страницы отчета или перейти к любой его странице. Нажмите одну из кнопок навигации или введите номер нужной страницы в поле ввода номера страницы, а затем нажмите клавишу ВВОД.

В режиме предварительного просмотра вы можете увеличивать масштаб, чтобы просмотреть подробности, или уменьшать его, чтобы просмотреть расположение данных на странице. Щелкните один раз, когда курсор мыши будет указывать на отчет. Чтобы отменить эффект увеличения, щелкните еще раз. Вы также можете использовать элемент управления масштабом в строке состояния.

Добавление или изменение группировки и сортировки в существующем отчете

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

Добавление группировки, сортировки и итогов

Вы можете выполнять операции сортировки, группировки и подведения итогов, щелкнув правой кнопкой мыши поля в режиме макета, а затем выбрав в контекстном меню нужную операцию. Чтобы перейти в режим макета, щелкните правой кнопкой мыши отчет в области навигации и выберите пункт Режим макета .

Примечание: Хотя в инструкциях этого раздела не говорится прямо об области , мы советуем открыть ее и отслеживать изменения на ней в процессе работы. Вы получите более полное представление о действиях Access, а освоив работу с областью Группировка, сортировка и итоги , вы с ее помощью сможете дополнительно корректировать отчет. Чтобы отобразить панель Группировка, сортировка и итоги :

    на вкладке Конструктор в группе Группировка и итоги щелкните Группировка и сортировка .

Сортировка по одному полю

    Щелкните правой кнопкой любое значение в поле, по которому требуется выполнить сортировку.

    В контекстном меню выберите нужный вариант сортировки. Например, чтобы выполнить сортировку текстового поля по возрастанию, щелкните Сортировка от А до Я . Чтобы выполнить сортировку числового поля в убывающем порядке, щелкните Сортировка по убыванию .

Access выполняет сортировку отчета, как вы указали. Если область Группировка, сортировка и итоги уже открыта, вы можете заметить, что в поле добавилась новая строка Сортировка .

Сортировка по нескольким полям

Примечание: Когда вы применяете сортировку, щелкая поле в режиме макета, вы можете проводить сортировку лишь одного поля за раз. Применение сортировки к другому полю удаляет сортировку по первому полю. Это отличается от способа проведения сортировок в формах, где можно установить разнообразные порядки сортировки, щелкая каждое поле по очереди и выбирая нужный порядок сортировки. Чтобы создать уровни сортировки для нескольких полей, см. раздел .

Группировка по полю

    Щелкните правой кнопкой любое значение в поле, по которому требуется выполнить группировку.

    В контекстном меню выберите Группировка .

Access добавит уровень группировки и создаст заголовок группы. Если область Группировка, сортировка и итоги уже открыта, будет видно, что к полю добавилась новая строка Группировка .

Добавление итогового значения в поле

Этот параметр позволяет вычислить сумму, среднее, количество или другие статистические поля. Итоговое значение добавляется в конец отчета, а групповые итоги добавляются к группам отчета.

    Щелкните правой кнопкой любое значение в поле, по которому требуется рассчитать итоговое значение.

    Щелкните Итог .

    Выберите операцию, которую нужно выполнить: Сумма , Среднее , Число записей (чтобы посчитать все записи), Количество значений (чтобы посчитать только записи со значением данного поля), Максимум , Минимум , Стандартное отклонение или Дисперсия .

Access добавит в колонтитул отчета такой элемент управления, как вычисляемый текст, в котором подводится общий итог. Если в отчете содержатся уровни группировки, Access добавит колонтитулы групп (если их нет) и поместит итоговое значение в каждый колонтитул.

Примечание: Вы также можете добавить итоги, щелкнув поле, по которому их необходимо рассчитать, и на вкладке Конструктор в группе Группировка и итоги щелкнув Итоги .

Добавление группировки, сортировки и итогов с помощью области "Группировка, сортировка и итоги"

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

Отображение области "Сортировка, группировка и итоги"

    На вкладке Конструктор в группе Группировка и итоги щелкните Группировка и сортировка .

    Access отобразит область Группировка, сортировка и итоги .

Чтобы добавить уровень группировки или сортировки, щелкнитеДобавить группировку или Добавить сортировку .

В область Группировка, сортировка и итоги будет добавлена новая строка и отобразится список доступных полей.

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

Дополнительные сведения о создании и использовании выражений см. в статье Создание выражений .

Примечания:

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

    Вы можете назначить до десяти уровней сортировки и группировки в отчете.

Изменение параметров группировки

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


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

Интервал группировки. Эта настройка определяет способ группировки записей. Например, текстовые поля можно сгруппировать вместе по первому символу (если они начинаются на "А", "Б" и т. д.). Поля даты можно сгруппировать по дню, неделе, месяцу, кварталу или ввести свой интервал.

Итоги. Чтобы добавить итоги, щелкните этот параметр. Вы можете добавить итоги к нескольким полям, а также рассчитать несколько типов итоговых значений для одного поля.

    Итог по полю и выберите поле, по которому нужно рассчитать итоговые значения.

    Щелкните стрелку раскрывающегося списка Тип и выберите способ расчета.

    Выберите Показать общий итог , чтобы добавить общий итог в конец отчета (его колонтитул).

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

    Выберите Показать в заголовке группы или Показать в колонтитуле группы , чтобы отобразить общий итог в нужном месте.

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

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

Чтобы добавить или изменить заголовок:

    щелкните голубой текст после надписи с заголовком ;

    появится диалоговое окно Масштаб ;

    введите новый заголовок в диалоговом окне, а затем нажмите ОК .

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

С разделом/без раздела примечания. Используйте этот параметр, чтобы добавить или удалить раздел колонтитулов после каждой группы. Прежде чем удалить раздел колонтитулов, содержащий элементы управления, Access попросит вас о подтверждении.

Неразрывное представление группы. Этот параметр определяет, как группы располагаются на странице при печати отчета. Вам может понадобиться расположить группы как можно ближе друг к другу для их удобного просмотра на странице. Но это обычно увеличивает расход бумаги при распечатке отчета, потому что на большинстве страниц будет пустое место внизу.

    Не удерживать группу на одной странице. Используйте этот параметр, если вам не важно расположение групп при разрыве страниц. Например, 10 элементов группы из 30 элементов могут располагаться внизу одной страницы и остальные 20 вверху следующей страницы.

    Удерживать группу на одной странице. Этот параметр помогает свести к минимуму число разрывов страниц в группе. Если группа не помещается на оставшемся месте одной страницы, Access оставляет это место пустым и размещает группу на следующей странице. Большие группы все же могут располагаться на нескольких страницах, но этот параметр сводит такие случаи к минимуму.

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

Изменение приоритета уровней группировки и сортировки

Чтобы изменить приоритет, щелкните строку в области Группировка, сортировка и итоги , а затем стрелку вверх или вниз справа строки.

Удаление уровней группировки и сортировки

Чтобы удалить уровень, в области Группировка, сортировка и итоги выберите строку, которую вы хотите удалить, а затем нажмите клавишу DELETE или кнопку Удалить справа от строки. При удалении уровня группировки, если заголовок группы или колонтитул содержали поле группировки, Access переместит его в раздел подробностей отчета. Все другие элементы управления удаляются.

Создание сводного отчета (без сведений о записях)

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

6.1. Вычисляемые поля.

6.2. Создание выражений с помощью Построителя выражений.

6.3. Обзор встроенных функций СУБД MS Access.

6.4. Итоговые запросы.

6.5. Перекрестные запросы.

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

В QBE СУБД MS Access такие возможности предоставляются через вычисляемые поля и групповые операции.

6.1. Вычисляемые поля

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

Стоимость: Товары! Цена * Количество * (1-Скидка)

КоличествоМужчин: Sum(IIf(Пол = "м"; 1; 0))

ФИО: Фамилия &" "& Left(Имя;1) &". "& Left(Отчество;1) &"."

Стоимость, КоличествоМужчин и ФИО являются именами вычисляемых полей и отображаются в режиме таблицы в заголовке столбца, символ двоеточие исполняет роль разделителя между именем вычисляемого поля и выражением.

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

Стоимость: Цена*[Количество товара]

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

Обновить вычисленные результаты вручную невозможно.

Для построения сложных выражений в СУБД MS Access входит утилита, называемая Построитель выражений.

6.2. Создание выражений с помощью Построителя выражений

Построитель выражений можно запустить щелкнув по клавише Построить на панели инструментов Конструктор запросов или выбрав в контекстном меню поля бланка запроса QBE команду меню Построить… .

Рис.6.2. Диалоговое окно Построителя выражений
со сформированным выражением

6.3. Обзор встроенных функций СУБД MS Access

СУБД MS Access содержит более 100 встроенных функций (рис.6.3), которые можно использовать при формировании вычисляемого поля или при задании условия отбора.

Возвращает значение дня месяца от 1 до 31

Возвращает значение месяца от 1 до 12

MonthName(месяц[; флаг])

Возвращает название месяца соответствующего номеру месяца: 1 – январь, 2 – февраль, и т. д. Если значение аргумента флаг Истина, то функция возвращает аббревиатуру месяца: 1 – янв, 2 – фев и т. д.

Возвращает значение года от 100 до 9999

Weekday(дата[; число])

Если аргумент число не указан, возвращает значение дня недели от 1 (воскресенье) до 7 (суббота). Если аргумент число имеет значение 0, то возвращает значение дня недели от 1 (понедельник) до 7 (воскресенье)

Возвращает целое число от 0 од 23, представляющее значение часа

DatePart(интервал; дата)

Возвращает числовое значение в зависимости от значения аргумента интервал:

"q" – квартал (от 1 до 4);

"m" – месяц (от 1 до 12);

"yyyy" – год (от 100 до 9999);

"ww" – неделя (от 1 до 53);

и т. п. (см. справку по функции).

Возвращает текущую системную дату

Продолжение таблицы 6.1

Описание

Left(текст; n)

Возвращает n левых символов аргумента текст

Right(текст; n)

Возвращает n правых символов аргумента текст

Mid(текст; нач_поз[; n])

Возвращает n символов начиная с позиции нач_поз аргумента текст. Если аргумент n не указан, то возвращает все символы до конца строки начиная с позиции нач_поз аргумента текст.

Возвращает количество символов (длину строки) в аргументе текст

LTrim(текст)

Возвращает строковое значение аргумента текст без начальных пробелов

RTrim(текст)

Возвращает строковое значение аргумента текст без заключительных пробелов

Trim(текст)

Возвращает строковое значение аргумента текст без начальных и заключительных пробелов

Возвращает строковое значение аргумента число

Format(переменная; формат)

Возвращает значение аргумента переменная в формате заданным аргументом формат

6.3. Итоговые запросы

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

Количество сделок с Партнерами за определенный промежуток времени;

Средний объем продаж по каждому месяцу за предыдущий год.

Ответы на такие вопросы дает итоговый запрос.

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

По умолчанию для каждого поля, занесенного в бланк запроса, устанавливается значение Группировка (итоги не подводятся).

Для подведения итогов необходимо заменить установку Группировка на конкретную итоговую функцию. В СУБД MS Access предусмотрено 9 функций (табл. 6.2), обеспечивающих выполнение групповых операций.

Таблица 6.2

Назначение

Возвращает сумму набора значений

Возвращает среднее арифметическое набора значений

Возвращает наименьшее значение из набора значений

Возвращает наибольшее значение из набора значений

Возвращает количество записей в наборе значений отличных от Null

Возвращает первое значение поля в группе

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

Возвращает среднеквадратичное отклонение набора значений

Возвращает дисперсию набора значений

Групповая операция имеется установка Выражение . Данная установка применяется, когда в выражении (строка Поле ) используется несколько итоговых функций.

В раскрывающемся списке строки Групповая операция имеется установка Условие . Данная установка применяется, когда в строке Условие отбора записано условие выборки, но данные столбца (поля) не должны участвовать в групповой операции.

Для решения более сложных статистических задач в СУБД MS Access предусмотрен специальный тип запроса – перекрестный запросов.

6.4. Перекрестные запросы

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

Для построения перекрестного запроса достаточно трех полей базового источника. По повторяющимся значениям одного поля формируются названия заголовков строк итоговой (сводной) таблицы (рис.6.4). По повторяющимся значениям другого поля формируются названия заголовков столбцов итоговой (сводной) таблицы. Результаты статистической обработки по третьему полю отображаются в ячейках сводной таблицы (область значений). Пример перекрестного запроса в режиме конструктора представлен на рис.6.5, а результаты выполнения запроса на рис. 6.6.

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

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

Рис.6.4. Макет перекрестной таблицы

Рис.6.5. Сформированный перекрестный запрос в QBE

Рис.6.6. Результат выполнения запроса в режиме Таблицы