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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Рис. 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. Сохраните запрос с именем Оценки студентов.

Рис. 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. Сохраните запрос с именем Задолженность.

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

Й этап

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

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

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

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

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

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

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

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

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

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

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

Примечание.

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

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