Система управления базами данных Access. Создание запросов

Шандриков А.С.

Филиал учреждения образования

«Белорусский государственный технологический университет»

«Витебский государственный технологический колледж»

 

Система управления базами данных Access. Создание запросов

 

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

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

В СУБД ACCESS используются несколько типов запросов.

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

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

- открыть вкладку СОЗДАНИЕ;

- щёлкнуть ЛКМ по кнопке Мастер запросов в группе команд Запросы (рис. 1). Откроется диалоговое окно Новый запрос (рис. 2);

- выделить параметр Простой запрос и щёлкнуть ЛКМ по кнопке OK. Откроется окно Создание простого запроса (рис. 3);

Рис. 3. Окно Создание простого запроса для выбора полей

 - в раскрывающемся списке Таблицы и запросы открыть таблицу Студент;

 - в списке Доступные поля поочерёдно дважды щёлкнуть ЛКМ по названиям полей Номер_группы, Фамилия, Имя и Отчество. Указанные поля будут перемещены в список Выбранные поля;

- в раскрывающемся списке Таблицы и запросы открыть таблицу Факультет;

 - в списке Доступные поля поочерёдно дважды щёлкнуть ЛКМ по названиям полей Наименование_факультета и Декан. Указанные поля будут перемещены в список Выбранные поля. На рис. 3 представлен результат выполнения описанных действий;

- щёлкнуть ЛКМ по кнопке Далее;

Рис. 4. Окно Создание простого запроса для выбора имени запроса

- на экране появится очередное окно Создание запроса (рис. 4), в котором запрашивается имя создаваемого запроса и предлагается вариант по умолчанию. Пользователь может согласиться с предложенным вариантом или задать другое имя, например, Студенты и щёлкнуть ЛКМ по кнопке Готово. В результате выполненных действий откроется таблица (рис. 5), состоящая из всех заданных пользователем полей с отображаемой в них информацией.

Рис. 5. Таблица запроса

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

- открыть вкладку СОЗДАНИЕ;

- щёлкнуть ЛКМ по кнопке Конструктор запросов в группе команд Запросы (рис. 1);

Рис. 6. Окно Добавление таблицы

- в диалоговом окне Добавление таблицы (рис. 6) выбрать вкладку, содержащую объекты, данные из которых будут использованы в запросе. В рассматриваемом примере следует выбрать вкладку Таблицы (открыта по умолчанию);

- поочерёдно дважды щёлкнуть ЛКМ по каждому объекту, который нужно добавить в запрос, и щёлкнуть ЛКМ по кнопке Закрыть. Для создания рассматриваемого запроса потребуется выбрать таблицы Группа и Студенты.

- добавить нужные поля в строку Поле в бланке запроса и при необходимости указать условия и порядок сортировки. В строку Поле были добавлены поля Номер_группы из таблицы Группа и Фамилия, Имя, Отчество и Коммерческий из таблицы Студент. Для полей Группа и Фамилия была задана сортировка по возрастанию. В поле Коммерческий задано условие отбора Да. Результат выполнения данного действия представлен на рис. 7.

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

Рис. 8. Группа команд Результаты

- для просмотра результатов щёлкнуть ЛКМ по кнопке Выполнить из группы команд Результаты (рис. 8). На экране отобразится таблица с результатами запроса (рис. 9). В таблице приведены сведения только о тех студентах, которые обучаются на коммерческой основе.

Рис. 9. Вывод результатов запроса

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

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

- открыть вкладку СОЗДАНИЕ и щёлкнуть ЛКМ по кнопке Мастер запросов;

Рис. 10. Первое диалоговое окно с выбранным запросом

 - в открывшемся диалоговом окне Новый запрос (рис. 2) выделить параметр Перекрестный запрос и щёлкнуть ЛКМ по кнопке OK. Откроется окно Создание перекрёстного запроса (рис. 10);

- в области Показать выбрать опцию Запросы;

Рис. 11. Второе диалоговое окно с выбранными полями для заголовков строк

 - выбрать запрос Студенты и щёлкнуть ЛКМ по кнопке Далее;

- во втором диалоговом окне Создание перекрёстного запроса (рис. 11) в списке Доступные поля поочерёдно щёлкнуть ЛКМ по названиям полей Фамилия, Имя и Отчество. Указанные поля будут перемещены в список Выбранные поля. Значение этого поля будет использовано для создания строк таблицы. В случаях, когда поле содержит повторяющиеся значения в перекрёстном запросе будет произведено группирование данных;

- щёлкнуть ЛКМ по кнопке Далее. Откроется следующее диалоговое окно (рис. 12);

Рис. 12. Третье диалоговое окно с выбранными полями для заголовков столбцов

