Подсчет данных при помощи запроса

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

Предполагаемое действие:


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

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

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

  • открыть запрос в режиме таблицы и добавить строку итогов. Строка итогов — новое средство в Office Access 2007, позволяющее использовать статистические функции в одном или нескольких столбцах в результатах запроса без необходимости изменять структуру запроса;
  • создать итоговый запрос. Итоговый запрос вычисляет промежуточные итоги для групп записей, строка итогов — общие итоги для одного или нескольких столбцов (полей) данных. Например, при необходимости вычисления промежуточных сумм по всем продажам по городам или по кварталам, следует использовать итоговый запрос для группировки записей по нужной категории, а затем просуммировать все объемы продаж;
  • создать перекрестный запрос. Перекрестный запрос — это особый тип запросов, отображающий результаты в сетке, по виду схожей с листом Microsoft Office Excel 2007. Перекрестные запросы суммируют значения и затем группируют их по двум наборам фактических данных — один набор вдоль боковой стороны (заголовки строк) и второй набор вдоль верхней части (заголовки столбцов). Например, можно использовать перекрестный запрос для отображения итоговых значений продаж для каждого города за последние три года, как показано в следующей таблице:
Город 2003 2004 2005
Краснодар 254 556 372 455 467 892
Санкт-Петербург 478 021 372 987 276 399
Москва 572 997 684 374 792 571
... ... ... ...

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

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

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

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

К началу страницы К началу страницы

Подготовка образцов данных

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

В приложении Access имеется несколько способов добавить таблицы с примерами в базу данных. Данные можно ввести вручную или скопировать каждую таблицу в программу электронных таблиц, например в приложение Microsoft Office Excel 2007, а затем импортировать листы в приложение Access. Также можно вставить данные в текстовый редактор, например в Блокнот, а затем импортировать информацию из текстового файла.

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

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

Таблица Категории:

Категория
Куклы
Игры и головоломки
Картины и рамы
Видеоигры
DVD-диски и фильмы
Модели для сборки, хобби
Спортивное снаряжение

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

Наименование товара Цена Категория
Фигурка программиста 12,95р. Куклы
Забава C# (Настольная игра для всей семьи) 15,85р. Игры и головоломки
Схема реляционной базы данных 22,50р. Картины и рамы
Волшебная микросхема (500 частей) 32,65р. Игры и головоломки
Игра Access! 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 Игра Access! 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%

 Примечание.   Помните, что в типичной базе данных таблица «Заказано» будет содержать только поле «Код Товара», без поля «Наименование товара». В данном образце таблицы используется поле «Наименование товара», чтобы сделать данные более простыми для восприятия.

Ввод образца данных вручную

  1. На вкладке Создание в группе Таблицы щелкните Таблица.

В приложении Access новая пустая таблица добавляется к базе данных.

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

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

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

Новое поле в таблице

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

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

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

Клавиши быстрого доступа  Нажмите клавиши CTRL+S.

Появится диалоговое окно Сохранить как.

  1. В поле Имя таблицы введите имя таблицы примера, а затем нажмите кнопку ОК.

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

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

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

Создание образца листа Excel

  1. Запустите программу электронных таблиц и создайте новый пустой файл. Если используется приложение Excel, то новая книга будет создана по умолчанию.
  2. Скопируйте первую приведенную выше таблицу и вставьте ее в первый лист, начиная с первой ячейки.
  3. Следуя инструкциям используемой программы электронных таблиц, переименуйте лист, назвав его так же, как и таблицу примера. Например, если таблица примера называется Категории, то лист следует назвать точно так же.
  4. Повторите шаги 2 и 3, копируя каждую таблицу примера на пустой лист и изменяя имя листа.

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

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

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

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

Или

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

Появится диалоговое окно Внешние данные — Имя программы Электронная таблица.

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

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

  1. По умолчанию мастер выбирает первый лист в книге (если вы следовали шагам в предыдущем разделе, это лист «Клиенты»). Данные из листа отображаются в нижней части страницы мастера. Нажмите кнопку Далее.
  2. На следующей странице мастера выберите Первая строка содержит названия столбцов, а затем нажмите кнопку Далее.
  3. При необходимости на следующей странице мастера воспользуйтесь текстовыми полями и списками в группе Описание поля, чтобы изменить имена полей и типы данных или исключить поля из операции импорта. В противном случае нажмите кнопку Далее.
  4. Оставьте выбранным параметр Автоматически создать ключ, а затем нажмите кнопку Далее.
  5. По умолчанию Access применяет имя листа к новой таблице. Оставьте это имя или введите другое, а затем нажмите кнопку Готово.
  6. Повторите шаги с 1 по 7, пока не будет создано по таблице на каждый лист в книге.

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

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

  1. В области переходов щелкните правой кнопкой мыши каждую таблицу, созданную на предыдущем шаге, и выберите команду Конструктор.
  2. Найдите поле первичного ключа в каждой таблице. По умолчанию Microsoft Access называет эти поля Код.
  3. В столбце Имя поля каждого поля первичного ключа добавьте имя таблицы.

