Запросы. Запрос - это требование на получение определенной информации из БД

Запрос - это требование на получение определенной информации из БД. Действия, необходимые для извлечения нужных сведений из БД, составляют содержание обработки запросов. В MS Access для создания запросов используется язык QBE (Query-By-Example – язык запросов по образцу), который в свою очередь основан на языке SQL и на применении визуальных шаблонов при формировании команд SQL.

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

Существуют различные типы запросов:

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

· Запросы с обобщением предусматривают выполнение вычислений над данными, например, суммы, среднего значения для некоторой группы записей.

· Запросы с параметрами позволяют ввести в диалоговом окне один или несколько конкретных значений параметров запроса.

· Перекрестные запросы предназначены для выполнения статистических вычислений в больших объемах данных и представления их в формате электронной таблицы.

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

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

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

Простые запросы в MS Access могут быть созданы с помощью Мастера запросов (кнопки Запросы, Создать окна базы данных, опция Простой запрос диалогового окна Новый запрос). При запуске программа Мастера задает пользователю несколько вопросов о содержании и стиле создаваемого запроса и далее генерирует программу и результат запроса. Созданный Мастером запрос может быть сохранен и при необходимости доработан в Конструкторе запросов.

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

На рис.31 показан пример создания запроса выборки с помощью Конструктора запросов. Содержание запроса заключается в выборке из таблицы «Товар» Мониторов, у которых страна производитель-Россия и стоимость не превышает 10 000 руб. При создании запроса в его бланк методом drag and drop из таблиц переносятся нужные поля, а в строку Условия отбора вносятся нужные ограничения.

Рис.31 Окно режима запроса на выборку.

В случае сложного отбора, соответствующие операции AND, записываются на одной строке, а операции OR – на следующей строке. На рис.32.а демонстрируются результаты запроса, а на рис.32.б – результаты самого запроса.

На рис.33 приведен запрос сгенерированный на языке SQL, который доступен для редактирования. Доступ к окну с инструкцией SQL -запроса может быть осуществлен кнопкой Вид панели инструментов Конструктор запросов, команда Режим SQL. Здесь хорошо видна структура SQL -запроса: из таблицы Товар вывести атрибут Тип и отфильтровать по атрибутам [Дата производства] из таблицы Товар с условием: ([Дата производства] 01.08.2010) < AND ([Дата производства] < 01.11.2010) Or ([Дата производства] < 01.04.2010).

а. б.

Рис.32 Окно режима запроса на выборку для диапазона параметров

Рис.33 Окно запроса на выборку для диапазона параметров на языке SQL

сгенерированный СУБД MS Access

Рис.34 Окно построения выражения для запроса на выборку

Для задания операторов сравнения в строке Условия отбора Конструктора следует использовать кнопку Построить панели инструментов Конструктора запросов (рис.34). Здесь условия отбора те же, что и для рис.33.

При построении запроса на выборку с параметром воспользуйтесь правилом ввода в ячейку строки Условия отбора текста приглашения, заключенного в квадратные скобки. Например, для поля, в котором отображается текущее количество товаров на складе “ < [Остаток товара на складе]”. Здесь в кавычках условие и в скобках название условия, которое затем при вызове запроса требуется ввести, например некое число. В результате получим следующий вид запроса рис.35.

Рис.35 Окно построения запроса на выборку с параметром

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

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

Between [Введите начальную дату:] And [Введите конечную дату:].

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

Например, следующее выражение выполняет поиск слов, начинающихся с указанной буквы:

LIKE [Введите первую букву выражения: ] & "*".

Следующее выражение выполняет поиск слов, содержащих указанный знак:

LIKE "*" & [Введите искомый символ: ] & "*".

При построении запроса с обобщением необходимо выбрать групповую операцию (клик правой кнопкой мыши на строке Условие отбора) в столбцах требующих обобщения, отбора одинаковых свойств по столбцам (рис.36 а).

Если надо подсчитать число одноименных позиций используют групповую операцию Count.

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

а. б.

в. г.

Рис.36 Окно построения запроса с группировкой и обобщением

На рис.36 г дан результат выполнения запроса: проведена группировка по типам товара, подсчитано количество заказанного товара каждого типа и его общая стоимость.

Для создания перекрестного запроса целесообразно использовать режим Мастера. Последовательность выполнения запроса дана на рис.37. При необходимости использования режима Конструктор следуйте указаниям справочной системы MS Access.

Рис.37 Окно построения перекрестного запроса

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

Для построения запроса на Удаление, Добавление или Обновление постройте в Конструкторе сначала обычный запрос, а затем выберите Тип запроса в соответствии с рис.38.

Рис.38 Окно построения запроса на изменение и его SQL-вид

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

Более подробные сведения о создании запросов можно получить в рекомендуемой литературе. С примерами запросов можно ознакомиться также в БД «Борей».

Отчеты

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

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

Для создания отчетов в MS Access обычно используются следующие средства:

· Автоотчет - позволяет построить простой отчет по данным таблицы или запроса в виде столбца или ленты;

· Мастер отчетов - предназначен для построения отчетов в режиме ответов на вопросы Мастер отчетов, созданный отчет может быть доработан в Конструктор отчетов;

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

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

