Лабораторная работа по ACCESS №2 Запросы Базы данных и их конструирование

Предварительная работа:

1.Откройте базу данных «факультет.accbd», созданную в лабораторной работе «Создание таблиц баз данных и связей между ними в среде Microsoft Access».

2. Создайте недостающие таблицы и связи между ними по следующей схеме данных.

3. Для заполнения поля «название предмета» используйте «Мастер подстановок», для чего в режиме конструктора таблиц, при задании типа данных выберите значение «Мастер подстановок» и выполните все шаги. Для подстановки использовать переключатель «Будет введен фиксированный набор значений». Указать число столбцов – 1, ввести названия предметов. После этого создать подпись, которую будет содержать столбец подстановки, и сохранить измененную конструкцию таблицы.

4. Аналогичным образом заполните поля «вид отчетности»(зачет/экзамен) и «оценка»

Информация

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

Последовательное выполнение ряда запросов позволяет решать достаточно слож­ные задачи, не прибегая к программированию. В Access может создаваться нес­колько видов запросов:

- запрос на выборку – выбирает данные из таблиц и дру­гих запросов, и его резуль­та­том является таблица, которая существует до зак­ры­тия запроса;

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

- запросы на обновление, удаление, добавление – это запросы-действия, в результате вы­пол­не­ния которых изменяется таблицы исходных данных;

- перекрестный запрос – это запросы действия на основе которых можно пронаблюдать распределение величин по двум параметрам;

- параметрический запрос – это запрос с переменным условием.

Запрос на выборку играет особую роль, так как на его основе строятся запросы другого вида.

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

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

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

Порядок работы:

1.В окне базы данных выберите вкладку Создание. В разделе Другие выберите Конструктор запросов.

2.В появившемся окне Добавление таблицы выберите таблицы «Предмет» и «Сессия» и закройте это окно.

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

4.Щелкните на кнопке для просмотра запроса. Вернуться в режим конструктора можно нажатием кнопки (в правом нижнем углу).

5.Закройте запрос и сохраните его под именем «Предмет Преподаватель Дата».

Самостоятельное задание.

1.Составьте запрос по следующей схеме

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

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

?(вопрос) – заменяет один неизвестный символ;

*(звезда) – заменяет несколько неизвестных символов.

Создание запроса с использованием логических операций в условии отбора.

Откройте конструктор для создания нового запроса: Фамилии студентов, сдавших экзамены без троек.

Порядок работы:

1.В окне базы данных выберите вкладку Создание. В разделе Другие выберите Конструктор запросов.

2.В появившемся окне Добавление таблицы выберите таблицу «Сессия», «Студенты» и таблицу «Предмет», закройте это окно.

3.В окне конструктора появится возможность выбора полей из разных таблиц. Выберите поля ID Студента и оценка (из таблицы «Сессия»), поле Вид отчетности и Название предмета (из таблицы «Предметы») и поле Фамилия (из таблицы «Студенты»). В строке Условие отбора для поля Оценка следует записать: 4 Or 5. В строке Условие отбора для поля Вид отчетности введите «зачет». В результате получится следующая схема

4.Закройте запрос и сохраните его под именем «4 или 5 за зачет».

5.Откройте и просмотрите данные в запросе.

Создание запроса с групповыми операциями.

Откройте Конструктор для создания нового запроса: Количество студентов, сдавших экзамены по всем предметам.

Порядок работы:

1.В конструкторе запросов выберите две таблицы: «Предмет» и «Сессия».

2.Выберите поля Название предмета, ID предмета, Вид отчетности, ID Студентa. Уберите флаг «вывод на экран» в поле ID предмета. На вкладке Работа с запросами/Конструктор в разделе «Показать или скрыть» нажмите клавишу Итоги . В конструкторе появится строка Групповые операции. Среди всех групповых операций для поля ID Студента выберите операцию Count (счетчик) для подсчета количества записей в данном поле

3.Закройте конструктор и сохраните запрос под именем «Количество сдавших зачеты и экзамены».

4.Просмотрите результат.

Самостоятельное задание.

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

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

Таблица 1

Групповые операции запросов

Функция	Результат	Тип поля
Sum	Сумма значений поля	Числовой, Дата/время, Денежный и Счетчик
Avg	Среднее от значений поля	Числовой, Дата/время, Денежный и Счетчик
Min	Наименьшее значение поля	Текстовый, Числовой, Дата/время, Денежный и Счетчик
Max	Наибольшее значение поля	Текстовый, Числовой, Дата/время, Денежный и Счетчик
Count	Число значений поля без учета пустых значений	Текстовый, Числовой, Дата/время, Денежный, Счетчик, Логический, Поле объекта OLE
StDev	Среднеквадратичное откло­не­ние от среднего значения поля	Числовой, Дата/время, Денежный и Счетчик
Var	Дисперсия значений поля	Числовой, Дата/время, Денежный и Счетчик

Создание запроса с параметром.

Запрос с параметром используется для задания условия отбора данных. При запуске такого запроса на экран выдается диалоговое окно для ввода значения в качестве условия отбора. Чтобы создать запрос с параметром, необходимо ввести текст сообщения в строке Условие отбора.

Создайте следующий запрос: Предмет, по которым сдают экзамен или зачет (вид отчетности задать как параметр).

Порядок работы.

1.Откройте окно конструктора для создания запроса.

2.Выберите таблицу «Предмет». Выберите поля: Название, Фамилия лектора, Вид отчетности. В строке Условие выбора наберите следующий текст в квадратных скобках: введите вид отчетности. В результате получится следующая схема

3.Закройте запрос и назовите его «Параметр – экзамен или зачет».

Самостоятельное задание.

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

Создание запроса с вычисляемыми полями.

Выполните проектирование запроса, который: начисляет стипендию в зависимости от среднего балла, по следующему правилу: если средний балл 5 – стипендия 3600руб, если больше 4,5 – 2600руб, если >3,8 – стипендия 1600руб, иначе — стипендии нет.

Порядок работы.

1. Откройте окно конструктора для создания запроса.

Выберите таблицу «Студенты» и «сессия». Выберите поля: Фамилия, оценка.

2.Среди групповых операций для поля оценка выберите операцию Avg для подсчета среднего балла по всем предметам.

3.Для создания «Вычисляемого поля»:

- Установить курсор в крайний свободный столбец в режиме конструктора запросов

- Вызвать построитель выражений

- Построить выражение, используя предложенные пиктограммы знаков сравнения и встроенные функции Access

4.Для проверки указанного условия в вычисляемое поле следует ввести формулу:

Стипендия: IIf([Avg-оценка]=5;3600;IIf([Avg-оценка]>4,5;2600;IIf([Avg-оценка]>3,8;1600;0))).

В формуле использована функция поверки условия IIf. Общий формат записи функции можно записать в виде:

IIf(условие; команда 1;команда 2).

При истинности условия выполняется команда 1, в противном случае команда 2. В скобках указаны параметры функции:

[Avg-оценка] – поле таблицы, для которого выполняется проверка. Если используется поле из другой таблицы или в запросе используется несколько таблиц, то необходимо уточнить обращение до вида [Название таблицы]![Название поля].

0 – отсутствие начисления стипендии.

Дата добавления: 2015-10-29