6. Создание запросов

Запросы являются основным средством просмотра, изменения и анализа информации, содержащейся в таблицах базы данных.

6.1. Формирование запросов с помощью Мастера

Простые запросы – те, которые ограничены одной таблицей и не содержат каких-либо критериев, условий, фильтров и т.п.

Задача 1: Простой запрос (одна таблица). Необходимо создать список сотрудников со сведениями о стаже работы.

Открыть окно базы данных, выбрать вариант Запросы (Queries) и нажать кнопку «Создать» (рис. 6.1).

Рис. 6.1

 

В открывшемся окне рис. 6.2, выбрать источник «Таблица:Сотрудники» и переместить наименования полей в правое окно. В данном случае нас интересуют поля: «Фамилия» и «Стаж работы».

Рис. 6.2

 

В следующем окне задать имя запроса, например, «Фамилии и стаж». Заполнив последнее окно, нажать на кнопку «Готово», результаты отражены на рис. 6.3.

Рис. 6.3

 

Задача 2: Групповая обработка данных (несколько таблиц). Требуется определить суммы, выплачиваемые категориям сотрудников в качестве оклада и надбавки за стаж.

Открыть Мастер запросов (рис. 6.1), выбрать «Простой запрос». Внимание! Мы будем трижды повторять операцию по переносу доступных полей в запрос. В окне «Создание простых запросов» открыть источники из таблицы «Сотрудники» перенести поле «Должность» в правое окно. Вновь открыть строку с источниками, выбрать таблицу «Штатные должности», из которой перенести в правое окно поле «Оклад по должности», а из таблицы «Выслуга лет» - соответствующее поле «Надбавка за стаж». В итоге будет сформировано предписание, которое показано на рис. 6.4.

Рис. 6.4

 

После того, как будет нажата кнопка «Далее», появится окно, представленное на рис. 6.5.

Рис. 6.5

 

В окне выбрать «Итоговый» и нажать на кнопку «Итоги», в появившемся окне «Итоги» (рис. 6.6), отметить «Sum» для всех строк, что позволит сформировать таблицу с итоговыми (суммарными) значениями по каждой категории сотрудников.

 

Рис. 6.6

 

Чтобы получить информацию о количестве сотрудников по категориям, необходимо установить флажок «Подсчет числа записей». После того, как будет нажата кнопка «Ок», появится форма «Создание простых запросов» с активной кнопкой «Готово». Не забывайте присваивать имя запросу, которое вводится в последующей таблице, например, введем «Выплаты по категориям должностей». Сформированный запрос из трех таблиц можно открыть в окне базы данных, таблица отображена на рис. 6.7.

Рис. 6.7

 

6.2. Проверка связанных таблиц для формирования запроса

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

1. В окне базы данных открыть объекты «Запросы».
2. Выделить наименование запроса, например, «Выплаты по категориям должностей», как это показано на рис. 6.8.
3. Воспользоваться пиктограммой Конструктор, после чего будет открыто окно конструктора запросов, представленное на рис. 6.9.

Рис. 6.8

Рис. 6.9

 

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

 

6.3. Формирование запросов в режиме Конструктора

Конструктор запросов позволяет создавать следующие основные виды запросов:

Запрос на выборку (Select query). Эти запросы позволяют извлечь информацию, рассчитать показатели и создать перекрестные ссылки, но не изменяют данные в таблицах.

Запрос на изменение (Action query). Запросы этого типа дают возможность корректировать информацию, которая содержится в таблицах. Существует четыре вида запросов на изменение:

• Запрос на создание таблицы (Make-table query)- создает новую таблицу на основе данных, уже содержащихся в одной или нескольких таблицах.
• Запрос на удаление (Delete query) – удаляет все записи из одной или нескольких таблиц на основании критериев, задаваемых пользователем.
• Запрос на присоединение (Append query) – добавляет целые записи или только указанные поля в таблице.
• Запрос на обновление (Update query) – изменяет данные в существующих таблицах на основании информации в окне Конструктора.

 

Задача 3: Работа с Конструктором запросов. Выбрать данные о надбавках за стаж работы инженеров организации.

В окне новый запрос выбрать способ создания запроса – Конструктор. В результате будет открыто окно «Добавление таблицы» рис. 6.10.

Рис. 6.10

 

Одновременно с окном (рис. 6.10) откроется окно с наименованием «Запрос1: запрос на выборку», в котором можно создать запросы по полям  нескольких таблиц.

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

Рис. 6.11

 

Действия пользователя в окне Конструктора сводятся к следующим шагам:

- Отформатировать таблицы так, чтобы были видны наименования всех полей.

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

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

- Поставить или убрать пометку в строке «Вывод на экран» бланка запросов, в зависимости от желания отображения этих полей на экране.