- щёлкнуть ЛКМ по названию поля Наименование_факультета. Значение указанного поля будет использовано для создания столбцов и их заголовков в таблице запроса. Каждому уникальному значению этого поля будет соответствовать свой столбец в таблице сформированного запроса;

Рис. 13. Четвёртое диалоговое окно с выбранными полем для ячейки таблицы и вычисляемой функцией

 - щёлкнуть ЛКМ по кнопке Далее. Откроется следующее диалоговое окно (рис. 13), в котором пользователь должен:

• задать поле для каждой ячейки, расположенной на пересечении строк и столбцов. В рассматриваемом примере это должно быть поле Номер_группы;

• в списке Функции выбрать функцию Min;

- щёлкнуть ЛКМ по кнопке Далее;

- в следующем открывшемся окне (рис. 14) задать имя запросу или оставить предложенное по умолчанию;

- щёлкнуть ЛКМ по кнопке Готово. На экране появится таблица сформированного запроса (рис. 15).

Из этой таблицы легко определить номер   группы, в которой обучается студент, и к какому факультету она относится.

Рис. 14. Окно с предложенным именем запроса

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

 

Рис. 15. Таблица полученного запроса (фрагмент)

Рис. 16. Таблица полученного запроса

с отсортированными по возрастанию полями (фрагмент)

 

На рис. 17 показан бланк создания запроса в режиме Конструктор. На этом рисунке видна вся информация, которая должна быть задана в ячейках бланка при создании запроса в режиме Конструктор запросов. 

Рис. 17. Бланк запроса в режиме Конструктор

Повторяющиеся значения. Данный вид запроса применяется при необходимости выявить количество повторяющихся записей или отыскать все записи с повторяющимися значениями. Повторяющиеся записи являются причиной избыточность информации, но при этом следует иметь ввиду, что среди повторяющихся записей могут быть и такие, наличие которых необходимо для правильной работы с БД. В некоторых случаях при просмотре данных, полученных после запроса, может сложиться впечатление, что в БД есть повторяющиеся записи, хотя пользователю известно, что базовые таблицы содержат только уникальные записи. Причина заключается в том, что при формировании запроса не были заданы поля, однозначно определяющие запись. Предположим, что был создан запрос на отыскание повторяющихся записей по номеру группы. В диалоговом окне Новый запрос (рис. 2.) был выделен параметр Повторяющиеся значения и после щелчка ЛКМ по кнопке OK во втором диалоговом окне Поиск повторяющихся записей (рис. 18) была выбрана таблица Студент. Во втором диалоговом окне (рис. 19) запрашиваются поля, содержащие повторяющиеся значения. Обычно в качестве такого поля с повторяющимися записями служит первичный ключ, но пользователь также

Рис. 18. Окно выбора таблицы с повторяющимися записями

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

Рис. 19. Окно с заданным полем для поиска повторяющихся записей

В следующем диалоговом окне (рис. 20) запрашиваются дополнительные поля для поиска повторяющихся записей. Если такие поля не задавать, то в результате запроса будет получена информация о количестве повторов в поле Номер_группы (рис. 21).

Рис. 20. Окно с пустым дополнительным полем для поиска повторяющихся записей

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

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

При указании трёх повторяющихся полей – Номер_группы, Фамилия и Имя – будут найдены однофамильцы с одинаковыми именами, обучающиеся в одной

Рис. 21. Результат выполнения запроса по одному полю

группе.

Если у однофамильцев разные имена, то записи, повторяющиеся в поле Номер_группы, в таблицу запроса будут выведены полностью со всеми заданными полями (рис. 23).

 

 

 

 

 

Рис. 22. Результат выполнения запроса по двум полям

Рис. 23. Результат выполнения запроса по трём полям

Рассмотрев приведённые примеры можно сделать следующий выводы:

 

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

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

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

- открыть вкладку СОЗДАНИЕ и в группе команд Запросы щёлкнуть ЛКМ по кнопке Конструктор запросов;

- в открывшемся окне Добавление таблицы (рис. 6) выбрать таблицу, для которой создаётся запрос (в рассматриваемом случае – таблицу Студент) и последовательно щёлкнуть ЛКМ по кнопкам Добавить и Закрыть;

- на открывшейся вкладке КОНСТРУКТОР в группе команд Тип запроса (рис. 24) щёлкнуть ЛКМ по кнопке Удаление. Откроется бланк запроса (рис. 25);

Рис. 24. Группа команд Тип запроса

Рис. 25. Бланк запроса на удаление

- перетащить из левой части окна в верхнюю часть бланка запроса таблицу, из которой будут удаляться записи (таблицу Студент);

