Назначение групповых операций

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

В Access предусматривается девять статистических функций:

• sum - сумма значений некоторого поля для группы;
• Avg - среднее от всех значений поля в группе;
• мах, Min - максимальное, минимальное значение поля в группе;
• count - число значений поля в группе без учета пустых значений;
• StDev - среднеквадратичное отклонение от среднего значения поля в группе;
• var - дисперсия значений поля в группе;
• First и Last - значение поля из первой или последней записи в группе.

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

Порядок создания запроса с использованием групповых операций

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

Выполняется команда Вид|Групповые операции (View|Totals) или на панели инструментов конструктора запросов нажимается кнопка Групповые операции (Totals). Можно также нажать правую кнопку мыши и выбрать в контекстном меню Групповая операция (Total) (курсор мыши должен быть уста-1новлен в бланке запроса). В бланке запроса появляется строка Групповая Операция (Total), в которой для всех полей записано группировка (Group By).

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

Рассмотрим конструирование запроса с групповой операцией на примере таблицы ПОСТАВКА_ПЛАН.

Запрос с функцией Sum

Определим, какое суммарное количество каждого из товаров должно быть Обставлено покупателям по договорам. Все данные о запланированном к Доставке количестве товара указаны в таблице ПОСТАВКА_ПЛАН.

Создадим запрос на выборку для таблицы ПОСТАВКА_ПЛАН. Из списка таблицы перетащим в бланк запроса поле код_тов - код товара. Это поле создадим для дальнейшей группировки по нему. Перетащим в бланк запроса поле кол_пост, по которому будет вычисляться функция sum для подсчета суммарного количества конкретного товара, заказанного во всех договорах.

Нажмем кнопку Групповые операции (Totals). Заменим слово группировка

(croup By) в столбце кол_пост на функцию Sum. Для этого вызовем список и выберем эту функцию. Бланк запроса примет вид, показанный на рис. 4.13.

В поле код_тов отображается не код товара, сохраняемый в таблице ПОСТАВКА_ПЛАН, а его наименование. Это определяется тем, что в таблице для поля код_тов построен список на основе таблицы товар. Если в вашей базе данных поле код_тов не преобразовано в поле со списком, как показано в главе 3, в таблице запроса будет отображаться код товара.

Подпись поляSum - КОЛ_ПОСТ можно заменить наЗаказано товаров . Дляввода этой подписи перейдем в режим конструктора, в бланке запроса установим курсор мыши на поле кол_пост и нажмем правую кнопку. В контекстном меню выберем Свойства (Properties). В окне Свойства поля (Field Properties) наберем в строке Подпись (Caption) - заказано товаров.

Таблица результата после доработки запроса показана на рис. 4А5.

Рис. 4.15. Таблица результата с измененной подписью поля

Сохраним запрос-выборку под именем "Заказано товаров".

Запрос с функцией Count

Определим, сколько раз отгружался товар по каждому из договоров. Факт отгрузки фиксируется документом "Накладная".

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

Нажмем кнопку Групповые операции (Totals). Заменим слово группировка (Group By) в одном из столбцов с именем ном_дог на функцию count. Бланк запроса примет вид, показанный на рис. 4.20.

Сохраним запрос под именем "Число отгрузок по договорам". Результат выполнения запроса показан на рис. 4.21.

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

1. В окне базы данных щелкните на кнопке Запросы .

2. Дважды щелкните на значке Создание запроса в режиме конструктора .

3. В открывшемся окне диалога (рис. 17.6) выделите строку Контакты.

4. Щелчком на кнопке Добавить добавьте выбранную таблицу в верхнюю область конструктора запроса.

5. Выделите пункт Список и снова щелкните на кнопке Добавить .

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

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

Рис. 17.6 . Добавление таблицы

8. Перетащите поле Фамилия в ячейку Поле первого столбца конструктора.

9. В ту же ячейку второго столбца перетащите поле Имя таблицы Контакты.

10. В третий, четвертый и пятый столбцы бланка запроса перетащите поле Дата таблицы Список (рис. 17.7).

