Главная / Информационные основы систем управления / Создание итоговых, параметрических и перекрестных запросов. Формирование отчетов по запросам

Создание итоговых, параметрических и перекрестных запросов. Формирование отчетов по запросам

Лабораторная работа № 4

Цель работы: создание итоговых, параметрических и перекрестных запросов и отчетов по запросам на основе учебной базы «Библиотека».

Краткие теоретические сведения

Создание итоговых запросов. Иногда нас интересуют не отдельные записи таблицы, а итоговые значения по группам данных. Например, может понадобиться общая сумма книг для всех читателей за последний месяц или вы хотите узнать средний объем заказов по каждому месяцу прошлого года. Ответы на подобные вопросы дает итоговый запрос. Для задания вычислений итоговых значений нажмите кнопку «S» (групповые операции) на панели инструментов Конструктора запросов, чтобы в бланке запроса появилась строка «Групповая операция».

Теперь записи по каждому полю будут группироваться, но итоги подводиться не будут. Если выполнить запрос, то вы получите набор записей, содержащий по одной строке для каждого уникального значения поля запроса, но без итогов. Для вычисления итогов замените установку «Группировка» в строке «Групповая операция» на конкретные итоговые функции.

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

1. Sum – возвращает сумму всех значений данного поля в каждой группе. Используется только для числовых или денежных полей.

2. Avg – возвращает среднее арифметическое всех значений данного поля в каждой группе. Используется только для числовых или денежных полей. При вычислении функции Access исключает значения Null.

3. Min – возвращает наименьшее значение, найденное в данном поле внутри каждой группы, для числовых полей возвращает наименьшее значение, для текстовых полей – наименьшее из символьных значений независимо от регистра. Access игнорирует значения Null.

4. Мах – возвращает наибольшее значение, найденное в данном поле внутри каждой группы, для числовых полей возвращает наибольшее значение, для текстовых полей – наибольшее из символьных значений независимо от регистра. Access игнорирует значения Null.

5. Count – возвращает число записей, в которых значения данного поля отличны от Null. Чтобы подсчитать число записей в каждой группе с учетом значений Null, введите в строку «Поле» специальное выражение COUNT (*).

6. StDev – возвращает стандартное отклонение всех значений данного поля в каждой группе. Эта функция применяется только к числовым или денежным полям. Если в группе меньше двух строк, Access возвращает значение Null.

7. Var – возвращает дисперсию значений данного поля в каждой группе. Эта функция применима только к числовым или денежным полям. Если в группе менее двух строк, Access возвращает значение Null.

8. First – возвращает первое значение данного поля в группе.

9. Last – возвращает последнее значение данного поля в группе.

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

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

Для создания параметрического запроса необходимо сначала создать любой другой запрос или использовать уже имеющийся. А в нем для каждого поля, которое предполагается использовать как параметр, вводится в ячейку строки «Условие отбора – текст приглашения», заключенный в квадратные скобки. Это приглашение будет выводиться при запуске запроса. Текст подсказки должен отличаться от имени поля, но может включать его.

Для поля, в котором отображаются даты, можно вывести приглашения «Введите начальную дату:» и «Введите конечную дату:» для определения диапазона отбираемых значений. Для этого в ячейку строки «Условие отбора» вводится выражение Between [Введите начальную дату:] And [Введите конечную дату: ].

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

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

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

Для этого:

1) создайте перекрестный запрос с помощью или без помощи Мастера;

2) отобразите его в режиме Конструктора;

3) выберите фоновую область окна Конструктора запроса вне бланка запроса и списка полей;

4) откройте окно свойств запроса нажатием кнопки «Свойства» на панели инструментов;

5) в ячейку свойства «Заголовки столбцов» введите заголовки столбцов в том порядке, в котором их следует выводить в запросе. Заголовки столбцов следует разделять точкой с запятой или использовать символ разделителя списка данной страны. (Для того чтобы найти конкретный символ разделителя списка, откройте окно Язык и стандарты панели управления Windows).

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

Примечания:

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

2. При частом использовании одних и тех же заголовков в разных запросах создайте таблицу с одним текстовым полем, в которой сохраняются заголовки столбцов. После этого открывайте таблицу и копируйте заголовки в ячейку свойства «Заголовки столбцов» (Column Headings).

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

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

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

Структура отчета. Отчеты состоят из разделов, а разделы могут содержать элементы управления. Но, в отличие от форм, разделов в отчетах больше, а элементов управления, наоборот, меньше. Со структурой отчета проще всего ознакомиться, создав какой-либо отчет с помощью Мастера, а затем открыв его в режиме Конструктора. Структура отчета состоит из пяти разделов: заголовка отчета, верхнего колонтитула, области данных, нижнего колонтитула и примечания отчета.

