Цель работы: научиться создавать запросы разных видов для работы с данными

Постановка задачи: создать запросы на выборку, создание таблиц, добавление и удаление данных.

Запросы в Access являются основным инструментом выборки, обновления и обработки данных в таблицах базы данных. Access в соответствии с концепцией реляционных баз данных для выполнения запросов использует язык структурированных запросов SQL (Structured Query Language). С помощью инструкций языка SQL реализуется любой запрос в Access. Рассмотрим пока графический способ создания запросов, не вникая в тонкости языка SQL.

Основным видом запроса является запрос на выборку. Результатом выполнения этого запроса является новая таблица, которая существует до закрытия запроса. Записи формируются путем объединения записей таблиц, на которых построен запрос. Способ объединения записей таблиц указывается при определении их связи в схеме данных или при создании запроса. Условия отбора, сформулированные в запросе, позволяют фильтровать записи, составляющие результат объединения таблиц.

В Access может быть создано несколько видов запроса:

• запрос на выборку — выбирает данные из одной таблицы или запроса или нескольких взаимосвязанных таблиц и других запросов. Результатом является таблица, которая существует до закрытия запроса. Формирование записей таблицы результата производится в соответствии с заданными условиями отбора и при использовании нескольких таблиц путем объединения их записей;

• запрос на создание таблицы — выбирает данные из взаимосвязанных таблиц и других запросов, но, в отличие от запроса на выборку, результат сохраняет в новой постоянной таблице;

• запросы на обновление, добавление, удаление — являются запросами действия, в результате выполнения которых изменяются данные в таблицах.

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

С помощью запроса можно выполнить следующие виды обработки данных:

• включить в таблицу запроса выбранные пользователем поля таблицы;

• произвести вычисления в каждой из полученных записей;

• выбрать записи, удовлетворяющие условиям отбора;

• сформировать на основе объединения записей взаимосвязанных таблиц новую виртуальную таблицу;

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

• создать новую таблицу базы данных, используя данные из существующих таблиц;

• произвести обновление полей в выбранном подмножестве записей;

• удалить выбранное подмножество записей из таблицы базы данных;

• добавить выбранное подмножество записей в другую таблицу.

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

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

Запрос с условием – запрос, позволяющий выбрать записи, удовлетворяющие определённому условию.

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

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

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

Статистические функции применимы, прежде всего, к полям с типом данных Числовой, Денежный, Дата/время.

В Access предусматривается девять статистических функций:

Sum — сумма значений некоторого поля для группы;

Avg — среднее от всех значений поля в группе;

Max, Min — максимальное, минимальное значение поля в группе;

Count — число значений поля в группе без учета пустых значений;

StDev — среднеквадратичное отклонение от среднего значения поля в группе;

Var — дисперсия значений поля в группе;

First и Last — значение поля из первой или последней записи в группе.

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

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

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

Создание простого запроса на выборку

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

Для создания запроса перейдите на вкладку «Создание» и откройте «Конструктор запросов». Поскольку вся информация хранится в таблице «Анкета», то необходимо добавить только ее.

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

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

Сохраните запрос под именем «Простой запрос на выборку».

Пример запроса на выборку из нескольких таблиц.

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

Откройте конструктор запросов и добавьте две таблицы: «Анкета» и «Ведомость по информатике». Обратите внимание, что ведомость должна быть правильно заполнена, учитывая таблицы «Зачеты» и «Анкета».

Добавьте поля «Фамилия», «Имя» и «Оценка».

Сохраните запрос под именем «Оценки по информатике».

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

Создание запроса с условием

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

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

Записать можно было все в одну строку поставив между значениями «OR».

Выполните запрос, если записи со значением «неудовлетворительно» (проверьте, что такие записи в таблице есть) не появились, значит запрос составлен верно.

Переименуйте запрос «Запрос с условием Успеваемость по информатике».

Создать запрос, который выведет всех студентов фамилии которых начинаются на букву «С», но имена не начинаются на «Т».

Ознакомьтесь с данными таблицы «Анкета» и подберите подходящие буквы, которые помогут удостовериться, что запрос работает правильно.

Результат запроса выглядит следующим образом

Фамилии только на букву «С», запись со студентом «Смирнова Татьяна Петровна» не вошла в выборку, поскольку имя начинается на букву «Т» и не удовлетворяет заданному условию.

Переименуйте запрос: «Запрос с условие Like, Not Like».

Создать запрос, который выводит фамилию и имя студентов, родившихся в период с 1999 по 2001 год

В условиях для данного запроса можно использовать либо знаки < >, либо Between.

