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

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

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

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

При работе с системой данных очень часто возникает задача соединения данных из различных связанных таблиц в одну. Рассмотрим создание такого запроса в нашей БД PlanEconomic. У нас имеется четыре отдельные таблицы с информацией об агентах, бумагах, заявках и портфелях. Вполне естественной представляется задача построения таблицы, содержащей информацию по содержанию портфелей, со следующей структурой:

наименование бумаги (данные из таблицы «Бумаги»);

наименование агента (данные из таблицы «Агенты»);

тип бумаги (данные из таблицы «Бумаги»);

номинальная стоимость пакета (вычисляется как произведение номинальной цены на количество бумаг данного вида, которым обладает текущий агент (из таблицы «Портфели»)).

Для решения такой задачи следует перейти к разделу «Запросы» главного окна БД, нажать на кнопку «Создать» и выбрать режим «Конструктор». Процесс создания запроса начинается с выбора таблиц, а также других запросов, на основе которых будет строиться текущий запрос. В дальнейшем состав этого набора может быть изменен. В нашем случае запрос будет построен на основе трех таблиц: «Бумаги», «Агенты» и «Портфели». Добавим их в запрос. Заметим, что при этом к запросу автоматически добавляются уже существующие связи между таблицами, заданные в схеме данных.

В процессе формирования запроса можно выделить ряд этапов:

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

задание порядка, в котором данные должны выводиться при выполнении запроса;

задание условий вывода записей в запросе.

Отметим, что колонки таблицы запроса содержат как поля таблиц (колонки 1–3), так и выражения, построенные на основе полей в результате вычислений. В частности, последняя колонка (ей присвоено имя «НоминалПакета») содержит вычисления: произведение номинальной цены на количество бумаг данного вида. В запросе это имеет вид:

НоминалПакета: [Номинал]*[СуммОбъем]

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

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

сумму значений поля;

среднее число значений в поле;

минимальное значение;

максимальное значение;

среднеквадратичное отклонение или дисперсию.

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

Благодаря указанию типа сортировки в колонке «ТипБум» записи будут выводиться отсортированными по типу бумаг. Сохраним запрос под именем «Содержимое портфелей». Просмотреть его в режиме таблицы можно точно так же, как и обычную таблицу. В главном меню на закладке «Запросы» нужно нажать кнопку «Открыть» – и на экране появится «Содержимое портфелей», которое можно распечатать или использовать для создания новых запросов. В дальнейшем сохраняется возможность каким-либо образом изменить запрос: добавить в него новые данные или изменить способ сортировки. Для этого можно снова вернуться в режим «Конструктор».

Ценность создания запросов, кроме всего прочего, состоит также в сокращении объема БД. Полученная нами виртуальная таблица на самом деле формируется только в момент вывода на экран, а вовсе не хранится в готовом виде, как это могло быть в случае с аналогичной таблицей в MS Excel, которая дублирует уже имеющуюся в другой таблице информацию, занимает лишнее место в БД и портит стройную логическую структуру единой информационной системы. В СУБД каждая информация хранится в своей уникальной ячейке и никогда не дублируется.

Рассмотрим еще один случай применения запросов для решения задач по обработке данных. Достаточно типичной для решений финансово-экономического характера является проблема группировки данных по тому или иному признаку. Например, в рамках построенной нами БД может быть поставлена задача определения суммарного (или среднего) спроса и предложения по ценным бумагам, циркулирующим на рынке. Решить ее можно, построив запрос, содержащий групповые операции. Для активизации возможности задания групповых операций в окне «Конструктора» запросов необходимо включить соответствующую функцию в меню «Вид», потому что по умолчанию функция «Групповые операции»  отключена.

Окно «Конструктора» в процессе создания запроса, выводящего информацию по суммарному спросу и предложению на ценные бумаги. Операция свертывания нескольких записей из таблицы «Заявки» в одну результирующую запись, осуществляемая для каждого наименования бумаги, определяется командой «Группировка», расположенной в строке «Групповая операция». Для двух последующих колонок запроса («СуммСпрос» и «СуммПредл») определены операции суммирования по группе Sum, расположенные в той же строке. В строке «Поле» для каждой колонки после наименования находится формула для перемножения цены заявки на ее объем. При этом требуется проверить знак (положительный или отрицательный) в поле «ЦенаЗаявки». Как мы условились раньше, для обозначения заявки на покупку мы вносим в это поле положительные значения, а для обозначения заявки на продажу – отрицательные. Это упрощает структуру таблиц, зато усложняет структуру запросов, поскольку сейчас нам придется построить логическое выражение для проверки знака (с помощью логической функции IIf). Таким образом, для вычисления «СуммСпрос» необходимо суммировать все значения, полученные перемножением положительных значений полей «ЦенаЗаявки» на соответствующие значения полей «ОбъемЗаявки». Для вычисления «СуммПредл» необходимо просуммировать все значения, полученные перемножением отрицательных значений полей «ЦенаЗаявки» (в процессе вычислений мы, конечно, сменим знак на положительный, умножив выражение на –1) на соответствующие значения полей «ОбъемЗаявки». То есть две формулы в строке «Поле» двух колонок примут следующий вид:

СуммСпрос: IIf ([ОбъемЗаявки]>=0;[ЦенаЗаявки]*[ОбъемЗаявки];0)

СуммПредл: IIf ([ОбъемЗаявки]<=0;-1*[ЦенаЗаявки]*[ОбъемЗаявки];0)

Результат выполнения описанного запроса (ему дано имя «Спрос и предложения» – это небольшая таблица, в которой суммируются все заявки на покупку или продажу каждой бумаги.

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

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

Запрос к серверу отправляет команды непосредственно в БД ODBC (например, MS SQL), причем используются только команды, поддерживаемые сервером. Например, запрос к серверу используется для возвращения или изменения данных.

Управляющий запрос создает или вносит изменения в объекты БД.

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

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

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

Запрос на обновление записей вносит общие изменения в группу записей одной или нескольких таблиц (например, если на 10 % поднимаются цены на все молочные продукты или на 5 % увеличивается зарплата сотрудников определенной категории). Такой запрос позволяет изменять данные в существующих таблицах.

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

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

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

 

Продолжение статьи читайте в следующем номере журнала.

 

АНАТОЛИЙ АЛИЗАР

<<< Главная страница | < Назад

pravo.kulichki.ru ::: pravo.kulichki.com ::: pravo.kulichki.net

2004-2015 Республика Беларусь