Создание запросов и отчетов, в том числе с использованием SQL
Цель работы
Приобретение практических навыков создания БД: запросов и отчетов в среде СУБД MS Access. Закрепление навыков в формировании условий отбора, в том числе через построители выражений.
Необходимые краткие сведения
Запросы
Запрос - это требование на получение определенной информации из БД. Действия, необходимые для извлечения нужных сведений из БД, составляют содержание обработки запросов. В MS Access для создания запросов используется язык QBE (Query-By-Example – язык запросов по образцу), который в свою очередь основан на языке SQL и на применении визуальных шаблонов при формировании команд SQL.
При выполнении действий запроса Access конструирует эквивалентный оператор языка SQL, который можно просмотреть, отредактировать и затем выполнить в новой редакции. Изменения, внесенные в запрос в режиме SQL, отражаются в бланке запроса конструктора.
Существуют различные типы запросов:
· Запросы на выборку содержат набор критериев для выборки необходимых данных из одной или нескольких таблиц.
· Запросы с обобщением предусматривают выполнение вычислений над данными, например, суммы, среднего значения для некоторой группы записей.
· Запросы с параметрамипозволяют ввести в диалоговом окне один или несколько конкретных значений параметров запроса.
· Перекрестные запросы предназначены для выполнения статистических вычислений в больших объемах данных и представления их в формате электронной таблицы.
· Активные запросы (запросы на изменение) служат для внесения изменений во множество записей. Изменения заключаются в удалении, добавлении, обновлении записей таблиц, а также в создании новых таблиц.
· Специальные запросы, которые могут быть созданы и оптимизированы только с помощью инструкций SQL в режиме SQL.
Данные для формирования запросов могут извлекаться из одной или нескольких таблиц. Если в основе запроса лежат несколько таблиц, то их следует связать одна с другой. Существующий запрос можно в дальнейшем использовать в качестве основы при создании нового запроса.
Простые запросы в MS Access могут быть созданы с помощью Мастера запросов (кнопки Запросы, Создать окна базы данных, опция Простой запрос диалогового окна Новый запрос). При запуске программа Мастера задает пользователю несколько вопросов о содержании и стиле создаваемого запроса и далее генерирует программу и результат запроса. Созданный Мастером запрос может быть сохранен и при необходимости доработан в Конструкторе запросов.
Более сложные запросы создаются в режиме Конструктора запросов (команда Конструктор диалогового окна Новый запрос). Окно Конструктора запросов состоит из двух частей: в верхней области отображаются списки полей таблиц и запросов, в нижней определяется макет запроса. Для реализации запроса с параметрами и ввода в диалоговом окне условия отбора используется форма записи в режиме QBE.
На рис.35 показан пример создания запроса выборки с помощью Конструктора запросов. Содержание запроса заключается в выборке из таблицы «Товар» Мониторов, у которых страна производитель-Россия и стоимость не превышает 10 000 руб. При создании запроса в его бланк методом drag and drop из таблиц переносятся нужные поля, а в строку Условия отбора вносятся нужные ограничения.
Рисунок 35 - Окно режима запроса на выборку.
В случае сложного отбора, соответствующие операции AND, записываются на одной строке, а операции OR – на следующей строке. На рис.36.а демонстрируются результаты запроса, а на рис.36.б – результаты самого запроса.
На рис.37 приведен запрос сгенерированный на языке SQL, который доступен для редактирования. Доступ к окну с инструкцией SQL-запроса может быть осуществлен кнопкой Вид панели инструментов Конструктор запросов, команда Режим SQL. Здесь хорошо видна структура SQL-запроса: из таблицы Товар вывести атрибут Тип и отфильтровать по атрибутам [Дата производства] из таблицы Товар с условием: ([Дата производства] 01.08.2010) < AND ([Дата производства] < 01.11.2010) Or ([Дата производства] < 01.04.2010).
а. б.
Рисунок 36 - Запрос на выборку для диапазона параметров
Рисунок 37 - Запроса на выборку для диапазона параметров на языке SQLсгенерированный СУБД MS Access
Рисунок 38 - Окно построения выражения для запроса на выборку
Для задания операторов сравнения в строке Условия отбора Конструктора следует использовать кнопку Построитьпанели инструментов Конструктора запросов (рис.38). Здесь условия отбора те же, что и для рис.37.
При построении запроса на выборку с параметром воспользуйтесь правилом ввода в ячейку строки Условия отборатекста приглашения, заключенного в квадратные скобки. Например, для поля, в котором отображается текущее количество товаров на складе “ < [Остаток товара на складе]”. Здесь в кавычках условие и в скобках название условия, которое затем при вызове запроса требуется ввести, например некое число. В результате получим следующий вид запроса рис.39.
Для сложного, составного, запроса для каждого поля, которое предполагается использовать как параметр, введите в ячейку строки Условие отборавыражение с текстом приглашения, заключенным в квадратные скобки.
Рисунок 39 - Окно построения запроса на выборку с параметром
Например, для поля, в котором отображаются даты, чтобы задать границы диапазона значений, можно ввести приглашения следующего вида:
Between [Введите начальную дату:] And [Введите конечную дату:].Чтобы запросить у пользователя один или несколько знаков для поиска записей, которые начинаются с этих знаков или содержат их, создайте запрос с параметрами, использующий оператор LIKE и подстановочный знак (*).
Например, следующее выражение выполняет поиск слов, начинающихся с указанной буквы:
LIKE [Введите первую букву выражения: ] & "*".Следующее выражение выполняет поиск слов, содержащих указанный знак:
LIKE "*" & [Введите искомый символ: ] & "*".При построении запроса с обобщением необходимо выбрать групповую операцию (клик правой кнопкой мыши на строке Условие отбора)в столбцах требующих обобщения, отбора одинаковых свойств по столбцам (рис.40 а).
Если надо подсчитать число одноименных позиций используют групповую операцию Count.
Для расчета суммы используется групповая операция Выражение(рис.40 б), для которого с помощью построителя выражения и функции Sum,рассчитывается общая сумма (рис.40 в).
а. б.
в. г.
Рисунок 40 - Окно построения запроса с группировкой и обобщением
На рис.40 г дан результат выполнения запроса: проведена группировка по типам товара, подсчитано количество заказанного товара каждого типа и его общая стоимость.
Для создания перекрестного запроса целесообразно использовать режим Мастера. Последовательность выполнения запроса дана на рис.41. При необходимости использования режима Конструктор следуйте указаниям справочной системы MS Access.
Рисунок 41 - Окно построения перекрестного запроса
В Конструктор помимо выбора полей группировки требуется выбрать поле, по которому будет проводится обобщение, используя функцию Значение и вид группировки, например, Count. Для столбца группировки в строке Поле необходимо построить выражение, в соответствии с которым будет проводится группировка.
Для построения запроса на Удаление, Добавление или Обновление постройте в Конструкторесначала обычный запрос, а затем выберите Тип запроса в соответствии с рис.42.
Рисунок 42 - Окно построения запроса на изменение и его SQL-вид
Для простых случаев обработки таблиц вместо создания запроса можно использовать Фильтры, однако запросы являются более гибким и мощным средством получения необходимых данных. Созданные и сохраненные запросы отображаются в разделе Запросы окна базы данных для использования и редактирования.
Более подробные сведения о создании запросов можно получить в рекомендуемой литературе. С примерами запросов можно ознакомиться также в БД «Борей».
Отчеты
Отчет представляет собой форматированное сочетание данных из одной или нескольких таблиц, выводимое на устройство отображения: экран, принтер, в файл. Обычно в основе отчета лежат записи таблиц БД или запросов. Также в отчет могут быть включены схемы и диаграммы, в том числе фотографии и иллюстрации, графические элементы управления, верхние и нижние колонтитулы, содержащие служебную информацию.
При создании отчета желательно заранее представить схему будущего отчета на бумаге. Для этого необходимо хорошо представлять его назначение, структуру, требования к упорядочивание данных и их группировке.
Для создания отчетов в MS Access обычно используются следующие средства:
· Автоотчет - позволяет построить простой отчет по данным таблицы или запроса в виде столбца или ленты;
· Мастер отчетов - предназначен для построения отчетов в режиме ответов на вопросы Мастер отчетов, созданный отчет может быть доработан в Конструктор отчетов;
· Конструктор отчетов – позволяет отредактировать уже имеющийся отчет или создать новый с широкими возможностями размещения и форматирования полей, группировки и сортировки данных, а также встраивания диаграмм, рисунков, пиктограмм и т.п.
Окончательно подготовленный отчет, как правило, выводится на печать. Для предварительного просмотра отчета можно использовать одноименную кнопку панели инструментов. Имя созданного и сохраненного отчета отображается в разделе Отчеты окна базы данных.
Создание отчета начинается с кнопки Создать в разделе Отчеты, выборе способа создания отчета и таблиц, например, Автоотчет (рис.43).
Рисунок 43 - Окно построения автоотчета
Это простейший вид отчета, в нем отсутствуют группировки обобщения и подсчеты в соответствии с возможной задачей представления информации. Автоотчет кратко можно назвать информационно-итоговым. Ниже приведен сам бланк Автоотчета (рис.44).
Рисунок 44 - Бланк Автоотчета
Рисунок 45 - Создание отчета в режиме Мастера
Рисунок 46 - Отчет, созданный в мастере при открытии его в режиме
Конструктора
Рассмотрим создание отчета врежиме Мастера. При выборе режима мастера отчетов последовательность создания отчета, который должен содержать данные заказов типов товара с группировкой заказов по сотрудникам, приведена на рис.47 (последовательно слева на право, сверху вниз). В создаваемом макете предлагается выбрать уровни группировки, итоги для обобщения и сам вид представления данных.
На рис.46 приведен созданный отчет, открытый в режиме Конструктора. В режиме Конструктора отчетов на экране появляются специальные панели инструментов, используемых при разработке отчета. Окно Конструктора отчета состоит из нескольких полос (их создание рассмотрим ниже).
Войдя в Конструктор и выделив выбранное поле, после нажатия правой кнопки мыши можно через поле Свойстваи вкладку Данныевойти в Построитель выражения (рис.40 в) и произвести необходимую корректировку вычисляемых полей. Кроме того, нажав правую кнопку мыши можно ввести уровни группировки, изменить фон заливки, вызвать панель инструментов и т.д.
Рисунок 47 - Внешний вид странички отчета, созданного в режиме Мастера
Здесь имеют место следующие полосы:
| Название полос | Назначение полос |
| заголовок отчета | · служит для внесения названия отчета; |
| верхний колонтитул | · служит для внесения названия выводимых полей; |
| заголовок группы «Сотрудник» | · выводятся данные о сотруднике (фамилия); |
| заголовок группы «Код заказа» | · выводятся данные о ряде группируемых полей: название заказчика, код заказчика, код заказа; |
| область данных | · выводятся данные о типе товара и его количестве; |
| примечание группы «Код заказа» | · подсчитывается число записей по данному заказу и общее количество товара, проданного по данному заказу; |
| примечание группы «Сотрудник» | · подсчитывается число записей у данного сотрудника (фактически число заказов) и общее количество товара, проданного данным сотрудником; |
| нижний колонтитул | · выводятся сведения о номере странице и их числа, дате и др. дополнительной информации; |
| примечание отчета | · выводятся сведения об общем количество проданного товара всеми сотрудниками. |
В режиме Конструктора отчета окно, как мы уже говорили, состоит из нескольких полос, которые можно добавлять по необходимости (область данных, верхний и нижний колонтитулы, заголовок отчета, примечания отчета, заголовок и примечания группы). При первичном открытии вновь создаваемого отчета окно состоит обычно только из одной области – Области данных (рис.48). При выборе позиций Колонтитулыи Заголовок/примечание отчета (рис.48) появляются соответствующие полосы.
Для ввода областей группировки в шаблон отчета служит кнопка Сортировка и группировка панели Конструктора Отчетов при вызове ее нажатием правой кнопкой мыши (рис.48). После её активизации в столбце Поле/выражение следует выбрать имя поля для группировки. Кроме того, следует установить опции Заголовка группы, Примечания группы для включения в состав каждой группы промежуточного итога (подитога) (рис.49).
На рис.52 показан вид окна Конструктора при создании отчета «Расчет объема закупок заказчиков» по данным запроса на основе всех таблиц БД с полями название заказчика, код заказа, дата заказа, тип, количество, цена. В отчете кроме вывода значений полей предполагается группировка по полю «название заказчика», подведение промежуточных итогов – суммы заказов каждого заказчика и подсчет итогов – общей суммы заказов всех заказчиков.
Для введения поля таблицы/запроса или вычисляемого поля в нужную полосу отчета следует нажать кнопку Поле (TextBox) в Панели элементов (рис.51 слева) Конструктора и разместить поле в нужное место формы. При этом в полосе, например, область данных появится два окна: Полеи Свободный. Окно Полеслужит для введения названия, а Свободный для отображения данных. Окно Полецелесообразно скопировать и поместить в соответствующий колонтитул или заголовок группы (в нашем примере в колонтитул), а из поля данных удалить (рис.52).
Рисунок 48 - Первый этап создания отчета в режиме Конструктора
Рисунок 49 - Второй этап создания отчета в режиме Конструктора
Для связывания окна Свободный с объектом вначале необходимо активизировать источник записей. Для этого необходимо при нахождении курсора на конструкторе форм нажать правую кнопку мыши и выбрать поле Свойства. В появившемся окне в поле список выбрать объект Отчети зайти во вкладку Данные. Во вкладке Данныепоявится перечень настроек, где верхней строкой будет позиция Источник записей. Здесь необходимо выбрать источник записей (рис.50): соответствующую таблицу или запрос.
Замечание. В качестве источника записей целесообразно использовать специально созданный запрос (например, Запрос 1, рис.50), в который будут сведены все необходимые для формы поля (атрибуты). В противном случае процесс выбора источника записей придется многократно повторять.
Далее после активизации окна Свободный нажатием правой кнопки мыши и выбора поля Свойства, появится окно данного поля во вкладке Данные. Здесь в строке данные из предложенного списка, определенного выбранным источником записей, надо выбрать объект, с которым надо связать данное поле (рис.51).
Аналогично для вычисляемых полей (рис.52, полоса примечание группы) во вкладке Данные активизируется окно Построителя выражений(рис.40 в) с помощью появившейся справа крайней кнопки-многоточия. В окне Построителявыражений вводится нужное выражение из операторов и операндов - констант, переменных, функций и полей таблиц/запросов.
Рисунок 50 - Третий этап создания отчета в режиме Конструктора
Рисунок 51 - Присвоение имен полям данных отчета
Значения, размещенные в полосе Колонтитул, будут повторяться на каждой странице отчета, в полосе Область данных – в каждой записи, в полосе Заголовка (Примечания) выводятся данные для отдельной группы.
В примере на рис.52 в полосе Примечание группы и Примечание отчета помещены из Панели элементов объекты Поле, которые с помощью Построителя выражений связаны с выражением, вычисляемыми полями:
=Sum([Заказано]![Количество])
и =Sum ([Товар]![Цена]*[Заказано]![Количество]).
Кроме того, в отчет введены Заголовок и функция Date() для вывода даты выполнения отчета.
Для добавления элементов управления в макет отчета Access предоставляет панель элементов. Элементы управления можно присоединить к определенному полю используемой таблицы или запроса. Свойства каждого элемента управления можно изменить.
Рисунок 52 - Структура отчета, созданного в режиме Конструктора
Оформить отчет можно с использованием стандартных средств форматирования, введения рисунков, изменения фона и т.д. (рис.53).
Другие примеры отчетов имеются в БД «Борей». Сведения о создании и работе с отчетами содержатся в Справочной системе Access.
Рисунок 53 - Внешний вид странички отчета, созданного в режиме
Конструктора
Порядок выполнения работы
3.3.1 Откройте БД«Борей» и ознакомьтесь с примерами запросов в этой базе.
3.3.2 Откройте созданную Вами ранее БД. Создайте запрос на выборку из таблицы «Товары», соответствующей Вашей БД (например, мониторы, смартфоны и т.п.). Результатом запроса должна быть таблица, содержащая записи с типами товаров и двумя – тремя основными параметрами, удовлетворяющими установленным критериям для выборки.
Откройте окно SQL и ознакомьтесь с текстом инструкции SQL-запроса. Измените инструкцию SQL-запроса и пронаблюдайте соответствующие изменения в окне Конструктора запросов. Обратите внимание, что текст SQL-запроса может быть скопирован в буфер обмена и затем вставлен в нужную процедуру программы.
3.3.3 Постройте запрос на выборку с параметром на базе таблицы «Заказчики». Запрос должен выводить сведения о телефоне заказчика по параметру – имени заказчика. Просмотрите соответствующий оператор SQL-запроса.
Создайте запрос на выборку с двумя параметрами из таблицы «Товары», соответствующей Вашей БД.
3.3.4 Разработайте запрос с обобщением (группировкой), в котором должен быть предусмотрен вывод кода заказа, название заказчика и сумма стоимости каждого заказа из таблицы «Заказано».
Замечание: для построения запроса следует использовать все таблицы БД, в операции группировки по коду заказа – функцию Sum.
3.3.5 Создайте запрос на удаление записей из таблицы «Заказы». С помощью данного запроса должен быть аннулирован заказ с определенным кодом. В условие отбора включается код (номер) удаляемого заказа. После исполнения данного заказа будет удалена не только соответствующая запись из таблицы «Заказы», но и связанные записи из таблицы «Заказы», если установлены соответствующие правила поддержания целостности БД.
3.3.6 Постройте перекрестный запрос на основе таблицы «Заказы», позволяющий получить сведения о количестве заказов у каждого сотрудника по месяцам года. Указание: следуйте инструкциям Мастера создания перекрестных таблиц. В качестве заголовков строк используйте поле «Сотрудник», заголовков столбцов – поле «Дата исполнения», интервала группировки – месяц и функцию Count для вычисления количества заказов.
3.3.7 По данным таблицы «Товары», соответствующей Вашей БД, создайте Автоотчет с расположением полей в виде столбцов.
3.3.8 С помощью Мастера Отчетов создайте отчет, который должен содержать данные заказов тип товара с группировкой заказов по сотрудникам. В отчете должны быть представлены следующие поля: название заказчика, сотрудники, код заказчика, код заказа, тип товара, количество. Кроме того, в отчете должны быть подведены промежуточные итоги по сумме заказов для каждого сотрудника и общей сумме всех заказов.
3.3.9 В режиме Конструктора Отчетов разработайте отчет с группировкой по названиям заказчиков. Поля вывода для отчета: название заказчика, код заказа, дата заказа, тип товара, количество, цена. В отчете должен быть выведен заголовок, а также промежуточные итоги по сумме заказов заказчиков и итоги по общей сумме заказов. Оформите отчет с использованием средств форматирования.
3.3.10 Оформите отчет по лабораторной работе и защитите её.
Контрольные вопросы
1. Для чего предназначены запросы? Приведите примеры.
2. Охарактеризуйте основные типы запросов в MS Access.
3. Какие средства MS Access используются при создании запросов?
4. Объясните общий формат команды языка SQL.
5. Объясните особенности языка QBE.
6. Как связан шаблон запроса QBE с операторами SQL? Поясните на примере.
7. Выполните запрос выборки для Вашей БД.
8. Как создается запрос с параметрами? Продемонстрируйте пример.
9. Разработайте запрос с группировкой с выводом количества имеющихся заказов в БД для каждого заказчика.
10. Создайте пример запроса на обновление данных.
11. Объясните назначение и создание перекрестного запроса.
12. Каково назначение отчетов в БД?
13. Для чего предназначены колонтитулы отчетов? Какая информация в них размещается?
14. Охарактеризуйте средства создания отчетов в MS Access.
15. Создайте отчет с группировкой по датам исполнения заказов и сортировкой по сотрудникам, отвечающим за заказ.
16. Как ввести в отчет иллюстрацию или рисунок? Покажите на примере.
17. Назовите основные средства форматирования отчетов.
18. По данным таблицы “Заказано” создайте отчет с группировкой по типам товара и сортировкой по количеству (по убыванию).
Литература
1. Голицына О.Л., Максимов Н.В., Попов И.И. Базы данных: Учебное пособие.- М.: Форум: ИНФРА-М, 2004.-352с.
2. Хомоненко А.Д., Гридин В.В. Microsoft Access. Быстрый старт.- СПб.: БХВ -Петербург, 2002.- 304с.
3. Изучаем Access 2000. Мультимедийный курс.–Библиотека Кирилла и Мефодия.-2003.
4. Робинсон С. Access 2000. Учебный курс. – СПб.: Питер, 2000.
ЛАБОРАТОРНАЯ РАБОТА № 4