Задание N1 (стр. 7 )

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

Между таблицами автоматически устанавливаются необходимые связи:

· одно-многозначная связь между СТУДЕНТ и УСПЕВАЕМОСТЬ по составному ключу НГ+НС в соответствии с построенной ранее схемой данных;

· связь-объединение между таблицами УПЕВАЕМОСТЬ и ПРЕДМЕТ, поскольку они имеют поля с одинаковым именем КП и одинаковым типом данных.

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

Перетащим с помощью мыши поля, включаемые в результат выполнения запроса, в строку бланка запроса Поле:

· ФИО - из таблицы СТУДЕНТ,

· НП - из таблицы ПРЕДМЕТ;

· ОЦЕНКА - из таблицы УСПЕВАЕМОСТЬ.

Сохраните этот запрос под именем Успеваемость студентов.

Ввод значений в условия отбора записей

ЗАДАНИЕ

Пусть необходимо получить информацию об успеваемости конкретных студентов - и Маковой.

Зададим в строке Условие отбора их фамилии. Запишем фамилии студентов в разных строках бланка запроса, поскольку необходимо выбрать записи со значением в поле ФИО - Боярская или Макова. Фамилии необходимо заключить в круглые скобки.

Поскольку инициалы студентки Маковой неизвестны, ее фамилию зададим с использованием символов шаблона *.

После заполнения бланка запроса выполним его. Сохраните запрос с именем Успеваемость двух студенток.

Ввод параметров в запрос

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

ЗАДАНИЕ

Пусть необходимо получить информацию об оценке студента по заданному предмету.

В условие отбора поля ФИО вместо конкретной фамилии введем название параметра, по которому будет запрашиваться фамилия при выполнении запроса. Название параметра введем как текст, заключенный в квадратные скобки [Фамилия и инициалы студента].

Этот текст Access воспринимает как имя параметра. Введем в поле НП второй параметр запроса: [Наименование предмета].

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

Использование имен полей различных таблиц в условии отбора

ЗАДАНИЕ

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

Для решения этой задачи необходимо использовать таблицы:

· ИЗУЧЕНИЕ, в которой содержатся сведения об изучаемых в текущем семестре предметах, а также о продолжительности (поле ЧАСЫ) разных видов занятий (поле ВИДЗ) в этом семестре;

· ПРЕДМЕТ, в которой содержатся сведения об общей продолжительности изучения предмета (поле ЧАСЫ) и числе семестров изучения (ЧС).

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

<>[ИЗУЧЕНИЕ]![ЧАСЫ]*[ЧС]

Заметим, что указание таблицы ИЗУЧЕНИЕ для поля ЧАСЫ является обязательным, потому что поле с таким же именем имеется и в таблице ПРЕДМЕТ.

Сохраните запрос с именем Часы практик и запустите его.

III. Использование вычисляемых полей в запросах

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

Рассмотрим технологию создания запроса с вычисляемым полем на примере таблицы ПРЕДМЕТ.

ЗАДАНИЕ

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

Для решения этой задачи рассчитаем разность между общим числом часов по предмету (поле ЧАСЫ) и суммой часов лекций (поле ЛЕК) и практики (поле ПР). В ответ включим только те записи, для которых эта разность равна нулю.

Создадим запрос на выборку для таблицы ПРЕДМЕТ. Перетащим в бланк запроса поля НП, ПР, ЛЕК, ЧАСЫ.

Создание вычисляемого поля.

Для получения разности создадим вычисляемое поле в пустой ячейке строки Поле, записав туда выражение:[ЧАСЫ]-[ПР]-[ЛЕК].

Для отбора записей с ненулевым значением разности в вычисляемом поле в строку Условие отбора введем <>0 (не равно 0). Поле ввода выражения система формирует имя вычисляемого поля по умолчанию - Выражение 1, Это имя ставится перед выражением. Для изменения имени установим курсор мыши в вычисляемом поле бланка запроса и нажмем правую кнопку мыши. В контекстно-зависимом меню выберем Свойства поля и в строку Подпись введем новое имя поля ЧАСЫ<>ПР+ЛЕК. Имя поля может быть исправлено также непосредственно в бланке запроса. Сохраним запрос под именем Разность часов по предмету.

Построенный запрос может быть использован для проверки правильности заполнения поля ЧАСЫ в таблице ПРЕДМЕТ.

V. Использование групповых операций в запросах

Назначение групповых операций

Групповые операции позволяют выделить группы записей с одинаковыми значениями в указанных полях и использовать для других полей этих групп определенную статистическую функцию. В Access предусматривается девять статистических функций:

· Sum - сумма значений некоторого поля для группы;

· Avg – среднее от всех значений поля в группе;

· Мах, Мin - максимальное, минимальное значение поля в группе;

· Count - число значений поля в группе без учета пустых значений;