11. В раскрывающемся списке ячейки Групповая операция третьего столб ца бланка запроса выберите пункт Min .

12. В той же ячейке четвертого столбца выберите пункт Мах .

13. В пятом столбце задайте групповую операцию Count. Групповые операции построенного запроса обработают все записи таблицы Список, соответствующие конкретному человеку из таблицы Контакты, и вместо самих данных таблицы Список выведут в соответствующее поле результата запроса только значение величины, вычисляемой по определенной формуле. Доступные групповые операции перечислены в табл. 17.1.

ТАБЛИЦА 17.1 . Групповые операции

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

14. Щелкните на кнопке Вид , чтобы выполнить запрос. Появится таблица с пятью столбцами. Два первых столбца содержат фамилии и имена людей. По ним выполняется группировка, то есть расчет значений остальных полей запроса выполняется для записей таблицы Список, сопоставляемых с одним человеком. Как уже говорилось выше, соответствие контакта таблицы Список и человека из таблицы Контакты определяется полями Код_Контакты, с помощью которых осуществляется связь этих двух таблиц. Третий и четвертый столбцы запроса выводят соответственно дату первого (функция Min) и последнего (функция Мах) контакта с данным человеком. Пятый столбец содержит количество записей в таблице Список (функция Count), соответствующих. данному человеку, то есть число контактов с ним. Единственный недостаток построенного запроса - это непонятные имена столбцов. Давайте скорректируем их.

15. Щелчком на кнопке Вид вернитесь в конструктор запроса.

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

17. В ячейке Поле четвертого столбца введите Дата последнего контакта: Дата.

18. В первой строке пятого столбца бланка запроса введите Число контактов: Дата.

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

19. Снова щелкните на кнопке Вид .

20. Закройте запрос.

21. Для сохранения изменений структуры щелкните на кнопке Да .

22. В окне диалога Сохранение введите имя Итоговый запрос и щелкните на кнопке ОК .

Сегодня поговорим на тему «Групповые операции в запросах Access». Групповые операции в запросах Access позволяют выделить группы записей с одинаковыми значениями в указанных полях и вычислить итоговые данные для каждой из групп по другим полям, используя одну из статистических функций. Статистические функции применимы, прежде всего, к полям с типом данных Числовой, Денежный, Дата/время.
В Access предусматривается девять статистических функций:

• Sum - сумма значений некоторого поля для группы;
• Avg - среднее от всех значений поля в группе;
• Max, Min - максимальное, минимальное значение поля в группе;
• Count - число значений поля в группе без учета пустых значений;
• StDev - среднеквадратичное отклонение от среднего значения поля в группе;
• Var - дисперсия значений поля в группе;
• First и Last - значение поля из первой или последней записи в группе.

Результат с использованием групповых операций содержит по одной записи для каждой группы. В запрос, прежде всего, включаются , по которым производится группировка, и поля, для которых выполняются статистические функции. Кроме этих полей в запрос могут включаться поля, по которым задаются условия отбора.
Рассмотрим конструирование однотабличного запроса с групповой операцией на примере таблицы ПОСТАВКА_ПЛАН.

Запрос с функцией Sum

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

1. Создайте в режиме конструктора запрос на выборку из таблицы ПОСТАВКА_ПЛАН.
2. Из списка таблицы перетащите в бланк запроса поле КОД_ТОВ ― код товара. По этому полю будет производиться группировка записей таблицы.
3. Перетащите в бланк запроса поле КОЛ_ПОСТ, по которому будет подсчитываться суммарное количество каждого из товаров, заказанных во всех договорах.
4. Выполните команду Итоги (Totals) из группы Показать или скрыть (Show/Hide). В бланке запроса появится новая строка Групповая операция (Total) со значением Группировка (Group By) в обоих полях запроса.
5. В столбце КОЛ_ПОСТ замените слово Группировка (Group By) на функцию Sum. Для этого вызовите список и выберите эту функцию. Бланк запроса примет вид, показанный на рис. 4.11.
6. Для отображения результата запроса (рис. 4.12) щелкните на кнопке Выполнить (Run) в группе Результаты (Results).
7. Замените подпись поля Sum-КОЛ_ПОСТ на Заказано товаров. Для этого перейдите в режим конструктора, в бланке запроса установите курсор мыши на поле КОЛ_ПОСТ и нажмите правую кнопку. В контекстном меню выберите Свойства (Properties). В окне Свойства поля (Field Properties) введите в строке Подпись (Caption) - Заказано товаров. Для открытия окна свойств может быть выполнена команда Страница свойств (Property Sheet) в группе Показать или скрыть (Show/Hide).

