Формирование запросов

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

В Microsoft Access имеется удобное для пользователя графическое средство формирования запроса по образцу – QBE (Query By Example), с помощью ко­торого легко может быть построен сложный запрос. Запрос QBE включает под­схему данных с используемыми таблицами и бланк запроса. При конструирова­нии запроса достаточно, работая мышью, выделить и перетащить необходимые поля из таблиц, представленных в схеме данных запроса, и бланк запроса и ввести условия отбора записей.

Средствами запроса можно:

– выбрать записи, удовлетворяющие условиям отбора;

– включить в результирующую таблицу запроса нужные поля;

– произвести вычисления в каждой из полученных записей;

– сгруппировать записи с одинаковыми значениями в одном или нескольких полях и выполнить над ними групповые функции;

– произвести обновление полей в выбранном подмножестве записей;

– создать новую таблицу базы данных, используя данные из существующих таб­лиц;

– удалить выбранное подмножество записей из таблицы базы данных;

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

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

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

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

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

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

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

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

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

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

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

Разработка запроса производится в режиме Конструктора запросов.

Для создания запроса в окне БД надо открыть вкладку Запрос и на­жать кнопку Создать. В открывшемся окне Новый запрос выбрать Конструктор. В окне Добавление таблицы выбрать используемые в запросе таблицы и нажать кнопку Добавить. Затем кнопкой Закрыть выйти из окна Добавление таблицы. В результате появится окно Конструктора запросов – <Имя запроса>: запрос на выборку.

Окно Конструктора запросов разделено на две панели. Верхняя панель содержит схему данных запроса, которая включает выбранные для данного запроса таблицы. Таблицы представлены списками полей. Схема дан­ных отображает связи между таблицами, имеющимися в схеме БД, и свя­зи объединения, которые устанавливает Access, если таблицы имеют поля с одина­ковыми именами и типами данных. Кроме того, пользователь сам может установить нужные связи (объединения), перетащив задействованные в связи поля из одного списка полей в другой. Нижняя панель является бланком запроса по образцу, который нужно заполнить. Каждый столбец бланка относится к одному полю, с которым нужно работать в запросе. Поля могут использоваться для включения их в результат выполнения запроса, для задания сортировки по ним и для задания условий отбора записей.

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

Рис. 14. Окно Конструктора запроса

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

Условия отбора записей могут задаваться для одного или нескольких полей в соответствующей строке бланка запроса. Условием отбора является выражение, которое состоит из операторов сравнения и операндов, используемых для сравнения. В качестве операндов выражения могут использоваться некоторые задан­ные значения и идентификаторы (ссылки). Идентификатор представляет собой ссылку на значение поля, элемента управления или свойства. Идентификаторами могут быть имена полей, таблиц, запросов, форм, отчетов и т. д. Они должны за­ключаться в квадратные скобки. Если необходимо указать ссылку на поле в кон­кретной таблице, форме, отчете, то перед именем поля ставится имя таблицы, также заключенное в квадратные скобки и отделенное от имени поля восклицательным знаком. Например, [Имя таблицы]![Имя поля].

Слово «выражение», как оно используется в Access, является синони­мом слова «формула». Выражение – это комбинация операторов, кон­стант, функций и свойств элементов управления, которая при вычисле­нии дает единственное значение. Access создает формулы с помощью формата а=b+с, где «а» – это результат, а «=b+с» – это выражение. Выражение может быть использовано для установки свойств таблиц или форм, для определения значений в полях или отчетах, как часть запро­сов и во многих других местах.

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

Наиболее часто используемыми логическими операторами являются And (и), Or (или) и Not (не). Когда критерии объединяются с помощью оператора And, запись выбирается только тогда, когда она удовлетво­ряет всем заданным критериям. Когда критерии объединяются с помощью оператора Or, запись выбирается только тогда, когда она удовлетворяет одному из заданных критериев. Оператор Not выбирает все записи, которые не удовлетворяют данному критерию.

Наиболее часто используемые операторы сравнения включают < (меньше, чем), > (больше, чем) и = (равно). Эти базовые операторы могут быть объединены для создания <= (меньше или равно), >= (больше или равно) и <> (не равно). Оператор Like также относится к операторам сравнения и используется для проверки того, соответствует ли текст заданному шаблону или нет.

Наиболее часто используемыми арифметическими операторами яв­ляются + (сложение), – (вычитание), * (умножение) и / (деление), кото­рые используются вместе с числами. Связанный с ними оператор & (тек­стовый вариант оператора +) используется для конкатенации, или объединения, двух текстовых строк.

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

Выражение вводится в бланк запроса в пустую ячейку строки Поле (рис. 14). В этой ячейке после нажатия клавиши <Enter> или перевода кур­сора в другую ячейку формируется имя поля ВыражениеN, где N – целое чис­ло, увеличивающееся на единицу для каждого нового создаваемого вычисляемо­го поля в запросе. Имя вычисляемого поля выводится перед выражением и отделяется от него двоеточием. Например, Выражение1:[Услуги]![Стоимость_работы]+[Склад]! [Цена]*[Заказ]![Количество_деталей], где Услуги, Склад и Заказ – имена таблиц, а Стоимость_работы, Цена и Количество_деталей – имена полей.

Имя вычисляемого поля ВыражениеN становится заголовком столбца в табли­це с результатами выполнения запроса. Это имя можно изменить. Пользователь имеет возможность переименовать вычисляемое поле. Для этого достаточно в планке запроса вместо ВыражениеN ввести новое имя.

Для создания вычисляемых выражений в запросах удобно пользоваться Построителем выражений. Общие сведения о Построителе выражений можно найти из справочной системы.

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

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

Запрос с параметром. Конкретное значение поля в условии отбора может вводиться непосредственно вбланк запроса или задаваться пользователем при выполнении запроса в диалого­вом окне. Чтобы выводилось диалоговое окно для ввода конкретного значения поля и условия отбора, нужно определить параметр запроса. Имя параметра запроса может вводиться непосредственно в условия отбора в квадратных скоб­ках (рис. 16).

Рис. 16. Создание запроса с параметром

При выполнении запроса это имя появится в диалоговом окне Введите зна­чение параметра (рис. 17). Если в запрос вводится несколько параметров, то порядок их ввода через диалоговые окна определяется порядком расположения полей с па­раметрами в бланке запроса.

Рис. 17. Окно ввода параметра

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

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

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

– Группировка – если по данному полю производится группировка значений.

– Одна из статистических функций (табл. 4).

Таблица 4

Статистические функции

Продолжение табл. 4

– Условие – если данное поле используется в запросе для отбора записей.

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

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

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

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

Рис. 19. Итоговый запрос

Простейшие запросы некоторых видов могут быть созданы с помощью Мастеров Access. Мастер запросов ускоряет процесс создания запроса, автоматически вы­полняя первоначальные простейшие действия по подготовке запроса. Вызванный Мастер запросов запрашивает у пользователя сведения и создаёт запрос на основе ответов пользователя. При необходимости можно в режиме Конструктора отредактировать запрос.

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

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

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

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

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

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

ЛЕКЦИЯ № 14