2015-05-14
1461
Лабораторная работа № 16
Cоставление форм, запросов, отчетов в MS ACCESS
(2 часа)
Цель и содержание: научиться создавать различные формы, отчеты и запросы к связанным таблицам БД.
Теоретическое обоснование
Таблицы, формы, отчеты и запросы – основные объекты в современных базах данных.
Для упрощения просмотра, ввода и модификации данных в конкретной таблице, создается одна или несколько форм. Формы являются шаблонами, управляющими отображением информации при вводе, просмотре и корректировке данных.Формы облегчают работу по вводу информации в БД, выводят данные из таблиц и запросов в удобном для чтения и анализа формате.
Отчет – это средство для организации просмотра и распечатки итоговой информации в удобном для пользователя виде. В отчете можно получить результаты сложных расчетов, статистических сравнений, а также поместить рисунки и диаграммы.
Запросы являются средством обработки данных, хранимых в таблицах.Запросы собирают затребованные данные из одной или нескольких таблиц по заданным критериям,являются инструментом поиска и структурирования данных. При использовании технологии клиент-сервер, где основные БД хранятся на сервере БД, система запросов позволяет уменьшить объем информации, передаваемый через сеть. Запрос – это вопрос о данных, то есть набор условий, по которым производится выборка информации из таблиц. Запуск запроса формирует новую таблицу данных, которая при повторных запусках запроса может обновляться в соответствии с изменением информации в исходных таблицах. Существуют разные типы запросов: на выборку, запрос с параметрами, перекрестные запросы, запрос на изменение таблицы.
Запросы с параметром – это отбор записей в базовой таблице по введенным параметрам.
Запросы на выборку – это получение результирующей таблицы, в которой отображаются записи из базовых таблиц, удовлетворяющие условиям запроса. При построении выражений в запросах используют:
· знаки сравнения: < меньше, <=меньше или равно, > больше, >= больше или равно, = равно, <> не равно;
· логические операции: And (И), Not (Нет), Or (Или);
· операторы SQL: In (для определения, содержится ли элемент данных в списке значений) и Between…And (для выбора значений из определенного интервала).
· функции: Avg() – среднее арифметическое значение; Count() – количество записей; Sum() – сумма всех записей и т.д.
Например:
а) значения меньше 50, но больше 60 включительно, задаются в условии как <=50 Or >=60;
б) значения из отрезка [50;60] задаются в условии как >=50 And <=60 или; Between 50 and 60;
в) все данные за 2008 год в условии задаются как Between #01.01.2008#аnd#31.12.2008#;
г) значения из списка перечисленных значений задаются в условии как In(50; 55; 57; 60).
Используя запросы в числовых полях, данные можно обработать с применением статистических функций (таблица 2.1), которые выполняют вычисления со столбцами данных и возвращают единственное значение.
В Office Access 2007 предусмотрено несколько способов добавления статистических функций в запрос:
· открыть запрос в режиме таблицы и добавить строку итогов. Строка итогов – новое средство в Office Access 2007, позволяющее использовать статистические функции в одном или нескольких столбцах в результатах запроса без необходимости изменять структуру запроса;
Таблица 2.1 – Статистические функции Access
| Функция | Описание | Обрабатываемые типы данных |
| Average | Вычисляет для столбца среднее значение. Столбец должен содержать числовые или денежные величины или значения даты или времени. Функция игнорирует пустые значения. | «Числовой», «Денежный», «Дата/время» |
| Count | Подсчитывает количество элементов в столбце. | Все типы данных, кроме сложных наборов скалярных данных |
| Maximum | Возвращает элемент, имеющий наибольшее значение. Для текстовых данных наибольшим будет последнее по алфавиту значение, причем Access не учитывает регистр. Функция игнорирует пустые значения. | «Числовой», «Денежный», «Дата/время» |
| Minimum | Возвращает элемент, имеющий наименьшее значение. Для текстовых данных наименьшим будет первое по алфавиту значение, причем Access не учитывает регистр. Функция игнорирует пустые значения. | «Числовой», «Денежный», «Дата/время» |
| Standard Deviation | Показывает, насколько значения отклоняются от среднего. | «Числовой», «Денежный» |
| Sum | Суммирует элементы в столбце. Работает только с числовыми или денежными данными. | «Числовой», «Денежный» |
| Variance | Вычисляет дисперсию для всех значений в столбце. Эта функция работает только с числовыми и денежными данными. Если таблица содержит менее двух строк, Access возвращает пустое значение. | «Числовой», «Денежный |
· создать итоговый запрос. Итоговый запрос вычисляет промежуточные итоги для групп записей, строка итогов – общие итоги для одного или нескольких столбцов (полей) данных. Например, при необходимости вычисления промежуточных сумм по всем месторождениям, следует использовать итоговый запрос для группировки записей по каждому из них, а затем просуммировать все;
· создать перекрестный запрос. Перекрестный запрос – это особый тип запросов, отображающий результаты в сетке, по виду схожей с листом Microsoft Office Excel 2007. Перекрестные запросы суммируют значения и затем группируют их по двум наборам фактических данных – один набор вдоль боковой стороны (заголовки строк) и второй набор вдоль верхней части (заголовки столбцов). Перекрестный запрос не всегда заполняет все поля в наборе результатов, потому что таблицы, используемые в запросе, не всегда содержат значения для всех возможных точек данных. При создании перекрестного запроса обычно включаются данные из нескольких таблиц, и всегда включаются три типа данных: данные, используемые в качестве заголовков строк, данные, используемые в качестве заголовков столбцов и значения, которые необходимо просуммировать, или с которыми необходимо произвести другие вычисления.
Методика и порядок выполнения работы
Создадим формы, отчеты и запросы к таблицам БД Продажи.
Загрузите СУБД Access и откройте БД под именем «Фамилия_Продажи».
1. Задание 1. Создание формы для связанных таблиц. Для этого откройте вкладку Создание, найдите раздел Формы. Формы можно создать в виде обычной формы и с помощью мастера форм.
2.1 Средство "Форма" можно использовать для быстрого создания формы с одним элементом. Форма такого типа одновременно отображает сведения только об одной записи. Для создания формы с одним элементом:
– в области навигации выберите таблицу (например, Товары) с данными, которые требуется добавить в форму;
– на вкладке Создание в группе Формы выберите команду Форма 
. Access создаст форму и отобразит ее в режиме макета. В этом режиме в форму можно вносить изменения, но она продолжает отображать данные. Например, можно изменить размеры текстовых полей, чтобы в них поместились все данные. Чтобы начать работу с формой, переключитесь в режим формы на вкладке Главная в группе Представления выберите пункт Вид, а затем – Режим формы. 
. Если Access обнаруживает таблицу с отношением "один ко многим" к таблице или запросу, использованным при создании формы, он создает для формы подтаблицу на основе связанной таблицы или запроса. При необходимости можно удалить подтаблицу из формы. Для этого нужно переключиться в режим макета, выбрать подтаблицу и нажать клавишу DELETE.
2.2 Для создания формы с помощью мастера на вкладке Создание в группе Формы нажмите кнопку Другие формы, а затем выберите команду Мастер форм 
.. Далее следуйте инструкциям мастера. На первом шаге диалога с мастером Создание форм определите состав полей таблицы данных. Для этого выберите базовую таблицу Данные о производителях, в списке Доступные поля можно выбрать поля, отражаемые в форме. Щелкнув кнопку >>, включите в список Выбранные поля все поля таблицы Данные о производителях. Затем, выбрав таблицу Товары, добавьте два любых поля таблицы Товары, щелкая кнопку >, кроме поля Производитель (это поле дублирует поле Производитель таблицы Данные о производителях) в список Выбранные поля. Щелкните кнопку Далее.
На следующем шаге диалога с мастером выберите вид представления данных, указав в качестве главной таблицы Данные о производителях и включив опцию Подчиненные формы. Щелкнув кнопку Далее, выберите внешний вид подчиненной формы табличный (или в один столбец, ленточный), выберите стиль оформления (стандартный, изящный,деловой и т.п.), например, Международный.
На следующих этапах диалога с мастером Создание форм задайте имя для каждой из связанных форм. Завершите создание форм, щелкнув кнопку Готово.
Для запуска щелкнем по ярлычку главной таблицы Данные о производителях. После этого на экране раскроется окно формы Данные о производителях с подчиненной формой Товары. Используя навигационные клавиши (◄, ► и др.) внизу окна, пролистайте все записи, перейдите на первую и последнюю.
Введите данные (взять произвольные) о двух новых Скважинах на любом месторождении. Если окажется, что размер поля в форме мал для представления данных, то закрыв окно формы, укажите главную форму Данные о производителях и щелкните кнопку Конструктор на панели инструментов. Для изменения размера элементов управления формы наведите указатель на границу выделенного элемента управления и, когда указатель примет форму руки, переместите элементы управления. Закройте режим конструктора, сохранив изменения макета формы.
3. Закройте окно формы и, открыв таблицы Данные о производителях и Товары, просмотрите внесенные записи и убедитесь, что в обеих таблицах внесены связанные записи.
Задание 2. Создание отчётов. В отчет можно включить все поля одной или нескольких таблиц, или выбрать только требуемые. При этом можно использовать Простой отчет или Мастер отчетов. На вкладке Создание в группе Другие щелкните Мастер запросов.
1. Простой отчет - это самый быстрый способ создания отчета, потому что с его помощью отчет формируется сразу же, без запроса дополнительной информации. В отчете будут представлены все записи базовой таблицы или запроса. После этого отчет можно сохранить и изменить в режиме макета или конструктора в соответствии с поставленной задачей. Создайте отчет по одной из таблиц, например Товары. Для этого выберите ее. На вкладке Создание в группе Отчеты щелкните Отчет. 
. Приложение Access создаст отчет и отобразит его в режиме макета. Укажите имя отчета, просмотрите его и сохраните
4.2 Рассмотрим использование Мастера отчетов. Для этого, выбрав в списке объектов Отчеты, щелкните кнопку Создание отчета с помощью мастера. На первом шаге мастера Создание отчетов, выберите таблицу Данные о производителях, включите в отчет поля Производитель и Телефон. Выберите таблицу Товары, включите в отчет поля Наименование товара, Цена, Количество в упаковке. Щелкнув кнопку Далее, выберите в качестве главной таблицы таблицу Данные о производителях. На следующем шаге диалога с мастером Создание отчетов добавьте уровень группировки, выбрав поле Производитель. Щелкните кнопку Далее, выберите сортировку по возрастанию по полю Цена. Щелкнув кнопку Далее, выберите вид макета ступенчатый (или дркгой) и включив опцию настройки ширины полей для размещения их на одной странице. Выберите любой стиль оформления создаваемого отчета. Щелкните кнопку Далее. На заключительном этапе Создания отчета укажите имя Пример отчета 1, щелкните кнопку Готово для завершения создания отчета. Созданный отчет появится в главном окне базы данных (рисунок 1), в разделе Отчеты. Просмотрите полученный отчет, щелкнув по его имени. После просмотра отчета закройте его, щелкнув кнопку Закрыть на панели инструментов.
Задание 3. Создание запросов. Ассеss работает с различными типами данных (числовой, текстовый, денежный и т. д.), чтобы показать, к какому типу он отнес данное условие отбора, Access автоматически заключает строки в кавычки («легкая»), даты – в решетки (#01.05.2008#), числа оставляет, как есть (15), а логические проверяет, чтобы были либо ИСТИНА, либо ЛОЖЬ.
Создадим простой запрос на выборку, который отберет данные о товарах с количеством на складе от 200м до 3500 шт. и ценой менее 50 рублей. Для этого будут использоваться поля таблиц Товары и Данные о производителях. Для создания запросов можно использовать Мастер запросов или Режим конструктора. Рассмотрим обе возможности.
1. Выберите вкладку Создание раздел Другие кнопку Мастер запросов. Аналогично процедуре создания Формы: выберите таблицу Товары для запроса и с помощью клавиши «>» перенесите поля Наименование товара, Производитель, Цена, Количество на складе, Продано в окно Запроса. Затем из таблицы Данные о производителях добавьте поле Юридический адрес. Щелкните Готово. На следующем шаге задайте имя запроса, например Запрос по продажам. Выберите Просмотр отчета. Щелкните Готово и посмотрите на результат отбора по указанным полям. Закройте созданный запрос, его имя появилось в главном окне Базы данных.
Откройте его еще раз, щелкнув по имени, и перейдите на структуру запроса (Вид на Панели инструментов). Рассмотрите структуру Запроса. В строке Сортировка поля Цена выберитесортировку по возрастанию. Посмотрите результат запроса: щелкните на Панели инструментов значок! (Запуск). Вернитесь на структуру запроса и поставьте сортировку по убыванию. Посмотрите результат выполнения Запроса. Измените запрос, добавив в строке Условие отбора поля Количество на складе условие Between 200 and 3500. Запустите Запрос с изменениями на выполнение. Вернитесь на структуру запроса и добавьте условие <50 в поле Цена. Посмотрите результат запроса. Закройте его с сохранением.
2. Создадим Запрос такого же вида, но с помощью Конструктора. Выберите вкладку Создание раздел Другие, щелкните кнопку Новый запрос -создание в режиме конструктора.
Из окна Все таблицы, перетащите таблицы Данные о производителях, а затем – Товары.
Перетаскивая поля (Наименование товара, Цена, Количество на складе, Продано, Производитель) из таблиц Товары и Данные о производителях в бланк запроса, определите поля таблиц для запроса, порядок их размещения. В строке Вывод на экран включите флаг отображения полей. В строке Условие отбора в столбце Производитель введите условие отбора "ОАО «Весна»", в столбце Продано введите условие отбора Between 200 and 3500, в столбце Цена условие <20.
Перейдите в режим таблицы и просмотрите записи БД, отобранные согласно созданному запросу. Закройте окно запроса, сохраните макет запроса под именем Запрос ОАО «Весна».
3. Создадим новый запрос Поиск товаров как запрос с параметром. Для этого с оздайте запрос на выборку и откройте его в режиме конструктора (Конструктор. Представление, в котором отображается структура следующих объектов базы данных: таблицы, запросы, формы, отчеты и макросы. В режиме конструктора можно создавать новые объекты базы данных или изменять структуру существующих.). Затем, из окна Всех таблиц, перетащите таблицы Данные о производителях, затем – Товары. Перетаскивая поля из таблиц Товары и Данные о производителях в бланк запроса, определите поля таблиц для запроса, порядок их размещения. Например, Наименование товара, Количество на складе, Цена, Продано, Производитель, Телефон. В строке Вывод на экран включите флаг отображения полей. В строке Условие отбора в столбце поля Производитель, которое предполагается использовать как параметр, введите в квадратных скобках в ячейку строки Условие отбора текст приглашения [Введите название Производителя], который должен отображаться в диалоговом окне.Сохраните запрос, задав ему имя Запрос по производителю. Для проверки действия запроса откройте его и запустите (или выберите в меню Вид опцию Режим таблицы). В появившемся окне Введите значение параметра введем название производителя, которого надо найти, например, «ОАО «Весна»». Посмотрите на результат Запроса. При запуске запроса с параметрами текст отображается в диалоговом окне без скобок. Закройте Запрос и выполните поиск другого производителя, задав новый параметр. Описанные действия могут использоваться для создания запроса с параметрами из следующих типов запросов: на выборку, перекрестный, на добавление, на создание таблицы, на обновление.
Задание 4. Создание итогового запроса с добавлением новой строки. Строка итогов добавляется в запрос путем открытия запроса в режиме таблицы, добавления новой строки, а затем выбора нужной статистической функции, например Sum, Min, Max, или Avg. Например, создадим запрос по данным из таблиц, выбрав поля: Наименование товара, Цена, Количество на складе, Продано. И вычислим общее количество всех товаров на складе. Для этого:
· На вкладке Создание в группе Другие щелкните Конструктор запросов и выберите указанные поля. Выполните запрос, нажав кнопку Выполнить 
. Результаты запроса будут отображены в режиме таблицы. При необходимости переключитесь в режим конструктора и скорректируйте запрос. Сохраните запрос.
· Откройте запрос в режиме таблицы. Для этого щелкните правой кнопкой вкладку документа запроса и выберите Режим таблицы. На вкладке Главная в группе Записи выберите команду Итоги. В таблице появится новая строка Итог.
· В строке Итог щелкните ячейку в поле, по которому необходимо провести суммирование (Количество на складе), и выберите в списке функцию Sum. Выполните запрос и сохраните его.
· Выполняя аналогичные действия, создайте новый запрос с вычислением среднего значения Продано и наибольшего значения Цены. Чтобы очистить итоговое значение для столбца, щелкните строку Итог в этом столбце, а затем выберите из раскрывающегося списка значение Нет.
5. Создание запроса с вычислением. Как правило, в запрос добавляется только поле группы и поле значения. Однако можно использовать вычисление вместо поля значения. Поля, на которые ссылается выражение должны иметь типы данных «Числовой» или «Денежный». Если выражение ссылается на поля с другими типами данных, то при попытке переключения в режим таблицы будет отображено сообщение об ошибке: «Несоответствие типов данных в выражении условия отбора».
Для этого:
· Создайте запрос с помощью Конструктора запросов, в ыбрав из таблицы Товары поля Наименование товара, Цена и Продано. Просмотрите результат запроса и обязательно сохраните его, чтобы была возможность выполнять вычисления в дальнейшем.
· В новом столбце вычислим Общую сумму выручки по каждому товару, как произведение значений столбцов Цена и Продано. Для этого:
· Перейдите в режим Конструктора для созданного запроса. Щелкните в пустом столбце бланка запроса в строке Поле и для ввода выражения вызовите Построитель в группе Настройка запроса. На экране появится диалоговое окно построителя выражений (рисунок 2.1).
Рисунок 2.1 – Окно Построителя выражений
· В первом окне откройте созданный запрос, нажав на окошко с плюсом возле его имени. В центральном окне появится перечень полей из запроса. Для выбора значения поля, дважды щелкните мышью по его названию. В окно построителя введите выражение: [Цена]*[Продано, шт]. Выполните полученный запрос. Перейдите в режим Конструктора запросов, в последнем столбце запроса вместо поля будет написано:
Выражение1: [Цена]*[Продано, шт].
Замените «Выражение1» на «Общая сумма выручки» (двоеточие и квадратные скобки надо сохранить). Выполните повторно запрос и сохраните его.
· Выполните объединение нескольких полей с комментариями. Добавьте новое поле Производитель из таблицы Данные о производителях в запрос. (Щелкните правой клавишей выше области бланка запроса и выберите команду Добавить таблицу). В строку Вывод на экран бланка Запроса уберите галку и он не будет выводиться на экран. В пустой столбец добавьте выражение с помощью Построителя:
Выражение2:[Наименование товара] & " – производитель " & [Производитель]. Посмотрите результат запроса и сохраните его под новым именем.
· В пустой столбец введите выражение: Выражение3: Date()-[Товары]![Срок реализации]. Таким образом, можно вычислить разность в днях между текущей датой и датой окончания срока реализации товара. Функцию Date(), определяющую текущую дату можно найти в подразделе Встроенные функции раздела Функции Построителя выражений. Посмотрите результат выполнения запроса.