1. Сохраните под именем Заказано товаров.
2. Чтобы подсчитать количество товаров, заказанных в каждом месяце, выполните группировку по двум полям: КОД_ТОВ и СРОК_ПОСТ, в котором хранится месяц поставки (рис. 4.13).
3. Чтобы подсчитать количество товаров, заказанных в заданном месяце, предыдущий запрос дополните вводом параметра запроса в условие отбора (рис. 4.14).

Запрос с функцией Count

Задача. Определите, сколько раз отгружался товар по каждому из договоров. Факт отгрузки фиксируется в таблице НАКЛАДНАЯ.

1. Создайте запрос на выборку на основе таблицы НАКЛАДНАЯ.
2. Из списка полей таблицы НАКЛАДНАЯ перетащите в бланк запроса поле НОМ_ДОГ. По этому полю должна производиться группировка.
3. По сути, смысл задачи сводится к подсчету в таблице числа строк с одинаковым номером договора, поэтому неважно по какому полю будет вычисляться функция Count. Перетащите в бланк запроса любое поле, например опять НОМ_ДОГ.
4. Выполните команду Итоги (Totals) из группы Показать или скрыть (Show/Hide). Замените слово Группировка (Group By) в одном из столбцов с именем НОМ_ДОГ на функцию Count. Бланк запроса примет вид, показанный на рис. 4.15.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

   Выполнить .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Сегодня мы подробно поговорим о запросах в Access.

Запросы, как вы уже знаете, нужны для работы с данными, находящимися в таблицах.
Чтобы создать запрос…
1) …в окне базы данных открываете Запросы
2) …и создаете запрос с помощью конструктора.

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

Отсев пустых строк

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

Почему это происходит?
Дело в том, что у нас в таблице tbPerson наряду с владельцами собак записаны и судьи (Петровская, Елец, Терещук). Судьи не имеют права привозить своих собак на выставку, поэтому в строках с их фамилиями пустые ячейки с кличками собак.
Есть два способа удалить пустые строки.
1. Поставить условие на значение клички собаки Is not Null , т.е. НЕ ПУСТА.

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

Вопрос вам: какие настройки нужно поменять в диалоговом окне параметров объединения?

Запросы с вычислениями

До сих пор мы только отбирали записи по различным условиям. Но Access позволяет не только просматривать данные, записанные в таблицах, но и производить ВЫЧИСЛЕНИЯ: по дате рождения определять возраст; из имени, фамилии и отчества делать фамилию с инициалами; по цене единицы товара и его количества определять общую стоимость покупки; по дате выдачи книги в библиотеке определять величину штрафа при задолженности и многое другое. Для вычислений используются встроенные функции (похожие на те, что есть в Excel).

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

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

Выражения для вычислений записываются в самой верхней строке (Поле ) области условий. До сих пор мы писали условия в нижних строках (Условия отбора ).

Чтобы вам не запутаться: в верхней строке мы пишем, ЧТО выводить на экран, а затем (в нижней) – при КАКОМ УСЛОВИИ .

Задание : Напишите выражение для вывода в одной ячейке фамилии владельца и в скобках города, в котором он живет. Вот так: Иванов (Москва) . Город и фамилия должны подставляться из таблицы.

Построитель выражений

Чтобы удобнее было редактировать выражения, существует специальный редактор – «Построитель выражений». Он выглядит так:

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

В построителе выражений вы можете выбирать из библиотеки функции:

и данные из таблиц (можно пользоваться ТОЛЬКО теми таблицами, которые используются в данном запросе и отображены в области данных):

Когда вы двойным щелчком мыши выбираете из списка имя поля или функцию, Access часто вставляет еще слово «выражение», показывая, что НА ЭТО МЕСТО можно вставить другие функции и имена полей. Не забывайте удалять лишние слова «выражение»!

Мы познакомимся с текстовыми и временными функциями, а также с условным оператором Iif(condition; if-true; if-false).

Текстовые функции позволяют преобразовывать строковые переменные:
Left(«Иванов»; 2) = «Ив» оставляет n левых символов
LCase(«Иванов») = иванов делает все буквы строчными
InStr(1; «Иванов»; «но») = 4 находит подстроку (третий аргумент) в строке (второй аргумент), и равняется позиции (от начала) подстроки в строке
Len («Иванов») = 6 выводит количество символов в строке
StrComp(«Иванов»; «Петров») = -1 сравнивает две строки: если они равны, то выдает 0
и другие…

Временные позволяют работать с временными переменными:
Month(#12.04.2007#) = 4
Year(#12.04.2007#) = 2007
Day(#12.04.2007#) = 12.
Now() = 28.04.2008 14:15:42 (текущие дата и время)
Date() = 28.04.2008 (сегодняшняя дата)
DateDiff(«d»; #12.04.2007#; #28.04.2007#) = 16 находит разницу между двумя датами («d» – в днях, “ww”- в неделях, “m” – в месяцах, “yyyy” – в годах и др.)
и другие…

Логические обрабатывают условные выражения:
Iif(<=1; «щенок»; «взрослый») аналог функции ЕСЛИ из Ecxel.
и другие…

Задание : напишите выражение, которое из фамилии, имени и отчества делает фамилию с инициалами. Иванов Иван Иванович -> Иванов И.И.
Задание
Задание
Дополнение : Есть два способа вычислить возраст собаки: один более точный, другой менее:
1) из текущего года вычесть год рождения собаки;
2) с помощью функции DateDiff посчитать, сколько дней прошло с рождения до сегодняшнего дня. Примените в одном из заданий один способ, в другом – другой.

Запросы с параметром

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

Устроен этот запрос так:

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

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

Запросы с группировкой

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

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

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

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

Добавляем в область таблиц таблицу с оценками (tbMarks). Группируем оценки по турнирному номеру собаки и среди групповых операций выбираем среднее значение Avg (от англ. average – среднее).

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

Можно также округлить значения до одного знака после запятой: Round(+;1)

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

Запросы на изменение, удаление, добавление

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

Оператору базы данных накануне выставки пришла новая информация:
1) собака Дези заболела и не сможет принять участия в выставке;
2) по ошибке Гардиен, являющийся на самом деле английским сеттером, был указан как ирландский сеттер;
3) владелец Мигунова подала заявку на участие в выставке еще одной своей собаки (кличка: Гарри, порода: сеттер-гордон, пол: м, дата рождения: 15.09.07).

Приступим к редактированию базы данных.
1) Удалить запись с Дези.
Создаем запрос quDelDog. Тип запроса – на удаление. Со сменой типа запроса несколько изменяется и область условий. Появилась новая ячейка Удаление . Под ней вы указываете условие, по которому нужно отобрать записи, подлежащие удалению. Даже если вы указываете условие отбора для одного поля, удалена будет ВСЯ запись.

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

2) У Гардиена изменить ирландский сеттер на английский сеттер.
Создаем запрос quUpdateDog. Тип запроса – на обновление. Находим Гардиена и обновляем у него породу.

Откройте таблицу tbDog и убедитесь, что у Гардиена порода – английский сеттер.

3) Добавить запись с Гарри.
Создаем запрос quAddDog. Тип запроса – на добавление. У запросов на добавление есть одна особенность: в области таблиц отображены НЕ те таблицы, КУДА вы добавляете запись, а ОТКУДА вы берете данные (если это требуется). Целевую таблицу (в которую добавляются записи) вы указываете в диалоговом окне, которое появляется сразу, как только вы установите тип запроса (на добавление):

