Лабораторная работа №5.

Запросы  

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

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

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

1. Типы запросов

     Запросы, которые можно использовать в Access, можно разделить на 5 основных категорий, которые поименованы в Таблице 1.

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

2. Запросы на выборку

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

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

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

     Далее выберите таблицу Клиенты и передвиньте её поля в список выбранных полей (рис. 1). Аналогично добавьте в список поля таблицы Автомобили.

Рис.1. Создание простого запроса

     Нажмите кнопку Готово. В результате будет выполнен запрос, вид которого показан на рис. 2. 

Рис.2. Таблица, созданная простым запросом

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

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

1. Запустите Мастер запросов с помощью команды Создание > Другие > Мастер запросов и выберите в первом окне Мастера пункт Простой запрос.

2. В следующем окне выберите для запроса следующие поля: Водитель из таблицы Автомобили, Дата и Оплата из таблицы Заказы.

3. В следующем окне выберите опцию итоговый и нажмите кнопку Итоги (рис. 3). В новом окне для подсчета итоговой суммы установите флажок на опциях подсчет числа записей в Заказы.и Sum (Сумма).

     Другие флажки имеют следующие значения:
      • Avg – среднее арифметическое;
      • Min – минимальное значение;
     • Max – максимальное значение.

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

4. Далее в новом окне выберите опцию Интервал группировки дат - По месяцам и нажмите кнопку Готово.

     В результате будет выполнен запрос и получена следующая таблица (рис. 4):

Рис. 4. Результат итогового простого запроса

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

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

     Для удобства в дальнейшем использовании замените имя поля ФамилияИмя таблицы Клиенты на имя Клиент.

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

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

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

Рис. 5. Создание запроса с помощью Конструктора

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

Рис. 6. Отмененные заказы  в период с 08.10.2014 г. по 09.10.2014 г.

2.4. Изменение порядка расположения полей и удаление полей из запроса

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

     Если вы по ошибке перенесли в бланк запроса поле (или несколько полей), которые вам не нужны, то выделите столбец и нажмите клавишу Delete.
 

2.5. Параметрический запрос

     В некоторых случаях может возникнуть потребность получить данные по отдельным их группам, не выводя в таблицу остальные. При этом может оказаться так, что заранее не известно какие итоговые данные могут понадобиться в данный момент. В этом случае, использование фиксированных значений критериев отбора в бланке запроса, оказывается непригодным.
     Access для решения этой задачи вместо того, чтобы в строку Условие отбора вводить конкретное значение, предлагает ввести в нее текст, заключенный в квадратные скобки []. Например, можно в Конструкторе запросов выбрать все поля таблицы Заказы и в условие отбора по полю Карточка записать [Введите номер карточки] (рис. 7).

Рис. 7. Параметрический запрос
 

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

Рис. 8. Окно параметрического запроса

     Результатом такого запроса будет таблица (рис. 9):

Рис. 9. Результат параметрического запроса

 

2.6. Запросы с итогами

 

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

Таблица 1
Функция	Групповая операция операция
Sum	Вычисление суммы всех значений заданного поля для каждой группы. Функция используется только для числовых и денежных полей.
Avg	Вычисление среднего значение в каждой из групп заданного поля. Функция используется только для числовых и денежных полей.
Min	Находится наименьшее значение в каждой группе заданного поля. Для числовых полей возвращается наименьшее значение, а для текстовых – наименьшее из символьных значений не зависимо от регистра (нулевые значения исключаются).
Max	Находится наибольшее значение в каждой группе заданного поля. Для числовых полей возвращается наибольшее значение, а для текстовых – наибольшее из символьных значений не зависимо от регистра. (нулевые значения исключаются).
Count	Возвращается число записей, значение которых отличны от нуля, в каждой группе заданного поля. Для подсчета числа записей с учетом нулевых значений в строку «Поле№» необходимо ввести выражение «Count(*)».
StDev	Рассчитывает стандартное отклонение для всех значений заданного поля в каждой группе. Используется только для числовых полей или полей денежного типа.
Var	Рассчитывает величину дисперсии для всех значений заданного поля в каждой группе. Используется только для числовых полей или полей денежного типа.
First	Возвращает первое значение поля в каждой группе.
Last	Возвращает последнее значение поля в каждой группе.

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

     Откроем БД Такси и создадим простой запрос с полями Водитель, Номер, Марка из таблицы Автомобили и полями Оплата и Состояние таблицы Заказы. Отсортируем заказ по полю Водитель. Отберем только те поля, которые соответствуют выполненным заказам. Конструктор такого запроса показан на рис. 10.

