Практическая работа 7. обработка данных при помощи специальных запросов

ПРАКТИЧЕСКАЯ РАБОТА 7. ОБРАБОТКА ДАННЫХ ПРИ ПОМОЩИ СПЕЦИАЛЬНЫХ ЗАПРОСОВ

Цели:

Освоить технологию:

· создания перекрестных запросов с помощью конструктора;

· создание перекрестных запросов с помощью мастера;

· создание запросов на создание таблицы;

· создание запросов на изменение данных.

Оглавление

Задание 1. Создание перекрестного запроса с помощью конструктора 2

Задание 2. Для самостоятельной работы. 4

Задание 3. Отбор повторяющихся записей. 4

Задание 4. Создание перекрестного запроса с помощью мастера 6

Задание 5. Запрос на создание таблицы. 8

Задание 6. Запрос на изменение (обновление) 9

Задание 7. Запросы на добавление и удаление записей. 10

Виды специальных запросов

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

· Запрос на обновление – задается условие для отбора записей, которые надо обновить, и формула обновления, затем запросу присваивается специальный вид "Обновление". Каждое обращение к запросу производит очередное обновление таблицы, поэтому надо осторожно применять этот запрос.

· Запрос на удаление – задается условие для отбора записей, которые надо удалить из базы данных, затем запросу присваивается специальный вид "Удаление". Результаты запросы необратимы, поэтому надо осторожно применять этот запрос.

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

· Запрос на добавление – используется для добавления записей из одной таблицы в другую.

Часть 1. Перекрестные запросы

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

Перекрестный запрос можно создать с помощью мастера или в режиме конструктора.

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

Задание 1. Создание перекрестного запроса с помощью конструктора

Как правило, если студент сдает экзамен или зачет по дисциплине вовремя, то он имеет одну оценку по предмету в каждом семестре. Но в рассматриваемой модели предусмотрен случай, когда студент может иметь несколько оценок по предмету в одном семестре. Например, если он сначала, не явился на экзамен (или зачет), потом явился, но не сдал предмет, потом сдал на «3», потом на «4» и т.д. В качестве оценки по предмету понимается последняя полученная оценка (или максимальная, что одно и то же).

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

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

Пример итоговой таблицы запроса приведен на Рис. 1.

практическая работа 7. обработка данных при помощи специальных запросов - student2.ru

Рис. 1. Фрагмент из итоговой таблицы перекрестного запроса Итога семестра

Технология работы