- перетащить знак «звёздочка» (*) из списка полей таблицы Студент в ячейку Поле бланка запроса. В ячейке Имя таблицы появится поле Студент, а в ячейке Удаление в поле Студент – значение Из;

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

- ввести условие в ячейку Условие отбора. В данном примере таким условием будет <#31.12.1999#.

- для просмотра записей, которые будут удалены, следует подать команду Режим/Режим таблицы из группы команд Результаты. Автоматически открывается вкладка ГЛАВНАЯ и экране будет отображена таблица удаляемых записей (рис. 26);

Рис. 26. Таблица удаляемых записей (фрагмент)

- для удаления записей необходимо по команде Режим/Конструктор из группы команд Режимы вернуться в бланк запроса, а затем на открывшейся вкладке КОНСТРУКТОР щёлкнуть ЛКМ по кнопке Выполнить из группы команд Результаты. Записи будут удалены.

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

Для создания запроса необходимо:

Рис. 27. Бланк запроса на обновление

- после активизации режима Конструктор запросов и выбора таблицы Факультет в открывшемся окне Добавление таблицы (рис. 6) выбрать таблицу Факультет и последовательно щёлкнуть ЛКМ по кнопкам Добавить и Закрыть;

- щёлкнуть ЛКМ по кнопке Обновление из группы команд Тип запроса;

- перетащить из списка полей в бланк запроса обновляемое поле Наименование_факультета (рис. 27);

- в появившемся поле Обновление набрать обновляющий текст «Экономико-технологический»;

- поле Условие отбора набрать обновляемый текст «Экономический»;

- для обновления записи щёлкнуть ЛКМ по кнопке Выполнить из группы команд Результаты. Записи будут обновлены во всех таблицах БД.

На создание таблицы. Запрос на создание таблицы создаёт новую таблицу, используя поля из одной или нескольких таблиц. Применение данного запроса целесообразно при создании таблицы для экспорта данных в другие БД ACCESS или при создании архивной таблицы, содержащей ранее созданные данные. Предположим, что необходимо создать сокращённую таблицу Студент_Новая_таблица, которая будет содержать поля Номер_группы, Фамилия, Имя и Отчество и Наименование_факультета из таблицы Студент, и поле Декан из таблицы Факультет. Для создания такого запроса:

- после активизации режима Конструктор запросов в открывшемся окне Добавление таблицы (рис. 2.160) выбрать таблицы Студент и Факультет и щёлкнуть ЛКМ по кнопке Закрыть;

- щёлкнуть ЛКМ по кнопке Создание таблицы из группы команд Тип запроса;

- в открывшемся окне Создание таблицы (рис. 28) задать имя таблицы Студент Новая таблица и щёлкнуть ЛКМ по кнопке OK;

Рис. 28. Окно с запросом имени создаваемой таблицы

- перетащить из списка полей в бланк запроса заданные поля. Результат выполненных действий представлен на рис. 29.

Рис. 29. Бланк запроса на создание таблицы

- щёлкнуть ЛКМ по кнопке Выполнить из группы команд Результаты. В списке объектов ACCESS появится созданная таблица, после чего она готова к просмотру и редактированию (при необходимости). На рис. 30 представлена созданная таблица.

Рис. 30. Таблица Студент Новая таблица (фрагмент)

На добавление записей. Данный запрос добавляет группу записей из одной или нескольких таблиц в конец заданной пользователем таблицы или в несколько таблиц. Предположим, что в таблицу Группа нужно добавить поля Наименование_факультета и Декан из таблицы Факультет. Для этого:

- открыть вкладку СОЗДАНИЕ и щёлкнуть ЛКМ по кнопке Конструктор запросов из группы команд Запросы.

- в появившемся окне Добавление таблицы (рис. 6) поочерёдно выбрать таблицы Группа и Факультет;

- щёлкнуть ЛКМ по кнопке Добавление из группы команд Тип запроса;

Рис. 31. Окно выбора таблицы, в которую добавляется запись

- в отрывшемся диалоговом окне Добавление (рис. 31) в раскрывающемся списке имя таблицы выбрать таблицу, в которую добавляется запись (записи). Так как таблица Группа находится в текущей БД, то активизировать опцию в текущей базе данных и щёлкнуть ЛКМ по кнопке OK;

- так как таблица Факультет содержит все нужные для добавления поля, то перетащить из списка полей в бланк запроса поля со «звездочками» (*) из таблиц Группа и Факультет (рис. 32).

Таблица Группа после  добавления полей представлена на рис. 33.

Рис. 33. Таблица Группа с добавленными полями (фрагмент)

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

 

Литература

 

Шандриков, А.С. Информационные технологии в лесном хозяйстве / А.С. Шандриков – Минск : РИПО, 2018 – 390 с.