Например, переименуйте поле «Код» таблицы «Категории» в «Код категории», а поле таблицы «Заказы» — в «Код заказа». В таблице «Заказано» переименуйте поле в « Код сведений». В таблице «Товары» переименуйте поле в «Код товара».

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

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

К началу страницы К началу страницы

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

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

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

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

Выбранные таблицы отображаются в виде окон в верней части конструктора запросов.

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

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

Каждое поле отображается в ячейке в бланке запроса.

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

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

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

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

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

-или-

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

  1. На вкладке Начальная страница в группе Записи выберите команду Итоги.

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

  1. В строке Итог щелкните ячейку в поле, по которому необходимо провести суммирование, и выберите в списке функцию 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 Игра Access! 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

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

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

При использовании образцов данных дважды щелкните таблицу «Заказы».

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

  1. Дважды щелкните поле, которое требуется просуммировать. Убедитесь, что поле имеет тип данных «Числовой» или «Денежный». При попытке суммировать значения в нечисловых полях, например текстовом, Access выводит сообщение об ошибке Несоответствие типов данных в выражении условия отбора.

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

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

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

Строка Итог будет отображена в бланке запроса, а строка Группировка — в столбце «Стоимость доставки».

  1. Измените значение в ячейке в строке Итог на Sum.
  2. Нажмите кнопку ВыполнитьИзображение кнопки, чтобы выполнить запрос и отобразить результаты в режиме таблицы.

 Совет.   Обратите внимание на то, что в приложении Access «Сумма_» присоединяется к началу имени поля, в котором производится суммирование. Чтобы заменить заголовок столбца на более понятное имя, например Всего поставлено, переключитесь в режим конструктора и щелкните строку Поле столбца «Стоимость доставки» в бланке запроса. Поместите курсор рядом со словами Стоимость доставки и введите слова Всего поставлено с последующим знаком двоеточия, например: Всего поставлено: Стоимость доставки.

  1. При необходимости сохраните запрос и закройте его.

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

  1. На вкладке Создание в группе Другие щелкните Конструктор запросов.
  1. В диалоговом окне Добавление таблицы дважды щелкните таблицы «Заказы» и «Заказано», а затем нажмите кнопку Закрыть, чтобы закрыть диалоговое окно.
  2. Добавьте поле «Дата заказа» из таблицы «Заказы» в первый столбец в бланке запроса.
  3. В строке Условия отбора первого столбца введите Date() -1. Это выражение исключает записи с текущей датой из вычисляемого итогового значения.
  4. Затем создайте столбец, вычисляющий объемы продаж для каждой операции. Введите в строку Поле второго столбца в бланке запроса следующее выражение:

Значение объемов продаж: (1-[Заказано].[Скидка]/100)*([Заказано].[Цена]*[Заказано].[Количество]).

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

  1. На вкладке Конструктор в группе Скрыть/Отобразить выберите команду Итоги.

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

  1. Измените значение во втором столбце в ячейке строки Итог на Sum. Функция Sum добавляет объемы продаж для каждой операции.
  2. Нажмите кнопку ВыполнитьИзображение кнопки, чтобы выполнить запрос и отобразить результаты в режиме таблицы.
  3. Сохраните запрос как Продажи за день.

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

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

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

К началу страницы К началу страницы

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

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

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

Таблица «Товары»

Код Товара Наименование товара Цена Категория
1 Фигурка программиста 12,95р. Куклы
2 Забава C# (Настольная игра для всей семьи) 15,85р. Игры и головоломки
3 Схема реляционной базы данных 22,50р. Картины и рамы
4 Волшебная микросхема (500 частей) 32,65р. Картины и рамы
5 Игра Access! 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 Игра Access! 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%

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

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

  1. На вкладке Создание в группе Другие щелкните Конструктор запросов.
  1. В диалоговом окне Добавление таблицы выберите таблицы, с которыми будет производиться работа, нажмите кнопку Добавить, а затем нажмите кнопку Закрыть после завершения добавления таблиц.

