Текст книги "Базы данных: Создание отчетов в СУБД MS Access 2007"

Базы данных: Создание отчетов в СУБД MS Access 2007

Практикум нацелен на формирование компетенции студента манипулировать информационными ресурсами средствами СУБД, представлять содержание прикладных и информационных процессов.

Основные понятия

База данных (БД) – поименованная совокупность взаимосвязанных данных, отображающая состояние объектов и их отношений в некоторой предметной области, и используемая для удовлетворения информационных потребностей пользователей. Базы данных в СУБД МS Access принадлежат к типу реляционных баз данных. В таких базах данных сведения, относящиеся к различным вопросам, сохраняются в отдельных таблицах, между которыми установлены связи.

База данных – файл, состоящий из таблиц и других информационных объектов.

Таблица – информационная объект, состоящий из записей. Таблица – это фундаментальная структура данных, основной информационный объект в СУБД.

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

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

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

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

Элемент управления в отчете – объект в отчете, улучшающий интерфейс пользователя при работе с данными, например, кнопка, переключатель, вкладка и пр.

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

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

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

Начало работы

Для получения представления об отчетах базы данных предлагается открыть в СУБД MS Access учебную базу данных «Борей 2007».

Открытие базы данных

Для открытия базы данных «Борей 2007» необходимо:

1. Запустить Microsoft Access, щелкнув на кнопке Пуск.

2. Выбрать «Все программы», затем Microsoft Office, затем Microsoft Access 2007. Откроется окно Microsoft Access 2007.

3. В области «Категории шаблонов» выбрать «Учебная база данных» и затем щелкнуть Борей 2007:

•  если на Вашем компьютере база данных «Борей 2007» открывается не в первый раз, то на экране Вы увидите окно заставки БД «Борей 2007»;

•  если в первый раз, то MS Access сначала выполнит ее загрузку. Для начала загрузки нужно щелкнуть командную кнопку «Загрузка» и следовать инструкциям.

Открывается окно базы данных «Борей 2007».

Закрывается база данных обычным для MS Office способом: в системном меню, содержащем команды работы с файлом, выбрать «Закрыть базу данных».

Задание 1. Запустите MS Access 2007. Откройте базу данных «Борей 2007». Закройте ее. Снова откройте базу данных «Борей 2007».

Особенности интерфейса MS Access 2007

В MS Access 2007 отличный от предыдущих версий интерфейс. Меню и панели инструментов заменяет лента меню с разными вкладками: Главная, Создание, Внешние данные, Работа с базой данных.

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

Рис. 1. Лента меню MS Access 2007 с вкладками

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

Под лентой расположены слева Область перехода, а справа Окно редактирования, в котором отображается редактируемый объект.

Рис. 2. Окно базы данных «Борей 2007»

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

Объекты базы данных

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

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

•  Форма хранит данные таблиц или запросов в виде совокупности экранных карточек, каждая из которых отображает содержимое одной записи таблицы или запроса, что удобно при обновлении данных в таблицах.

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

•  Макрос хранит совокупность операций обработки данных, которые можно запускать одним нажатием клавиши Enter, что удобно при необходимости часто выполнять одни и те же типовые операции.

•  Модуль хранит программу обработки данных, написанную на языке программирования, чаще всего на языке, встроенном в MS Access, Access Basic.

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

Задание 2. Сверните и разверните списки групп в Области перехода.

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

Таблицы и другие виды объектов скрыты в Области перехода в группе «Вспомогательные объекты».

В группе «Неназначенные объекты» хранятся объекты, создаваемые пользователем дополнительно. Например, здесь можно сохранить созданные Вами запросы.

Обратите внимание, что разные виды объектов обозначены разными значками:

Можно изменить представление объектов в Области перехода на привычный, применяемый в ранних версиях MS Access. Для этого нужно щелчком на значке списка (▼) в заголовке Области перехода развернуть список фильтров объектов и выбрать «Все объекты Access».

Задание 3. Просмотрите содержимое группы «Вспомогательные объекты». Откройте разные виды объектов, просмотрите их содержание. Закройте группу «Вспомогательные объекты». Измените представление объектов в Области перехода, чтобы сгруппировать объекты по видам: таблицы, запросы, формы, макросы, модули.

