В этой статье объясняется, как использовать агрегатную функцию в Access для суммирования данных в результирующем наборе запроса. В ней также кратко объясняется, как использовать другие агрегатные функции, такие как COUNT и AVG, для подсчета или усреднений значений в результирующем наборе. Кроме того, в ней объясняется, как использовать строку Итого для суммирования данных без изменения структуры запросов.
Выберите нужное действие
- Общее представление о способах суммирования данных
- Подготовка примера данных
- Суммирования данных с помощью строки итогов
- Вычисление общих итогов с помощью запроса
- Вычисление итогов по группе с помощью итогового запроса
- Суммирование данных из нескольких групп с помощью перекрестного запроса
- Справочные сведения об агрегатных функциях
Общее представление о способах суммирования данных
Просуммировать числовой столбец в запросе можно с помощью агрегатной функции. Агрегатные функции выполняют вычисления со столбцами данных и возвращают единственное значение. Access предоставляет различные агрегатные функции, включая Sum, Count, Avg (для вычислений средних значений), Minи Max. Суммирование данных выполняется путем добавления функции в Sum запрос. Вы подсчитываете Count данные с помощью функции и т. д.
Кроме того, Access предоставляет несколько способов добавления Sum и других агрегатных функций в запрос. Вы можете:
- Открыть запрос в режиме таблицы и добавить строку итогов. Строка Итог, функция Access, позволяет использовать агрегатную функцию в одном или нескольких столбцах результирующий набор запроса без изменения структуры запроса.
- Создать итоговый запрос. Итоговый запрос вычисляет промежуточные итоги для групп записей, а строка итогов — общие итоги для одного или нескольких столбцов (полей) данных. Например, если вы хотите вычислить промежуточную сумму всех продаж по городам или по кварталам, следует использовать итоговый запрос для группировки записей по нужной категории, а затем просуммировать все объемы продаж.
- Создать перекрестный запрос. Перекрестный запрос — это особый тип запросов, отображающий результаты в сетке, напоминающей лист Excel. Перекрестные запросы суммируют значения и затем группируют их по двум наборам фактов — вдоль боковой стороны (заголовки строк) и в верхней части (заголовки столбцов). Например, вы можете использовать перекрестный запрос для отображения итоговых значений продаж для каждого города за последние три года, как показано в таблице:
| Город | 2003 | 2004 | 2005 |
|---|---|---|---|
| Краснодар | 254 556 | 372 455 | 467 892 |
| Санкт-Петербург | 478 021 | 372 987 | 276 399 |
| Москва | 572 997 | 684 374 | 792 571 |
| ... | ... | ... | ... |
Примечание
В разделах с практическими сведениями в этом документе подчеркивается использование Sum функции, но в строках и запросах всего можно также использовать другие агрегатные функции. Дополнительные сведения см. в статье Справочник по агрегатным функциям далее в этой статье.
Дополнительные сведения о способах использования других агрегатных функций см. в разделе Отображение итогов по столбцу в таблице.
В следующих разделах описано, как добавить строку Итогово, использовать запрос итогов для суммирования данных в группах и использовать перекрестный запрос, который подытоживает данные между группами и интервалами времени. По мере продолжения помните, что многие агрегатные функции работают только с данными в полях, для определенных типов данных. Например, SUM функция работает только с полями, для которых заданы типы данных Number, Decimal или Currency. Дополнительные сведения о типах данных, которые требуются каждой функции, см. в статье Справочник по агрегатным функциям далее в этой статье.
Общие сведения о типах данных см. в статье Изменение типа данных для поля.
Подготовка примера данных
В инструкциях этой статьи приводятся таблицы с примерами данных. Они помогают понять, как работают агрегатные функции. Вы можете добавить примеры таблиц в новую или существующую базу данных.
Сделать это в Access можно несколькими способами. Вы можете ввести данные вручную, скопировать каждую таблицу в редактор электронных таблиц (такой как Excel) и импортировать листы в Access или же вставить данные в текстовый редактор, например Блокнот, и импортировать их из созданных текстовых файлов.
В пошаговых инструкциях этого раздела объясняется, как вводить данные вручную на пустой лист, а также как копировать примеры таблиц в редактор электронных таблиц и затем импортировать их в Access. Дополнительные сведения о создании и импорте текстовых данных см. в статье Импорт данных или связывание с данными текстового файла.
В пошаговых инструкциях в этой статье используются приведенные ниже таблицы. Создайте пример данных на их основе:
Таблица "Категории"
| Категория |
|---|
| Куклы |
| Игры и головоломки |
| Картины и рамы |
| Видеоигры |
| DVD-диски и фильмы |
| Модели для сборки, хобби |
| Спортивное снаряжение |
Таблицы "Товары"
| Наименование товара | Цена | Категория |
|---|---|---|
| Фигурка программиста | 12,95 ₽ | Куклы |
| Эксперименты с C# (игра для всей семьи) | 15,85 ₽ | Игры и головоломки |
| Схема реляционной базы данных | 22,50 ₽ | Картины и рамы |
| Волшебная микросхема (500 деталей) | 32,65 ₽ | Игры и головоломки |
| Доступа! Игра! | 22,95 ₽ | Игры и головоломки |
| Компьютерные маньяки и мифические животные | 78,50 ₽ | Видеоигры |
| Упражнение для компьютерных гикеров! DVD-диск! | 14,88 ₽ | DVD-диски и фильмы |
| Неуловимая летающая пицца | 36,75 ₽ | Спортивное снаряжение |
| Внешний дисковод гибких дисков 5.25'' (масштаб 1:4) | 65,00 ₽ | Модели для сборки, хобби |
| Недвижущаяся фигурка бюрократа | 78,88 ₽ | Куклы |
| Тьма | 53,33 ₽ | Видеоигры |
| Собери клавиатуру | 77,95 ₽ | Модели для сборки, хобби |
Таблица Заказы
| Дата заказа | Дата отгрузки | Город назначения | Стоимость доставки |
|---|---|---|---|
| 14.11.2005 | 15.11.2005 | Москва | 55,00 ₽ |
| 14.11.2005 | 15.11.2005 | Санкт-Петербург | 76,00 ₽ |
| 16.11.2005 | 17.11.2005 | Санкт-Петербург | 87,00 ₽ |
| 17.11.2005 | 18.11.2005 | Москва | 43,00 ₽ |
| 17.11.2005 | 18.11.2005 | Краснодар | 105,00 ₽ |
| 17.11.2005 | 18.11.2005 | Новосибирск | 112,00 ₽ |
| 18.11.2005 | 19.11.2005 | Иркутск | 215,00 ₽ |
| 19.11.2005 | 20.11.2005 | Владивосток | 525,00 ₽ |
| 20.11.2005 | 21.11.2005 | Иркутск | 198,00 ₽ |
| 20.11.2005 | 21.11.2005 | Краснодар | 187,00 ₽ |
| 21.11.2005 | 22.11.2005 | Санкт-Петербург | 81,00 ₽ |
| 23.11.2005 | 24.11.2005 | Москва | 92,00 ₽ |
Таблица Сведения о заказах
| Идентификатор заказа | Наименование товара | Код товара | Цена за единицу | Количество | Скидка |
|---|---|---|---|---|---|
| 1 | Собери клавиатуру | 12 | 77,95 ₽ | 9 | 5% |
| 1 | Недвижущаяся фигурка бюрократа | 2 | 78,88 ₽ | 4 | 7,5% |
| 2 | Упражнение для компьютерных гикеров! DVD-диск! | 7 | 14,88 ₽ | 6 | 4% |
| 2 | Волшебная микросхема | 4 | 32,65 ₽ | 8 | 0 |
| 2 | Компьютерные маньяки и мифические животные | 6 | 78,50 ₽ | 4 | 0 |
| 3 | Доступа! Игра! | 5 | 22,95 ₽ | 5 | 15% |
| 4 | Фигурка программиста | 1 | 12,95 ₽ | 2 | 6% |
| 4 | Неуловимая летающая пицца | 8 | 36,75 ₽ | 8 | 4% |
| 5 | Внешний дисковод гибких дисков 5.25'' (масштаб 1:4) | 9 | 65,00 ₽ | 4 | 10% |
| 6 | Схема реляционной базы данных | 3 | 22,50 ₽ | 12 | 6,5% |
| 7 | Тьма | 11 | 53,33 ₽ | 6 | 8% |
| 7 | Схема реляционной базы данных | 3 | 22,50 ₽ | 4 | 9% |
Примечание
Помните, что в типичной базе данных таблица "Сведения о заказах" будет содержать только поле "Код товара" (без поля "Наименование товара"). В данном примере поле "Наименование товара" используется для упрощения восприятия данных.
Ввод примеров данных вручную
на вкладке Создание в группе Таблицы нажмите кнопку Таблица. Access добавит в базу данных новую пустую таблицу.
Примечание
Эту операцию следует выполнять только при необходимости добавить таблицу в базу данных. При открытии новой пустой базы данных это действие не требуется.
Дважды щелкните первую ячейку в строке заголовков и введите имя поля из примера таблицы. По умолчанию Access обозначает пустые поля в строке заголовка текстом Добавить новое поле, например:
С помощью клавиш со стрелками перейдите к следующей пустой ячейке заголовка и введите имя второго поля. Можно также нажать
TABили дважды щелкнуть новую ячейку. Повторите эти действия, чтобы ввести имена всех полей.Введите данные в пример таблицы. По мере ввода данных Access определяет их тип для каждого поля. Если вы плохо знакомы с реляционными базами данных, для каждого поля таблицы следует задать конкретный тип данных, например "Число", "Текст" или "Дата/время". Это обеспечивает точный ввод данных и помогает предотвратить ошибки, например использование цифр номера телефона в вычислениях. Для этих примеров таблиц можно определить тип данных автоматически.
Завершив ввод данных, нажмите кнопку Сохранить. Сочетание клавиш нажмите клавиши CTRL+S. Откроется диалоговое окно Сохранение документа.
В поле Имя таблицы введите имя примера таблицы и нажмите кнопку ОК. Следует использовать указанные имена таблиц, потому что они применяются в запросах, приведенных в пошаговых инструкциях.
Повторяйте эти шаги, пока не создадите каждый из примеров таблиц, приведенных в начале этого раздела.
Если вы не хотите вводить данные вручную, можно выполнить указанные ниже действия для копирования информации в файл электронной таблицы, а затем импортировать данные из него в Access.
Создание листов с примерами данных
Запустите редактор электронных таблиц и создайте пустой файл. Если вы используете Excel, по умолчанию создается пустая книга.
Скопируйте первый пример таблицы и вставьте его на первый лист, начиная с первой ячейки.
Присвойте листу имя с помощью функций редактора электронных таблиц. Оно должно совпадать с именем примера таблицы. Например, если пример называется Категории, присвойте листу это же имя.
Повторяйте шаги 2 и 3, чтобы скопировать каждый пример таблицы на пустой лист и переименовать этот лист.
Примечание
К файлу электронной таблицы может понадобиться добавить листы. Сведения о том, как сделать это, см. в справке редактора электронных таблиц.
Сохраните книгу в нужной папке на компьютере или в сети и переходите к следующей процедуре.
Создание таблиц базы данных на основе листов
- На вкладке Внешние данные в группе Импорт & link щелкните Создать источник> данныхиз файла>Excel. Откроется диалоговое окно Получение внешних данных — электронная таблица Excel .
- Нажмите кнопку Обзор, откройте файл электронной таблицы, созданный на предыдущих этапах, и нажмите кнопку ОК. Откроется окно мастера импорта электронных таблиц.
- По умолчанию мастер выбирает первый лист в книге (в этом примере — лист Клиенты), и данные из этого листа появляются в нижней части страницы мастера. Нажмите кнопку Далее.
- На следующей странице мастера выберите Первая строка содержит названия столбцов, а затем нажмите кнопку Далее.
- Если нужно, вы можете изменить имена полей и типы данных или пропустить некоторые поля, воспользовавшись текстовыми полями и списками в группе Параметры поля. В противном случае нажмите кнопку Далее.
- Оставьте параметр автоматически создать ключ выбранным и нажмите кнопку Далее.
- По умолчанию Access использует имя листа для новой таблицы. Оставьте это имя или введите другое, а затем нажмите кнопку Готово.
- Повторяйте шаги 1–7, пока не создадите таблицу на каждом листе в книге.
Переименование полей первичного ключа
Примечание
При импорте листов Access автоматически добавил в каждую таблицу столбец первичного ключа. По умолчанию Access назвал этот столбец ID и присвоит AutoNumber ему тип данных. В этом разделе описано, как переименовать каждое поле первичного ключа. Это позволяет четко определить все поля в запросе.
- В области навигации щелкните правой кнопкой мыши каждую таблицу, созданную на предыдущем шаге, и выберите команду Конструктор.
- Для каждой таблицы найдите поле первичного ключа. По умолчанию Access присваивает каждому полю имя Код.
- В столбце Имя поля каждого поля первичного ключа добавьте имя таблицы. Например, переименуйте поле "Код" таблицы "Категории" в "Код категории", а поле таблицы "Заказы" — в "Код заказа". В таблице "Сведения о заказах" переименуйте поле в "Код сведений", а в таблице "Товары" — в "Код товара".
- Сохраните изменения.
В последующих ссылках на примеры таблиц в данной статье указываются поля первичного ключа, переименованные, как описано выше.
Суммирования данных с помощью строки итогов
Вы можете добавить строку Итого в запрос, открыв запрос в режиме таблицы, добавив строку, а затем выбрав агрегатную функцию, которую вы хотите использовать, например Sum, Min, Maxили Avg. В этом разделе объясняется, как создать простой запрос на выборку и добавить строку итогов. Не обязательно использовать примеры таблиц, представленные в предыдущем разделе.
Создание простого запроса на выборку
- На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов.
- Дважды щелкните таблицу или таблицы, которые вы хотите использовать в запросе. Выбранные таблицы отображаются в виде окон в верхней части конструктора запросов.
- Дважды щелкните поля таблицы, которые вы хотите использовать в запросе. Можно включить поля, содержащие описательные данные, например имена и описания, но следует обязательно добавить поле, содержащее числовые или денежные значения. Каждое поле отображается в ячейке в бланке запроса.
- Нажмите кнопку Выполнить , чтобы выполнить запрос. Результаты запроса будут отображены в режиме таблицы.
- При необходимости переключитесь в Конструктор и скорректируйте запрос. Для этого щелкните правой кнопкой мыши вкладку документа для запроса и выберите команду Конструктор. После этого можно изменить запрос, добавив или удалив поля таблицы. Чтобы удалить поле, выберите столбец в бланке запроса и нажмите клавишу DELETE.
- Сохраните запрос.
Добавление строки итогов
- Убедитесь в том, что запрос открыт в режиме таблицы. Для этого щелкните правой кнопкой мыши вкладку документа для запроса и выберите команду Режим таблицы. -или- В области навигации дважды щелкните запрос. Запрос будет выполнен, а его результаты будут загружены в таблицу.
- На вкладке Главная в группе Записи нажмите кнопку Итоги. В таблице появится новая строка Итог.
- В строке Итог щелкните ячейку в поле, по которому вы хотите вычислить сумму, и выберите в списке функцию Sum.
Скрытие строки итогов
- На вкладке Главная в группе Записи нажмите кнопку Итоги.
Дополнительные сведения об использовании строки итогов см. в разделе Отображение итогов по столбцу в таблице.
Вычисление общих итогов с помощью запроса
Общие итоги — это сумма по всем значениям столбца. Можно вычислять нескольких типов общих итогов, включая:
- Простой общий итог, суммирующий значения одного столбца. Например, можно вычислить общую стоимость доставки.
- Вычисляемый общий итог, суммирующий значения нескольких столбцов. Например, можно вычислить общий объем продаж, умножив стоимость нескольких элементов на количество упорядоченных элементов, а затем подведя итоговые значения.
- Общий итог за исключением нескольких записей. Например, можно вычислить сумму продаж только по последней пятнице.
В следующих разделах описано, как вычислить каждый из типов общих итогов. В инструкциях используются таблицы "Заказы" и "Сведения о заказах".
Таблица "Заказы"
| Идентификатор заказа | Дата заказа | Дата отгрузки | Город назначения | Стоимость доставки |
|---|---|---|---|---|
| 1 | 14.11.2005 | 15.11.2005 | Москва | 55,00 ₽ |
| 2 | 14.11.2005 | 15.11.2005 | Санкт-Петербург | 76,00 ₽ |
| 3 | 16.11.2005 | 17.11.2005 | Санкт-Петербург | 87,00 ₽ |
| 4 | 17.11.2005 | 18.11.2005 | Москва | 43,00 ₽ |
| 5 | 17.11.2005 | 18.11.2005 | Краснодар | 105,00 ₽ |
| 6 | 17.11.2005 | 18.11.2005 | Новосибирск | 112,00 ₽ |
| 7 | 18.11.2005 | 19.11.2005 | Иркутск | 215,00 ₽ |
| 8 | 19.11.2005 | 20.11.2005 | Владивосток | 525,00 ₽ |
| 9 | 20.11.2005 | 21.11.2005 | Иркутск | 198,00 ₽ |
| 10 | 20.11.2005 | 21.11.2005 | Краснодар | 187,00 ₽ |
| 11 | 21.11.2005 | 22.11.2005 | Санкт-Петербург | 81,00 ₽ |
| 12 | 23.11.2005 | 24.11.2005 | Москва | 92,00 ₽ |
Таблица "Сведения о заказах"
| Код сведений | Идентификатор заказа | Наименование товара | Код товара | Цена за единицу | Количество | Скидка |
|---|---|---|---|---|---|---|
| 1 | 1 | Собери клавиатуру | 12 | 77,95 ₽ | 9 | 0,05 |
| 2 | 1 | Недвижущаяся фигурка бюрократа | 2 | 78,88 ₽ | 4 | 0,075 |
| 3 | 2 | Упражнение для компьютерных гикеров! DVD-диск! | 7 | 14,88 ₽ | 6 | 0,04 |
| 4 | 2 | Волшебная микросхема | 4 | 32,65 ₽ | 8 | 0,00 |
| 5 | 2 | Компьютерные маньяки и мифические животные | 6 | 78,50 ₽ | 4 | 0,00 |
| 6 | 3 | Доступа! Игра! | 5 | 22,95 ₽ | 5 | 0,15 |
| 7 | 4 | Фигурка программиста | 1 | 12,95 ₽ | 2 | 0,06 |
| 8 | 4 | Неуловимая летающая пицца | 8 | 36,75 ₽ | 8 | 0,04 |
| 9 | 5 | Внешний дисковод гибких дисков 5.25'' (масштаб 1:4) | 9 | 65,00 ₽ | 4 | 0,10 |
| 10 | 6 | Схема реляционной базы данных | 3 | 22,50 ₽ | 12 | 0,065 |
| 11 | 7 | Тьма | 11 | 53,33 ₽ | 6 | 0,08 |
| 12 | 7 | Схема реляционной базы данных | 3 | 22,50 ₽ | 4 | 0,09 |
Вычисление простого общего итога
На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов.
Дважды щелкните таблицу, которую вы хотите использовать в запросе. При использовании примера данных нужно дважды щелкнуть таблицу "Заказы". Таблица появится в окне в верхней части конструктора запросов.
Дважды щелкните поле, для которого вы хотите найти сумму. Убедитесь, что поле имеет тип данных "Число" или "Денежный". При попытке суммировать значения в нечисловых полях, например в текстовом поле, Access выводит сообщение об ошибке Несоответствие типов данных в выражении условия отбора. Если вы используете пример данных, дважды щелкните столбец "Стоимость доставки". Вы можете добавить дополнительные числовые поля, если хотите вычислить для них общие итоги. Итоговый запрос может вычислять общие итоги для нескольких столбцов.
На вкладке Конструктор запросов в группе Показать и скрыть щелкните Итоги. В бланке появится строка Итог, а в ячейке столбца "Стоимость доставки" будет указано Группировка.
Измените значение в ячейке строки Итог строки на Sum.
Чтобы выполнить запрос и отобразить результаты в режиме таблицы, нажмите кнопку Выполнить.
Совет
Access добавляется
SumOfк началу имени поля, которое вы суммируете. Чтобы изменить заголовок столбца на более осмысленный, например Total Shipping, вернитесь в режим конструктора и щелкните строку Поле столбца Плата за доставку в конструкторской сетке. Поместите курсор рядом с полем Стоимость доставки и введитеTotal Shipping: Shipping Fee.При необходимости вы можете сохранить запрос и закрыть его.
Вычисление общего итога за исключением нескольких записей
На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов.
Дважды щелкните таблицу Order и Order Details(Сведения о заказе).
Добавьте поле "Дата заказа" из таблицы "Заказы" в первый столбец на бланке запроса.
В строке Условия первого столбца введите
Date() -1. Это выражение исключает записи с текущей датой из вычисляемого итогового значения.Затем создайте столбец, который вычисляет объем продаж по каждой транзакции. Введите в строку Поле второго столбца на бланке следующее выражение:
Total Sales Value: (1-[Order Details].[Discount]/100)*([Order Details].[Unit Price]*[Order Details].[Quantity])Убедитесь, что для полей ссылок выражений заданы типы данных Number или Currency. Если оно ссылается на поля с другими типами данных, то при попытке выполнения запроса появится сообщение Несоответствие типов данных в выражении условия отбора.На вкладке Конструктор запросов в группе Показать и скрыть щелкните Итоги. В бланке появится строка Итог, а в первом и втором столбцах будет указано Группировка.
Во втором столбце измените значение в ячейке строки Итог строки на Sum. Функция Sum суммирует отдельные показатели продаж.
Чтобы выполнить запрос и отобразить результаты в режиме таблицы, нажмите кнопку Выполнить.
Сохраните запрос с именем Продажи за день.
Примечание
При следующем открытии запроса в Конструкторе можно заметить небольшие изменения в значениях, указанных для строк Поле и Итог в столбце "Общее значение продаж". Выражение будет заключено в функцию Sum , а в строке Итог отображается выражение вместо суммы.
Например, если вы используете пример данных и создадите запрос (как показано на предыдущих шагах), вы увидите следующее:
Total Sales Value: Sum((1-[Order Details].Discount/100)*([Order Details].Unitprice*[Order Details].Quantity))
Вычисление итогов по группе с помощью итогового запроса
В этом разделе описано, как создать итоговый запрос для вычисления промежуточных итогов по группам данных. Помните, что по умолчанию итоговый запрос может включать только поле или поля, содержащие данные, по которым выполняется группировка, например поле "Категории", а также поля со значениями, которые вы хотите просуммировать, например поле "Продажи". Итоговые запросы не могут включать другие поля, описывающие значения внутри категории. Если вы хотите отобразить эти описательные данные, создайте второй запрос на выборку, объединяющий поля из итогового запроса с полями, содержащими дополнительные данные.
В этом разделе описано, как создать итоги и выбрать запросы, необходимые для определения общего объема продаж для каждого продукта. В качестве примера используются следующие таблицы:
Таблицы "Товары"
| Код товара | Наименование товара | Цена | Категория |
|---|---|---|---|
| 1 | Фигурка программиста | 12,95 ₽ | Куклы |
| 2 | Эксперименты с C# (игра для всей семьи) | 15,85 ₽ | Игры и головоломки |
| 3 | Схема реляционной базы данных | 22,50 ₽ | Картины и рамы |
| 4 | Волшебная микросхема (500 деталей) | 32,65 ₽ | Картины и рамы |
| 5 | Доступа! Игра! | 22,95 ₽ | Игры и головоломки |
| 6 | Компьютерные маньяки и мифические животные | 78,50 ₽ | Видеоигры |
| 7 | Упражнение для компьютерных гикеров! DVD-диск! | 14,88 ₽ | DVD-диски и фильмы |
| 8 | Неуловимая летающая пицца | 36,75 ₽ | Спортивное снаряжение |
| 9 | Внешний дисковод гибких дисков 5.25'' (масштаб 1:4) | 65,00 ₽ | Модели для сборки, хобби |
| 10 | Недвижущаяся фигурка бюрократа | 78,88 ₽ | Куклы |
| 11 | Тьма | 53,33 ₽ | Видеоигры |
| 12 | Собери клавиатуру | 77,95 ₽ | Модели для сборки, хобби |
Таблица "Сведения о заказах"
| Код сведений | Идентификатор заказа | Наименование товара | Код товара | Цена за единицу | Количество | Скидка |
|---|---|---|---|---|---|---|
| 1 | 1 | Собери клавиатуру | 12 | 77,95 ₽ | 9 | 5% |
| 2 | 1 | Недвижущаяся фигурка бюрократа | 2 | 78,88 ₽ | 4 | 7,5% |
| 3 | 2 | Упражнение для компьютерных гикеров! DVD-диск! | 7 | 14,88 ₽ | 6 | 4% |
| 4 | 2 | Волшебная микросхема | 4 | 32,65 ₽ | 8 | 0 |
| 5 | 2 | Компьютерные маньяки и мифические животные | 6 | 78,50 ₽ | 4 | 0 |
| 6 | 3 | Доступа! Игра! | 5 | 22,95 ₽ | 5 | 15% |
| 7 | 4 | Фигурка программиста | 1 | 12,95 ₽ | 2 | 6% |
| 8 | 4 | Неуловимая летающая пицца | 8 | 36,75 ₽ | 8 | 4% |
| 9 | 5 | Внешний дисковод гибких дисков 5.25'' (масштаб 1:4) | 9 | 65,00 ₽ | 4 | 10% |
| 10 | 6 | Схема реляционной базы данных | 3 | 22,50 ₽ | 12 | 6,5% |
| 11 | 7 | Тьма | 11 | 53,33 ₽ | 6 | 8% |
| 12 | 7 | Схема реляционной базы данных | 3 | 22,50 ₽ | 4 | 9% |
Следующие действия также предполагают наличие отношения "один-ко-многим" между полями "Код товара" в таблицах "Заказы" и "Сведения о заказах" с таблицей "Заказы" на стороне "один" данного отношения.
Создание итогового запроса
На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов.
Выберите таблицы, с которыми вы хотите работать, и нажмите кнопку Добавить. Таблица появится в виде окна в верхней части конструктора запросов. При использовании примеров таблиц, указанных выше, добавьте таблицы "Товары" и "Сведения о заказах".
Дважды щелкните поля таблицы, которые вы хотите использовать в запросе. Как правило, в запрос добавляются только поле группы и поле значения. Однако вы можете использовать вычисление вместо поля значения. Дальнейшие действия объясняют, как это сделать.
Добавьте поле "Категория" из таблицы "Товары" в бланк запроса.
Создайте столбец, вычисляющий объемы продаж для каждой транзакции, введя во второй столбец бланка запроса следующее выражение:
Total Sales Value: (1-[Order Details].[Discount]/100)*([Order Details].[Unit Price]*[Order Details].[Quantity])Убедитесь, что поля, на которые вы ссылаетесь в выражении, относятся к типам данных Number или Currency. Если оно ссылается на поля с другими типами данных, то при попытке переключения в режим таблицы появится сообщение об ошибке Несоответствие типов данных в выражении условия отбора.На вкладке Конструктор запросов в группе Показать и скрыть щелкните Итоги. В бланке появится строка Итог, в первом и втором столбцах которой будет указано Группировка.
Во втором столбце измените значение в строке Итог строки на Sum. Функция Sum суммирует отдельные показатели продаж.
Чтобы выполнить запрос и отобразить результаты в режиме таблицы, нажмите кнопку Выполнить.
Оставьте запрос открытым, чтобы использовать его в следующем разделе. Использование условий с запросом итогов. Запрос, созданный в предыдущем разделе, включает все записи в базовых таблицах. Он не исключает никакие заказы при вычислении итогов и отображает итоги для всех категорий. Если вам нужно исключить некоторые записи, можно добавить условия в запрос. Например, вы можете пропустить транзакции с суммой менее 100 ₽ или вычислить итоги только по некоторым категориям товаров. В этом разделе описано использование трех типов условий:
Условия, игнорирующие некоторые группы при вычислении итогов. Например, можно вычислить итоги только для категорий товаров "Видеоигры", "Картины и рамы" и "Спортивное снаряжение".
Условия, скрывающие некоторые итоговые значения после их вычисления. Например, можно отобразить только итоговые значения свыше 150 000 ₽.
Условия, исключающие некоторые записи при вычислении итогового значения. Например, можно исключить отдельные транзакции продаж, если значение (
Unit Price * Quantity) падает ниже 100 долл. США. На примере следующих действий объясняется, как добавить условия одно за другим и как это повлияет на результаты запроса. Добавление условий в запросОткройте запрос из предыдущего раздела в Конструкторе. Для этого щелкните правой кнопкой мыши вкладку документа для запроса и выберите команду Конструктор. -или- В области навигации щелкните запрос правой кнопкой мыши и выберите конструктор.
В строке Условия столбца Идентификатор категории введите
=Dolls Or Sports or Art and Framing.Чтобы выполнить запрос и отобразить результаты в режиме таблицы, нажмите кнопку Выполнить.
Вернитесь в конструктор и в строке Критерии столбца Total Sales Value (Общее значение продаж) введите
>100.Выполните запрос, чтобы просмотреть результаты, а затем переключитесь в Конструктор.
Теперь добавьте условия для исключения отдельных транзакций, сумма которых меньше 100 ₽. Для этого необходимо добавить еще один столбец.
Примечание
Невозможно указать третье условие для столбца "Общее значение продаж". Любое условие, указанное для этого столбца, будет применено к итоговому значению, а не к отдельным значениям.
Скопируйте выражение из второго столбца в третий столбец.
В строке Итого для нового столбца выберите Where и в строке Условия введите
>20.Выполните запрос, чтобы просмотреть результаты, а затем сохраните его.
Примечание
При следующем открытии запроса в Конструкторе можно заметить небольшие изменения в бланке. Во втором столбце выражение в строке Поле будет заключено в функцию Sum, а в строке Итоги будет указано Выражение вместо функции Sum.
Total Sales Value: Sum((1-[Order Details].Discount/100)*([Order Details].Unitprice*[Order Details].Quantity))Вы также увидите четвертый столбец. Этот столбец — копия второго столбца, но условия, заданные во втором столбце, на самом деле отображаются как часть нового столбца.
Суммирование данных из нескольких групп с помощью перекрестного запроса
Перекрестный запрос — это особый тип запросов, отображающий результаты в сетке, напоминающей лист Excel. Перекрестные запросы суммируют значения и затем группируют их по двум наборам фактов — один набор вдоль боковой стороны (заголовки строк) и второй набор вдоль верхней части (заголовки столбцов). На рисунке показана часть набора результатов для примера перекрестного запроса.
Помните, что перекрестный запрос не всегда заполняет все поля в наборе результатов, потому что таблицы, используемые в запросе, не всегда содержат значения для всех возможных точек данных.
При создании перекрестного запроса обычно включаются данные из нескольких таблиц, и всегда включаются три типа данных: данные, используемые в качестве заголовков строк, данные, используемые в качестве заголовков столбцов и значения, которые вы хотите просуммировать или с которыми необходимо произвести другие вычисления.
Инструкции в данном разделе предполагают использование следующих примеров таблиц:
Таблица "Заказы"
| Дата заказа | Дата отгрузки | Город назначения | Стоимость доставки |
|---|---|---|---|
| 14.11.2005 | 15.11.2005 | Москва | 55,00 ₽ |
| 14.11.2005 | 15.11.2005 | Санкт-Петербург | 76,00 ₽ |
| 16.11.2005 | 17.11.2005 | Санкт-Петербург | 87,00 ₽ |
| 17.11.2005 | 18.11.2005 | Москва | 43,00 ₽ |
| 17.11.2005 | 18.11.2005 | Краснодар | 105,00 ₽ |
| 17.11.2005 | 18.11.2005 | Новосибирск | 112,00 ₽ |
| 18.11.2005 | 19.11.2005 | Иркутск | 215,00 ₽ |
| 19.11.2005 | 20.11.2005 | Владивосток | 525,00 ₽ |
| 20.11.2005 | 21.11.2005 | Иркутск | 198,00 ₽ |
| 20.11.2005 | 21.11.2005 | Краснодар | 187,00 ₽ |
| 21.11.2005 | 22.11.2005 | Санкт-Петербург | 81,00 ₽ |
| 23.11.2005 | 24.11.2005 | Москва | 92,00 ₽ |
Таблица "Сведения о заказах"
| Идентификатор заказа | Наименование товара | Код товара | Цена за единицу | Количество | Скидка |
|---|---|---|---|---|---|
| 1 | Собери клавиатуру | 12 | 77,95 ₽ | 9 | 5% |
| 1 | Недвижущаяся фигурка бюрократа | 2 | 78,88 ₽ | 4 | 7,5% |
| 2 | Упражнение для компьютерных гикеров! DVD-диск! | 7 | 14,88 ₽ | 6 | 4% |
| 2 | Волшебная микросхема | 4 | 32,65 ₽ | 8 | 0 |
| 2 | Компьютерные маньяки и мифические животные | 6 | 78,50 ₽ | 4 | 0 |
| 3 | Доступа! Игра! | 5 | 22,95 ₽ | 5 | 15% |
| 4 | Фигурка программиста | 1 | 12,95 ₽ | 2 | 6% |
| 4 | Неуловимая летающая пицца | 8 | 36,75 ₽ | 8 | 4% |
| 5 | Внешний дисковод гибких дисков 5.25'' (масштаб 1:4) | 9 | 65,00 ₽ | 4 | 10% |
| 6 | Схема реляционной базы данных | 3 | 22,50 ₽ | 12 | 6,5% |
| 7 | Тьма | 11 | 53,33 ₽ | 6 | 8% |
| 7 | Схема реляционной базы данных | 3 | 22,50 ₽ | 4 | 9% |
Ниже объясняется, как создавать перекрестный запрос, группирующий итоги продаж по городам. Запрос использует два выражения для возврата форматированной даты и общего объема продаж.
Создание перекрестного запроса
- На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов.
- Дважды щелкните таблицы, которые нужно использовать в запросе. Таблица появится в виде окна в верхней части конструктора запросов. При использовании примеров таблиц дважды щелкните таблицы "Заказы" и "Сведения о заказах".
- Дважды щелкните поля, которые вы хотите использовать в запросе. Каждое имя поля отображается в пустой ячейке в строке Поле сетки конструктора. При использовании примеров таблиц добавьте поля "Город назначения" и "Дата отгрузки" из таблицы "Заказы".
- В следующей пустой ячейке в строке Поле скопируйте и вставьте или введите следующее выражение:
Total Sales: Sum(CCur([Order Details].[Unit Price]*[Quantity]*(1-[Discount])/100)*100) - На вкладке Конструктор запроса в группе Тип запроса щелкните Перекрестная вкладка. В бланке запроса будут отображены строки Итог и Перекрестный.
- Щелкните ячейку в строке Итог в поле "Город назначения" и выберите Группировка. Выполните те же действия для поля "Дата отгрузки". Измените значение в ячейке Итоги поля "Итоги продаж" на Выражение.
- В строке Перекрестная диаграмма задайте для ячейки в поле Город значение Заголовок строки, в поле Дата доставки — заголовок столбца, а для поля Всего продаж — значение.
- На вкладке Конструктор запросов в группе Результаты нажмите кнопку Выполнить. Результаты запроса будут отображены в режиме таблицы.
Справочные сведения об агрегатных функциях
В следующей таблице перечислены и отписаны агрегатные функции Access, которые можно использовать в строке итогов и в запросах. Помните, что в Access предусмотрено больше агрегатных функций для запросов, чем для строки итогов.
| Функция | Описание | Поддерживаемые типы данных |
|---|---|---|
| Среднее | Вычисляет среднее значение для столбца. Столбец должен содержать числовые или денежные величины или значения даты или времени. Функция игнорирует пустые значения. | "Число", "Денежный", "Дата/время" |
| Count | Подсчитывает число элементов в столбце. | Все типы данных, за исключением сложных повторяющихся скалярных данных, таких как столбец многозначных списков. Дополнительные сведения о многозначных списках см. в статье Создание или удаление многозначного поля. |
| Максимум | Возвращает элемент, имеющий наибольшее значение. Для текстовых данных наибольшим будет последнее по алфавиту значение, причем Access не учитывает регистр. Функция игнорирует пустые значения. | "Число", "Денежный", "Дата/время" |
| Minimum | Возвращает элемент, имеющий наименьшее значение. Для текстовых данных наибольшим будет первое по алфавиту значение, причем Access не учитывает регистр. Функция игнорирует пустые значения. | "Число", "Денежный", "Дата/время" |
| Standard Deviation | Показывает, насколько значения отклоняются от среднего. Дополнительные сведения об этой функции см. в статье Отображение итогов по столбцу в таблице. |
"Число", "Денежный" |
| Sum | Суммирует элементы в столбце. Подходит только для числовых и денежных данных. | "Число", "Денежный" |
| Variance | Вычисляет статистическую дисперсию для всех значений в столбце. Подходит только для числовых и денежных данных. Если таблица содержит менее двух строк, Access возвращает пустое значение. Дополнительные сведения о функциях для расчета дисперсии см. в разделе Отображение итогов по столбцу в таблице. |
"Число", "Денежный" |