Вычисления в отчетах access. Создание отчетов Access. Мастер отчетов. Работа с отчетом в режиме конструктора. Типы агрегатов, которые можно добавить в отчет

Практическая работа № 16

Создание запросов Access в режиме конструктора. Запросы на выборку. Вычисляемые поля в запросах.

Создание отчетов Access. Мастер отчетов. Работа с отчетом в режиме конструктора.

1. Откройте БД Student, созданную в практической работе 15.

2. Войдите в режим конструктора запросов: на вкладке Создание в группе «Запросы» выберите кнопку «Конструктор запросов». В конструкторе запросы создаются вручную. Источником данных для запроса могут быть одна или несколько таблиц или запросов. Добавьте таблицу Студенты как источник данных. Изучите окно бланка запроса и инструментарий. Источники данных отображены в верхней части бланка запроса. Нижняя часть бланка предназначена:

· для задания полей, отображаемых в запросе (строка Поле и строка Имя таблицы);

· для задания способа упорядочения данных в запросе (строка Сортировка);

· для задания условий отбора (строки Условие отбора);

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

· для задания вычисляемых выражений в полях запроса.

3. Включите в запрос все поля таблицы Студенты . Включите сортировку по фамилии и запустите запрос на выполнение (кнопка в панели инструментов).

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

4. Запросы на выборку . Создайте запросы на выборку. Построение каждого нового запроса выполняется в конструкторе (как в пункте 3). Макет можно также многократно копировать, сохраняя каждый запрос с именем, отражающим содержание запроса. Задайте по очереди Условия отбора, указанные ниже, просмотрите результаты построенных запросов, и сохраните каждый из них под соответствующими именами. Итак, выберите:

· Студентов, фамилии которых начинаются на первые буквы алфавита (Like "[А-И]*") (имя Первые );

· Студентов, которые учатся в одной группе (имя Группа );

· Студентов, кроме тех, кто учится в определенной группе (например, Not И-105) (имя Кроме группы ).

5. Запросы на выборку с логическими операциями . Создайте запросы на выборку наложением условий на поля оценок за экзамены. Источником данных для запросов будут таблицы Студенты и Сессия . Сохраните с указанными именами. Итак, выберите:

· студентов только на отлично (имя Отличники ).

· студентов на хорошо и отлично (4 Or 5) (имя Четыре и пять ).

· студентов, имеющих хотя бы одну тройку (имя Троечники ).

6. Запросы на выборку с параметром . Если вместо условия отбора в строке задать текстовое приглашение на ввод, заключенное в квадратные скобки, например [Введите фамилию], то при запуске запроса можно будет задать параметр. Параметр - это любое значение, по которому будут отыскиваться все записи с указанным значением поля (в примере это фамилия). В новых запросах выполните выборку по значению параметров:

· По фамилии студента (имя Фамилия ).

· По коду студента (Имя Код ).

7. Обратите внимание, что при поиске с параметром используется точное соответствие значению параметра.

8. Вычисляемые поля в запросе . В новом запросе:

· Вычисляемое поле с названием Средний балл для вычисления среднего балла каждого студента:

Имя запроса – Средний балл .

· Вычисляемое поле с названием Сумма баллов для вычисления суммы баллов каждого студента. Имя запроса – Сумма баллов .

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

· Найдите средние оценки по предметам для каждой группы. В запрос должны быть включены поля Группа и Экамен1, Экзамен2, Экзамен3. Поле фамилии в этом запросе не нужно! Используйте группировку в поле Групповая операция и групповую функцию Avg() во всех полях оценок. Сохраните с именем Средние по группам .

· Найдите средние оценки по предметам для всего контингента студентов. В этот запрос включите только поля оценок с группировкой функцией Avg() во всех полях оценок. Сохраните с именем Средние по предметам .

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

11. Создание отчетов с использованием мастеров . На вкладке Создание в группе «Отчеты » выберите Мастер отчетов . Выберите источником данных таблицу Студенты . Выберите для включения в запрос поля Группа, Фамилия, Имя, Адрес, Телефон в указанном порядке. Не добавляя уровни группировки, отсортируйте поля по возрастанию номера группы, затем по возрастанию фамилии, затем имени. Выберите макет Табличный, любой стиль, задайте имя Студенты .

12. Отчет открывается в режиме просмотра. Обратите внимание на содержание вкладки просмотра (она называется «Предварительный просмотр»).

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

14. Группировка данных . Для объединения записей в группы используется команда Группировка на вкладке «Инструменты конструктора отчетов - Конструктор ». В отчете Студенты записи о студентах должны быть сгруппированы по номерам групп. Используйте группировку, чтобы добавить в отчет область Заголовок группы для номеров групп, и задайте порядок сортировки по возрастанию. Перенесите в область Заголовок группы надпись Группа из верхнего колонтитула, и поле Группа из области данных. Отделите тонкой линией сверху данные о различных группах.