Для следующего запроса необходим тип данных «Дата». В таблице «Преподаватели» поменяйте тип данных у поля «Год рождения» на «дата и время» и переименуйте на «Дата рождения». Маску сотрите. Сохраните запрос и перейдите в режим таблицы. Заполните актуальными данными таблицу.

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

Месяц можно поменять, в зависимости от данных в таблице «Преподаватели».

DatePart("m";[Год рождения])=6

Результат запроса следующий.

Переименуйте запрос: «Педагоги с днем рождения в июне».

Ознакомьтесь с теоретическим материалом Запросы с условием

Самостоятельно создайте три разных запроса с разными типами данных. Названия запросам задайте: "Самостоятельно с условием ...... "

Запрос с параметром

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

Если в условие отбора для поля «Фамилия» указать: [Введите фамилию], то при выполнении запроса появится следующее диалоговое окно

Введите любую фамилию и нажмите «ОК».

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

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

Переименуйте запрос: "Запрос с параметром, группы".

Посмотрите видео с возможностями Access при создании запроса с параметром.

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

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

В условии указываем Like далее пишем параметр [Введите первую букву имени] связку & и * поскольку длина фамилии может быть разной. Аналогичное условие и для поля с отчеством.

После выполнения запроса появятся диалоговые окна. Перед вводом данных проверьте, что такие студенты есть у вас в таблице «Анкета».

Результат будет следующим

Переименуйте запрос: «Запрос с параметром, первая буква имени и отчества».

Самостоятельно создать два запроса с параметром, дать названия по типу: «Самостоятельно с параметром ……»

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

Откройте конструктор запросов и в типе запросов выберите «Перекрестный».

После этого в нижней части окна конструктора должна появиться дополнительная строка «Перекрестная таблица».

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

Для данного запроса необходимы таблица «Анкета» и таблица «Группы».

Добавьте поля «Город проживания» из таблицы «Анкета», «Наименование» из таблицы «Группы» и «Фамилия» из таблицы «Анкета».

В качестве столбцов будущей таблицы будут наименования групп, строками будут непосредственно города, а на пересечении количество студентов. В строке «Групповые операции» у поля «id_зачетной_книжки » для вычисления количества используется функция Count.

Выполните получившийся запрос. Результат должен получиться следующий.

Сохраните запрос под именем «Перекрестный запрос».

Запрос «Создание таблицы»

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

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

В типе запросов выберите «Создание таблицы». Название новой таблицы: Оценки по дисциплине «Информатика».

Откройте новую таблицу и сравните с запросом.

Запрос на добавление, обновление и удаление

Скопируйте и вставьте таблицу «Анкета». В диалоговом окне выберите значение «только структура». Имя таблицы «Студенты группы Т-222» (имя таблицы устанавливанием учитывая данные собственной базы данных).

Создаем запрос на выборку с условием: записи только со значением поля группы «Т-222». Добавляем все поля таблицы «Анкета» Так как поле группа данной таблицы хранит не название самой группы, а ее id, то необходимо указать id_группы. Выполняем запрос. Результат должен быть следующий.

Открываем режим конструктора. Выбираем тип запроса «Добавление». В выпадающем списке выбираем таблицу «Студенты группы Т-222».

Выполняем запрос. Должно открыться диалоговое окно с добавлением записей. Нажмите кнопку «Да» и откройте таблицу и проверьте добавление данных.

Сохраните запрос под именем «Добавление студентов Т-222». Добавьте в таблицу «Анкета» студента из данной группы. Запустите запрос на добавление и проверьте добавился ли этот студент в таблицу «Студенты группы Т-222».

Создайте самостоятельно запрос на добавление. Имя запроса «Самостоятельно добавление…..».

Запрос на обновление

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

Откройте конструктор запросов и выберите тип запроса «Обновление».

Добавьте таблицу «Анкета» и «Ведомость по информатике».

В условие отбора необходимо добавить запись «[Введите фамилию]», как при запросе с параметром. В обновление добавить значение «отлично».

Откройте таблицы «Анкета», «Ведомость по информатике» и посмотрите фамилию студента с оценкой не «отлично». Запустите запрос и введите необходимую фамилию. Появится следующее окно.

Проверьте произошло ли обновление оценки.

Сохраните запрос под именем «Изменение оценки на отлично»

Запрос на удаление

Создать запрос на удаление студентов 1998 года рождения

Откройте конструктор запросов и выберите тип запроса «Удаление». Добавьте таблицу «Анкета» и выберите поле «Год рождения». В условие отбора укажите значение «1998» (можно указать другой год).

Появится окно на удаление данных.

Проверьте удалились ли записи. Сохраните запрос под именем «Запрос на удаление».