· StDev - среднеквадратичное отклонение от среднего значения поля в группе;

· Var - дисперсия значений поля в группе;

· First и Last - значение поля из первой или последней записи в группе.

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

Порядок создания запроса с использованием групповых операций

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

Конструирование однотабличного запроса с групповой операцией

Рассмотрим технологию конструирования однотабличного запроса с групповой операцией на примере таблицы СТУДЕНТ.

Запрос с функцией Count

ЗАДАНИЕ

Создать запрос на выборку для таблицы СТУДЕНТ.

Из списка таблицы СТУДЕНТ перетащим в бланк запроса поле НГ - номер группы. Это поле создадим для указания, что по этому полю должна производиться группировка. Перетащим в бланк запроса поле НС, по которому будет вычисляться функция Соипt для подсчета числа студентов в группе.

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

Подпись поля Count_НС замените на Фактическое число студентов. Для ввода этой подписи в бланке запроса установите на поле НС курсор мыши и нажмите правую кнопку. В контекстно-зависимом меню выберите Свойства.

В окне Свойства поля в строку Подпись введите Фактическое число студентов. Просмотрите запрос. Сохраните запрос под именем Ф_число студентов.

Запрос с функцией Avg

ЗАДАНИЕ

Подсчитать средний проходной балл в группе.

Сформируем запрос на выборку для таблицы СТУДЕНТ с функцией Avg для поля ПБАЛЛ - средний проходной балл студентов. В бланке запроса заполните поля НГ и ПБАЛЛ. Нажмите кнопку Групповые операции, замените слово Группировка в таблице ПБАЛЛ на функцию Avg. Для ограничения точности результата двумя знаками выберите в окне Свойства поля в строке Формат поля - Фиксированный. Сохраните запрос под именем Средний прох. балл. Просмотрите запрос.

Запрос с несколькими групповыми функциями.

ЗАДАНИЕ

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

Это возможно, т. к. группы записей в обоих случаях формируются одинаково. Сформируйте запрос и сохраните его под именем Число студ. и средний балл группы.

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

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

ЗАДАНИЕ

Подсчитаем число студентов в группах с проходным баллом большим, чем 4,5.

Для этого в запрос Число студентов и средний балл группы включите поле ПБАЛЛ и в строке Групповые операции заменим слово Группировка словом Условие, выбрав его из списка. После этого введем в строку Условие отбора: >4,5. Просмотрите и сохраните запрос под именем Балл > 4,5.

ЗАДАНИЕ №6

РАЗРАБОТКА ОТЧЕТОВ

I. Создание отчета для одной таблицы

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

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

СПИСКИ СТУДЕНТОВ

(Текущая дата)

Список студентов группы__________

Номер Фамилия рождения Прох. Балл

Средний проходной балл группы _________

Рис.5. Макет для создания отчета со списками студентов по группам

Создание однотабличного отчета в режиме конструктора

В окне базы данных выберем закладку Отчеты и нажмем кнопку Создать.

В окне Новый отчет выберем таблицу СТУДЕНТ, которая будет источником данных для отчета, и выберем режим работы - Конструктор.

В открывшемся окне конструктора отчет содержит разделы, указанные на рисунке. Если отсутствует раздел Заголовок отчета, добавьте его с помощью кнопки Заголовок/Примечание отчета или выполнив команду Вид - Заголовок/Примечание отчета.

Рис.6 Разделы отчета в режиме конструктора

Группировка данных отчета

Так как общий список студентов должен быть разбит по группам, выполним группировку по полю НГ (номер группы). Для этого нажмем кнопку Сортировка и группировка на панели инструментов конструктора и заполним поля открывшегося окна.

В окне будет представлено поле НГ, по которому определена группировка и поле НС, по которому определена сортировка. Для создания в отчете заголовка и примечания группы в окне Сортировка и группировка выбрать:

· Заголовок группы Да

· Примечание группы Да

· Группировка По полному значению

· Интервал 1

· Не разрывать Полную группу

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

После определения группировки в окне конструктора отчетов появляются дополнительные разделы Заголовок группы НГ, Примечание группы НГ.

Размещение данных в разделах отчета

Данные в отчете должны быть сконструированы так, как показано на макете отчета (рис.5).

Размещение полей из таблиц

Значение номера группы должно быть представлено один раз в заголовке группы. Разместим поле НГ в разделе Заголовок группы НГ. Для этого нажмем кнопку панели инструментов конструктора отчетов Список полей и перетащим поле НГ в раздел заголовка НГ. Откорректируем подпись поля, изменив ее на Список студентов группы. Установим нужный шрифт в элементах. Нажмем кнопку панели инструментов По размеру данных для установки размеров рамки по размеру текста подписи или по команде Формат-Размер-По размеру данных.

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

Включение вычисляемого поля в отчет.