- Ввести условие отбора в строку «Условие отбора». По условию задачи, необходимо получить сведения об инженерах. Для этого для поля «Должность» набрать слово «Инженер».

- Просмотреть результаты запроса, нажав на пиктограмму  (Run) в строке панелей инструментов. Будет открыта таблица рис. 6.12..

Рис. 6.12

 

- Сохранить запрос, выполнив команду Файл – Сохранить, например, под именем – «Надбавки у инженеров».

 

Задача 4. Запрос на обновление. Отделу кадров требуется исправить значение фамилии сотрудницы на основании приказа о смене фамилии.

Предположим, что сотрудница Виноградова С.С. представила документ о смене фамилии на Новикову. В этом случае, необходимо провести изменения во всех связанных таблицах, в которых встречается фамилия Виноградова С.С.

Порядок выполнения запроса на обновление заключается в следующем:

1. Открыть схему связей, выделить связь «Один-ко-многим» у ключевого поля Фамилия в таблице Сотрудники, проверить, как они организованы. Для этого по связи щелкнуть правой клавишей мыши и в контекстном меню выбрать команду «Изменить связь», после чего откроется окно, показанное на рис. 6.13.

Рис. 6.13

 

2. В окне «Изменение связей» установить отметку «Каскадное обновление связанных полей», нажать на кнопку «ОК».
3. В окне базы данных выбрать объекты Запросы, нажать на пиктограмму .
4. Перенести из окна «Добавление таблицы» в окно конструктора таблицу Сотрудники, закрыть окно.
5. В строке меню окна базы данных раскрыть список у пиктограммы «Тип запроса», выбрать из списка команду Обновление, как это показано на рис. 6.14.

Рис. 6.14

 

6. Перенести имя поля «Фамилии» в окно параметров конструктора (обратите внимание, что наименования строк в окне параметров изменились).
7. В поле «Условие отбора» введите значение, которое будем менять, в данном примере это фамилия сотрудника «Виноградова С.С.».
8. В поле «Обновление» введите новую фамилию «Новикова С.С.», как это показано на рис. 6.15.

Рис. 6.15

 

9. Проведите проверку работоспособности запроса, нажав в окне базы данных на пиктограмму  (Запуск). После чего появится транспарант, представленный на рис. 6.16.

Рис. 6.16

 

10. Сохраните запрос, для этого следует закрыть окно конструктора, появится окно, в которое следует ввести имя запроса, например, «Изменение фамилии». После закрытия конструктора появится новый транспарант с предупреждением, который представлен на рис. 6.17. Надо дать подтверждение.

Рис. 6.17

 

11. Увидеть результаты запроса можно только в таблицах, которые содержат фамилии сотрудников. В данном примере – это таблицы Сотрудники и Распределение льгот.

 

Задача 5. Создание запросов на выборку. Контекстный поиск. Найти в текстовых полях с фамилиями сочетание букв «КОВ», создать таблицу с фамилиями этих сотрудников и годом их рождения.

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

1.      Открыть объект Запросы, нажать на пиктограмму , выбрать наименование таблицы Сотрудники.

2.      В окно параметров из таблицы перенести поля «Фамилия» и «Год рождения».

3.      В строку «Условие отбора» ввести Like «*ков*», что означает – похожий. Как это показано на рис. 6.18.

Рис. 6.18

 

4.      Сохранить запрос, закрыв конструктор, например с именем «Контекстный поиск фамилий». Для просмотра результатов запроса, необходимо в объекте Запросы найти таблицу под именем Контекстный поиск фамилий и дважды щелкнуть по нему. Для данного примера результат выполнения запроса показан на рис. 6.19.

 

Рис. 6.19

 

Задача 6. Создание запросов на добавление. Планово-экономический отдел в штатное расписание организации предложил внести новую должность.

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

1.      Открыть объект Запросы. Начать создавать новый запрос в режиме Конструктор, как это было описано ранее.

2.      Для решения поставленной задачи, в режиме конструктора открыть таблицу Штатные должности.

3.      В строке меню окна базы данных раскрыть список «Тип запроса» и выбрать команду  Добавление, как это показано на рис. 6.20, а затем указать место в окне «Добавление».

 

 

 

 

 

 

 

 

 

 

                                                 Рис. 6.20

 

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

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

6.      Проверка работоспособности запроса осуществляется переходом в окне базы данных к объекту Таблицы, выбору таблицы Штатные должности, запуска ее и заполнения полей этой таблицы, вводя значения в новую строку.

Рис. 6.21

 

Задача 7. Создание запроса на удаление. Бухгалтерия получает распоряжение о ликвидации льготы сотрудникам фирмы по оплате отдыха.

