какая операция реализуется в активных запросах субд access
Какая операция реализуется в активных запросах субд access
7. Формирование запросов в СУБД Access
СУБД Access позволяет создавать запросы трех типов: запросы выбора, перекрестные запросы, запросы действия.
Запрос выбора является наиболее часто используемым типом запроса. Он дает возможность: выбирать записи, удовлетворяющие условиям отбора; включать в результирующую таблицу поля из одной или нескольких таблиц в нужном порядке; осуществлять вычисления над полями БД; выполнять статистические расчеты для групп записей. Разновидностью запроса выбора является запрос с параметрами — это запрос, при выполнении отображающий в собственном диалоговом окне приглашение ввести интересующее пользователя значение критерия отбора записей.
Перекрестный запрос представляет собой специальный запрос итогового типа. Он отображает результаты итоговых статистических расчетов над значениями некоторого поля в виде перекрестной таблицы. В ней значения одного или нескольких столбцов слева образуют заголовки строк, верхняя строка – заголовки столбцов из значений определенного поля, а на пересечении строк и столбцов – итоговые значения.
СУБД Access позволяет создавать запросы с помощью Мастеров и с помощью Конструктора. Мастера используются для создания следующих запросов:
· простого запроса на выборку полей из источника запроса и подведение итогов;
· запроса на поиск повторяющихся записей в таблице;
· запроса на поиск записей, не имеющих подчиненных им записей в другой таблице.
С помощью Конструктора можно создать любой запрос выбора, перекрестный запрос, запрос действия.
7.2. Создание запроса выбора
Для создания запроса выбора с помощью Конструктора необходимо открыть окно Конструктора запроса, выполнив действия:
Окно БД Þ объект Запросы Þ [Создать] Þ
окно Новый запрос Þ выбрать Конструктор Þ
окно Добавление таблицы Þ выбрать таблицы-источники запроса Þ
Окно Конструктора запроса имеет вид как на рис. 1.
Рис. 1. Окно Конструктора запроса
Оно разделено на две панели.
· Сортировка – дает возможность отсортировать записи в результирующей таблице запроса;
· Вывод на экран – позволяет управлять отображением полей в этой таблице;
· Условие отбора – служит для задания условий отбора записей;
· или – позволяет объединять условия отбора логической операцией ИЛИ. При этом условия отбора могут указываться в нескольких строках бланка запроса.
Включение отдельных полей в бланк запроса можно выполнить одним из следующих способов:
· перетащить поле из списка полей в крайнюю слева свободную клетку строки Поле;
· дважды щелкнуть по имени поля в списке полей ;
· щелкнуть в клетке строки Поле и из раскрывающегося списка выбрать нужное поле.
Включение в бланк запроса всех полей таблицы можно выполнить, если:
· дважды щелкнуть по имени таблицы и перетащить все выделенные поля в клетку строки Поле ;
Для очистки отдельных столбцов бланка запроса используется команда
Правка / Удалить столбцы
Для очистки всего бланка запроса служит команда
Правка / Очистить бланк
Формирование запроса на вывод полей из одной или нескольких таблиц
Перетаскиваются из списков полей в строку Поле только те поля, которые должны присутствовать в результирующей таблице запроса, или все поля всех таблиц, а затем в сроке Вывод на экран отмечаются флажками только нужные.
Формирование запроса с применением сортировки
Сортировка возможна по одному или нескольким полям одновременно. Для сортировки по нескольким полям поля располагаются в бланке запроса в том порядке, в котором требуется выполнять сортировку. В Access записи сортируются сначала по самому левому полю, затем по полю, расположенному в следующем столбце справа и т.д. Для задания порядка сортировки необходимо щелкнуть в строке Сортировка для поля, по которому сортируются записи, и из раскрывающегося списка выбрать – по возрастанию или по убыванию.
Формирование запроса с условиями отбора
Формирование запроса с вычисляемым полем
Для создания вычисляемого поля в пустую клетку строки Поле вводится имя вычисляемого поля с двоеточием, после которого – выражение. Например,
Если выражение сложное, то для его создания целесообразно использовать построитель выражений.
Формирование запроса с группировкой
Часто требуется в таблице видеть не все записи, а только итоговые значения по группам записей. Расчет итогов для некоторых полей групп производится с помощью статистических функций, которые были описаны выше.
Для создания запроса с группировкой выполняется следующее:
· перетаскивается в первую клетку строки Поле то поле, по которому производится группировка записей. Затем перетаскиваются в последующие клетки поля, по которым подводятся итоги;
· в строке Групповая операция для полей, по которым подводятся итоги, производятся щелчки и из раскрывающегося списка выбирается требуемая статистическая функция.
Формирование запроса с параметрами
Если необходимо часто выполнять один и тот же запрос выбора, меняя только в условиях отбора значения полей, то целесообразно создать запрос с параметрами. В строках Условие отбора, или для полей, играющих роль параметров, вводится в квадратных скобках текст приглашения на ввод интересующих пользователя значений этих полей. Этот текст будет выводиться в диалоговом окне Введите значение параметра при выполнении запроса.
7. 3. Создание перекрестного запроса
Создание перекрестного запроса с помощью Конструктора начинается с открытия окна Конструктора запроса. В этом окне в бланк запроса последовательно перетаскиваются:
· поля, значения которых будут заголовками строк перекрестной таблицы;
· поле, значения которого будут заголовками столбцов перекрестной таблицы;
· поле, по которому подводится итог с использованием статистической функции.
Затем выполняется команда Запрос / Перекрестный. В результате в бланке запроса появятся две новые строки: Групповая операция и Перекрестная таблица. В строке Перекрестная таблица для полей со значениями в роли заголовков строк выбирается из раскрывающегося списка значение Заголовки строк, а для поля со значениями в роли заголовков столбцов – Заголовки столбцов. В строке Групповая операция для поля, по которому подводится итог, из раскрывающегося списка выбирается необходимая статистическая функция.
7.4. Создание запросов действия
Формирование запроса на создание таблицы БД
После открытия окна конструктора запроса вводится команда
Запрос / Создание таблицы
В появившемся окне Создание таблицы указывается имя создаваемой таблицы и куда ее следует поместить – в текущую БД или в другую БД. Затем из списков полей перетаскиваются в бланк запроса поля, которые должны быть в этой таблице, и при необходимости задаются условия отбора записей. После выполнения запроса новая таблица будет в списке таблиц окна БД.
Формирование запроса на обновление полей таблицы БД
После открытия окна конструктора запроса вводится команда
В бланке запроса появится новая строка Обновление. Затем в бланк запроса перетаскиваются все поля таблицы, и для полей, подлежащих обновлению, в строке Обновление задаются выражения, значения которых будут новыми значениями обновляемых полей. При необходимости обновления значений полей только в некоторых записях задаются условия отбора записей.
Формирование запроса на добавление записей к таблице БД
При открытии окна конструктора запроса в качестве источника запроса указывается таблица, из которой добавляются записи в другую таблицу. Записи таблицы-источника должны содержать такие же поля, что и пополняемая таблица БД. В окне конструктора запроса вводится команда Запрос / Добавление. Появится диалоговое окно Добавление, в котором требуется указать имя пополняемой таблицы и где эта таблица находится – в текущей БД или в другой БД. Кроме того, в бланке запроса появится новая строка Добавление.
Затем перетаскиваются те поля из списка полей таблицы-источника, которые совпадают с полями пополняемой таблицы. Их имена Access автоматически укажет в строке Добавление как имена полей пополняемой таблицы.
Формирование запроса на удаление записей из таблицы БД
Если между таблицами установлена связь с обеспечением целостности данных, но без каскадного удаления записей, то прежде составляется запрос на удаление записей из подчиненной таблицы, а затем – из главной.
Если связь между таблицами с обеспечением целостности данных и с каскадным удалением записей, то создается запрос на удаление записей только из главной таблицы.
При составлении запроса на удаление записей из главной таблицы (в обоих случаях) в окне конструктора запроса вводится команда Запрос / Удаление.
В бланк за п роса перетаскиваются поля, участвующие в условиях отбора записей на удаление, и задаются условия отбора.
7.5. Выполнение и сохранение запроса
После формирования запроса его необходимо выполнить. Из окна Конструктора запроса это можно сделать, введя одну из команд:
Вид / Режим таблицы
7 . Формирование запросов в СУБД Access
Тренировочные задания
1. Создать многотабличный запрос на вывод из БД ДЕКАНАТ-БУХГАЛТЕРИЯ сведений о студентах следующей структуры:
В запросе записи рассортировать по алфавитному порядку ФИО.
2. Создать запрос на вывод из БД сведений о семейных студентах (которые женаты или замужем). Результирующая таблица запроса должна иметь следующую структуру:
3. Создать запрос на вывод из БД сведений о неуспевающих студентах (у которых оценка и по информатике, и по математике 2). Результирующая таблица запроса должна быть следующей структуры:
4. Создать параметрический запрос, дающий возможность выводить из БД сведения об успеваемости студентов любой группы. Результирующая таблица запроса должна быть следующей структуры:
5. Создать запрос, в котором рассчитать для каждого студента средний балл, полученный им в сессию. Результирующая таблица запроса должна быть следующей структуры:
6. Создать итоговый запрос, позволяющий для каждого студента рассчитать сумму всех начислений. Результирующая таблица запроса должна иметь следующую структуру:
7. Создать перекрестный запрос, в котором для каждой группы получить суммы начислений по их видам. Результирующая таблица запроса должна иметь следующую структуру:
Запрос сохранить с именем К_ОТЧЕТУ.
7. Формирование запросов в СУБД Access
6. В виде чего выводится результат запроса?
7. Какую структуру имеет бланк запроса, отображаемый в окне Конструктора запроса
8. Как создается вычисляемое поле в запросе?
9. Какая команда используется для выполнения запроса из окна Конструктора запроса?
10. Можно ли сохранить результат запроса?
Ищем педагогов в команду «Инфоурок»
Специальность 34.02.01 «Сестринское дело»
При запуске Ms Access возникает диалоговое окно, предлагающее открыть одну из баз данных (программа может работать только с одной БД), с которой уже работали, или создать новую.
Окно базы данных появляется при открытии базы данных. Основные функции окна:
просмотр состава БД;
открытие объектов БД;
создание и удаление объектов БД.
После создания файла базы данных необходимо создать таблицы.
Первым этапом при создании таблицы является определение перечня полей, из которых она должна состоять, их типов и размеров.
Каждому полю таблицы присваивается уникальное имя, которое не может содержать более 64 символов, не разрешается использовать символы:
При проектировании и создании новой базы данных вы разрабатываете одну или несколько таблиц, продумываете поля (столбцы) для каждой таблицы, а затем задаете тип данных для каждого поля. Например, если требуется хранить значения даты и времени, выбирается тип данных «Дата/время», если необходимо хранить имена и адреса, для одного или нескольких полей задается тип данных «Текст», и т. д.
В Access 10 можно использовать следующие типы полей :
1.1 Алгоритм создания таблицы:
перейдите в окно база данных ;
выделите пункт Таблицы на панели Объекты ;
нажмите кнопку Создать ;
выберите режим её создания в появившемся окне Новая таблица :
Создание таблицы в режиме конструктора;
Создание таблицы с помощью мастера;
Создание таблицы путем ввода данных (в режиме таблицы).
1. В окне диалога Новая таблица нажмите кнопку Мастер .
2. В окне диалога Создание таблиц выберите Образец таблицы.
3. Определите перечень полей, которые вы хотите включить в таблицу. Для этого поочередно выделяйте мышью имя поля в списке образцы полей и нажимайте кнопку.
Алгоритм создания таблицы в режиме Конструктора :
Дважды щелкнуть левой кнопкой мыши на ярлыке Создание таблицы с помощью конструктора или нажать на кнопку Создать в верхней части окна базы данных, выбрать из списка в окне Новая таблица элемент Конструктор и нажать кнопку ОК. В том и в другом случае откроется пустое окно Конструктора таблиц.
Каждое поле обладает индивидуальными свойствами (характеристиками), по которым можно установить, как должны сохраняться, отображаться и обрабатываться данные. Набор свойств зависит от выбранного типа данных.
Число десятичных знаков
Значение по умолчанию
Условие на значение
Сообщение об ошибке
Свойство поля: маска ввода
Алгоритм создания таблицы в режиме Таблица :
Оставьте режим таблицы и щелкните по кнопке ОК.
Появится пустая таблица, поля которой не определены и не имеют названия. Тип поля будет выбран автоматически в зависимости от введенной информации.
Аналогично поступите для всех полей.
Любая таблица Microsoft Access может быть представлена в двух режимах:
1) режиме таблицы, предназначенном для ввода данных, их просмотра и редактирования;
2) режиме конструктора, предназначенном для создания и изменения таблицы.
Для изменения внешнего вида таблицы можно:
Изменить ширину столбцов.
Изменить ширину строк (только все сразу).
Закрепить столбцы (выделенные столбцы переносятся к началу таблицы);
Освободить все столбцы (действие, обратное закреплению столбцов, освобожденные столбцы остаются в начале);
Скрыть столбцы (сделать ширину выделенных столбцов нулевой);
Отобразить столбцы (показать скрытые столбцы, выбрав их имена из списка).
Связь данных в таблицах
Алгоритм создания связи:
Проверьте, присутствуют ли связываемые таблицы в окне Схема данных и, если нет, добавьте их.
Выберите поле в главной таблице и перетащите его мышью на связываемое поле во второй таблице.
В появившемся диалоговом окне Изменение связей установите требуемые связи и их свойства:
Включите значок Обеспечение целостности данных. Это невозможно будет сделать, если типы обоих полей заданы не одинаково.
Анализ табличных данных
Проведем поиск записей по образцу в таблице Список.
установить курсор в нужное поле, например, Фамилия ;
щелкнуть по кнопке, которая позволяет найти запись по введенному значению. В результате появится диалоговое окно;
Для сортировки записей можно также использовать команду меню Записи | Сортировка. Для сортировки записей формы по нескольким полям она должна быть открыта в режиме таблицы. При переходе в режим формы просмотр записей формы будет осуществляться в соответствии с произведенной сортировкой.
Способы создания формы:
Быстрые мастера создают формы одного из трех стандартных видов, включая в форму все поля выбранной таблицы или запроса.
Мастер форм предоставляет расширенные возможности по настройке формы.
Конструктор позволяет «вручную» доработать форму до желаемого результата.
Мастера для создания специализированных форм (диаграмма, сводная таблица).
Мастер форм представляет расширенные возможности по настройке форм по сравнению с быстрыми мастерами:
В форму могут быть включены данные нескольких таблиц или запросов (выбранные таблицы должны быть связаны).
Можно выбрать поля из списка доступных полей и задать будет ли поле отображено в форме.
Установить режим вывода информации (таблица, лента, столбец или выровненный).
Выбрать стиль оформления.
3.2 Конструктор форм
Создание формы с помощью Мастера редко дает удовлетворительный результат. Доработка формы происходит в режиме конструктора.
Основные используемые инструменты:
Форма в режиме Конструктор состоит из нескольких разделов:
Заголовок формы и Примечание формы
Верхний колонтитул и Нижний колонтитул
Создание элемента управления:
С помощью Панели элементов в форме могут быть размещены разные элементы управления.
Алгоритм вставки элемента управления:
Нажмите кнопку, соответствующую создаваемому элементу.
Начертите мышью в бланке прямоугольник, определяющий размер выбранного элемента.
фильтровать и сортировать данные;
группировать записи (рассчитывая различные итоговые значения) ;
вносить изменения сразу в несколько записей;
создавать таблицы, как результат запроса.
1. Запрос на выборку осуществляет загрузку и отображение записей из одной или различных таблиц. Запрос на выборку можно использовать не только для того, чтобы выбирать из таблиц базы данных содержащуюся в них информацию, но также чтобы производить вычисления и отображать результаты вычислений в динамической таблице. Сдедует иметь в виду, что результаты вычислений не сохраняются в таблицах базы данных. При выполнении запроса вычисления выполняются всякий раз заново, поэтому результаты всегда отражают текущее состояние базы данных.
2. Перекрестный запрос позволяет выполнить подсчеты (суммы, количество записей и средние значения) по данным одного поля таблицы. Результаты группируются в ячейках, соответствующих конкретным полям обсчитываемой БД.
3. Запрос на изменение помогает выполнить операции с записями, удовлетворяющими заданным критериям: запрос на удаление удаляет из таблицы заданные записи; запрос на обновление изменяет соответствующие записи таблицы; запрос на добавление добавляет в конец таблицы новые записи (соответствующие условиям отбора); запрос на создание таблицы создает новые таблицы, состоящие из заданных записей.
4. Запрос с параметрами позволяет ввести информацию, используемую в качестве критериев отбора.
Запрос (query) – это средство выбора необходимой информации из базы данных. Вопрос, сформированный по отношению к базе данных, и есть запрос.
Применяются два типа запросов: по образцу (QBE – Query by example) и структурированный язык запросов (SQL – Structured Query Language).
Запрос по образцу – средство для отыскания необходимой информации в базе данных. Он формируется не на специальном языке, а путем заполнения бланка запроса в окне Конструктора запросов.
1.1 Создание запроса на выборку с помощью Мастера
При создании query необходимо определить:
поля в базе данных, по которым будет идти поиск информации;
предмет поиска в базе данных;
перечень полей в результате выполнения запроса.
В окне база данных выбрать вкладку Запросы и дважды щелкнуть на пиктограмме Создание query с помощью мастера, появится окно Создание простых запросов.
Рис. 1.
Затем в окне Мастера надо выбрать подробный или итоговый отчет и щелкнуть на кнопке Далее. После этого необходимо задать имя запроса и выбрать один из вариантов дальнейшего действия: Открыть query для просмотра данных или Изменить макет запроса и нажать кнопку Готово. В результате чего получите готовый query.
1.2 Создание запроса на выборку с помощью Конструктора
Таким образом, при создании запросов на выборку в режиме конструктора можно выделить следующие этапы:
определение таблиц или запросов, на основе которых будет создаваться новый запрос;
определение порядка сортировки записей динамической таблицы;
определение полей, которые должны быть выведены в динамической таблице;
определение условий выбора данных;
Окно Конструктора состоит из двух частей – верхней и нижней. В верхней части окна размещается схема данных запроса, которая содержит список таблиц – источников и отражает связь между ними.
В нижней части окна находится Бланк построения запроса QBE (Query by Example), в котором каждая строка выполняет определенную функцию:
Поле – указывает имена полей, которые участвуют в запросе.
Имя таблицы – имя таблицы, с которой выбрано это поле.
Сортировка – указывает тип сортировки.
Вывод на экран – устанавливает флажок просмотра поля на экране.
Или – задаются дополнительные критерии отбора.
Рис. 2.
1.3 Вычисления в запросе
Запрос на выборку можно использовать не только для того, чтобы выбирать из таблиц базы данных содержащуюся в них информацию, но также чтобы производить вычисления и отображать результаты вычислений в динамической таблице. Следует иметь в виду, что результаты вычислений не сохраняются в таблицах базы данных. При выполнении запроса вычисления выполняются всякий раз заново, поэтому результаты всегда отражают текущее состояние базы данных.
Таким образом, запись полного имени поля таблицы выглядит следующим образом: [Имя таблицы]![Имя поля]. Имя вычисляемого поля выводится перед выражением и отделяется от него двоеточием.
Пр1: Выражение1: [Цена]*[Количество], где Цена и Количество — имена полей.
Пр2: Доход : [Продано]*[Цена]/6
Имя вычисляемого поля — Выражение1 — становится заголовком столбца в таблице с результатами выполнения запроса. Это имя можно изменить.
Для вычисляемых полей допускается сортировка, задание условий отбора и расчет итоговых значений как и для любых других полей. 
,
Для создания вычисляемого выражения можно использовать построитель выражений, окно которого представлено на рисунке
В верхней части окна построителя расположено поле выражения, в котором отображается создаваемое вычисляемое выражение. Microsoft Access часто помещает в это поле прототипы, заключенные в двойные угловые кавычки, вместо которых необходимо подставить нужные элементы. Выражения вводятся с помощью кнопок операторов и списков, расположенных в нижней части окна построителя.
Запросы на выборку можно также использовать для группировки записей и вычисления итоговых значений с помощью так называемых групповых функций :
групповая функция Sum вычисляет сумму всех значений заданного поля в каждой группе;
групповая функция Avg вычисляет среднее арифметическое всех значений данного поля в каждой группе;
групповая функция Min (Max) возвращает наименьшее (наибольшее) значение, найденное в этом поле в каждой группе;
групповая функция Count определяет количество записей в каждой группе и др.
Они позволяют извлечь из базы нужные сведения и представить их в виде, удобном для восприятия, а также предоставляют широкие возможности для обобщения и анализа данных.
Основные сведения в отчете берется из базовой таблицы, запроса или инструкции SQL, являющихся источниками данных для отчета. Другие сведения вводятся при разработке отчета. Для создания связи между отчетом и его источником данных применяются элементы управления (так же, как в формах). Такими элементами могут быть поля, надписи, линии или другие графические объекты.