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

Цель работы:

· приобрести навыки создания запросов на выборку;

· приобрести навыки создания простых отчетов.

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

Задание 1

Создать Запрос на выборку следующей информации о студентах: Фамилия, Имя, Отчество, Дата рождения, Рост и Вес. Вывести информацию только о тех студентах, рост которых не менее 176 см, а вес не превышает 79 кг. Фамилии студентов должны быть отсортированы в алфавитном порядке.

Для создания запроса выполните следующие действия.

1. В области навигации перейти в категорию Запросы.

2. На вкладке Создание ленты меню в разделе Запросы нажмите кнопку Конструктор запросов

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

4. Закройте окно Добавление таблицы. В окне конструктора запросов с помощью курсора мыши откорректируйте размер таблицы Личные данные так, чтобы были видны названия всех полей таблицы, а линейка прокрутки исчезла.

5. Поместите в бланк запроса следующие поля: Фамилия, Имя, Отчество, Дата рождения, Рост и Вес. Размещение полей можно выполнить тремя способами:

· щелкнуть мышью в строке Поле: и выбрать нужное поле;

· перетащить мышью необходимое поле;

· двойным щелчком на имени поля в таблице.

Вид бланка запроса с размещенными полями показан на рисунке 2.1.

6. Для поля Фамилия в строке Сортировка:установите сортировку по возрастанию.

7. В бланке запроса для поля Рост наберите условие отбора >=176, а для поля Вес, соответственно, <79. Окончательно сформированное окно конструктора запроса должно выглядеть так, как показано на рисунке 2.1.

Рис. 2.1. Диалоговое окно конструктора запросов

8. По умолчанию Access присваивает запросу имя Запрос1. Выполните запрос нажатием кнопки меню Выполнить . Результат выполнения запроса приведен на рисунке 2.2.

Рис. 2.2. Результат выполнения запроса на выборку

9. Закройте вкладку Запрос1 и сохраните запрос под именем
Запрос1_на выборку. Убедитесь, что созданный запрос появился в категории Запросы.

Задание 2

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

Для создания запроса выполните следующие действия.

1. В области навигации перейти в категорию Запросы.

2. На вкладке Создание ленты меню в разделе Запросы нажмите кнопку Конструктор запросов В диалоговое окно конструктора запроса добавьте таблицы Дисциплины, Успеваемость и Личные данные, в которых находятся запрашиваемые поля.

3. Поместите в бланк запроса следующие поля: Фамилия(таблица Личные данные), Дисциплина(таблица Дисциплины), Оценка(таблица Успеваемость).

4. Для поля Фамилия в строке Условие отбора: наберите текст в квадратных скобках: [Введите фамилию студента]. Окно конструктора запроса должно выглядеть так, как показано на рисунке 2.3.

Рис. 2.3. Диалоговое окно конструктора запроса с параметром

5. Выполните запрос нажатием клавиши меню Выполнить . На экране появится диалоговое окно Введите значение параметра (рисунок 2.4), которое содержит приглашение ввести условие отбора для поля Фамилия.

Рис. 2.4. Диалоговое окно для ввода параметра запроса

6. В диалоговом окне в качестве параметра введите фамилию студента Пятый и нажмите кнопку OK. Результат выполнения запроса приведен на рисунке 2.5.

Рис. 2.5. Результат выполнения запроса с параметром

7. Сохраните запрос под именем Запрос2_параметрический и убедитесь, что он появился в категории Запросы.

8. Выполните несколько раз Запрос2_параметрический, используя в качестве параметра фамилии других студентов.

Задание 3

Создать Запрос с вычисляемым полем на выборку следующей информации о студентах: Фамилия, Место рождения, Дата рождения, Возраст.

Для создания запроса выполните следующие действия.

1. В области навигации перейти в категорию Запросы.

2. На вкладке Создание ленты меню в разделе Запросы нажмите кнопку Конструктор запросов В диалоговое окно конструктора запроса добавьте таблицу Личные данные.

3. Поместите в бланк запроса следующие поля: Фамилия,
Место рождения, Дата рождения.

4. Наберите в пустой ячейке бланка запроса вычисляемое поле Возраст: (двоеточие после названия поля обязательно!) и нажмите правую кнопку мыши. В появившемся меню укажите пункт
Построить… Для формирования выражений используется
Построитель выражений, диалоговое окно которого представлено на рисунке 2.6.

Рис. 2.6. Диалоговое окно Построитель выражений

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

Возраст:Year(Date())-Year([Личные данные]![Дата рождения])

Пояснение. В этом выражении встроенная функция Date()из категории Дата/время используется для получения текущей даты. Встроенная функция Year извлекает год. Аргумент функции Year – [Личные данные]![Дата рождения] содержит поле Дата рождения из таблицы Личные данные.

Окончательный вид диалогового окна конструктора запроса с вычисляемым полем показан на рисунке 2.7.

Рис. 2.7. Диалоговое окно конструктора запроса с вычисляемым полем

Сохраните запрос под именем Запрос3_с вычисляемым полем. Выполните запрос и сравните полученный результат с результатом, который приведен на рисунке 2.8.

Рис. 2.8 – Результат выполнения запроса с вычисляемым полем

Задание 4

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

Для создания запроса выполните следующие действия.

1. В области навигации перейти в категорию Запросы.

2. На вкладке Создание ленты меню в разделе Запросы нажмите кнопку Конструктор запросов В диалоговое окно конструктора запроса добавьте таблицу Личные данные.

3. Поместите в бланк запроса поля: Код студента и Общежитие.

