Запросы

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

· фильтрация и сортировка выполняется быстрее, чем запросы;

· фильтры и условия сортировки хранятся временно, а запросы можно сохранять на диске и использовать в дальнейшем;

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

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

Рис. 17. Создание запроса в режиме конструктора

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

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

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

Можно также запустить Мастер, щелкнув на команде Запросы в меню Вставка или щелкнув по кнопке Новый объект, а затем щелкнув дважды на Мастер простых запросов.

Существует много различных видов запросов, рассмотрим наиболее часто используемые.

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

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

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

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

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

В строке Условие отборазаписывается тот критерий, по которому выбираются записи для включения их в результирующую таблицу. По каждому полю можно создавать свое условие отбора. Выражения в Access представляют собой сочетания операторов, функций и свойств, определяющих конечный результат. Собственно, это часть формулы, расположенная справа от знака равенства. Выражения используются при определении свойств таблиц и форм, для вычисления значений в формах или отчетах, при построении запросов и во многих других ситуациях. Используя выражения можно создавать сложные критерии, которые включают несколько условий отбора, объединенных логическими, арифметическими операторами и операторами сравнения.

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

Рис. 19. Формирование запроса на выборку

с помощью бланка запроса по образцу

Рис. 20 Результирующая таблица выполнения запроса на выборку

Параметрический запросзапрашивает ввод параметров (например, фамилию автора) – задается в строке Условие отбора. Этот запрос можно получить из запроса на выборку и этот тип запросов часто используется для получения отчетов за определенный период времени.

Рис. 21. Формирование запроса с параметром на базе запроса на выборку

Рис. 22. Окно параметрического запроса и результата его выполнения

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

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

Итоговые запросы создают на основе бланка запроса на выборку, только теперь в нем появляется дополнительная строка — Группировка. Для введения этой строки в бланк надо щелкнуть на кнопке Групповые операции на панели инструментов СУБД Ассеss . В тех полях, по которым производится группировка, надо установить (или оставить) функцию Группировка. В тех полях, по которым следует провести итоговое вычисление, надо в строке Группировка раскрыть список и выбрать одну из нескольких итоговых функций Access. В строке Группировка можно указать лишь одну итоговую функцию, но если надо найти сумму, среднее, максимальное значение, и еще что-то, то одно и то же поле нужно включить в бланк запроса по образцу несколько раз.

Рис. 23. Формирование вычисляемого поля с помощью итоговых функций

Значения итоговых функций Access:

Avg - возвращает среднее значение данных, содержащихся в поле

Count - возвращает число записей, содержащих данные в этом поле

First - возвращает содержимое поля для первой записи

Last - возвращает содержимое поля для последней записи

Min - наименьшее значение в поле

Max - наибольшее значение в поле

StDev - возвращает среднеквадратичное отклонение значений, содержащихся в поле, от среднего

Sum - возвращает сумму значений данных, содержащихся в поле

Var - возвращает дисперсию значений в поле

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

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

Стоимость: [Цена] * [Количество]

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

В узкий столбец непросто записать длинную формулу, но если нажать комбинацию клавиш Shift+F2, то открывается вспомогательное диалоговое окно, которое называется Область ввода. В нем можно ввести сколь угодно длинную формулу, а потом щелчком на кнопке ОК перенести ее в бланк запроса по образцу. Если включить отображение вычисляемого поля, результаты расчетов будут выдаваться в результирующей таблице.

Запрос на действиевносит множественные изменения за одну операцию. Собственно, это запрос на выборку, который выполняет определенные действия над результатами отбора. Возможные действия: обновление, удаление и добавление записей, создание новой таблицы. В двух последних случаях результаты запроса добавляются в существующую таблицу или для них создается новая таблица. Используются такие запросы в основном разработчиками и администраторами базы данных.

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

Формы

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

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

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

Готовую форму можно использовать не только для ввода, но и для просмотра и изменения существующих записей.

Мастер форм создает форму в 4 этапа:

· выбор полей, данные для которых можно будет вводить в форме;

· выбор внешнего вида формы;

· выбор фонового рисунка формы;

· задание имени формы.

