Работа с данными, использую разработанную ранее БД "Студенты ОмГУПСа"

Лабораторная работа 2

Работа с данными

З а д а н и я.

1. Выполнить сортировку и фильтрацию данных.

2. Создать различные виды запросов.

3. Создать отчет.

Примечание. Для выполнения заданий использовать разработанную ранее БД Студенты ОмГУПСа.

2.1. Сортировка данных. Теоретические сведения

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

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

Задание 1.

Представить данные таблицы ФИО в упорядоченном виде:

– по фамилии в алфавитном порядке;

– по убыванию шифра;

– по имени в порядке убывания.

2.2. Фильтрация данных. Теоретические сведения

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

Для выборки записей, соответствующих одному конкретному значению из имеющегося набора данных по какому-либо полю (например, записей студентов по фамилии Иванов), нужно выполнить следующую последовательность действий:

§  щелкнуть в любой строчке нужного поля (Фамилия);

§  на вкладке ленты Главная выполнить команду Фильтрв группе инструментов Сортировка и фильтр;

§  в раскрывшемся списке указать критерий отбора (Иванов);

§  щелкнуть на кнопке ОК.

Ту же задачу можно выполнить по-другому: в группе инструментов Сортировка и фильтр выполнить команду Дополнительно → Изменить фильтр (рис. 1), щелкнуть в нужном поле на раскрывающемся списке, выбрать нужное значение (значения перечислены по возрастанию) и выполнить команду Применить фильтр.

Рис. 1. Работа с простым фильтром

Для осуществления выборки данных по определенному условию используется команда Дополнительно → Расширенный фильтр группы инструментов Сортировка и фильтр. Вид диалогового окна, открывающегося при выполнении этой команды, показан на рис. 2.

Рис.2. Диалоговое окно при работе с расширенным фильтром

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

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

Задание 2.

1. В таблице ФИО отобразить фамилии студентов по указанному преподавателем фильтру. После этого очистить все фильтры.

2. В таблице ФИО применить расширенный фильтр, включив в него Фамилию, специальность, стипендию (с условием отбора).

2.3. Создание запросов. Теоретические сведения

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

Запросы, как и другие объекты БД, можно создавать разными способами. Сначала необходимо включить в области переходов режим отображения запросов как объекта Access (рис. 3).

Рис. 3. Выбор запросов как объектов Access в области переходов

2.3.1. Создание запроса в режиме Мастера запросов.

Расчет итоговых значений по выбранному полю

Мастер запросов (рис. 4) позволяет создавать различные виды запросов, в том числе и простой  запрос, создание которого рассмотрено далее.

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

Рис. 4. Запуск Мастера запросов

а                                                                               б

Рис. 5. Первый (а) и второй (б) этапы работы Мастера запросов

Задание 3.

1. Создать запрос Ведомость на выплату стипендии в режиме Мастера запросов.

2. Выполнить расчет итоговых значений по выбранному полю

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

1) на вкладке ленты Создание, запустить Мастер запросов; выбрать Простой запрос; в таблице ФИО выбрать поля Шифр, Фамилия, Стипендия; установить переключатель Итоговый отчет; щелкнуть на кнопке Итоги и в открывшемся окне установить один из флажков (рис. 6). Сохранить запрос с именем Ведомость на выплату стипендии;

Рис. 6. Окно настройки параметров вычисление итоговых значений

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

Наиболее удобным для построения запросов является режим конструктора (рис. 7).

Рис. 7. Запуск Конструктора запросов

Щелчок по кнопке Конструктора запросов вызывает диалоговое окно Добавление таблицы, которое позволяет включить в схему данных запроса существующие таблицы и (или) запросы (рис. 8).

Рис. 8. Добавление таблицы в схему данных запроса

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

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

Рис. 9. Построение запроса в режиме Конструктора

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

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

Задание 4.

Создать запросы в режиме Конструктора:

1. Запрос с полями Фамилия, Имя, Отчество, Почтовый индекс, Город.

2. Запрос Ведомость на выплату стипендии, включив в него поля Фамилия, Имя, Отчество, Стипендия. Для поля Стипендия установить условие отбора согласно варианту из табл. 2.1. Вывести фамилии в алфавитном порядке.

3. Запрос на основе таблицы ФИО: студентов, не получающих стипендию (вывести на экран в алфавитном порядке).

Т а б л и ц а  2.1

Варианты индивидуальных заданий по созданию запроса

Вариант	Условие отбора стипендии	Вариант	Условие отбора стипендии
1	> 0	7	> 200
2	< 500	8	> 400
3	> 100	9	> 600
4	> 300	10	> 350
5	> 500	11	> 250
6	> 300	12	> 450

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

2.4. Создание отчета. Теоретические сведения

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

Microsoft Access предоставляет различные способы подготовки отчетов:

создание отчета вручную в режиме конструктора (рис.10);

полуавтоматическое создание отчета с помощью мастера отчетов (рис. 11);

Рис. 10. Построение запроса в режиме Конструктора

Рис. 11. Создание запроса с помощью Мастера отчетов

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

Задание 5.

1. С помощью  Мастера отчетов создать отчет Ведомость на выплату стипендии. В качестве источника данных выбрать созданный ранее запрос Ведомость на выплату стипендии.

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