5 Создание запросов
Запрос– один из объектовMicrosoft Access. Запросы являются мощным средством обработки данных, хранимых в таблицах Access, то есть информации из сформированной базы данных. С помощью запросов можно просматривать, анализировать и изменять данные из одной или нескольких таблиц. Они также используются в качестве источника данных для форм и отчетов.
Запросы позволяют вычислять итоговые значения и выводить их в компактном формате, подобном формату электронной таблицы, а также выполнять вычисления над группами записей.
СУБД Microsoft Access позволяет использовать для работы с информацией, хранящейся в базе данных, следующие типы запросов:
- запрос на выборку (при наличии условия отбора);
- запрос с параметрами (критерий отбора задает пользователь, введя нужный параметр при вызове запроса);
- перекрестный запрос (позволяет создавать результирующие таблицы на основе результатов расчетов, полученных при анализе группы таблиц);
- запрос на изменение (удаление, обновление и добавление) записей (позволяет автоматизировать заполнение полей таблиц);
- запросы SQL (на объединение, к серверу, управляющие, подчиненные), написанные на языке запросов SQL.
Для создания запроса следует перейти в область запросов окна базы данных (рисунки 5.1-5.2).
Запросы можно создавать самостоятельно в режиме Конструктора (рисунок 5.1) и с помощью Мастеров (рисунок 5.2). Мастера запросов автоматически выполняют основные действия в зависимости от ответов пользователя на поставленные вопросы, то есть в диалоговом режиме.
Рисунок 5.1 – Ярлык для создания запроса в режиме Конструктора
Рисунок 5.2 – Ярлык для создания запроса с помощью Мастеров
При создании запроса в режиме ответов на вопросы мастера необходимо открыть диалоговое окно мастера «Создание простых запросов» (рисунок 5.3, а) нажатием левой клавишей мыши на соответствующий ярлык (рисунок 5.2). Следует отметить, что с помощью мастеров производится выбор полей из одной или нескольких таблиц (или запросов) для формирования запроса, но условие или параметр отбора требуется задавать в режиме Конструктора.
|
|
|
|
а |
б |
Рисунок 5.3 - Диалоговые окна мастера «Создание простых запросов»
Например, на основе таблицы «Рабочие» базы данных «Мебельное производство» создан простой запрос с помощью Мастера (рисунок 5.3). Просмотр данных, отраженных в запросе, осуществляется в режиме таблицы (рисунок 5.4). В режим таблицы запрос переходит при завершении процесса его создания с помощью Мастера (рисунок 5.3, б).
Рисунок 5.4 - Просмотр данных, отраженных в запросе, в режиме таблицы
Как видно из рисунка 5.4 в таблице отражены все записи, содержащиеся в исходной таблице, то есть выбор записей, соответствующих какому-либо критерию, произведен не был. Поэтому далее в режиме Конструктора необходимо задать условие или параметр отбора для осуществления выбора конкретной информации. Переход из режима таблицы в режим Конструктора и наоборот производится аналогично способам для других объектов базы данных Microsoft Access (например, рисунок 5.5).
Рисунок 5.5 – Один из способов перехода в режим Конструктора
Создание запроса также можно начать с обращения к панели инструментов окна Access (при нахождении в области запросов базы данных) и выполнения команды «Новый объект → Запрос» (рисунок 5.6).
Рисунок 5.6 – Обращение к панели инструментов окна Access для создания запроса
В открывшемся диалоговом окне «Новый запрос» (рисунок 5.7) следует выбрать способ создания запроса – в режиме Конструктора, или тип запроса для создания его с помощью мастеров.
Рисунок 5.7 – Диалоговое окно «Новый запрос»
|
|
|
|
а |
б |
|
|
|
|
в |
г |
Рисунок 5.8 – Создание запросов с помощью мастеров
При выборе типа запроса (рисунок 5.8, а-б), откроется диалоговое окно соответствующего Мастера (рисунок 5.8, в-г).
При выполнении лабораторной работы № 3 «Работа с запросами» лабораторного практикума [3] студентам рекомендуется использовать для формирования запросов непосредственно режим Конструктора.
С помощью запросов можно также создавать новые таблицы, используя данные из одной или нескольких существующих таблиц.
Например, требуется сформировать новую таблицу «Операции», включающую такие же поля, как и таблица «Операции 3 ПА». В таком случае целесообразнее создать новую таблицу с помощью запроса «Создание таблицы» на основе ранее созданной таблицы «Операции 3 ПА», чем с самого начала формировать таблицу в режиме Конструктора таблиц. Такой же подход более удачен при необходимости создания таблицы, включающей помимо новых, неиспользованных в других таблицах базы данных полей, поля, уже содержащиеся в ранее созданных таблицах.
Рисунок 5.9 – Окно Конструктора запросов
Для создания новой таблицы с помощью запроса следует открыть окно Конструктора (рисунок 5.9) одним из приведенных выше способов. При этом также откроется окно «Добавление таблицы», в котором следует выбрать объект или несколько объектов, на основе данных которых будет создаваться запрос. В результате выбора, объект появляется в верхней части окна Конструктора (рисунок 5.10). После этого следует закрыть окно «Добавление таблицы».
Рисунок 5.10 – Выбор объекта (таблицы) для создания на основе его данных запроса
Следующим шагом при формировании запроса является выбор полей, информацию которых необходимо отразить на экране после выполнения (запуска) запроса. Выбранные поля будут представлены в нижней части окна Конструктора запросов (рисунок 5.11).
Чтобы выбрать поле необходимо выполнить двойной щелчок левой клавишей мыши на требуемом поле в области таблицы (в верхней части окна Конструктора), или перетащить имя поля из области таблицы в строку «поле» (в нижней части окна Конструктора), или непосредственно выбрать имя поля из предлагаемого списка (рисунок 5.12).
Рисунок 5.11 – Пример выбора полей
Рисунок 5.12 - Выбор имени поля из предлагаемого списка
В данном случае для создания новой таблицы на основе уже существующей требуются все поля таблицы, поэтому следует выбрать поле со звездочкой, которое отражает содержание таблицы в целом (рисунок 5.11).
Следует обратить внимание, что до настоящего момента работа по созданию запроса велась в окне с наименованием «Запрос1 : запрос на выборку», то есть при выполнении данного запроса из таблицы были бы выбраны заданные поля. Однако для формирования запроса с целью создания таблицы необходимо изменить тип запроса. Для этого можно воспользоваться несколькими способами, отраженными на рисунках 5.13-5.15.
Рисунок 5.13 – Вызов ниспадающего меню с заголовка запроса
Рисунок 5.14 - Вызов ниспадающего меню на панели инструментов
Рисунок 5.15 - Вызов ниспадающего меню из строки меню
После смены типа запроса изменяется его название в заголовке запроса и, что характерно только для данного случая, открывается окно для сохранения создаваемого объекта – новой таблицы, в котором следует задать ее имя и место хранения – в текущей базе данных (рисунок 5.16).
Рисунок 5.16 – Окно для сохранения новой таблицы
После создания запроса его требуется запустить, что осуществляется выполнением команды «Запуск» через кнопку на панели инструментов (рисунок 5.17) или с помощью команд строки меню (рисунок 5.18).
Рисунок 5.17 – Запуск запроса через кнопку на панели инструментов
Рисунок 5.18 – Запуск запроса с помощью команд строки меню
Если закрыть и сохранить запрос без осуществления запуска, для того, чтобы он сработал его необходимо открыть из окна базы данных, или, выделив, выполнить команду «Запуск».
При выполнении (запуске) запроса на создание таблицы Access запросит подтверждение выполнения данной команды (рисунок 5.19).
Рисунок 5.19 – Запрос Access на подтверждение выполнения команды
После того, как запрос выполнен, его следует закрыть и сохранить (рисунки 5.20-5.22).
Рисунок 5.20 – Запрос Access на сохранение запроса
Рисунок 5.21 – Ввод имени созданного запроса
Рисунок 5.22 – Сохраненный запрос
После создания и запуска запросы на создание таблицы рекомендуется удалять или переводить их тип в простой запрос на случай ненамеренного запуска, который приведет к удалению ранее созданной таблицы (рисунки 5.23-5.24), и если в нее были внесены изменения, то они будут утеряны. То же самое относиться и к запросам на изменение (удаление, обновление и добавление) записей.
Рисунок 5.23 – Запрос на подтверждение повторного создания таблицы
Рисунок 5.24 – Запрос на подтверждение удаления ранее созданной таблицы
В результате выполнения запроса «Создание таблицы Операции» в области таблиц окна базы данных появилось наименование новой таблицы «Операции» (рисунок 5.25).
Рисунок 5.25 – Наименование новой таблицы «Операции» в окне базы данных
Запрос на выборку используется наиболее часто. При его выполнении данные, удовлетворяющие условиям отбора, выбираются из одной или нескольких таблиц и выводятся в определенном порядке. Способность запросов отбирать данные из нескольких таблиц особенно полезна при создании форм и отчетов.
Например, из базы данных информационной системы «Мебельное производство» необходимо получить данные о сотрудниках 1977 года рождения (что является условием отбора).
В таком случае в окне Конструктора запросов следует выбрать таблицу, содержащую записи с интересующей информацией, в данном случае такой таблицей является таблица «Сотрудники». В нижней части окна Конструктора необходимо расположить поля, которые будут отражены после выполнения запроса, и задать условие отбора (рисунок 5.26).
Рисунок 5.26 – Выбор полей и ввод условия отбора
Отбор записей, соответствующих по содержанию условию отбора, производится последовательным перебором всего количества записей (строк) таблицы по заданному полю (столбцу), поэтому условие отбора должно быть значением одного из вариантов содержимого данного столбца таблицы.
Результаты выполнения запросов отражаются в режиме Таблицы, поэтому после создания запроса на выборку следует перейти в режим Таблицы (рисунок 5.27) с целью просмотра полученного результата (рисунок 5.28).
Рисунок 5.27 – Переход из режима Конструктора запроса в режим Таблицы
Рисунок 5.28 – Результат выполнения запроса на выборку
При необходимости отбора данных, принадлежащих одной категории, каждый раз для новой категории при запуске запроса, следует ввести параметр отбора. Для этого в соответствующем поле следует ввести в квадратных скобках выражение, которое будет выводиться на экран при каждом запуске запроса, и пользователь будет должен ввести интересующее значение (содержимое) данного поля.
Например, при параметре отбора [Введите фамилию] (рисунок 5.29) каждый раз при запуске запроса будет выводиться окно «Введите значение параметра» (рисунок 5.30), в которое следует ввести фамилию сотрудника, данные которого требуется вывести на экран. Результат выполнения параметрического запроса представлен на рисунке 5.31.
Рисунок 5.29 – Выбор полей и ввод параметра отбора
Рисунок 5.30 – Ввод значения параметра
Рисунок 5.31 – Результат выполнения параметрического запроса
Параметр и условие отбора можно задавать одновременно для двух разных полей.
Одним из вариантов условия отбора записей может быть математическое выражение, например, используя математические знаки «больше» или «меньше» можно отобрать сотрудников года рождения больше или меньше 1977. То же самое касается выбора сотрудников с количеством детей больше, меньше или равным заданному количеству. Выражение с применением математического знака также помещается в строку условия отбора.
Чтобы отобрать записи, соответствующие условию отбора в поле с логическим типом данных, следует знать, какие значения подразумеваются под наличием и отсутствием галочки. Данную информацию можно увидеть в режиме Конструктора таблицы в области свойств поля на вкладке «Общие» в строке «Формат поля». Возможные варианты форматов: Да/Нет; Истина/Ложь; Вкл/Выкл. Первая составляющая обозначается наличием галочки, вторая – ее отсутствием. Следовательно, они же и должны быть использованы в качестве значения условия отбора.
Можно также использовать запрос на выборку, чтобы сгруппировать записи для вычисления сумм; определения минимальных (min), максимальных (max) и средних значений (avg); пересчета и для других действий. Для этого используется итоговый запрос.
Например, необходимо определить общее количество детей всех сотрудников мебельного предприятия.
Чтобы запрос на выборку (рисунок 5.32) представлял собой итоговый запрос, необходимо добавить функцию групповой операции. Для этого следует нажать на кнопку «Групповые операции» на панели инструментов Конструктора запросов (рисунок 5.33, а), либо через правую клавишу мыши выбрать данную команду в ниспадающем списке (рисунок 5.33, б).
Рисунок 5.32 – Запрос на выборку
|
|
|
|
а |
б |
Рисунок 5.33 – Способ добавления функции «Групповые операции»
В результате выбора функции «Групповые операции» в нижней части окна Конструктора запросов добавится строка «Групповая операция» со значением по умолчанию – «Группировка» (рисунок 5.34, а).
В ниспадающем списке следует выбрать требуемое значение функции «Групповая операция» (рисунок 5.34, б), для рассматриваемого примеры такой функцией является «сумма» (рисунок 5.34, в).
|
|
|
|
|
а |
б |
в |
Рисунок 5.34 – Выбор значения функции «Групповая операция»
Результат рассматриваемого в примере итогового запроса отражается в режиме таблицы в виде единственного значения – общего количества детей всех сотрудников предприятия (рисунок 5.35).
Рисунок 5.35 – Результат итогового запроса
Итоговый запрос может быть также построен с применением условия или параметра отбора с целью предварительного выбора группы данных для проведения требуемых вычислений.
Если необходимо получить информацию об отношениях между полями, изначально отраженными в разных таблицах, но между которыми определена связь посредством множества таблиц, составляется запрос к связанным таблицам.
Например, следует установить, какие операции может выполнять рабочий – станочник Савельев. Непосредственной связи между полями «Операция» и «Фамилия» не существует. Поэтому необходимо сформировать запрос к связанным таблицам. Для этого при построении запроса в режиме Конструктора выбираются все необходимые таблицы (рисунок 5.36), информация из которых позволит проследить связь между интересующими полями.
Рисунок 5.36 – Результат выбора нескольких таблиц
Если связь между базовыми таблицами была определена ранее (в схеме данных), верхняя часть окна запроса в режиме конструктора будет выглядеть так, как показано на рисунке 5.36.
Access связывает используемые в запросе таблицы на основе информации о связи, заданной при их создании. Связь отображается в виде линии, соединяющей первичный ключ одной таблицы с соответствующим полем другой.
Если связь между таблицами не определена, Access попытается установить ее самостоятельно. В этом случае программа может связать первичный ключ одной таблицы с полем в другой таблице, имеющим такое же имя и подходящий тип данных. Такие связи следует удалить, и поставить связи самостоятельно таким же образом, как и при составлении схемы данных (пункт 3).
При построении запроса для примера требуется выбрать только два поля для представления их информации на экране - «Операция» и «Фамилия» (рисунок 5.36). Также отбор записей ведется с помощью условия или параметра отбора (рисунок 5.37).
Рисунок 5.37 – Использование параметра отбора в запросе к связанным таблицам
Результат выполнения запроса к связанным таблицам со значением параметра отбора «Савельев» представлен на рисунке 5.38.
Рисунок 5.38 – Результат запроса к связанным таблицам
Запрос на изменение - это запрос, который за одну операцию вносит изменения в несколько записей. Существует четыре типа запросов на изменение: на удаление, обновление и добавление записей, а также на создание таблицы.
Запрос на удаление удаляет группу записей, удовлетворяющих заданным условиям, из одной или нескольких таблиц, причем можно удалять только всю запись, а не отдельные поля внутри ее.
Запрос на обновление записей вносит общие изменения в группу записей одной или нескольких таблиц.
Запрос на добавление добавляет группу записей из одной или нескольких таблиц в конец одной или нескольких таблиц.
Например, если кто-нибудь из сотрудников мебельного предприятия сменил фамилию, нет необходимости вносить изменения во все таблицы базы данных информационной системы, хранящей различные сведения обо всех сотрудниках, необходимо лишь составить и выполнить запрос на замену. Однако, это возможно только в случае предварительного включения функции «Каскадное обновление связанных полей» (рисунок 3.30) при построении схемы данных (пункт 3).
Такой запрос строится на основе базовой таблице-справочнике, являющейся первоисточником изменяемой информации. При построении запроса (рисунок 5.39, а) необходимо изменить его тип с «запрос на выборку» на «запрос на обновление» (рисунок 5.39, б).
|
|
|
|
а |
б |
Рисунок 5.39 – Создание запроса на обновление
В нижней части окна Конструктора запросов добавится строка «Обновление», в которую необходимо ввести новое значение, в данном случае это новая фамилия сотрудника - Морозов. При этом следует ввести условие отбора, которое обеспечит выбор заменяемого значения – фамилии Колесников.
При выполнении созданного запроса на обновление данных Access запросит подтверждение на замену данных (рисунок 5.40-5.41).
Рисунок 5.40 – Запрос Access на подтверждение замены данных
Рисунок 5.41 – Сообщение Access о количестве измененных записей
В результате выполнения запроса на обновление автоматически изменилась фамилия сотрудника, как в таблице «Сотрудники» (рисунок 5.42, а), так и в связанной с ней таблице «Рабочие» (рисунок 5.42, б).
|
|
|
|
а |
б |
Рисунок 5.42 – Результат выполнения запроса на обновление данных
В перекрестном запросе отображаются результаты статистических расчетов (суммы, количество записей, средние значения), выполненных по данным из одного поля таблицы. Эти результаты группируются по двум наборам данных, один из которых расположен в левом столбце таблицы, а второй - в верхней строке.
Запрос SQL - это запрос, создаваемый при помощи инструкций SQL. Этот тип запросов довольно сложен для начинающих пользователей и используется обычно опытными пользователями, имеющими навыки программирования и общения с серверами баз данных.
Контрольные вопросы:
Для чего предназначены запросы?
Объясните назначение итогового запроса.
Что такое групповые операции?
Приведите примеры запросов с критериями и параметрами отбора.
Какие типы полей используются в итоговых запросах?