15. Просмотрите результат в режиме просмотра.

16. Создание отчетов с использованием мастеров. Группировка данных . Используйте Мастер отчетов для построения отчета для таблиц Студенты и Сессия . Выберите для включения в запрос поля Группа, Фамилия, Имя, и оценки за три экзамена. Добавьте уровень группировки (по группе), отсортируйте по возрастанию фамилии, затем имена. Выберите макет Ступенчатый , любой стиль, задайте имя Сессия.

Вычисления в формах используются:

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

Правила составления выражений в Access мы уже описывали (см. разд. "Использование выражений в запросах" гл. 4). Поэтому здесь мы остановимся в первую очередь на вычисляемых полях формы и опишем, где они нужны и как их создать.

Внимание
Формулы для вычислений в Access записываются с помощью специального синтаксиса, который диктует правила записи формул и в выражениях Access и в процедурах на VBA. Этот синтаксис несколько отличается от принятого в математике, однако во многом на него похож и интуитивно понятен. Описание синтаксиса вычислительных формул приведено в разд. "Выражения в Access" гл. 4 .

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

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

Создайте свободный элемент управления типа Поле (Text Box) в нужном месте формы.
Прямо в поле введите выражение =Date () . Знак равенства обязателен.
Откройте окно Свойства (Properties) для этого элемента и задайте для свойства Формат поля (Format) желаемый формат даты, например Длинный формат даты (Long Date).
Установите длину поля таким образом, чтобы в нем помещалось необходимое количество символов.
Измените название метки этого текстового поля. Созданное поле будет выглядеть, как показано на рис. 9.47.

Рис. 9.47 . Вычисляемое поле

ОТЧЕТ В MS ACCESS.

Microsoft Access отображает в отчете данные из запроса или таблицы, добавляя к ним текстовые элементы, которые упрощают его восприятие.

К числу таких элементов относятся:

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

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

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

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

Примечание. Используется для вывода данных, таких как текст заключения, общие итоговые значения или подпись, которые следует напечатать один раз в конце отчета. Несмотря на то, что в режиме Конструктора раздел «Примечание» отчета находится внизу отчета, он печатается над нижним колонтитулом страницы на последней странице отчета. Для добавления или удаления области примечаний отчета необходимо выбрать в меню Вид команду Заголовок/примечание отчета. Microsoft Access одновременно добавляет и удаляет области заголовка и примечаний отчета.

Способы создания отчета:

В Microsoft Access можно создавать отчеты различными способами:

Конструктор;

Мастер отчетов;

Автоотчет: в столбец;

Автоотчет: ленточный;

Мастер диаграмм;

Почтовые наклейки.

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

Для создания Автоотчета необходимо выполнить следующие действия:

В окне базы данных щелкнуть на вкладке Отчеты и затем щелкнуть на кнопке Создать. Появится диалоговое окно Новый отчет.

Выделить в списке пункт Автоотчет: в столбец или Автоотчет: ленточный.

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

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

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

Изменение масштаба отображения отчета:

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

Печать отчета

Для печати отчета необходимо выполнить следующее:

В меню Файл щелкнуть на команде Печать.

В области Печатать щелкнуть на варианте Страницы.

Чтобы напечатать только первую страницу отчета, введите 1 в поле «с» и 1 в поле «по».

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

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

Если при печати в конце отчета появляется пустая страница, убедитесь, что параметр Высота для примечаний отчета имеет значение 0. Если при печати пусты промежуточные страницы отчета, убедитесь, что сумма значений ширины формы или отчета и ширины левого и правого полей не превышает ширину листа бумаги, указанную в диалоговом окне Параметры страницы (меню Файл).

При разработке макетов отчета руководствуйтесь следующей формулой: ширина отчета + левое поле + правое поле <= ширина бумаги.

Для того чтобы подогнать размер отчета, необходимо использовать следующие приемы:

Изменить значение ширины отчета;

Уменьшить ширину полей или изменить ориентацию страницы.

ВЫЧЕСЛЕНИЯ В MS ACCESS.

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

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

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

Стоимость: Товары! Цена * Количество * (1-Скидка)

Количество Мужчин: Sum (IIf(Пол = «м»; 1; 0))

Ф.И.О.: Фамилия &» «& Left (Имя; 1) &». «& Left (Отчество; 1) &».»

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

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

Стоимость: Цена*[Количество товара]

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

Обновить вычисленные результаты вручную невозможно.

