Иногда в запросах требуется произвести некоторые вычисления, например, посчитать налог 13 % для каждой сделки.
Для этого необходимо выполнить:
1. Создайте копию запроса Поступившие заказы с именем Заказы_налог_прибыль.
2. Откройте его в режиме Конструктор.
3. В пустом столбце бланка запроса щелкните правой кнопкой мыши на ячейке Поле и в появившемся контекстном меню выберите команду Построитель. Перед вами появится окно Построитель выражений (рис. 12), который состоит из трех областей: поля выражения, кнопок операторов и элементов выражения. Сверху располагается поле выражения, в котором оно и создается. Вводимые в это поле элементы выбираются в двух других областей окна Построителя.
4. В левом списке откройте папку Запросы и выделите запрос Заказы_налог_прибыль. В среднем списке выделите поле Сумма и нажмите кнопку Вставить. Идентификатор этого поля появится в поле выражения Построителя.
5.
 |
Щелкните на кнопке * и введите 0,13 (см. рис. 12). Таким образом, мы посчитаем налог 13 %.
Рисунок 12. Построитель выражений для вычисляемых полей
6. Нажмите кнопку ОК, после чего в ячейке Поле появится значение Выражение1: [Сумма]*0,13.
7. Из контекстного меню этого поля откройте его Свойства. В окне Свойств в строке Подпись введите Налог, установите формат этого поля Денежный и закройте Конструктор (можно Выражение1 заменить на Налог).
8. Откройте запрос, просмотрите таблицу результата запроса. Добавьте подсчет суммы по этому полю Налог.
9. Самостоятельно добавьте в запрос Заказы_налог_прибыль поле Прибыль, в котором будет вычисляться доход от заказа (т. е. сумма минус налог). В свойствах этого поля введите подпись Прибыль, установите формат поля – Денежный.
Задание 3.5. Создание перекрестного запроса Мастером запросов
В перекрестном запросе мастер формирует таблицу, в которой левый столбец образует заголовки строк из значений одного поля, верхняя строка образует заголовки столбцов из значений другого поля, а на пересечении строк и столбцов размещаются итоговые значения, вычисленные по значениям третьего поля. Для получения итоговых значений записи группируются по полям, используемых в качестве заголовков строк и столбцов, а для значений третьего поля в полученных группах записей применяется одна из выбранных статистических функций.
1. Рассмотрим создание перекрестной таблицы на примере запроса Поступившие заказы.
Для этого выполним:
Ø По команде Создание => Другие запустим Мастер запросов;
Ø Выберем Перекрестный запрос =>OK;
Ø В окне Создание перекрестных таблиц выберем Показать запросы;
Ø Из списка выберем запрос Поступившие заказы=>Далее;
Ø Из области Доступные поля переведем в правую часть поле Фамилия=>Далее;
Ø Выберите поле Название компании =>Далее;
Ø Поля: Сумма, Функции: Сумма=>Далее;
Ø Согласитесь с именем Поступившие заказы перекрестный =>Готово.
Отобразится результат перекрестного запроса, в который введите подсчет итоговых значений по столбцам для каждой компании. Результаты образца запроса в окончательном виде представлены на рисунке 13.
Рисунок 13. Таблица результата перекрестного запроса
2. Самостоятельно создайте два перекрестных запроса на основе запроса Заказы_налог_прибыль. Первый перекрестный – по сумме налога, второй – по сумме прибыли.
Лабораторная работа № 4
Использование форм в базе данных
Формы - это объекты базы данных, предназначенные для просмотра данных таблиц и запросов, для ввода данных в базу, редактирования существующих данных и выполнения заданных действий. Форма может содержать графики, рисунки и другие внедренные объекты.
Можно вносить данные в таблицы и без помощи форм. Но существует несколько причин, которые делают формы незаменимым средством ввода данных в базу:
Ø при работе с формами ограничен доступ к таблицам;
Ø разные пользователи могут иметь разные права доступа к информации в базе. Для ввода данных им предоставляются разные формы, хотя данные из форм могут поступать в одну таблицу;
Ø экранные формы можно сделать точной копией бумажных бланков документов, благодаря этому уменьшается количество ошибок при вводе и снижается утомляемость персонала и т.д.
Создавать формы можно на основе нескольких таблиц или запросов с помощью Мастера, используя средство автоформы, в режиме Конструктора, сохраняя таблицу или запрос как форму. Созданную любым способом форму можно затем изменять в режиме Конструктор.
На вкладке Создание в группе Формы отображены способы создания форм, которые представлены на рисунке 14.
Рисунок 14. Способы создания форм
Задание 4.1. Создание формы Мастером форм
С помощью Мастера форм создадим форму для таблицы Сотрудники.
Для этого необходимо:
1. Выполнить команду Создание => Формы => Другие формы => Мастер форм.
2. В диалоговом окне Создание форм выберите таблицу Сотрудники и все поля, которые будут помещены в форму (кроме первого Код сотрудника). Перейдите на следующий уровень кнопкой Далее.
3. В следующих диалоговых окнах мастера выберите внешний вид формы В один столбец, стиль-любой, задайте имя формы Сотрудники фирмы и щелкните на кнопку Готово.
Просмотрите форму в режиме Форма. Для внесения изменений в заголовок, размер ячеек (полей), изменений гарнитур шрифта и прочее существуют два режима:
Ø Конструктор для существенных изменений структуры формы созданной Мастером;
Ø Макет, который полностью копирует отображения формы, но в макете возможны изменения шрифтов, сетки и др. Выберите удобные способы форматирования макета формы. Если устанавливаются одинаковые форматы для нескольких полей, то их включают в группу при нажатой клавиши Shift.
a) Используя режим Макет выполните корректировку созданной формы.
b) В режиме Форма введите новую запись, включив друга в качестве сотрудника фирмы (использовать в группе Записи => Создать или кнопки внизу формы 
.
4. Самостоятельно создайте форму для таблицы Клиенты. Вид формы выберите Ленточный или Табличный. Выполните форматирования. Измените заголовок, подписи некоторых полей в режиме макета.
Задание 4.2. Включение элементов управления в форму
При открытии формы в режиме Конструктор на ленте появляются две вкладки группы Инструментов конструктора форм. С помощью Конструктор в форму можно добавлять различные Элементы управления как форм так и отчетов, которые представлены на рисунке 15.
Рисунок 15. Элементы управления вводимые в форму (Отчет)
Создадим в форме Сотрудники фирмы несколько кнопок. Для этого:
Ø Откройте форму Сотрудники фирмы в режиме Конструктор. Этот режим предназначен для создания и редактирования форм.
Ø В заголовок формы добавьте текст Сотрудники фирмы.
Ø В примечание формы добавьте объект Кнопка (вкладка ленты Конструктор => Элементы управления).
Ø После того как вы «нарисуете» кнопку указателем мыши, на экране появится диалоговое окно Создание кнопок (рис. 16).
Ø В категории Работа с формой выберите действие Закрытие формы и нажмите кнопку Далее.
Ø Выберите рисунок или текст, который будет размещаться на кнопке.
Ø В последнем диалоговом окне Мастера кнопокзадайте имя кнопки (Закрытие формы) и нажмите Готово.
Рисунок 16. Создание управляющих элементов (кнопок) на форме
Мастер кнопок написал для данной кнопки процедуру на языке Microsoft Visual Basic. Просмотреть процедуру обработки события можно с помощью команды Обработка событий из контекстного меню кнопки.
5. Самостоятельно создайте кнопки Следующая запись, Первая запись, Последняя запись, Удаление записи, Ввод новой записи в области примечания, как это сделано на рисунке 17.
Рисунок 17. Управляющие элементы в форме
Задание 4.3. Создание многотабличных форм командой Форма
Создание формы для таблицы имеющей подчиненную удобнее использовать команду Форма в группе Формы. Для этого необходимо выполнить:
Ø в области перехода выделите таблицу Сотрудники;
Ø выбрать команду Создание => Формы => Форма;
Ø откроется созданная форма в режиме Форма, состоящая из двух частей шапки (одна запись таблицы Сотрудники) и табличная часть с отображением всех договоров заключенных сотрудником (из подчиненной таблицы Заказы), указанным в шапке формы. Переведите форму в режим Макет, исправьте предлагаемый заголовок на Заключенные договора сотрудниками фирмы. Выполните форматирование Формы и сохраните ее с именем Сотрудники_Заказы. Просмотреть форму в режиме Формы.
Используя кнопки внизу формы, просмотрите заключенные договора другими сотрудниками. Введите итоги подсчитывающие количество и сумму заключенных договоров каждым сотрудником. Введите себя в качестве нового сотрудника в форму и 2-3 договора, заключенные вами. Закройте форму и просмотрите содержимое таблиц Сотрудники _ Заказы. Какие изменения вы обнаружили?
1. Самостоятельно создайте сложноподчиненную форму для таблицы Клиенты. Сохраните ее с именем Клиенты_Заказы. Выполните форматирование, введите параметры для расчета по некоторым полям и др.
Задание 4.4. Создание форм с набором вкладок
Иногда на форме требуется разместить несколько страниц, содержащих данные из различных источников, справочную или вспомогательную информацию. Для этой цели можно использовать набор вкладок. Для этого необходимо выполнить следующее:
Ø Создайте пустую форму.
Ø Для добавления к форме набора вкладок щелкните по кнопке Вкладка группы Элементы управления. Сначала добавятся только две вкладки с формальными именами Вкладка 1 и Вкладка 2.
Ø Добавьте еще одну вкладку.
Ø Переименуйте ярлычки вкладок так, чтобы на них отображались названия данных, которые будут в них располагаться: Сотрудники фирмы, Клиенты фирмы, Помощь.
Ø Перейдите на вкладку Сотрудники и перетащите на нее мышкой форму Сотрудники фирмы.
Ø Аналогичным образом поместите форму на вкладку Клиенты фирмы.
Ø На вкладку Помощь поместите некоторые советы по работе с базой данных.
Ø Данную форму сохраните с именем Фирма и ее клиенты.
Ø Откройте форму Фирма и ее клиенты и просмотрите содержимое вкладок.
Задание 4.5. Создание кнопочной формы
В Microsoft Access можно создавать кнопочные формы. Они содержат только кнопки и предназначены для выбора основных действий в базе данных. Для создания кнопочной формы необходимо выполнить:
Ø на вкладке ленты Работа с базами данных выбрать команду Диспетчер кнопочных форм;
Ø если кнопочной формы в базе данных нет, то будет выведен запрос на подтверждение ее создания. Нажмите Да в диалоговом окне подтверждения;
Ø перед вами появится Диспетчер кнопочных форм, в котором щелкните по кнопке Создать;
Ø в диалоговом окне Создание (рис. 18) введите имя новой кнопочной формы и нажмите ОК;
Рисунок 18. Задание имени кнопочной формы
Ø имя новой кнопочной формы добавится в список Страницы кнопочной формы окна Диспетчер кнопочных форм (рис. 19). Выделите имя новой кнопочной формы и щелкните по кнопке Изменить;
Рисунок 19. Диспетчер кнопочных форм
Ø в диалоговом окне Изменение страницы кнопочной формы щелкните по кнопке Создать. Появится диалоговое окно Изменение элемента кнопочной формы (рис. 20);
Рисунок 20. Создание кнопок на форме
Ø в поле Текст введите текст подписи для первой кнопки кнопочной формы, а затем выберите команду из раскрывающегося списка в поле Команда. В поле Форма выберите форму, для которой будет выполняться данная команда;
Ø аналогичным образом добавьте кнопки Клиенты, Заказы, Выход;
Ø в диалоговом окне Диспетчер кнопочных форм выберите имя вашей кнопочной формы и щелкните по кнопке По умолчанию;
Ø рядом с названием кнопочной формы появится надпись «(по умолчанию)»;
Ø чтобы закончить создание кнопочной формы, щелкните по кнопке Закрыть.
В итоге должна получиться форма, представленная на рисунке 21.
Рисунок 21. Главная кнопочная форма
Добавьте в форму какой-нибудь рисунок.
Для того чтобы главная кнопочная форма появлялась на экране при запуске приложения, необходимо в главном меню нажать на кнопку Параметры Access (рис. 22). Для текущей базы данных установите форму просмотра - «кнопочная форма».
Рисунок 22. Задание параметров Access
Лабораторная работа № 5
Создание отчетов
В целом отчеты похожи на формы, но они, как правило, предназначаются для вывода информации из базы данных на принтер. Поэтому в отчетах данные форматируют так, чтобы их было удобно размещать на страницах. Отчеты позволяют группировать данные, разбивая их на логически цельные блоки. Имеются разные варианты оформления отчетов, предоставляет возможность добавлять итоговые значения, поясняющие надписи, колонтитулы, номера страниц, стили и различные графические элементы. Отчеты можно создать на основе таблиц, запросов и некоторых видов форм.
Именами всех созданных в работе отчетов выберем сокращенную форму заголовков в отчетах.
Создавать отчеты в базе данных можно несколькими способами:
Ø с помощью Мастера отчетов;
Ø на основе таблиц или запросов - Отчет;
Ø Пустой отчет;
Ø Наклейки;
Ø в режиме Конструктор.
 |
Рисунок 23. Способы создания отчетов
Задание 5.1. Мастер отчетов
Создадим отчет на основе запроса Заказы_налог_прибыль, включив все поля в отчет, выполним форматирование информации.
Для этого необходимо выполнить следующие действия:
1. Введите команду Создание Отчеты => Мастер отчетов.
2. Выберите из списка запрос Заказы_налог_прибыль, который будет использован как источник данных.
3. В появившемся диалоговом окне Создание отчетов (рис. 24) переместите все доступные поля в область «выбранные поля».
Рисунок 24. Мастер отчетов
4. Затем нажимая на кнопку Далее перейдете не следующий шаг Мастера. Пропускаете шаги: Выберите вид представления данных и следующий (Добавить уровни группировки?).
5. На шаге Мастера, который представлен на рисунке 25, выберите поля для сортировки.
Рис 25. Выбор сортировки информации для Отчета
6. На следующем шаге выберите стиль оформления отчета и далее имя отчета, затем после команды Готово отобразится созданный отчет в режиме П редварительного просмотра. Отчет, созданный мастером, необходимо отформатировать. Закрыв предварительный просмотр, отчет отобразится в режиме Конструктор. Выполнить форматирование отчета проще в режиме Макет, но введения в отчет графических элементов, дополнительных полей и другое можно выполнить, используя инструменты Конструктора. Окончательно отчет в режиме Просмотра должен выглядеть не хуже представленного на рисунке 26.
Рисунок 26. Окончательный вид отчета
В нижней строке видно сколько страниц необходимо для отображения отчета (пустых страниц быть не должно).
1. Самостоятельно с помощью Мастера отчетов создайте отчет Список сотрудников. В качестве источника данных используйте таблицу Сотрудники из которой выбрать все поля кроме Код сотрудника и Фото. Ориентацию листа выберите Альбомную. Информация о каждом сотруднике (запись) должна размещаться на лист без продолжения ее на следующем листе (уменьшать ширину столбцов, увеличивать высоту строк и др.). Итоги не подсчитывать.
Задание 5.2. Создание отчетов командой Отчет
2. Создайте самостоятельно отчет таблицы Клиенты со всеми полями. Для этого сначала выделите таблицу Клиент ы, затем командой Создание => Отчеты => Отчет появится макет отчета. В режиме Макет выполните форматирование информации и тогда ваш отчет будет иметь вид как на рисунке 27.
Рисунок 27. Окончательный вид отчета
3. Создайте самостоятельно отчет таблицы Заказы со всеми полями. В режиме Макет выполните форматирование информации.
4. Можете дополнительно создать и другие отчеты по запросам, формам (главное не количество, а качество).
Задание 5.3. Создание почтовых наклеек
Создадим почтовые наклейки таблицы Клиенты. Для этого необходимо:
Ø выполнить команду: Создание =>Отчеты => Наклейки;
Ø в диалоговом окне (рис. 28) укажите размер наклейки, систему единиц, тип наклейки и нажмите кнопку Далее;
Рисунок 28. Диалоговое окно Создание наклеек
Ø установите шрифт, размер, цвет текста и начертание. Нажмите кнопку Далее;
Ø выберите поля, которые будут размещаться на наклейке. Например, Название компании, Адрес, Телефон и Факс. Если на каждой наклейке требуется вывести определенный текст, то введите его в прототип наклейки;
Ø при необходимости измените, название отчета с наклейками и нажмите кнопку Готово. И вы будете иметь почтовые наклейки, представленные на рисунке 29.
Рисунок 29. Вид почтовых наклеек с пояснениями в поле прототип наклейки
1. Иногда в отчетах требуется вычислять не только итоговые значения, среднее, минимальное или максимальное значения, а также проценты.
Самостоятельно используя Мастер отчетов и в качестве источника данных укажите запрос Заказы_налог_прибыль выберите на определенном шаге мастера включить Итоги, затем в диалоговом окне для поля Сумма установите флажок в столбце sum, чтобы посчитать итоговую сумму и Показать процент.
Отчет после форматирования должен иметь вид, представленный на рисунке 30.
Рисунок 30. Вычисление суммы и процентной доли заказа для каждой компании
Список литературы
1. Стоцкий Ю., Васильев А., Телина И. Office 2007. Самоучитель. – СПб.:Питер, 2008. – 524 с.:ил.
2. Белянин М.В. Создание баз данных в Access 2007. – М.:НТ Пресс, 2008.=281с.:ил.
3. В.М. Паклина и др. Создание баз данных в Microsoft Access 2007. /Методические указания к лабораторным работам/. ГОУ ВПО УГТУ-УПИ, 2009.- 42 с.:ил.
Контрольные вопросы
К работе №1
1. Что называется базой данных (БД)?
2. Какие модели данных используются в БД?
3. Основные понятия реляционной БД.
4. Что такое система управления базами данных (СУБД)?
5. Чем отличается Microsoft Excel от Microsoft Access?
6. Какие объекты базы данных Microsoft Access вы знаете?
7. Какой объект в базе данных является основным?
8. Что называется полями и записями в БД?
9. Какие типы данных вы знаете?
10. Как можно переименовать поле?
11. Как можно создать поле с раскрывающимся списком?
12. С каким расширением сохраняется файл БД Access?
К работе №2
1. С помощью чего можно создавать таблицы?
2. Что такое ключевое поле?
3. Как установить несколько ключевых полей?
4. Как установить связи между таблицами?
5. Какие существуют отношения между таблицами?
6. Что означают на схеме данных «1» и «да»?
7. Зачем нужен Мастер подстановок?
К работе №3
1. Для чего предназначены запросы?
2. Какие виды запросов вы знаете?
3. С помощью чего можно создавать запросы?
4. Для чего используют запрос с параметром?
5. Как можно сделать вычисления в запросах?
6. Что означает запись в условии запроса «=50»?
7. Можно ли создавать запросы на основе нескольких таблиц?
К работе №4
1. Для чего предназначены формы?
2. Почему форма является незаменимым средством в БД?
3. С помощью чего можно создавать формы?
4. На основе чего можно создавать формы?
5. Как создать кнопку на форме?
6. Как можно разместить несколько таблиц и запросов на одной форме?
7. Как создать главную кнопочную форму?
К работе №5
1. Для чего предназначены отчеты?
2. Какие способы создания отчетов вы знаете?
3. Как в отчетах можно посчитать итоговые значения?
4. Какие итоговые значения можно посчитать в отчетах?
5. Как в Access напечатать почтовые наклейки?