Предварительно следует сделать напоминание, что удаление записей из таблицы проводят в двух случаях. Первый случай, когда действительно требуется удалить запись или группу записей из базы данных ввиду того, что они больше не понадобятся. Например, студенты, которые окончили ВУЗ, уже не должны фигурировать в ведомостях групп. Второй случай, когда требуется удалить записи с ошибками, а затем на их место ввести новые записи без ошибок. Это обстоятельство следует учитывать при формировании запроса на удаление. Общий вид окна конструктора запроса на удаление показан на рис. 6.22.

Рис. 6.22

 

1.      Проверить параметры связей для таблицы, в которой будут удаляться записи. Должна стоять пометка «каскадное удаление связанных записей», как это показано на рис. 6.23.

Рис. 6.23

 

2.      В окне базы данных открыть объект Запросы и создать новый запрос с помощью конструктора. Указать имя таблицы и режим «Удаление».

3.      Заполнить параметры запроса на удаление, как это показано на рис. 6.24.

Рис. 6.24

 

4.      Сохранить запрос, например, под именем – Удаление видов льгот.

 

6.4.         Работа с построителем выражений

Построитель выражений (Expression Builder) является служебной программой Access, содержит область ввода (верхняя часть окна), трех списков и строки с операторами. Формировать выражение можно непосредственно в окне ввода вручную, но для того, чтобы избежать ошибки, лучше использовать окна со списками. Построитель выражений используется не только в режиме конструктора, хотя при формировании запросов наиболее эффективным является способ – строить сложные и простые выражения этим средством.

 

Задача 8. Выполнение вычислений в режиме Конструктора. Подсчитать суммарные выплаты сотрудникам фирм, которые занимают должность инженера.

 

1.      Открыть новый запрос в конструкторе, нажав на кнопку , а затем выбрать «Конструктор». Перенести таблицы базы данных в окно запроса, которые понадобятся для выполнения задачи. В данном примере потребуются таблицы Сотрудники и Выслуга лет.

2.      В окне параметры запроса Конструктора расположить поля: «Фамилия», «Должность», «Основной оклад» и «Надбавка за стаж».

3.      В поле «Должность» для параметра «Условие отбора» ввести условие: «Инженер».

4.      Установить курсор указателя мыши в свободном поле (справа) против параметра «Поле».

5.      Вызвать приложение Построитель выражений, для чего в строке меню базы данных щелкнуть по пиктограмме  (Построить), после чего откроется окно «Построитель выражений», которое с введенным выражением представлено на рис. 6.25.

Рис. 6.25

 

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

7.      В левом нижним окне построителя выражений раскрыть список «Таблицы», а затем папку с наименованием «Сотрудники».

8.      Перенести из среднего окна наименование поля в окно выражений. Для этого следует выделить наименование поля (в данном случае «Основной оклад») и нажать на кнопку «Вставить».

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

10.  Повторить действия по добавлению полей в построитель выражений (в данном случае, необходимо из таблицы Выслуга лет выбрать поле «Надбавка за стаж»).

11.  Нажать на кнопку «ОК» и сохранить имя запроса в базе данных, например «Полная зарплата». Пример созданной таблицы с помощью построителя выражений представлен на рис. 6.26. Посмотреть результаты выполнения запроса можно в объекте «Запросы», раскрыв таблицу , которая отображена на рис.6.27.

 

Рис. 6.26

Рис. 6.27

 

6.5.         Автоматизация работы с запросами

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

.

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

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

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

2.      Из таблиц выбрать поля и занести в окно параметров («Распределение льгот» и «Фамилия»).

3.      В строке «Условия отбора» записать в квадратных скобках текст, который будет появляться в диалоговом окне. Например, [Введите название льготы для сотрудника], как это показано на рис. 6.28.

4.      Закрыть окно конструктора и сохранить имя запроса, например, «Отбор по параметру льготы».

Рис. 6.28

 

5.      Открыть запрос и ввести в диалоговое окно «Введите значение параметра» текст, который представлен на рис. 6.28, система сформирует таблицу с результатами отбора, которые отображены на рис. 6.29 справа.

 

 

 

 

 

 

 

 

 

Рис. 6.29

 

6.6.   Перекрестный запрос

Перекрестный запрос – это особый тип запроса с группировкой данных. Он предназначен для анализа информации, хранящейся в двух и более полях таблицы. Формирование перекрестного запроса осуществляется с помощью Мастера перекрестных запросов. Хотя, перекрестный запрос можно выполнить с помощью Конструктора запросов (это очень трудоемкая операция, которая требует применения построителя выражений для каждого отдельного поля, которое входит в анализ). Несмотря на то, что Мастер перекрестных запросов работает только с одной таблицей, все равно можно организовать любо сочетание данных, которые находятся в разных таблицах. Для этого предварительно создают запрос на выборку в Конструкторе, для формирования новой таблицы с данными, а затем применяют Мастер перекрестных запросов. Продемонстрируем работу с Мастером перекрестных запросов для задачи, которая стоит перед руководителем организации.

 