или

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

При использовании образцов таблиц, представленных выше, добавьте таблицы «Товары» и «Заказано».

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

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

  1. Добавьте поле «Категория» из таблицы «Товары» в бланк запроса.
  2. Создайте столбец, вычисляющий объемы продаж для каждой операции, введя во второй столбец бланка запроса следующее выражение:

Значение объемов продаж: (1-[Заказано].[Скидка]/100)*([Заказано].[Цена]*[Заказано].[Количество])

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

  1. На вкладке Конструктор в группе Скрыть/Отобразить выберите команду Итоги.

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

  1. Измените значение во втором столбце в строке Итоги на Sum. Функция Sum добавляет объемы продаж для каждой операции.
  2. Нажмите кнопку ВыполнитьИзображение кнопки, чтобы выполнить запрос и отобразить результаты в режиме таблицы.
  3. Оставьте запрос открытым, чтобы использовать его в следующем разделе.

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

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

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

  • Условия, игнорирующие некоторые группы при вычислении итогов.    Например, можно вычислить итоги только для категорий товаров «Видеоигры», «Картины и рамы» и «Спортивное снаряжение».
  • Условия, скрывающие некоторые итоговые значения после их вычисления.    Например, можно отобразить только итоговые значения свыше 150 000р.
  • Условия, исключающие некоторые записи при вычислении итогового значения.    Например, можно исключить отдельные операции, в которых значение (Цена * Количество) ниже 100р.

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

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

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

или

В области переходов щелкните правой кнопкой запрос и выберите Конструктор.

  1. В строке Условия отбора столбца «Код категории» введите =Куклы Or Спортивное снаряжение or Картины и рамы.
  2. Нажмите кнопку ВыполнитьИзображение кнопки, чтобы выполнить запрос и отобразить результаты в режиме таблицы.
  3. Переключитесь в режим конструктора и в строке Условия отбора столбца «Значение объемов продаж» введите >100.
  4. Выполните запрос, чтобы просмотреть результаты, а затем переключитесь в режим конструктора.
  5. Добавьте условия, исключающие операции менее чем на 100р. Для этого необходимо добавить другой столбец.

 Примечание.   Невозможно указать третье условие для столбца «Значение объемов продаж». Любое условие, указанное для этого столбца, будет применено к итоговому значению, а не к отдельным значениям.

  1. Скопируйте выражение из второго столбца в третий столбец.
  2. В строке Итог нового столбца выберите Условие, а в строке Условия отбора введите >20.
  3. Выполните запрос, чтобы просмотреть результаты, а затем сохраните запрос.

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

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

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

К началу страницы К началу страницы

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

Перекрестный запрос — это особый тип запросов, отображающий результаты в сетке, схожей с листом 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 Игра Access! 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 Gloom 11 53,33р. 6 8%
7 Схема реляционной базы данных 3 22,50р. 4 9%

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

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

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

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

При использовании образцов таблиц дважды щелкните таблицы «Заказы» и «Заказано».

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

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

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

  1. В следующую пустую ячейку строки Поле скопируйте и вставьте или введите следующее выражение: Значение объемов продаж: Sum((1-[Заказано].Скидка/100)*([Заказано].[Цена]*[Количество]*(1-[Скидка])/100)*100)
  2. На вкладке Конструктор в группе Тип запроса выберите команду Перекрестный.

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

  1. Щелкните ячейку в строке Итог в поле «Город назначения» и выберите Группировка. Выполните те же действия для поля «Дата исполнения». Измените значение в ячейке Итоги поля «Объем продаж» на Выражение.
  2. В строке Перекрестный задайте ячейке в поле «Город назначения» значение Заголовки строк, полю «Дата исполнения» — значение Заголовки столбцов, а полю «Объем продаж» — значение Значение.
  3. На вкладке Конструктор в группе Результаты выберите команду Запуск.

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

К началу страницы К началу страницы

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

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

Функция Описание Обрабатываемые типы данных
Average Вычисляет для столбца среднее значение. Столбец должен содержать числовые или денежные величины или значения даты или времени. Функция игнорирует пустые значения. «Числовой», «Денежный», «Дата/время»
Count Подсчитывает количество элементов в столбце.

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

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

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

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

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

«Числовой», «Денежный»
Sum Суммирует элементы в столбце. Работает только с числовыми или денежными данными. «Числовой», «Денежный»
Variance

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

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

«Числовой», «Денежный»

К началу страницы К началу страницы

 
 
Применимо к:
Access 2007