4. В соответствии с заданием измените название поля Код студента на Количество. Для этого перед названием поля Код студента необходимо набрать текст Количество: (двоеточие после названия поля обязательно!), чтобы название поля имело вид
Количество: Код студента.

5. На вкладке Конструктор ленты меню в разделе Показать или скрыть нажмите кнопку Итоги , после чего в бланке запроса появиться строка Групповая операция:

6. В строке Групповая операция: откройте поле со списком и установите:

· для поля Количество:Код студента значение Count;

· для поля Общежитие значение Условие.

В соответствии с требованием запроса установите в строке Условия отбора значение Да. Окно конструктора запроса с групповой операцией должно выглядеть так, как показано на рисунке 2.9.

Рис. 2.9. Диалоговое окно конструктора запроса с групповой операцией

7. Сохраните запрос под именем Запрос4_с групповой операцией и убедитесь, что он появился в объектах Запросы.

8. Выполните запрос и сравните полученный результат с результатом, который показан на рисунке 2.10.

Рис. 2.10. Результат выполнения запроса

Задание 5

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

В этой таблице названием строк будет поле Фамилия, названием столбцов поле Дисциплина, а сама таблица будет содержать значения поля Оценка.

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

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

1. В области навигации перейти в категорию Запросы.

2. На вкладке Создание ленты меню в разделе Запросы нажмите кнопку Конструктор запросов . В диалоговое окно конструктора запроса добавьте таблицы Личные данные, Успеваемость и Дисциплины, в которых находятся запрашиваемые поля.

3. Поместите в бланк запроса поля: Фамилия, Дисциплина, Оценка. Для поля Фамилия в строке Сортировка: установите значение по возрастанию. Диалоговое окно конструктора запроса на выборку показано на рисунке 2.11.

Рис.2.11. Диалоговое окно конструктора запроса на выборку

4. Выполните запрос и сохраните его под именем Запрос для перекрестного.

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

1. В области навигации перейти в категорию Запросы.

2. На вкладке Создание ленты меню в разделе Запросы нажмите кнопку Мастер запросов . В окне Новый запрос выберите Перекрестный запрос и нажмите OK.

3. В диалоговом окне Создание перекрестных таблиц щелкните по переключателю Запросы, выберите запрос Запрос для перекрестного и нажмите кнопку Далее > (рисунок 2.12).

Рис. 2.12. Диалоговое окно Создание перекрестных таблиц

4. В следующем окне выберите поле Фамилия, которое будет использоваться в качестве заголовков строк, и нажмите кнопку Далее >.

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

6. В следующем окне выберите поле Оценка и функцию Среднее.

В ответе на вопрос Вычислить итоговое значение для каждой строки? установите флажок в положение Да и нажмите кнопку Далее > (рисунок 2.13).

Рис. 2.13. Создание перекрестных таблиц: выбор функции

7. В следующем окне задайте имя запроса Запрос5_перекрестный и нажмите кнопку Готово.

8. Выполните запрос. Результат выполнения запроса показан на рисунке 2.14.

Рис. 2.14. Результат выполнения Перекрестного запроса

Создание простых отчетов

Задание 6

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

Для создания отчета выполните следующие действия.

1. На вкладке Создание ленты меню в разделе Отчеты нажмите кнопку Мастер отчетов .

2. Из раскрывающегося списка Таблицы и запросы выберите Таблица: Личные данные. Из панели Доступные поля: выделите и перенесите в панель Выбранные поля: поле Фамилия. Повторите эти действия для поля Дисциплина из таблицы Дисциплины и поля Оценка из таблицы Успеваемость. Окончательный вид диалогового окна показан на рисунке 2.15. Нажмите кнопку Далее>.

Рис. 2.15. Выбор полей для отчета Успеваемость

3. В следующем окне согласитесь с видом представления данных, как показано на рисунке 2.16, и нажмите кнопку Далее>.

Рис. 2.16. Выбор вида представления данных

4. В следующем окне убедитесь, что группировка осуществляется по полю Фамилия, и нажмите кнопку Далее>.

5. В следующем окне задайте порядок сортировки для полей Дисциплина и Оценка, как показано на рисунке 2.17. Для вычисления среднего балла каждого студента нажмите на кнопку Итоги….

Рис. 2.17. Определение порядка сортировки полей в отчете Успеваемость

6. Для вычисления среднего балла в открывшемся окне Итоги для поля Оценка выберите функцию Avg, и нажмите кнопку OK, как показано на рисунке 2.18.

Рис. 2.18. Вычисление итогов для отчета Успеваемость

7. В следующих окнах выберите вид макета для отчета ступенчатый, ориентацию Книжная и нажмите кнопку Далее>.

8. В последнем окне задайте имя отчета Итоги сессии и нажмите кнопку Готово.

9. Перейдите в режим Конструктор и отредактируйте отчет Итоги сессии таким образом, чтобы он принял вид, как показано на рисунке 2.19.

Рис. 2.19 – Окончательный вид отчета Итоги сессии

Контрольные задания

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

Результат выполнения запроса показан на рисунке 2.20.

Рис. 2.20. Результат выполнения запроса Дисциплина

2. Создать запрос на выборку следующей информации о студентах: Фамилия, Общежитие, Рост. Создать вычисляемое поле Рост в футах. Вывести информацию только о студентах, проживающих в общежитии и отсортировать в порядке уменьшения роста студентов. Сохранить запрос под именем Рост в футах.

Справка. 1 фут » 0,3048м.

Результат выполнения запроса показан на рисунке 2.21.

Рис. 2.21. Результат выполнения запроса Рост в футах

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

Рис. 2.22. Результат выполнения запроса Средний балл