Для построения сложных выражений в СУБД MS Access входит утилита, называемая Построитель выражений.

Построитель выражений можно запустить щелкнув по клавише Построить на панели инструментов Конструктор запросов или выбрав в контекстном меню поля бланка запроса QBE команду меню Построить… .

Рис. 9.

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

Рис. 10.

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

При анализе данных очень часто интересуют не отдельные записи, а итоговые значения по группам данных, например:

Количество сделок с Партнерами за определенный промежуток времени;

Средний объем продаж по каждому месяцу за предыдущий год.

Ответы на такие вопросы дает итоговый запрос.

Для вычисления итоговых значений необходимо нажать кнопку Групповые операции на панели инструментов Конструктор запросов , чтобы в бланке QBE появилась строка Групповая операция (после имени таблицы).

По умолчанию для каждого поля, занесенного в бланк запроса, устанавливается значение Группировка (итоги не подводятся).

Для подведения итогов необходимо заменить установку Группировка на конкретную итоговую функцию. В СУБД MS Access предусмотрено 9 функций, обеспечивающих выполнение групповых операций.

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

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

Для решения более сложных статистических задач в СУБД MS Access предусмотрен специальный тип запроса - перекрестный запросов.

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

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

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

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

Создание отчетов

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

Чтобы создать отчет:

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

Удаление полей

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

Чтобы удалить поле в отчете:

Щелкните любую ячейку в поле, которое вы хотите удалить.
Нажмите клавишу «Удалить».

Когда вы удаляете поле, обязательно удалите его заголовок. Просто выберите заголовок и нажмите клавишу «Удалить».

Печать и сохранение отчетов в режиме предварительного просмотра

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

Чтобы распечатать отчет:

Сохранение отчетов

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

Access предлагает варианты сохранения отчета в виде файла Excel, текстового файла, PDF, файла XPS, вложения электронной почты, текстового файла или HTML-документа. Поэкспериментируйте с различными вариантами экспорта, чтобы найти тот, который наилучшим образом соответствует вашим потребностям.

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

Чтобы добавить к отчету вычисляемое поле "Сумма":

Откройте отчет "Товары на складе" в режиме Конструктора.
На панели элементов нажмите кнопку Надпись (Label) и поместите новую надпись справа от надписи "На складе" (Units in Stock) в разделе верхнего колонтитула. Введите в качестве текста надписи слово "Сумма" (Sum).
Чтобы создать вычисляемое поле "Сумма" (Sum), нажмите кнопку Поле (Text Box) на панели элементов и добавьте текстовое поле справа от текстового поля "НаСкладе" (UnitsInStock) в разделе данных. Удалите метку нового текстового поля.
Щелкните внутри текстового поля и введите в качестве его значения выражение:
= [НаСкладе]* [Цена] (= * ).
Откройте окно свойств поля, дважды щелкнув по нему, и на вкладке Макет (Format) в поле со списком свойства Формат поля (Format) выберите значение Денежный (Currency).

Замечание

Ввести выражение в текстовое поле можно несколькими способами: выделите его и нажмите комбинацию клавиш +, чтобы войти в режим редактирования содержимого поля, или сделайте двойной щелчок по полю для отображения диалогового окна Свойства (Properties), а затем введите выражение как значение свойства Данные (Source).

Рис. 6.27.

Повторите шаги 3 и 4 для того, чтобы создать текстовое поле в разделе примечаний группы "Категория" (Category), но в качестве выражения для вычисления промежуточного значения следует ввести: =Sum([НаСкладе] * [Цена]) (=Sum( * )). Нажмите кнопку Полужирный (Bold) на панели форматирования, чтобы установить полужирный шрифт для текста в новом поле. Откройте диалоговое окно Свойства (Properties) и раскройте в нем вкладку Другие (Other), в поле Имя (Name) введите имя текстового поля "Сумма" (Sum). Затем раскройте вкладку Макет (Format) и в поле со списком свойства Формат поля (Format) выберите значение Денежный (Currency).
Поскольку ширина области данных нашего отчета увеличилась, уменьшите ширину полей страницы, чтобы все поля поместились на одной странице. Для этого выберите команду Файл, Параметры страницы (File, Page Setup) и задайте вместо ширины левого и правого полей, заданных по умолчанию, значение 10 мм (см. разд. "Просмотр и печать отчетов" в этой главе).
Нажмите кнопку Вид (View) на панели инструментов Конструктор отчетов (Report Design), чтобы оценить результаты своей работы. Отчет будет выглядеть так, как показано на рис. 6.27. К прежним данным добавился крайний правый столбец со значениями стоимости каждого товара в имеющемся количестве на складе и промежуточных итоговых сумм по стоимости товаров для каждой категории.