Поскольку мы не берем данные из других таблиц, а создаем новую запись, то область таблиц должна быть ПУСТОЙ! (там не должно быть ни одной таблицы). В области условий в строке Поле вы пишете, ЧТО добавить (новое значение для каждого поля), а в строке Добавление КУДА (имена полей):

Откройте таблицу tbDog и убедитесь, что Гарри в ней появился!

Язык запросов SQL

Когда вы нажимаете на «восклицательный знак», запрос выполняется. Так это представляется на взгляд новичка.
Профессионалы знают, что на самом деле в этот момент выполняется инструкция на специальном языке запросов SQL. Дело в том, что Access – не единственная система управления базами данных (СУБД). Может быть, вы слышали о таких СУБД в Интернете как MySQL, FreeBSD??? Access просто предлагает очень удобный интерфейс для работы с БД, а в других СУ нет никакой кнопочки с восклицательным знаком. Зато там обязательно есть специальное окошко, в котором можно писать SQL-инструкции.
Access тоже позволяет редактировать запросы в режиме SQL-инструкций:

Правила языка SQL не так сложны. Вы можете сами в этом убедиться! Сделайте простой запрос на выборку (например, выведите кличку, породу и дату рождения собаки по кличке Гарри). Теперь откройте запрос на поиск Гарри в режиме SQL!
Инструкция устроена очень просто:
SELECT поле1, поле2,…
FROM таблица1, таблица2,…
WHERE условие1, условие2,…

Откройте теперь запросы на обновление, изменение, удаление (quDelDog, quUpdateDog, quAddDog) в режиме SQL и выпишите на листе бумаги шаблоны их SQL-инструкций (как это только что было сделано для запроса на выборку).

Запросы с параметром, запросы на группировку, запросы с вычислениями представляют собой такие же SQL-запросы, но только с несколько более сложными условиями отбора. Язык SQL – незаменимый инструмент для тех, кто работает с базами данных!

Задания

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

Их немало! Но, коль скоро вы их освоили, вы сможете отыскать в базе данных любую информацию.

Проверьте свои знания! Выполните в вашей базе данных следующие запросы (или переименуйте уже выполненные так, чтобы их имена совпадали с именами задач):

1. выборка

quSelectDog : Найдите всех шар-пеев и сеттеров-гордонов из регионов (НЕ из Москвы); используйте оператор «не равно».

2. оператор Like

quLike : Найдите всех абонентов МТС (те, у кого номер мобильного телефона начинается с 8(916)…).

3. выражения

quEvalText : Напишите выражение, которое из фамилии, имени и отчества делает фамилию с инициалами. Иванов Иван Иванович -> Иванов И.И.
quEvalDate : напишите выражение, которое вычисляет, сколько собаке лет по дате ее рождения.
quEvalIif : напишите выражение, которое определяет по возрасту, в какой возрастной категории выступает собака: «щенок» - до года; «юниор» - от года до двух лет; «сеньор» - старше двух лет.
Дополнение : Примените в одном задании один способ вычислить возраст собаки, в другом – другой: 1) из текущего года вычесть год рождения собаки; 2) с помощью функции DateDiff посчитать, сколько дней прошло с рождения до сегодняшнего дня.

4. параметр

quParametre : Создайте запрос, который будет выводить всех собак по фамилии владельца, являющейся свободным параметром.

5. группировка

quGroup : Выясните, какая собака вызвала наиболее противоречивые мнения судей.

6. редактирование БД

Незадолго до выставки владелец Гороховец уехал на ПМЖ (постоянное место жительства) в Германию и передал всех собак своему другу Карпову Михаилу Игоревичу. Требуется:
1) quAddOwner : добавить запись о новом владельце;
2) quUpdateOwner : изменить у собак Гороховца ID владельца на ID Карпова;
3) quDelOwner : удалить Гороховца из базы данных.

Жду ваших БД с выполненными заданиями, а также шаблоны SQL-инструкций для запросов на обновление, изменение, удаление.