Разработка проекта SErvices
Часть 2. Фильтры и запросы
Основные положения
1. Наличие в базе данных взаимосвязанных таблиц уже предоставляет определенные возможности для выбора информации по различным критериям. Например, по таблице Заказчики можно просмотреть все заказы этой фирмы (рис.1), а по таблице Исполнители – все выполненные сотрудником заказы (рис.2).
Рис.1
Рис.2
2. Кроме того, с помощью кнопки 
можно в любой таблице установить Фильтр По выделенному значению в любом поле, т. е. по определенной дате, фирме, по конкретной сумме стоимости работ или затрат, по какому-либо исполнителю или виду работ. Аналогичным образом можно устанавливать фильтр в нескольких полях таблицы. Снятие фильтра производится кнопкой 
.
3. Запросы используются для просмотра, изменения и анализа данных различными способами. Например, можно выбрать определенные данные из нескольких таблиц, выполнить вычисления с табличными данными, получить итоговые суммы, сгруппировать и отсортировать результаты. Запросы также можно использовать в качестве источников записей для форм, отчетов и страниц доступа к данным. Существует несколько типов запросов:
§ запросы на выборку;
§ запросы с параметрами, при выполнении которого появляется приглашение ввести данные, например, условие для отбора записей или значение, которое требуется вставить в поле;
§ перекрестные запросы, которые позволяют отобразить результаты статистических расчетов (суммы, количество записей и средние значения), выполненных по данным из одного поля таблицы. Эти результаты группируются по двум наборам данных, один из которых расположен в левом столбце таблицы, а второй — в верхней строке;
§ запросы на изменение;
§ запросы SQL.
Постановка задачи
Создать следующие запросы.
1. Прибыль по заказам (запрос на выборку).
2. Заказы с прибылью 1000 рублей и более (запрос на выборку).
3. Общие итоги по фирмам (запрос на выборку).
4. Итоги по фирме (запрос с параметром).
5. Стоимость заказов по фирмам и месяцам (перекрестный запрос).
Рекомендуемый план разработки проекта
1. Запустите Microsoft Access, выберите пункт «Открыть базу данных» и откройте ранее созданный файл (например, Services. mdb).
2. Создайте запрос Прибыль по заказам. Для этого в окне базы данных перейдите на страницу «Запросы» и запустите создание запроса с помощью мастера. В открывшемся окне выберите таблицу Заказы и перенесите поля Дата, КодФ, Стоимость и Затратаы из левого списка в правый (рис.3):
Рис.3
Нажмите кнопку 
, перейдите в следующее окно и отметьте пункт «подробный (вывод каждого поля каждой записи)» (рис. 4):
Рис. 4
Нажмите кнопку и в следующем окне введите название запроса «Прибыль по заказам» (рис. 5):
Рис. 5
После нажатия кнопки 
откроется окно с результатом запроса (рис. 6):
Рис.6
Добавьте поле прибыль в созданный запрос. Для этого кнопкой 
откройте его в режиме «Конструктор» в пятое поле впишите формулу: [Стоимость]-[Затраты] (рис.7). Сделать это можно или вручную или с помощью построителя выражений (рис.8), который можно запустить кнопкой 
. Работа с построителем выражений описана в Приложении.
Рис.7
Рис.8
Нажав кнопку 
, откройте запрос в виде таблицы (рис.9).
Рис.9
Примечание. Редактирование разрядности, заголовков столбцов и т. п. производится в окне «Свойства поля», вызываемого нажатием кнопки 
.
3. Создайте запрос Заказы с прибылью 1000 рублей и более копированием созданного ранее запроса Прибыль по заказам и откройте его в режиме «Конструктор». В пятом поле добавьте условие >=1000 (в соответствующей строке). Кроме того, выберите режим сортировки «по возрастанию» в поле «Дата» (рис.10).
Рис.10
4. Создайте запрос Общие итоги по фирмам с помощью мастера (рис.11):
Рис.11
На втором шаге выберите пункт «итоговый» (рис.12) и нажмите кнопку 
. В появившемся окне (рис.13) отметьте суммы по полям «Стоимость» и «Затраты», а также подсчет записей.
Рис.12
Рис.13
Результат созданного запроса показан на рисунке 14.
Рис.14
Откройте полученный запрос в режиме «Конструктор» и, используя окно свойств поля, измените разрядность сумм, заголовки полей, а также добавьте поле «Прибыль». В результате запрос должен иметь следующий вид (рис.15):
Рис.15
Примечание. В поле «Прибыль» надо записать формулу
[Sum - Стоимость]-[Sum - Затраты]
И выбрать вид групповой операции «Выражение».
5. Создайте запрос Итоги по фирме копированием созданного ранее запроса Общие итоги по фирмам и откройте его в режиме «Конструктор». Добавьте в верхнее окно таблицу Заказчики – кнопкой 
или через контекстное меню, вызываемое правой кнопкой мыши. Замените первое поле, содержащее код фирмы-заказчика из таблицы Заказы на название фирмы-заказчика из таблицы Заказчики (рис.16) и, чтобы сделать его параметром, добавьте квадратные скобки в условие отбора (текст в квадратных скобках будет выводиться в окне ввода значения параметра).
Рис.16
6. Для создания перекрестного запроса Стоимость заказов по фирмам и месяцам нажмите кнопку 
, выберите в появившемся окне соответствующий пункт (рис.17) и нажмите «ОК».
Рис.17
В следующем окне выберите таблицу Заказы (рис.18) и нажмите «Далее».
Рис.18
На следующем шаге выберите поле, значения которого станут заголовками строк итоговой таблицы запроса, а именно, поле «КодФ» (рис.19).
Рис.19
Чтобы назначить значение заголовков столбцов, выберите поле «Дата» (рис.20).
Рис.20
В качестве интервала для группировки данных по времени выберите месяц (рис.21).
Рис.21
Далее, выберите какое значение (сумма) и какого поля (стоимость) должно включаться в итоговую таблицу запроса (рис.22).
Рис.22
И на последнем шаге назначьте имя создаваемому запросу (рис.23).
Рис.23
Результатом запроса (после корректировки разрядности сумм и заголовков столбцов) будет таблица следующего вида (рис.24).
Рис.24
Упражнения. Совершенствование проекта
Создайте запрос на выборку Общие итоги по исполнителям (рис.25), аналогичный запросу Общие итоги по фирмам.
Рис.25
Создайте запрос с параметром Итоги по исполнителю, аналогичный запросу Итоги по фирме. Создайте перекрестный запрос Прибыльность заказов по фирмам и месяцам (рис.26), аналогичный запросу Стоимость заказов по фирмам и месяцам.
Рис.26
Добавьте параметр (название фирмы) в созданный перекрестный запрос Прибыльность заказов по фирмам и месяцам. Создайте перекрестный запрос Прибыльность видов работ по месяцам (рис.27).
Рис.27
Приложение
Создание выражения с помощью построителя выражений
Запустите построитель выражений. В левом нижнем поле построителя выберите папку, содержащую нужный элемент. В нижнем среднем поле дважды щелкните элемент, чтобы вставить его в поле выражения, или выберите тип элементов. Если выбран тип в нижнем среднем поле, то значения будут отображаться в нижнем правом поле. Дважды щелкните значение, чтобы вставить его в поле выражения.
Совет. Любая часть выражения может быть непосредственно введена в поле выражения.
Вставьте необходимые операторы в выражение. Для этого поместите указатель мыши в определенную позицию поля выражения и выберите одну из кнопок со знаками операций, расположенных в середине окна построителя. Закончив создание выражения, нажмите кнопку OK. Созданное выражение будет скопировано в ту позицию, из которой был вызван построитель выражений. Если в ней уже содержится значение, то исходное значение или выделенный текст будут заменены на новое выражение.