Область перехода можно свернуть и развернуть щелчком на кнопке с двойными стрелками (<<) или (>>) в верхнем правом углу панели.

Задание 4. Сверните и разверните Область перехода.

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

Вывести ее можно в Области перехода из группы «Вспомогательные объекты», далее форма «Заставка».

Задание 5. Закройте заставку БД «Борей 2007».

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

Закрыть схему данных можно щелком на кнопке «Закрыть» вкладки меню «Конструктор».

Задание 6. Откройте и закройте схему данных.

Справочная система MS Access 2007

Как любое приложение MS Office СУБД MS Access 2007 имеет свою справочную систему. Для того, чтобы ее открыть, нужно:

1. В правой части строки меню щелкнуть на кнопке (?).

2. В окне «Справка: Access» войти в нужный раздел справки в нижней части окна или в строке поиска ввести поисковый контекст и нажать Enter.

Задание 7. Откройте справочную систему MS Access 2007. Откройте раздел «Формы и отчеты». Просмотрите содержание раздела. Закройте справочную систему.

Открытие отчета и просмотр данных

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

Рис. 3. Окно отчета «Адресная книга сотрудников»

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

Задание 8.

1. Откройте отчет «Адресная книга сотрудников», просмотрите его.

2. Закройте отчет «Адресная книга сотрудников».

3. Закройте базу данных «Борей 2007».

Простые отчеты

Отчеты можно создавать на базе таблиц: первичных, хранящих исходные данные, и запросных – получаемых в результате выполнения запроса.

Создание простого отчета

Для создания простого отчета достаточно:

1. выделить в Области перехода источник данных для отчета – таблицу или запрос;

2. перейти на вкладку меню «Создание» (рис. 4);

Рис. 4. Вкладка меню для создания отчетов

3. щелчком выбрать инструмент «Отчет».

В Области редактирования появляется отчет в режиме макета.

Задание 9. Создайте простой отчет на базе таблицы «Заказы». Сохраните его под именем «Заказы1»

Просмотр отчета

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

Рис 5. Режимы работы с отчетом

Таблица 1

Поиск данных в отчете

Для поиска данных нужно:

1. Находясь в режиме «Представление отчета», щелкнуть на кнопке «Найти» вкладки меню «Главная». Появится диалог «Поиск и замена».

2. На вкладке «Поиск»:

•  в поле для ввода «Образец» ввести поисковый контекст: либо целиком, либо частично.

•  В поле «Совпадение» если поисковый контекст вводится частично, то следует указать вид совпадения поискового контекста с имеющимся в этом поле данным: с любой частью поля или с началом поля;

•  В поле «»Поиск в» указать имя отчета.

3. Щелкнуть на кнопке «Найти далее». В отчете поисковый контекст будет выделен черным.

4. Щелчок на кнопке «Найти далее» выделит следующее данное, удовлетворяющее критерию поиска, если таковое имеется.

Задание 10. Выполните поиск данных, задав поисковый контекст целиком. Затем выполните поиск, задав поисковый контекст частично.

Фильтрация записей

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

Для фильтрации Вы должны:

1. Оставить курсор в том поле, по которому будет выполняться фильтрация.

2. Щелкнуть на кнопку «Фильтр» вкладки панели инструментов «Главная».

Открывается окно диалога для определения фильтра – критерия выбора.

3. Определить критерий выбора карточек либо точно путем установки/снятия флажков со значений поля, по которому выполняется фильтрация, либо задать выражение, используя предлагаемые операторы «начинается с…», «содержит…» и пр. т.е. путем настройки фильтра (Рис. 6):

Рис. 6. Фильтрация по настраиваемому фильтру

В случае настройки фильтра ввести в диалог «Настраиваемый фильтр» поисковый контекст (Рис. 7):

Рис. 7. Настройка фильтра

4. Щелкнуть на кнопке ОК. Будут отобраны записи с выбранным значением поля.

Для отмены выборки нужно щелкнуть на кнопке «Дополнительно» вкладки панели инструментов «Главная», далее «Отменить фильтр» либо использовать здесь же кнопку «Применить фильтр».