На каждом этапе Мастер подробно запрашивает и объясняет все действия. Созданные мастером формы являются функциональными и не более того. Однако их можно легко доработать в соответствии со своими нуждами – добавить или удалить надписи, переместить элементы управления, вставить логотипы и рисунки, то есть сделать форму более привлекательной внешне и удобной в использовании.

Рис. 28. Создание формы с помощью мастера

Конструкториспользуется в основном не для создания формы, а для просмотра и редактирования формы. Именно в режиме Конструктора происходит создание новых или изменение имеющихся элементов управления, их взаимного расположения, оформление заголовка, вставка внешних объектов по технологии OLE (например, фотографий, видеозаписей, музыкальных фрагментов).

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

Заголовок таблицы в созданной форме не связан ни с одним из полей таблицы. Поэтому элемент управления Надпись еще называют свободным полем. Текст, введенный в него, остается неизменным независимо от того, какую запись в этот момент просматривают в форме.

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

Кроме рассмотренных выше элементов управления Надпись и Поле, в Access существует еще несколько полезных элементов управления:

Переключатели. С ними можно связать команды, например выполняющие фильтрацию.

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

Список. Может содержать фиксированный набор значений или значения из заданного поля одной из таблиц. Позволяет не вводить данные, а выбирать их из списка.

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

Командные кнопки. С каждой из них можно связать какую-либо полезную команду, например команду поиска записи, перехода между записями и другие.

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

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

Свойства формы не привязаны к исходной таблице. Изменение свойств таблицы не влияет на соответствующие свойства формы, и наоборот.

Рис. 29. Окно создания формы в режиме конструктора с панелью элементов

Рис. 30. Окно редактирования формы в режиме Конструктора

Рис. 31. Окно готовой формы для просмотра и ввода данных

СУБД Access позволяет распечатать формы на принтере так, как они выглядят на экране. Но если вы щелкнете на кнопке Печать на панели инструментов, то все записи в форме будут распечатаны по одной - например, 100 записей займут 100 страниц. Печатью форм стоит пользоваться в тех случаях, когда нужно срочно распечатать только одну запись. Для печати записи нужно сначала выбрать запись, затем выбрать команду Файл - Печать и Выделенные записи,после щелчка на кнопке ОК запись распечатается на бумаге.

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

Отчеты

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

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

Большая часть того, что было сказано о формах, относится и к отчетам. Выбрав в диалоговом окне базы данных вкладку Отчеты и щелкнув на кнопке Создать, мы получаем диалоговое окно Новый отчет, позволяющее создать отчет автоматически (Автоотчет), с помощью Мастера или вручную с помощью Конструктора.

Рис. 32. Окно создания отчета

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

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

Как и формы, отчеты состоят из разделов, а разделы могут содержать элементы управления. Но, в отличие от форм, разделов в отчетах больше, а элементов управления, наоборот, меньше. Со структурой отчета проще всего ознакомиться, создав какой-либо Автоотчет, а затем открыв его в режиме Конструктора. Структураотчета состоит из пяти разделов: заголовка отчета, верхнего колонтитула, области данных, нижнего колонтитула и примечания. По сравнению с формами новыми являются разделы верхнего и нижнего колонтитулов.

Раздел заголовка служит для печати общего заголовка отчета.

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

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

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

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

Рис. 33. Автоотчет в столбец

Рис. 34. Автоотчет в ленточной форме

Чтобы создать отчет с помощью Мастеранужно выбрать команду Создать отчет с использованием мастера или выбрать Отчет, щелкнув на кнопке Новый объект, и затем выбрать пункт Мастер отчетов.

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

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

Чтобы создать отчет вручную с помощью Конструктора необходимо самостоятельно создать структуру отчета и оформить все ее разделы. Отчеты в основном создают автоматически (Автоотчет) или полуавтоматически
(с помощью Мастера), а потом доводят его до желаемого вида с помощью Конструктора. Работа с заголовками, элементами управления, колонтитулами по добавлению, форматированию, оформлению аналогична работе с формами и рассмотрена подробно ранее.

Рис. 35 Структура отчета в режиме Конструктора