Создание запросов в Access. Виды запросов
Система управления базами данных (СУБД) — это специальная программа или совокупность программ, которые необходимы для организации и ведения соответствующих баз данных. К одной из таких программ относится MS Access.
Понятие о запросах
Они применяются с целью сортировки, добавления, изменения, фильтрации, удаления определенной информации в БД. С их помощью производится отбор тех записей, которые нужны для составления определенных отчетов или форм, то есть работа осуществляется с частью базы данных.
При создании запросов в Access могут соединяться таблицы, группироваться и отбираться записи, подключаться расчетные операции.
Классификация запросов
Выделяют следующие виды запросов:
- Запрос на выборку, при котором происходит извлечение данных по указанному условию. В рамках данного запроса происходит группировка записей, а выполненные в полях таблицы вычисления представляются.
- Запрос на изменение, при котором изменяются данные в первоначальных таблицах. С помощью них данные могут подвергаться корректировке, а также могут создаваться новые таблицы.
- Запрос с параметрами, при котором вводятся определенные условия или данные.
- Перекрестные запросы, предназначенные для расчетов и предоставления данных, как правило, в форме электронных таблиц с целью облегчения анализа.
- SQL-запросы представляют собой запросы на получение определенной информации, их построение основано на соблюдении определенных правил с использованием определенного синтаксиса.
Запросы на выборку
Создание запросов в Access данного вида предполагает построение таблицы, содержащей такие же структурные элементы, как и обычная. Она создается на базе фактических данных.
Результаты представляют собой динамический набор данных, в связи с чем при закрытии набора записи «исчезают», оставаясь в первоначальных таблицах. Сохранение данных запросов означает сохранение их структуры.
Данные запросы формируются указанием полей и таблиц, их содержащих, включаемых в запрос, описанием рассчитываемых полей, совершаемых групповых операций над первоначальными записями, и формированием условий отбора (например, с какой по какую дату осуществлялась реализация определенной группы товаров).
Создание запросов в MS Access данного вида предполагает, что их можно создать вручную или при помощи «Мастера создания запросов».
Для определения полей и таблиц, включаемых в запрос, переходим в режим конструктора.
Для перехода в режим конструктора в Access 2013 нужно в области навигации кликнуть контекстной кнопкой мыши на имени формы и выбрать «Конструктор». Нажав ALT+F8, можно вызвать «Список полей», из которых поля можно перетащить непосредственно в форму.
Запросы на изменение
Эти виды запросов представляют собой некую разновидность первого вида, но нужны они для изменения данных, которые были извлечены. Они в Access помечены восклицательным знаком. В Access создание запросов на изменение возможно четырех типов:
- создание новых таблиц;
- добавление записей в исходные таблицы;
- изменение данных в исходных таблицах;
- удаление записей из исходных таблиц.
Поэтому данные действия могут разрушить базу данных, в связи с чем необходимо сначала проверять результат исполнения запроса, перейдя в режим таблицы.
Создание новых таблиц используется при архивировании данных, проведении бэкапов или экспорте данных.
Разновидностью запросов на изменение являются запросы на обновление, при использовании которых обновляются абсолютно все записи, которые удовлетворяют какому-либо заданному условию. Если в комплекс данных необходимо внести изменения, то используют данную разновидность.
Еще одной разновидностью являются запросы на удаление, которые уничтожают все записи, удовлетворяющие каким-либо заданным условиям.
Еще одной разновидностью рассматриваемых запросов являются запросы на добавление, при которых происходит добавление данных из одной таблицы в другую.
Перекрестные запросы
Данные виды применяют с целью объединения в перекрестную таблицу денежных или числовых данных, хранящихся в исходной таблице. Перекрестный запрос в Access создается при помощи мастера запроса, используя инструкции которого, можно легко создать данный вид. По своей сути перекрестный запрос похож на сводные таблицы Excel.
Перекрестная таблица создается при указании заголовков строк, столбцов, значений и групповой операции.
Схема построения данного типа таблицы сводится к следующему. С помощью СУБД осуществляется группировка данных по групповым полям. Данная операция проводится над числами в поле значений. В ячейку таблицы, находящуюся на пересечении столбца и строк, помещается итоговое значение этой группы.
Перекрестные запросы в Access, как правило, применяют для создания отчетов и диаграмм.
Запросы с параметрами
Данный вид запросов уточняет перед выполнением условие у пользователя.
Чтобы создать запрос в Access, необходимо в столбце запроса, в ячейке «Условие», поместить выражение ввода параметров в квадратных скобках.
Если запустить данный запрос, то выйдет диалоговое окно, в котором будет содержаться предложение ввода параметров.
SQL-запросы
В Access 2013 и других версий запросы выполняются при помощи языка структурированных запросов SQL.
Для создания SQL-запросов необходимо перейти в режим конструктора, затем на кнопке «Вид» выбрать «Режим SQL». В результате появится диалоговое окно, в котором в поле Select вводим названия столбцов, а в поле From — то, что будет выводиться в строках, например названия поставщиков (если первый столбец «Название»).
В Access используется не чистый SQL, а его диалект Jet-SQL. Основными инструкциями для запросов в этом языке являются: SELECT, по которому осуществляется выборка из записей по определенным условиям (названия полей исходных таблиц, переносящихся в результирующую таблицу), UPDATE — используется с целью редактирования записей, DELETE — для удаления каких-либо указанных записей, CREATE — для создания новых объектов БД. В MS Access также используются TRANSFORM для построения перекрестных запросов, WITH OWNER-ACCESS OPTION для создания специальных запросов пользователем, не имеющем доступа к таблицам, к которым должен быть доступ у этого запроса, IN (для обеспечения связи с удаленной БД), DISTINCTROW (создание запроса с возможным объединением данных). Также могут применяться итоговые функции SQL, встроенные функции Access и VBA.
В заключение
Создание запросов в Access происходит для формирования записей для других запросов, отчетов или форм. При помощи определенных действий собираются данные из нескольких таблиц. Запросы позволяют включить в формируемую таблицу отобранные поля, осуществить расчеты в каждой новой записи, отобрать записи, которые необходимы для удовлетворения неких условий, сгруппировать выражения с одинаковыми значениями в некоторых или одном поле, сформировать новую таблицу данных на основе существующих, добавить, удалить или обновить некоторые записи.
Источник
Какими способами можно создавать запросы
7. Формирование запросов в СУБД Access
7.1. Возможности , типы и способы создания запросов
Запрос – это важнейший инструмент для извлечения информации из одной или нескольких таблиц БД. Посредством запроса можно вносить изменения в саму БД. Запрос может служить источником данных для форм, отчетов и страниц доступа к данным. Его результатом является новая таблица , которая может быть просмотрена, проанализирована, а затем сохранена или не сохранена.
Запросы позволяют решать многие задачи, не прибегая к программированию. Например, представлять данные в агрегированном виде, производить вычисления над полями БД, группировать записи и находить для полей итоговые значения с помощью статистических функций: Sum , Avg ( соответственно сумма, среднее значений поля); Max , Min (соответственно максимальное, минимальное значение поля); Count (число значений поля) и др.
СУБД Access позволяет создавать запросы трех типов: запросы выбора, перекрестные запросы, запросы действия.
Запрос выбора является наиболее часто используемым типом запроса. Он дает возможность: выбирать записи, удовлетворяющие условиям отбора; включать в результирующую таблицу поля из одной или нескольких таблиц в нужном порядке; осуществлять вычисления над полями БД; выполнять статистические расчеты для групп записей. Разновидностью запроса выбора является запрос с параметрами — это запрос, при выполнении отображающий в собственном диалоговом окне приглашение ввести интересующее пользователя значение критерия отбора записей.
Перекрестный запрос представляет собой специальный запрос итогового типа. Он отображает результаты итоговых статистических расчетов над значениями некоторого поля в виде перекрестной таблицы. В ней значения одного или нескольких столбцов слева образуют заголовки строк, верхняя строка – заголовки столбцов из значений определенного поля, а на пересечении строк и столбцов – итоговые значения.
Запрос действия – это запрос , который вносит изменения в саму БД. Существует четыре типа запросов действия:
1) запрос на удаление — удаляет группу записей из одной таблицы или нескольких взаимосвязанных таблиц БД, для которых задано каскадное удаление связанных записей;
2) запрос на обновление — служит для изменения информации в полях таблицы БД;
3) запрос на добавление — производит добавление записей из таблицы с результатами запроса в таблицу БД;
4) запрос на создание таблицы — создает новую таблицу на основе всех или части данных из одной или нескольких таблиц БД. Этот запрос полезен в случае: создания таблицы для экспорта в другую БД Access; создания страниц доступа к данным, отображающих данные соответственно указанному моменту времени; создания резервной копии таблицы; создания архивной таблицы, содержащей старые записи.
СУБД Access позволяет создавать запросы с помощью Мастеров и с помощью Конструктора. Мастера используются для создания следующих запросов:
· простого запроса на выборку полей из источника запроса и подведение итогов;
· запроса на поиск повторяющихся записей в таблице;
· запроса на поиск записей, не имеющих подчиненных им записей в другой таблице.
С помощью Конструктора можно создать любой запрос выбора, перекрестный запрос, запрос действия.
7.2. Создание запроса выбора
Для создания запроса выбора с помощью Конструктора необходимо открыть окно Конструктора запроса, выполнив действия:
Окно БД Þ объект Запросы Þ [Создать] Þ
окно Новый запрос Þ выбрать Конструктор Þ
окно Добавление таблицы Þ выбрать таблицы-источники запроса Þ
Окно Конструктора запроса имеет вид как на рис. 1.
Рис. 1. Окно Конструктора запроса
Оно разделено на две панели.
Верхняя панель содержит схему данных запроса. В ней представлены списки полей, выбранных в качестве источника запроса таблиц и запросов. Если ранее была создана связь между этими таблицами, то она показывается на схеме данных. В противном случае может отображаться связь, автоматически созданная системой Access . Пользователь может сам установить новую связь между таблицами.
Нижняя панель есть бланк запроса по образцу ( QBE -запроса). Он представлен в виде таблицы, предназначенной для определения структуры результирующей таблицы запроса и задания условий отбора данных из таблиц. Каждый столбец бланка относится к одному полю таблицы. Строки бланка имеют следующее назначение:
· Поле – указывает имена полей , участвующих в формировании запроса;
· Имя таблицы — указывает имена таблиц, которым принадлежат эти поля;
· Сортировка – дает возможность отсортировать записи в результирующей таблице запроса;
· Вывод на экран – позволяет управлять отображением полей в этой таблице;
· Условие отбора – служит для задания условий отбора записей;
· или – позволяет объединять условия отбора логической операцией ИЛИ. При этом условия отбора могут указываться в нескольких строках бланка запроса.
Включение отдельных полей в бланк запроса можно выполнить одним из следующих способов:
· перетащить поле из списка полей в крайнюю слева свободную клетку строки Поле;
· дважды щелкнуть по имени поля в списке полей ;
· щелкнуть в клетке строки Поле и из раскрывающегося списка выбрать нужное поле.
Включение в бланк запроса всех полей таблицы можно выполнить, если:
· дважды щелкнуть по имени таблицы и перетащить все выделенные поля в клетку строки Поле ;
· перетащить звездочку , стоящую под заголовком таблицы, в клетку строки Поле . При этом в клетке отобразится только имя таблицы со звездочкой, но в результат запроса будут включены все поля таблицы.
Для очистки отдельных столбцов бланка запроса используется команда
Правка / Удалить столбцы
Для очистки всего бланка запроса служит команда
Правка / Очистить бланк
Формирование запроса на вывод полей из одной или нескольких таблиц
Перетаскиваются из списков полей в строку Поле только те поля, которые должны присутствовать в результирующей таблице запроса, или все поля всех таблиц, а затем в сроке Вывод на экран отмечаются флажками только нужные.
Формирование запроса с применением сортировки
Сортировка возможна по одному или нескольким полям одновременно. Для сортировки по нескольким полям поля располагаются в бланке запроса в том порядке, в котором требуется выполнять сортировку. В Access записи сортируются сначала по самому левому полю, затем по полю, расположенному в следующем столбце справа и т.д. Для задания порядка сортировки необходимо щелкнуть в строке Сортировка для поля, по которому сортируются записи, и из раскрывающегося списка выбрать – по возрастанию или по убыванию.
Формирование запроса с условиями отбора
Условия отбора — это ограничения, накладываемые на запрос для определения записей, включаемых в результирующую таблицу запроса. Они задаются выражениями в строках Условие отбора , или .
Если выражения вводятся в несколько клеток одной строки Условие отбора , то они автоматически объединяются с помощью логического оператора And. Если выражения вводятся в разные строки бланка запроса, то Access объединяет их логическим оператором Or.
В условии отбора может быть использована конструкция Between (между). Например , Between 100 and 200.
Формирование запроса с вычисляемым полем
Для создания вычисляемого поля в пустую клетку строки Поле вводится имя вычисляемого поля с двоеточием, после которого – выражение. Например,
Если выражение сложное, то для его создания целесообразно использовать построитель выражений.
Формирование запроса с группировкой
Часто требуется в таблице видеть не все записи, а только итоговые значения по группам записей. Расчет итогов для некоторых полей групп производится с помощью статистических функций, которые были описаны выше.
Для создания запроса с группировкой выполняется следующее:
· перетаскивается в первую клетку строки Поле то поле, по которому производится группировка записей. Затем перетаскиваются в последующие клетки поля, по которым подводятся итоги;
· вводится команда Вид / Групповые операции. В бланке запроса появляется новая строка Групповая операция , в которой для всех полей указано Группировка;
· в строке Групповая операция для полей, по которым подводятся итоги, производятся щелчки и из раскрывающегося списка выбирается требуемая статистическая функция.
Формирование запроса с параметрами
Если необходимо часто выполнять один и тот же запрос выбора, меняя только в условиях отбора значения полей, то целесообразно создать запрос с параметрами. В строках Условие отбора, или для полей, играющих роль параметров, вводится в квадратных скобках текст приглашения на ввод интересующих пользователя значений этих полей. Этот текст будет выводиться в диалоговом окне Введите значение параметра при выполнении запроса.
7. 3. Создание перекрестного запроса
Создание перекрестного запроса с помощью Конструктора начинается с открытия окна Конструктора запроса. В этом окне в бланк запроса последовательно перетаскиваются:
· поля, значения которых будут заголовками строк перекрестной таблицы;
· поле, значения которого будут заголовками столбцов перекрестной таблицы;
· поле, по которому подводится итог с использованием статистической функции.
Затем выполняется команда Запрос / Перекрестный. В результате в бланке запроса появятся две новые строки: Групповая операция и Перекрестная таблица. В строке Перекрестная таблица для полей со значениями в роли заголовков строк выбирается из раскрывающегося списка значение Заголовки строк, а для поля со значениями в роли заголовков столбцов – Заголовки столбцов. В строке Групповая операция для поля, по которому подводится итог, из раскрывающегося списка выбирается необходимая статистическая функция.
7.4. Создание запросов действия
Формирование запроса на создание таблицы БД
После открытия окна конструктора запроса вводится команда
Запрос / Создание таблицы
В появившемся окне Создание таблицы указывается имя создаваемой таблицы и куда ее следует поместить – в текущую БД или в другую БД. Затем из списков полей перетаскиваются в бланк запроса поля, которые должны быть в этой таблице, и при необходимости задаются условия отбора записей. После выполнения запроса новая таблица будет в списке таблиц окна БД.
Формирование запроса на обновление полей таблицы БД
После открытия окна конструктора запроса вводится команда
В бланке запроса появится новая строка Обновление. Затем в бланк запроса перетаскиваются все поля таблицы, и для полей, подлежащих обновлению, в строке Обновление задаются выражения, значения которых будут новыми значениями обновляемых полей. При необходимости обновления значений полей только в некоторых записях задаются условия отбора записей.
Формирование запроса на добавление записей к таблице БД
При открытии окна конструктора запроса в качестве источника запроса указывается таблица, из которой добавляются записи в другую таблицу. Записи таблицы-источника должны содержать такие же поля, что и пополняемая таблица БД. В окне конструктора запроса вводится команда Запрос / Добавление. Появится диалоговое окно Добавление, в котором требуется указать имя пополняемой таблицы и где эта таблица находится – в текущей БД или в другой БД. Кроме того, в бланке запроса появится новая строка Добавление.
Затем перетаскиваются те поля из списка полей таблицы-источника, которые совпадают с полями пополняемой таблицы. Их имена Access автоматически укажет в строке Добавление как имена полей пополняемой таблицы.
Формирование запроса на удаление записей из таблицы БД
Если между таблицами установлена связь с обеспечением целостности данных, но без каскадного удаления записей, то прежде составляется запрос на удаление записей из подчиненной таблицы, а затем – из главной.
В этом случае при удалении записей из подчиненной таблицы в окне конструктора запроса вводится команда Запрос / Удаление . В результате в бланке запроса появится новая строка Удаление. Тогда из списка полей подчиненной таблицы перетаскивается в бланк запроса символ звездочки (*), после чего в строке Удаление для этого поля отобразится значение Из. Затем из списка полей главной таблицы перетаскиваются поля, участвующие в условии отбора удаляемых записей, и для них в строке Удаление появится значение Условие. После этого задаются условия отбора удаляемых записей.
Если связь между таблицами с обеспечением целостности данных и с каскадным удалением записей, то создается запрос на удаление записей только из главной таблицы.
При составлении запроса на удаление записей из главной таблицы (в обоих случаях) в окне конструктора запроса вводится команда Запрос / Удаление.
В бланк за п роса перетаскиваются поля, участвующие в условиях отбора записей на удаление, и задаются условия отбора.
7.5. Выполнение и сохранение запроса
После формирования запроса его необходимо выполнить. Из окна Конструктора запроса это можно сделать, введя одну из команд:
Вид / Режим таблицы
Если результаты выполнения запроса не удовлетворяют, то можно вернуться к окну Конструктора запроса для его модификации по команде Вид / Конструктор . В противном случае результат запроса можно сохранить с помощью команды Файл / Сохранить или произведя закрытие окна Конструктора запроса.
7 . Формирование запросов в СУБД Access
Тренировочные задания
1. Создать многотабличный запрос на вывод из БД ДЕКАНАТ-БУХГАЛТЕРИЯ сведений о студентах следующей структуры:
В запросе записи рассортировать по алфавитному порядку ФИО.
2. Создать запрос на вывод из БД сведений о семейных студентах (которые женаты или замужем). Результирующая таблица запроса должна иметь следующую структуру:
3. Создать запрос на вывод из БД сведений о неуспевающих студентах (у которых оценка и по информатике, и по математике 2). Результирующая таблица запроса должна быть следующей структуры:
| НОМ_ЗАЧ | ФИО | ГРУП | ОЦ_ПО_ИНФ | ОЦ_ПО_МАТЕМ |
4. Создать параметрический запрос, дающий возможность выводить из БД сведения об успеваемости студентов любой группы. Результирующая таблица запроса должна быть следующей структуры:
| ГРУП | НОМ_ЗАЧ | ФИО | ОЦ_ПО_ИНФ | ОЦ_ПО_МАТЕМ |
5. Создать запрос, в котором рассчитать для каждого студента средний балл, полученный им в сессию. Результирующая таблица запроса должна быть следующей структуры:
| ГРУП | ФИО | ОЦ_ПО_МАТЕМ | ОЦ_ПО_ИНФ | СРЕДН_БАЛЛ |
6. Создать итоговый запрос, позволяющий для каждого студента рассчитать сумму всех начислений. Результирующая таблица запроса должна иметь следующую структуру:
7. Создать перекрестный запрос, в котором для каждой группы получить суммы начислений по их видам. Результирующая таблица запроса должна иметь следующую структуру:
| ГРУП | МАТ_ПОМ | НАДБАВ | ПРЕМИЯ | СТИП |
Запрос сохранить с именем К_ОТЧЕТУ.
8. Создать запрос действия , позволяющий в таблице УСПЕВАЕМОСТЬ обновить поле КУРС: для студентов, не имеющих оценок 2, увеличить его значение на единицу.
7. Формирование запросов в СУБД Access
1 . Каково назначение запросов выбора ?
2 . Когда создается параметрический запрос ?
3 . Что собой представляет перекрестный запрос ?
4 . Перечислите типы запросов действия .
5 . Назовите возможные способы создания запросов в СУБД Access.
6. В виде чего выводится результат запроса?
7. Какую структуру имеет бланк запроса, отображаемый в окне Конструктора запроса
8. Как создается вычисляемое поле в запросе?
9. Какая команда используется для выполнения запроса из окна Конструктора запроса?
10. Можно ли сохранить результат запроса?
Источник