Задание 11.

1. Выполните фильтрацию записей по полю «Сотрудник» путем точного задания значения кода. Отмените фильтр.

2. Выполните фильтрацию записей по полю «Клиент» для отбора записей, заканчивающихся на «М» Отмените фильтр.

Фильтрация в диапазоне значений

Если Вам требуется отфильтровать записи в диапазоне значений, то нужно воспользоваться кнопкой «Выделение». Вы должны:

1. Выделить поле, по которому будет выполняться фильтрация или просто установить в нем курсор.

2. Щелкнуть на кнопке «Выделение» вкладки меню «Главная» и выбрать команду «Между» либо, если задается одна из границ, то команды «Больше или равно» или «Меньше или равно».

3. В диалоге «Диапазон чисел» ввести границы диапазона, щелкнуть ОК.

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

Задание 12. Выполните фильтрацию записей по полю «Дата отгрузки» для отбора записей между двумя датами (выберите их самостоятельно). Отмените фильтр.

Работа с макетом отчета

Access 2007 предлагает интерактивный интерфейс работы с отчетом «что на экране, то и в результате» (WYSIWYG), давая пользователю новый режим работы с отчетом – режим макета. Режим макета позволяет изменять структуру, одновременно просматривая результаты. В режиме макета можно менять размеры и положение объектов формы: надписей и полей, менять шрифт надписей, менять порядок следования полей, используя обычные для MS Office способы. В режиме макета можно внести большинство изменений, обычно вносимых в конструкторе, и при этом просматривать данные в отчете.

Для работы с макетом на экран выводится вкладка меню «Формат» (Рис. 8):

Рис. 8. Вкладка меню «Формат» при работе с отчетом в режиме макета

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

Оформление заголовка и номеров страниц

Для изменения заголовка Вам нужно воспользоваться панелью «Элементы управления» вкладки меню «Формат» (Рис. 9).

Рис. 9. Инструменты для работы с объектами отчета

Вы должны:

1. Щелкнуть инструмент «Заголовок». Рамка объекта становится активной.

2. Внести изменения: вписать новое название, изменить форматы символов и абзаца заголовка и пр., используя панель «Шрифт» вкладки меню «Формат».

3. Выйти из режима редактирования щелчком мыши за пределами рамки объекта.

Аналогично можно изменить форматы представления номеров страниц отчета и отображения даты и времени создания отчета, используя для этого инструменты «Номер страницы» и «Дата и время» панели «Элементы управления».

Задание 13.

1. Измените заголовок отчета на «Заказы фирмы». Измените форматы изменить форматы символов и абзаца заголовка.

2. Формат страниц измените на «Страница N из M» с расположением в нижнем колонтитуле и выравнивании по центру.

Изменение форматов представления числовых данных, дат и времени

Для изменения форматов представления числовых данных, дат и времени Вы должны:

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

2. Используя панель «Форматирование» изменить формат данных.

Задание 14. Измените форматы полей:

1. «Стоимость доставки» на вывод одной значащей цифры после запятой.

2. «Дата оплаты» на вывод данных в среднем формате даты.

Добавление, удаление, изменение порядка следования полей

Для того, чтобы добавить новое поле в отчет, нужно:

1. Щелкнуть инструмент «Добавить поля». Справа открываются окна со списками полей.

2. Мышью перетащить нужное поле в нужное место отчета.

Для удаления поля из отчета нужно:

1. Щелчком поле выделить.

2. Из контекстного меню выбрать команду «Удалить».

Для изменения порядка следования полей Вам нужно:

1. Выделить заголовок поля.

2. Перетащить его мышью в нужное место.

Задание 15.

1. Добавьте в отчет новое поле «Тип платежа».

2. Удалите поле «Страна».

3. Поле «Город получателя » расположите перед полем «Адрес получателя». Поменяйте местами поля «Дата размещения» и «Клиент».

4. Измените фон надписи с датой создания отчета.

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

6. Просмотрите отчет в режиме «Представление отчета». Вернитесь в режим «Макет отчета».

Групповые операции в отчете

Расчет итоговых значений