Для включения расчетного реквизита Средний проходной балл группы нажмем кнопку Поле на панели элементов и разместим элемент Свободный в раздел Примечание группы НГ. Определим в свойствах этого элемента выражение для расчета среднего значения. Для этого напишем на закладке Данные в строку Данные функцию =Avg([ПБАЛЛ]), в строку Число десятичных знаков – 2, на закладке Макет в строку Формат поля – Фиксированный. Отредактируем подпись поля. Для этого выделим подпись и вызовем ее свойства. В свойствах объекта Поле на закладке Макет в строке Подпись запишем – Средний проходной балл группы.

Добавление текущей даты и номера страницы.

Для добавления в отчет текущей даты воспользуемся встроенной функцией Now(). Для этого создадим в заголовке отчета свободный элемент, нажав кнопку Поле, и зададим в окне его свойств на закладке Данные в стоке Данные = Now(). На закладке Макет в строке Формат поля выберем значение – Полный формат даты. Подпись этого поля выделим и удалим.

Для добавления номера страницы в нижний колонтитул создадим свободный элемент и заполним в его свойствах на закладке Данные строку Данные выражением =[Page]. Отредактируем подпись этого поля, записав в его свойствах на закладке Макет в строке Подпись - Стр.

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

В отчет будет добавлено поле, в свойствах которого на закладке Данные в строке Данные будет записано соответствующее выражение. Если в отчете имеется раздел заголовка, поле добавляется в этот раздел. Иначе поле вносится в раздел данных.

В качестве выражения записывается функция Format, которая формирует значение на основе заданных ей аргументов. Аргументами ее является функция Date(), возвращающая текущую системную дату, и формат, в котором должна выводиться дата.

Добавить в отчет поле нумерации страниц можно, выполнив в режиме конструктора команду Вставка - Номер страницы.

В окне диалога Номера страниц выбираются параметры, определяющие формат, расположение и выравнивание номеров страниц.

Для печати номера на первой странице устанавливается флажок Отображать номер на первой странице.

Номера страниц выводятся с помощью выражения, записываемого в свойствах поля на закладке Данные в строке Данные. Выражение может иметь вид ="Страница"&[Раgе] или =''Страница" &[Раgе]& "из" &[Раgе], что соответствует выбранному Страница N или Страница N из М.

Завершение оформления отчета. Для окончательного оформления введем в Заголовок отчета название отчета - СПИСКИ СТУДЕНТОВ. Для этого воспользуемся кнопкой панели элементов Надпись. Установим нужный шрифт надписи, воспользовавшись панелью форматирования.

Для вывода названия отчета на последующих страницах введем его в Верхний колонтитул. Для этого скопировать данные из раздела Заголовок отчета и вставить в раздел Верхний колонтитул, а затем выбрать нужный шрифт, Далее надо указать в свойствах отчета на закладке Макет в строке Верхний колонтитул - Без заголовка. Свойства отчета могут быть вызваны при установке курсора на квадрате пересечения линеек. Создадим линии в соответствии с макетом, воспользовавшись кнопкой панели элементов Линия. Окончательно имеем (см. рис.7). Сохраните отчет под именем Списки групп.

Рис.7. Окно конструктора отчетов при разработке отчета для вывода данных из одной таблицы СТУДЕНТ.

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

II. РАЗРАБОТКА МНОГОТАБЛИЧНЫХ ОТЧЕТОВ

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

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

Рассмотрим построение отчета для таблиц, находящихся в одномногозначных отношениях. В этих отношениях одна таблица является главной, а другая подчиненной.

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

Многотабличный отчет с основной частью на базе главной таблицы.

Рассмотрим технологию создания многотабличного отчета на примере таблиц КАФЕДРА и ПРЕПОДАВАТЕЛЬ. Пусть необходимо подготовить отчет, содержащий сведения о кафедрах, включая списки преподавателей.

Макет отчета. Предположим, что макет для отчета со списками преподавателей по кафедрам должен иметь вид, представленный на рис. 8 .

Кафедра

(Текущая дата)

Название кафедры______________________

Заведующий___________________________

Код______________ Телефон_____________

Преподаватели кафедры

Таб. номер Уч. степень Уч. звание

Рис. 8. Макет отчета о кафедрах.

В соответствии с проектом макета в отчет предполагается выводить данные по каждой кафедре, включая название, код и телефон, а также фамилию и фотографию заведующего. Эти данные содержатся в таблице КАФЕДРА. В табличной части по каждой кафедре необходимо вывести список преподавателей. Эти данные могут быть получены из таблицы ПРЕПОДАВАТЕЛЬ.

Предложенный макет дает основание выбрать в качестве основной таблицы отчета таблицу КАФЕДРА, а таблицу ПРЕПОДАВАТЕЛЬ - в качестве источника данных для подчиненной формы со списком преподавателей.