Рис. 10. Подготовка запроса с итогами

     Выполнив запрос, получим следующую таблицу (рис. 11):

Рис. 11. Таблица запроса до подведения итогов

     Вновь вернемся в режим Конструктора и щелкнем на кнопке Итоги . При этом в таблице Конструктора запроса появится строка Групповая операция.

     Для этой строки в столбце Марка выберем операцию Count, а в столбце Оплата - Sum (рис. 12). Это необходимо для того, чтобы в столбце Марка отображалось количество заказов водителя, а в столбце Оплата - сумма оплат по водителю. Кроме того изменим названия этих столбцов, записав значение Заказов в строке Подпись (эта строка находится справа в Окне свойств на закладке Общие) для поля Марка и название Сумма для поля Оплата.

Рис. 12. Конструктор итогового запроса

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

Рис. 13. Готовый итоговый запрос

 

2.7. Создание запроса с вычисляемым полем

 

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

     Создайте запрос и в пустом поле справа в строке Поле введите текст [Оплата]*0,13 (рис. 14):

Рис. 14. Формирование запроса с вычисляемым полем

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

Рис. 15. Запрос с вычисляемым полем

     Чтобы подвести суммарный итог, если это необходимо, поставьте курсор в последнюю строку таблицы и щелкните на кнопке Итоги (см. рис. 15). В полях Оплата и Налог в этой строке выберите тип итога Сумма. Получим запрос с итогами, как показано на рис. 15.

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

 

Рис. 16. Запрос с вычисляемым полем. Выбор формата поля Налог

3. Модифицирующие запросы

     Модифицирующим называется запрос, который приводит к изменению данных в БД. Такие запросы используются для последовательного выбора записей и внесение в них необходимых изменений.
     Запросы на изменение могут быть использованы для выполнения следующих операций:
• Обновление информации в группе записей.
• Создание таблицы.
• Удаление записей из таблицы (или группы таблиц).
• Добавление записей из одной таблицы в другую.
     Запросы на обновление являются разрушающими и требуют предельно аккуратного их использования поскольку их действие необратимо. Если вы оказались недостаточно внимательны, выполняя запрос на изменение, то ваша база данных может быть безнадежно запорчена.
     Перед выполнением запроса на обновление рекомендуется  сдать копию с БД и используйте ее для выполнения модифицирующих запросов. Пусть такой копией будет БД Такси0.accdb.
 

3.1. Создание запроса на изменение значений полей

     Рассмотрим действие запроса на изменение на примере изменения суммы оплаты в заказах, а именно все суммы отмененных заказов заменим на нуль.
     Используя режим Конструктора запроса, создадим запрос, в который включим только поля Оплата и Состояние (рис. 10). Щелкнем далее на кнопке Обновление. При этом в таблице запроса появится строка Обновление. Внесите в этой строке в клетку Оплата число 0 (нуль), а в строке Условие отбора для поля Состояние поставьте значение Отменен (рис. 17).

Рис. 17. Конструирование запроса на изменение значений поля

     Нажмите далее на кнопку Выполнить! При этом появится следующее окно предупреждения (рис. 18):

Рис. 18. Предупреждение о модификации данных

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

3.2. Создание запроса на удаление данных

     Теперь создадим запрос на удаление данных на примере удаления всех отмененных заказов в таблице Заказы. Используя режим Конструктора запроса, создадим запрос, в который включим только поле Состояние этой таблицы (рис. 16). Щелкнем далее на кнопке Удаление. При этом в таблице запроса появится строка Удаление. Внесите в строке Условие отбора значение Отменен (рис. 19).

Рис. 19. Конструирование запроса на удаление данных

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