Создание отчета начинается с кнопки Создать в разделе Отчеты, выборе способа создания отчета и таблиц, например, Автоотчет (рис.39).

Рис.38 Окно построения автоотчета

Это простейший вид отчета, в нем отсутствуют группировки обобщения и подсчеты в соответствии с возможной задачей представления информации. Автоотчет кратко можно назвать информационно-итоговым. Ниже приведен сам бланк Автоотчета (рис.39).

Рис.39 Бланк Автоотчета

Рис.40 Создание отчета в режиме Мастера

Рис.41 Отчет, созданный в мастере при открытии его в режиме Конструктора

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

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

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

Рис.42 Внешний вид странички отчета, созданного в режиме Мастера

Здесь имеют место следующие полосы:

Название полос	Назначение полос
заголовок отчета	· служит для внесения названия отчета;
верхний колонтитул	· служит для внесения названия выводимых полей;
заголовок группы «Сотрудник»	· выводятся данные о сотруднике (фамилия);
заголовок группы «Код заказа»	· выводятся данные о ряде группируемых полей: название заказчика, код заказчика, код заказа;
область данных	· выводятся данные о типе товара и его количестве;
примечание группы «Код заказа»	· подсчитывается число записей по данному заказу и общее количество товара, проданного по данному заказу;
примечание группы «Сотрудник»	· подсчитывается число записей у данного сотрудника (фактически число заказов) и общее количество товара, проданного данным сотрудником;
нижний колонтитул	· выводятся сведения о номере странице и их числа, дате и др. дополнительной информации;
примечание отчета	· выводятся сведения об общем количество проданного товара всеми сотрудниками.

В режиме Конструктора отчета окно, как мы уже говорили, состоит из нескольких полос, которые можно добавлять по необходимости (область данных, верхний и нижний колонтитулы, заголовок отчета, примечания отчета, заголовок и примечания группы). При первичном открытии вновь создаваемого отчета окно состоит обычно только из одной области – Области данных (рис.43). При выборе позиций Колонтитулы и Заголовок/примечание отчета (рис.43) появляются соответствующие полосы.

Для ввода областей группировки в шаблон отчета служит кнопка Сортировка и группировка панели Конструктора Отчетов при вызове ее нажатием правой кнопкой мыши (рис.43). После её активизации в столбце Поле/выражение следует выбрать имя поля для группировки. Кроме того, следует установить опции Заголовка группы, Примечания группы для включения в состав каждой группы промежуточного итога (подитога) (рис.44).

На рис.47 показан вид окна Конструктора при создании отчета «Расчет объема закупок заказчиков» по данным запроса на основе всех таблиц БД с полями название заказчика, код заказа, дата заказа, тип, количество, цена. В отчете кроме вывода значений полей предполагается группировка по полю «название заказчика», подведение промежуточных итогов – суммы заказов каждого заказчика и подсчет итогов – общей суммы заказов всех заказчиков.

Для введения поля таблицы/запроса или вычисляемого поля в нужную полосу отчета следует нажать кнопку Поле (TextBox) в Панели элементов (рис.46 с права) Конструктора и разместить поле в нужное место формы. При этом в полосе, например, область данных появится два окна: Поле и Свободный. Окно Поле служит для введения названия, а Свободный для отображения данных. Окно Поле целесообразно скопировать и поместить в соответствующий колонтитул или заголовок группы (в нашем примере в колонтитул), а из поля данных удалить (рис.47).

Рис.43 Первый этап создания отчета в режиме Конструктора

Рис.44 Второй этап создания отчета в режиме Конструктора

Для связывания окна Свободный с объектом вначале необходимо активизировать источник записей. Для этого необходимо при нахождении курсора на конструкторе форм нажать правую кнопку мыши и выбрать поле Свойства. В появившемся окне в поле список выбрать объект Отчет и зайти во вкладку Данные. Во вкладке Данные появится перечень настроек, где верхней строкой будет позиция Источник записей. Здесь необходимо выбрать источник записей (рис.45): соответствующую таблицу или запрос.

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

Далее после активизации окна Свободный нажатием правой кнопки мыши и выбора поля Свойства, появится окно данного поля во вкладке Данные. Здесь в строке данные из предложенного списка, определенного выбранным источником записей, надо выбрать объект, с которым надо связать данное поле (рис.46).

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

Рис.45 Третий этап создания отчета в режиме Конструктора

Рис.46 Присвоение имен полям данных отчета

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

В примере на рис.47 в полосе Примечание группы и Примечание отчета помещены из Панели элементов объекты Поле, которые с помощью Построителя выражений связаны с выражением, вычисляемыми полями:

=Sum([Заказано]![Количество])

и =Sum ([Товар]![Цена]*[Заказано]![Количество]).

Кроме того, в отчет введены Заголовок и функция Date() для вывода даты выполнения отчета.

Для добавления элементов управления в макет отчета Access предоставляет панель элементов. Элементы управления можно присоединить к определенному полю используемой таблицы или запроса. Свойства каждого элемента управления можно изменить.

Рис.47 Структура отчета, созданного в режиме Конструктора

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

Другие примеры отчетов имеются в БД «Борей». Сведения о создании и работе с отчетами содержатся в Справочной системе Access.

Рис.48 Внешний вид странички отчета, созданного в режиме Конструктора