Задача 9: Перекрестный запрос с использованием Мастера. Руководителю необходимо проанализировать состояние дел в организации по суммам, выплачиваемых надбавок за каждый вид категории должностей, в зависимости от стажа работы сотрудников на фирме.

Сведения о стаже работы сотрудника на фирме содержатся в таблице Сотрудники, а сведения о надбавки к окладу за отработанный стаж находятся в таблице Выслуга лет. Целесообразно создать запрос на построение таблицы, который позволит объединить такие поля, как: «Должность», «Стаж работы» (каждого сотрудника на фирме) и «Надбавка за стаж». Предположим, что такую таблицу с помощью Мастера запросов создали и сохранили ее под именем Формирование таблицы по надбавкам. Внешний вид таблицы представлен на рис. 6.30.

Рис. 6.30

 

Воспользуемся этой таблицей и выполним следующие действия:

1.      Перейти в режим «Запросы», нажать на кнопку «Создать». Выбрать в окне «Новый запрос», строку – «Перекрестный запрос», откроется окно, которое представлено на рис. 6.31. Чтобы увидеть перечень готовых запросов, необходимо отметить селекторную кнопку «Запросы».

Рис. 6.31

 

2.      Выделить строку в окне «Запрос: Формирование таблицы по надбавкам» и нажать на кнопку «Далее». Следующее окно Мастера предназначено для указания значений в будущей таблице, по которым проводится анализ. В решаемом примере, этими значениями будут данные из поля «Должность», как показано на рис. 6.32.

Рис. 6.32

 

3.      В следующем окне «Создание перекрестных таблиц» необходимо выбрать наименование полей, которые будут записаны в таблицу. Для решения примера выбираем поле «Стаж работы» (рис. 6.33) и двигаемся далее.

Рис. 6.33

 

4.      В открывшемся окне следует выбрать операцию, которую необходимо провести с данными в таблице. Для нашей задачи такой операцией является – Сумма, как это показано на рис. 6.34.

Рис. 6.34

 

5.      В последнем окне требуется ввести имя таблицы с перекрестным запросом и нажать на кнопку «Готово», после чего имя запроса для перекрестной таблицы сохранится в разделе «Запросы» базы данных, а на экране будет открыта таблица с результатами, которая показана на рис. 6.35.

Рис. 6.35

 

6.7.         Выводы

Формирование запросов входит в компетенции разработчика базы данных на физическом уровне, который реализует задачи, поставленные при логическом проектировании базы данных. Запросы различаются по назначению, поэтому они идентифицируются пиктограммами, которые имеют различные значки, что позволяет ориентироваться при его использовании в практической деятельности. Для созданной базы данных «Сотрудники фирмы» на рис. 6.36 отображено окно базы данных для объекта «Запросы» с перечнем созданных запросов. Тем не менее, следует отметить, что эксплуатация базы данных для лица, не знакомого с особенностями интерфейсов Access, вызывает некоторые сложности, поэтому, для конечного пользователя необходимо создавать специализированный интерфейс, позволяющий выполнять возложенные на него функции. С этой целью при проектировании базы данных на основе СУБД Access используют такие объекты, как: «Формы», «Отчеты», «Страницы», а для автоматизации работы пользователя встраивают объекты «Макросы» и «Модули».

Рис. 6.36

 

Вопросы для самоконтроля

1.       Как создать простой запрос к базе данных с помощью Мастера?

2.       Можно ли для формирования запроса использовать поля разных таблиц?

3.       Какие вычисления позволяет сделать Мастер запросов?

4.       Как с помощью Конструктор осуществляется проверка связанных таблиц в запросе?

5.       Какие существуют основные виды запросов в СУБД Access?

6.       Что следует понимать под термином: Запрос на изменение?

7.       В чем заключается процесс подготовки запроса на выборку?

8.       Как задать условия в запросе, чтобы осуществить контекстный поиск?

9.       Что можно добиться при создании запроса на добавление?

10.   Как сформировать предписание на удаление записей в таблице?

11.   Как использовать приложение Построитель выражений для формирования сложного запроса?

12.   В чем состоит технология создания окна с запросом, в каких случаях целесообразно это окно применять?

13.   Как создаются перекрестные запросы?

 

Задания для самостоятельной работы

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

·        Воспользуйтесь технологией создания перекрестных запросов для отображения распределения по категориям должностей на фирме с подсчетом суммарной заработной платы сотрудников, в зависимости от стажа работы на фирме. Сохраните запрос, например под именем «Сотрудники перекрестный-1».