Режим макета позволяет выполнять расчет итоговых значений в поле: минимальных, максимальных значений, количества записей и пр. в зависимости от того, какой тип данных хранит поле. Для этого в режиме макета можно использовать либо вкладку меню «Формат», либо контекстное меню.

Для расчета итоговых значений поля Вам нужно:

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

2. Открыть список итоговых расчетов, щелкнув на вкладке меню «Формат» кнопку «Итоги» или из контекстного меню.

Итоговое значение появляется в конце поля после последней записи.

Задание 16.

1. Рассчитайте минимальное и максимальное значение стоимости доставки. Просмотрите результат.

2. Рассчитайте число записей и количество значений в поле «Дата отгрузки». Просмотрите результат.

3. Просмотрите отчет в режиме «Представление отчета» и сохраните его под именем «Заказы1_итоги». Закройте отчет.

Группировка и сортировка записей

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

Для группировки Вы должны:

1. щелкнуть инструмент «Группировка» на вкладке меню «Формат». В области редактирования появятся дополнительные инструменты для группировки и сортировки (Рис. 10).

Рис. 10. Инструменты группировки и сортировки записей в отчете

Отказаться от проведения Группировки/Сортировки можно повторным щелчком на кнопке «Группировка» на вкладке меню «Формат».

Задание 17. Снова создайте на базе таблицы «Заказы» простой отчет. Начните проведение групповой операции и откажитесь от нее. Снова начните проведение групповой операции.

2. Щелкнуть инструмент «Добавить группировку» или «Добавить сортировку» Появляются окно «Группировка, сортировка и итоги» с инструментами для определения дальнейших условий выбранной групповой операции. Здесь нужно сначала выбрать поле, по значениям которого будет выполняться групповая операция, например, по полю «ИД клиента» (в отчете – поле «Клиент»):

В нашем примере на экране Вы увидите отчет в следующем виде (Рис. 11):

Рис. 11. Отчет с группировкой записей по полю «Клиент»

3. Если требуется, можно задать следующий уровень группировки записей, например, по «Дате размещения» заказа. Для этого щелкните на инструмент «Добавить группировку» в окне «Группировка, сортировка и итоги». Например, если Вы выберите дальнейшую группировку по полю «Дата размещения», то получите отчет в таком виде (Рис. 12):

Рис. 12. Отчет с группировкой записей по полям «Клиент» и «Дата размещения»

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

5. При необходимости щелкнуть в окне «Группировка, сортировка и итоги» на инструмент «Больше» и указать свои требования на расчет итоговых значений, правил оформления: без раздела заголовков, без раздела примечаний, размещать или нет группу на одной странице:

Задание 18.

1. Выполните группировку в отчете «Заказы» сначала по полю «ИД грузоотправителя» (заголовок поля в отчете «Доставка»), затем по полю «Дата отгрузки».

2. Задайте сортировку по полю «Дата отгрузки».

3. Внутри группы «ИД грузоотправителя» рассчитайте итоговое значение по полу «Стоимость доставки» «Среднее», показать его в примечании группы. Далее в отчете выделить средние значения по группе жирным шрифтом и залить фоновым цветом.

4. Внутри группы «Дата отгрузки» рассчитайте итоговое значение по полю «Ид заказа» по количеству записей и покажите его в заголовке группы.

5. Просмотрите отчет в режиме «Представление отчета» и сохраните его под именем «Заказы1_группировка».

Предварительный просмотр отчета

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

Рис. 13. Вкладка меню «Предварительный просмотр»

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

Задание 19. Создайте новый отчет на базе таблицы «Заказы». Откройте его в режиме представления. Самостоятельно:

1. Просмотрите отчет в разных масштабах, используя инструмент «Масштаб» и указатель мыши в виде лупы.

2. Сделайте верхнее поле равным 25 мм. Интервал строк – 1 мм.

3. Сохраните отчет в виде форматированного документа Word, под именем «Заказы1_печать» в папке «Мои документы». Просмотрите документ в текстовом процессоре Word.

4. Разместите отчет на двух страницах.

5. Сохраните отчет под именем «Заказы1_предварительный просмотр».