Раздел заголовка служит для печати общего заголовка отчета. Раздел верхнего колонтитула можно использовать для печати подзаголовков, если отчет имеет сложную структуру и занимает много страниц. Здесь можно также помещать и колонцифры (номера страниц), если это не сделано в нижнем колонтитуле. В области данных размещают элементы управления, связанные с содержимым полей таблиц базы. В эти элементы управления выдаются данные из таблиц для печати на принтере. Раздел нижнего колонтитула используют для тех же целей, что и раздел верхнего колонтитула. Раздел «Примечания» используют для размещения дополнительной информации.

Задание к лабораторной работе

1. Создать следующие запросы:

А) итоговый запрос, который выводит список читателей и общее количество заказанных ими книг:

Закладка «Запросы», «Создание запроса в режиме Конструктора»;

Добавить таблицы «Читатели», «Выдача книг» и закрыть окно;

В бланк запроса добавить из таблицы «Читатели» поле «Фамилия», а из таблицы «Выдача книг» – «Код книги»;

Выбрать из меню «Вид – Групповые операции». По умолчанию в новой строке бланка для всех полей устанавливается элемент «Группировка» и при выполнении запроса итоги не подводятся;

Для получения итогов для поля «Код книги» в строке «Групповая операция» выбрать функцию Count;

В поле заголовка этого столбца перед полем «Код книги» ввести «Общее кол-во книг»;

Установить сортировку по убыванию и сохранить запрос.

Б) Создать параметрический запрос, который выводит в алфавитном порядке список читателей, заказавших книги в определенный интервал времени, который будет устанавливаться пользователем:

Создать обычный запрос с помощью Конструктора;

Добавить таблицы «Читатели» и «Выдача книг», закрыть окно;

В бланк запроса добавить из таблицы «Читатели» поля «Фамилия», «Имя», «Отчество», а из таблицы «Выдача книг»―«Дата заказа»;

Для поля «Фамилия» установить сортировку по возрастанию;

В строке «Условия отбора» для поля «Дата заказа» ввести выражение: Between [Введите начальную дату:] And [Введите конечную дату:];

Далее необходимо указать тип каждого параметра: выбирать из меню «Запрос» пункт «Параметры» и в открывшемся окне в столбец «Параметры» ввести имя каждого параметра (в точном соответствии с бланком запроса), а во втором столбце выбрать нужный тип каждого параметра (ДатаВремя) и щелкнуть по кнопке «ОК»;

Сохранить и выполнить запрос.

В) Создать параметрический запрос, который выводит книги по первым буквам фамилии автора:

Cначала создать обычный запрос;

Добавить таблицы «Книги » и «Издательства»;

Выбрать поля: «Автор», «Название», «Год издания книги», «Город» и «Название издательства»;

Для поля «Автор» установить сортировку по возрастанию;

В строке условия отбора для поля «Фамилия» ввести выражение: Like [Введите первые буквы фамилии:] &«*»;

Далее указать тип параметра;

Сохранить и выполнить запрос.

Г) Создать запрос, который содержит список издательств и для каждого издательства выводит количество книг, изданных за каждый год работы библиотеки:

Закладка «Запросы», «Создание запроса в режиме Конструктора»;

Добавить таблицы «Книги » и «Издательства»;

Выбрать поля «Год издания» и «Код книги», «Название издательства»;

В меню «Запрос» выбрать пункт «Перекрестный» (в бланке появится две новые строки «Групповая операция» и «Перекрестная таблица»).

В строке «Перекрестная таблица» для поля «Название издательства» установить «Заголовки строк», а для поля «Год издания» – «Заголовки столбцов». В строке «Групповая операция» по ним осуществляется «Группировка»;

Для поля «Код книги» в строке «Перекрестная таблица» установить «Значение», а в строке «Групповая операция» – Count;

Добавить итоговый столбец: еще раз добавить в бланк поля «Код книги». В строке «Перекрестная таблица» установить «Заголовок строк», а в строке «Групповая операция» выбрать Count. Переименовать столбец в «Итого»;

Сохранить и выполнить запрос.

2. Создать и сохранить следующие запросы в базе данных «Библиотека».

А) Итоговые запросы:

Список городов и наименований издательств, общая сумма стоимости книг по группам (вычисляемому полю присвоить имя «Общая стоимость книг»);

Список фамилий читателей и для каждого читателя – количество книг, заказанных им в 1999 г. (вычисляемому полю присвоить имя «Общее количество книг»);

Список наименований издательств, фамилии авторов и общее количество книг, изданных каждым издательством (вычисляемому полю присвоить имя «Общее количество книг»).

Б) Параметрические запросы:

Условием выбора являются книги определенного года издания;

Поиск книг по ключевому слову в теме. Запрос создать на основании таблиц «Книги», «Издательства», «Темы»;

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

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

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

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

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

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

4. По каждому запросу создать отчет с помощью Мастера, в случае необходимости провести его более точную настройку в режиме Конструктора.