1. Создайте новый запрос в режиме конструктора (на ленте вкладка Создание/группа Другие/Конструктор запросов (Рис. 2).

2. Добавьте в запрос таблицы Группа, Студент, Дисциплина, Оценка.

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

4. Включите в запрос поля в следующем порядке

· Номер группы из таблицы Группа

· Фамилия, Имя, Отчество из таблицы Студент,

· Семестр из таблицы Оценка,

· Название (предмета) из таблицы Дисциплина,

· Оценка из одноименной таблицы.

5. Задайте сортировку по фамилии.

6. В поле Номер группы задайте условие отбора – номер какой-нибудь группы, в которой есть оценки у студентов, чтобы запрос не был пустым.

7. В поле Семестр задайте условие отбора – 1.

8. Выберите на ленте на вкладке Конструктор/Тип запроса/Перекрестный. В нижней части бланка запроса появятся новые строки Групповая операция и Перекрестная таблица (Рис. 2 ).

практическая работа 7. обработка данных при помощи специальных запросов - student2.ru

Рис. 2 .Бланк перекрестного запроса

9. Выполните следующие настройки запроса

· Для полей Номер группы, Фамилия, Имя, Отчество, Семестр в строке Перекрестная таблица выберите Заголовки строк;

· Для поля Название выберите Заголовки столбцов;

· Для поля Оценка в строке Групповая операция выберите функцию Max (что означает наибольшую из полученных оценок). В строке Перекрестная таблица для поля Оценка выбрать Значение;

10. Просмотрите результаты запроса.

11. Задайте другие условия отбора и просмотрите результаты запроса.

12. Удалите номер группы из условия отбора. Просмотрите результаты запроса. Вы получили статистику по всем студентам, которые имеют оценки за 1-й семестр.

13. Сохраните запрос с именем Итоги семестра.

Задание 2. Для самостоятельной работы

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

Технология работы

1. Скопируйте перекрестный запрос Итоги семестра.

2. Вставьте его с другим именем – Сколько раз сдавал.

3. Откройте запрос в режиме конструктора (Контекстное меню/Конструктор).

4. Для поля Оценка выберите другую функцию – Count (подсчитывает число значений).

5. Просмотрите результаты запроса.

Задание 3. Отбор повторяющихся записей

Создайте запрос, отбирающий студентов, имеющих 2 и более задолженностей по результатам 1-го семестра.

Должником считается студент, сдавший экзамен на 2 или не явившийся на экзамен.

Для проверки правильности работы запроса необходимо, чтобы в таблице Оценки обязательно были студенты, имеющие 1, 2 и более задолженностей. Введите такие данные или измените существующие. Для этого можно воспользоваться формой Студент и Оценки (см. практическую работу 6.4.4).

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

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

Технология работы

Й этап

1. Создайте новый запрос. Включите в запрос поля (Рис. 3)

· Номер группы из таблицы Группа;

· Фамилия, Имя, Отчество из таблицы Студент;

· Семестр из таблицы Оценка;

· Название из таблицы Дисциплина;

· Вид контроля, Дата сдачи, Оценка из таблицы Оценка.

Обратите внимание на порядок следования полей в запросе. Не забудьте связать таблицы в запросе.

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

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

4. Сохраните запрос с именем Оценки студентов.

практическая работа 7. обработка данных при помощи специальных запросов - student2.ru

Рис. 3. Бланк запроса Оценки студентов

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

5. Создайте новый запрос.

6. Включите в него не таблицы, а только что созданный запрос Оценки студентов.

7. Включите в бланк все поля, кроме Дата сдачи.

8. В поле Семестр задайте условие отбора – 1.

9. Просмотрите результаты запроса (вкладка Конструктор/группа Результаты/Режим/Режим таблицы). В таблице вы увидите те же результаты, но только не указаны даты сдачи. Обратите внимание, что почти все студенты имеют по 5 оценок за семестр, а некоторые больше. Это те, кто не являлся на экзамен или сдавал дисциплину по несколько раз.

10. Вернитесь в бланк запроса.

11. На вкладке Конструктор в группе Показать или Скрыть щелкните по кнопке Итоги. В нижней части бланка появится строка Групповая операция.

12. Для поля Оценка в строке Групповая операция задайте функцию Max (выберите из раскрывающегося списка).

13. Просмотрите результаты запроса (вкладка Конструктор/группа Результаты/Режим/Режим таблицы). В таблице вы увидите у каждого студента по 5 оценок. Из повторяющихся оценок с помощью операции группировка выбрана только одна (последняя и наибольшая).

14. В поле Оценка введите условие отбора 0 Or 2 Or -1 (Значение -1 «минус 1» означает, что зачет не сдан). Этим условием мы отбираем только задолженников (Рис. 4).

15. Просмотрите результаты запроса (Режим/Режим таблицы). Вы увидите только студентов, имеющих задолженности.

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

17. Сохраните запрос с именем Задолженность.

практическая работа 7. обработка данных при помощи специальных запросов - student2.ru

Рис. 4. Бланк запроса Задолженность

Й этап

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

18. На вкладке Создание в группе Другие нажмите кнопку Мастер запросов.

19. В окне Новый запрос в списке запросов выберите Повторяющиеся записи.

20. На первом шаге мастера выберите запрос Задолженность.

21. На втором шаге выберите поля Номер группы, Фамилия, Имя, Отчество, Семестр.

22. На следующем шаге не выбирайте ничего.

23. На следующем шаге задайте имя запроса – Больше 1-й задолженности – и откройте запрос для просмотра. Вы увидите список студентов, напротив каждой фамилии студента будет указано количество несданных экзаменов (зачетов и пр.). В запрос будут включены только те студенты, у кого больше 2 или более задолженностей.

24. Откройте бланк запроса и просмотрите, как он построен. В дальнейшем вы можете создавать такие запросы непосредственно в режиме конструктора.

25. Проверьте, что запрос работает правильно. Для этого сравните результаты запроса с выписанными в п. 16 результатами.

Задание 4. Создание перекрестного запроса с помощью мастера

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

Примечание.

В этом задании будет рассмотрен еще один способ создания перекрестного запроса – с помощью мастера.

Технология работы

Задание 5. Запрос на создание таблицы

Преобразовать запрос Больше 1-й задолженности в запрос на создание таблицы и создайте таблицу На отчисление.

Технология работы

1. Откройте запрос Больше 1-й задолженности в режиме конструктора.

2. В группе Тип запроса вкладки Конструктор выберите тип – Создание таблицы. Введите имя таблицы – На отчисление (Рис. 5).

практическая работа 7. обработка данных при помощи специальных запросов - student2.ru

Рис. 5. Создание запроса на создание таблицы Отчисление

3. Выполните запрос кнопкой Запуск в группе Результаты.

4. Закройте запрос.

5. Перейдите в окно Таблицы. Убедитесь в том, что там появилась новая таблица Отчисление.

Задание 6. Запрос на изменение (обновление)

Создайте запрос Изменение оплаты, в котором для групп, которые еще не закончили обучение, автоматически будет увеличена оплата за обучение на 10%.

Технология работы

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

2. Создайте новый запрос.

3. Включите в запрос таблицу Группа.

4. Выберите поля Номер группы, Плата за семестр, Обучение закончено.

5. Выполните запрос и просмотрите результаты. Будут отобраны все группы.

6. Перейдите в режим конструктора (контекстное меню/Конструктор).

7. В строке Условие отбора для поля Обучение закончено введите значение Ложь.

8. Выполните запрос. Будут отобраны группы, которые не закончили обучение.

9. Перейдите в режим конструктора (контекстное меню/Конструктор).

10. На ленте выберите Конструктор/Тип запроса/Обновление. На бланке запроса появится строка Обновление.

11. В строке Обновление для поля Плата за семестр введите выражение – условие увеличения оплаты на 10%, которое имеет вид

[Плата за семестр]*1,1.

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

12. Выполните запрос ТОЛЬКО1 раз. Во время выполнения подтвердите согласие на обновление данных.

13. Закройте запрос и сохраните его с именем Изменение оплаты.

14. Откройте таблицу Группа. Убедитесь, что оплата изменилась только для групп, не закончивших обучение.

ОБРАТИТЕ ВНИМАНИЕ! Для того чтобы не потерять данные в случае неправильного выполнения следующего задания создайте копию базы данных Деканати назовите ее Деканат (копия) и все следующие задания выполняйте в этой копии. В случае, если при выполнении дальнейших заданий вы потеряете данные, то всегда можно будет их восстановить из исходной базы данных.

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

Задание 7. Запросы на добавление и удаление записей

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

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

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

Запрос с параметром Удаление преподавателя запрашивает код преподавателя и удаляет запись из таблицы Преподаватель.

Технология работы

Й этап. Добавление в архив

1. На панели переходов скопируйте таблицу Преподаватель.

2. Вставьте копию. При вставке задайте имя Уволенный преподаватель и выберите переключатель практическая работа 7. обработка данных при помощи специальных запросов - student2.ru Только структура.

3. Откройте бланк структуры созданной таблицы в режиме конструктора.

4. Добавьте в нее новые поля Приказ, Основание, ДатаУвольнения (тип Дата/Время). Закройте таблицу.

5. Создайте запрос в режиме конструктора.

6. Добавьте в запрос таблицу Преподаватель. Включите все поля из таблицы.

7. В поле Код преподавателя задайте параметрическое условие отбора.

[Введите код преподавателя]

8. В пустом столбце в строке Поле наберите формулу

Приказ: [Номер приказа].

Эта формула создает в запросе вычисляемое поле Приказ, в которое данные (номер приказа) будут вводиться пользователем во время выполнения запроса.

9. В следующем пустом столбце в строке Поле наберите формулу

Основание: [Введите основание]

Эта формула создает в запросе вычисляемое поле Основание, в которое данные (основание увольнения) будут вводиться пользователем во время выполнения запроса.

10. В следующем пустом столбце в строке Поле наберите формулу

ДатаУвольнения: Date ()

Примечание.

Имя вычисляемого поля ДатаУвольнения должно быть «одним словом», т.е. не иметь пробелов.

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

11. Сохраните запрос с именем Добавление в архив (Рис. 6).

12. Выполните запрос. Во время выполнения запроса введите номер приказа, основание увольнения и код увольняемого преподавтеля. Обратите внимание, в каком порядке запрашиваются эти параметры. Убедитесь, что по запросу был отобран преподаватель с указанным кодом, и что в запросе были созданы поля Приказ, Основание и ДатаУвольнения.

практическая работа 7. обработка данных при помощи специальных запросов - student2.ru

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

13. Сейчас пока у вас создан запрос на выборку. Теперь его надо преобразовать. Перейдите в режим конструктора запроса.

14. В группе Тип запроса (вкладка Конструктор) выберите кнопку – Добавление. В открывшемся окне выберите из списка таблицу Уволенный преподаватель.

15. Посмотрите, как изменился бланк запроса. Появилась строка Добавление. В ней указаны поля из таблицы Уволенный преподаватель, которые соответствуют полям запроса. Если поля в таблице состоят из двух слов, записанных с пробелом, то они автоматически заключаются в квадратные скобки (Рис. 7).

практическая работа 7. обработка данных при помощи специальных запросов - student2.ru

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

16. Удалите квадратные скобки из названий полей (Рис. 8).

практическая работа 7. обработка данных при помощи специальных запросов - student2.ru

Рис. 8. Удалены квадратные скобки

17. Выполните запрос.

18. В области переходов перейдите к объекту Таблицы откройте таблицу Уволенный преподаватель. Убедитесь, что в ней появилась запись по результатам работы запроса.

Технология работы

1. На вкладке Создание /в группе Другие выберите кнопку Макрос.

2. В открывшемся бланке макроса в первой строке выберите команду ОткрытьЗапрос.

3. В нижней части бланка выберите из списка запросов Добавление в архив.

4. На второй строке выберите команду ОткрытьЗапрос.

5. В нижней части бланка в списке запросов выберите Удаление преподавателя (Рис. 8).

практическая работа 7. обработка данных при помощи специальных запросов - student2.ru