Построение запроса на основе других запросов

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

Пусть необходимо определить по результатам сессии средний балл в группе по информатике. Для получения этого запроса надо предварительно сконструировать два вспомогательных запроса: Сумма оценок по информатике в каждой группе и Число студентов в группе. Эти вспомогательные запросы должны содержать групповые операции и статистические функции Sum и Count соответственно. Искомый запрос должен быть построен на базе вспомогательных запросов и содержать вычисляемое поле: «Sum-ОЦЕНКА» / «Count-НС».

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

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

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

- выбрать объект Запрос;

- двойной щелчок на значке «Создание запроса в режиме конструктора» в окне БД;

- в окне Добавление таблицы включить в схему данных нужную таблицу;

- в бланк запроса перетащить нужные поля;

- для преобразования обычного запроса на выборку в перекрестный запрос выполнить команду Запрос-Перекрестный запрос;

- в бланке запроса появятся две дополнительные строчки Групповая операция и Перекрестная таблица;

- для поля, которое будет определять заголовки строк, в строке Перекрестная таблица следует выбрать из списка пункт Заголовки строк, для поля, определяющего заголовки столбцов, – выбрать Заголовки столбцов, для поля с итоговыми данными (значения которого будут стоять на пересечении) – выбрать Значение;

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

- Если есть поле, на которое накладывается некоторое условие, то для такого поля в строке Групповая операция нужно выбирать пункт Условие, в строку Условие отбора вводить выражение.

Задание

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

1 Запрос 1 (Студентки группы 41-1). Вывести список студенток из группы 41-1. Номер группы и пол на экран не выводить. Для этого из таблицы Студент выберите в бланк запроса поля: ФИО, НГ и ПОЛ. В условия отбора бланка запроса для полей НГ и ПОЛ из таблицы Студент впишите 41-1 и ж соответственно.

2 Запрос 2 (Запрос с параметром). Вывести адреса и фамилии студенток из группы, номер которой запрашивается с клавиатуры при выполнении запроса. Указание. Выберите одну таблицу Студент. В бланк запроса перетащите поля: ФИО, НГ, АДРЕС, ПОЛ. В условия отбора поля ПОЛ введите ж, а для поля НГ – текст в квадратных скобках [Укажите номер группы].

3 Запрос 3 (Двоечники). Какие студенты имеют двойки хотя бы по одному предмету? Указание. Выбрать таблицы Студент и Успеваемость. В бланк запроса перетащить поля: НГ, ФИО, ПР и ОЦЕНКА. В Условие отбора поля ОЦЕНКА введите число 2.

4 Запрос 4 (Высокий проходной балл). У каких студентов проходной балл при поступлении выше проходного бала в группе? В отобранных записях должны быть поля с фамилией студента, номером группы, проходным баллом студента и проходным баллом группы. Указание: Потребуется две таблицы Группа и Студент. В условие отбора для поля ПБАЛЛ таблицы Студент введите условие >[Группа]![ПБАЛЛ]. Используйте Построитель выражений.

5 Запрос 5 (Возраст студентов). Выдать список студентов старше 18 лет, проживающих на улице П. Железняка. В запросе предусмотреть поля с фамилией, адресом, датой рождения и вычисляемым полем Возраст. Указание. Выберите таблицу Студент. В пустую ячейку строки Поле введите Построителем выражений формулу Year(Date())-Year([Студент]![ГОДР]), а в условии отбора для данного поля укажите >=18. В условие отбора для адреса введите *Железняка* (обязательно с символами *).

6 Запрос 6 (Число студентов в группе). Посчитать фактическое число студентов в каждой группе. Это однотабличный запрос с групповой операцией и статистической функцией. Указание. Добавьте таблицу Студент. Выберите поля: НГ и НС. Щелкните по кнопке Групповые операции å на панели инструментов. В поле НГ оставьте текст Группировка, а в поле НС выберите функцию Count.

7 Запрос 7 (Средний балл студента). Посчитать средний балл каждого студента в сессию. Запрос постройте на базе двух таблиц: Студент и Успеваемость. В бланк запроса перетащите поля: НГ, НС, ФИО, ОЦЕНКА. Используйте групповые операции и функцию Avg.

8 Запрос 8 (Средний возраст в группе). Вычислите средний возраст студентов в каждой группе. Для этого создайте сначала вспомогательный запрос Возраст всех студентов, который содержит поля: НГ, ФИО и вычисляемое поле ВОЗРАСТ. Строку Условия отбора оставьте пустой. Сохраните запрос. Снова вызовите конструктор для создания нового запроса. Добавьте в поле таблиц вспомогательный запрос. Перетащите из него в бланк запроса поля: НГ и вычисляемое поле ВОЗРАСТ. Добавьте групповую операцию. В строке Групповая операция для поля НГ оставьте Группировка, а для поля ВОЗРАСТ выберите статистическую функцию Avg.

9 Запрос 10 (Успеваемость в группе). Для конкретной группы выдать таблицу, в которой в заголовках строк указаны фамилии студентов данной группы, а в заголовках столбцов – названия предметов. На пересечении строк и столбцов расположены оценки. Это перекрестный запрос.

10 Запрос 11 (Оценки по информатике). Сконструировать перекрестный запрос, который выдает следующую таблицу. Заголовками строк являются номера групп, а заголовками столбцов – значения оценок (5, 4, 3, 2). На пересечении строк и столбцов указано количество пятерок, четверок, троек и двоек, полученных по информатике.

Контрольные вопросы

1 Как в бланке запроса отобразить нужные поля таблицы?

2 Какая логическая операция применяется к условиям отбора, записанным в одной строке бланка запроса?

3 Какие операции сравнения и логические операторы можно использовать в условиях отбора?

4 Как вводится параметр в запрос?

5 Как указываются в запросе одинаковые поля, принадлежащие разным таблицам?

6 Допускается ли группировка записей запроса по нескольким полям?