2015-05-13
2623
Запрос – это объект БД, являющийся основным инструментом выборки, обновления и обработки данных в таблицах. Запрос выбирает информацию из таблиц базы данных и создает на их основе временную (хранящуюся в оперативной памяти) результирующую таблицу, которую еще называют моментальным снимком [3]. Результирующая таблица – это определенный «образ» отобранных полей и записей, причем работа с таким «образом» происходит значительно быстрее и эффективнее, чем с исходными таблицами. Запрос позволяет выбрать необходимые данные из одной или нескольких взаимосвязанных таблиц, произвести вычисления и получить результат в виде виртуальной таблицы, которая в свою очередь тоже может использоваться в качестве источника данных в других запросах, формах, отчетах. Помимо запросов на выборку данных СУБД Microsoft Access 2007 позволяет создавать запросы на создание новой таблицы или на модификацию записей в таблицах.
Отчет – это объект БД, предназначенный для вывода данных в виде печатного документа. В связи с этим режимы подготовки отчетов характеризуются наличием команд для группировки выводимых данных, вычисления итоговых значений, а также наличием специальных элементов оформления (заголовков, колонтитулов, примечаний и т. п.), характерных для печатных документов.
|
|
|
Существенным преимуществом запросов и отчетов является то, что они, созданные и сохраненные один раз, могут применяться многократно, отражая при этом текущее состояние базы данных.
Запросы и отчеты бывают однотабличными (в качестве источника данных выступает только одна таблица) и многотабличными, они могут создаваться с помощью мастера или в режиме конструктора, причем для создания запросов наиболее удобным инструментальным средством является графический конструктор, а для отчетов – мастер [4].
Рассмотрим формирование различных запросов и отчетов на конкретных примерах. В качестве источника информации используем БД Продажа канцтоваров, созданную при выполнении лабораторной работы 1 [2]. (Полная версия указанной базы данных для проведения с ней экспериментов предоставляется студентам преподавателем).
2.2. Создание запроса в режиме конструктора
2.2.1. Однотабличный запрос
2.2.1.1. Запрос на выборку
Запрос на выборку предназначен для отбора данных, хранящихся в таблице, без их изменения. Цель такого запроса состоит в получении результирующей таблицы, в которой отображаются только нужные по условию запроса записи исходной таблицы. В общем случае для создания однотабличного
запроса на выборку данных в режиме конструктора необходимо выполнить следующие действия.
|
|
|
1) На ленте активизировать вкладку Создание и в группе Другие нажать пиктограмму 
(Конструктор запросов). В окне Добавление таблицы на вкладке Таблицы в списке доступных объектов двойным щелчком выбрать таблицу, на основе которой составляется запрос (например, Товары), и нажать кнопку <Закрыть>. В режиме конструктора запросов экран Microsoft Access 2007 имеет вид, представленный на рис. 1.
Если необходимо удалить таблицу из схемы данных запроса, то нужно щелкнуть правой кнопкой мыши на заголовке окна этой таблицы и в раскрывшемся контекстном меню выбрать пункт Удалить таблицу.
2) Заполнить бланк запроса. При этом в столбцах бланка в строке Поле нужно указать поля таблицы, с которыми в запросе предполагается выполнение каких-либо действий (вывод поля в результирующую таблицу, анализ его значений и др.). Выбор поля можно осуществлять из раскрывающегося списка непосредственно в строке Поле или двойным щелчком на имени в списке полей таблицы в схеме данных запроса (в этом случае имя поля автоматически помещается в ближайший свободный столбец), или перетаскиванием из списка полей в нужный столбец бланка. Выбрав первую строку списка полей, обозначенную звездочкой, можно одновременно включить в запрос все поля таблицы. Удалить в бланке запроса ненужные столбцы или вставить пустые столбцы в требуемом месте можно с помощью соответствующих пиктограмм в группе Настройка запроса на ленте инструментов конструктора.
| Все множество полей таблицы |
| Схема данных запроса |
| Переход в режим конструктора |
| Бланк запроса |
Рис. 1. Вид экрана Microsoft Access 2007 в режиме конструктора запросов
Если в результирующей таблице нужно представить записи в порядке возрастания или убывания значений какого-либо поля, то следует задать для этого поля соответствующий признак в строке Сортировка.
Если поле должно отображаться в результирующей таблице, то нужно установить для него флажок в строке Вывод на экран (при выборе поля из списка полей таблицы этот флажок включается автоматически). Когда поле включают в запрос лишь для выполнения с ним каких-либо действий, например, для сортировки или отбора данных, но в отображении значений этого поля нет необходимости, флажок Вывод на экран снимают.
В строке Условие отбора задается критерий отбора записей в зависимости от значений указанного поля. Во многих случаях при вводе условия отбора или других параметров запроса целесообразно применить Построитель выражений (рис. 2), окно которого открывается щелчком на пиктограмме 
в группе Настройка запроса на ленте или в контекстном меню соответствующего столбца бланка. Окно построителя выражений содержит полный набор кнопок для ввода арифметических и логических операций, а также списки всех объектов базы данных, в том числе списки таблиц и их полей. В условии отбора, формируемом с помощью построителя выражений, во многих случаях автоматически появляется слово «Выражение», которое обычно следует удалить.
Рис. 2. Вид окна построителя выражений
Для изменения ширины столбца в бланке запроса нужно установить указатель мыши на его правую границу в верхней узкой строке серого цвета. Когда указатель примет вид двунаправленной стрелки, следует выполнить двойной щелчок левой кнопкой мыши (для автоматической настройки по максимальной ширине содержимого столбца) или протянуть указатель мыши до нужного положения.
3) Запустить запрос на исполнение щелчком на пиктограмме в группе Результаты на ленте инструментов. Если созданный запрос предполагается применять неоднократно, то при закрытии окна рекомендуется запрос сохранить, присвоив ему информативное имя, характеризующее содержимое результирующей таблицы, например: Товары из Японии. Запуск сохраненного запроса осуществляется двойным щелчком на его значке в области переходов открытой базы данных.
|
|
|
Рассмотрим примеры создания различных запросов на выборку данных из таблицы Товары, выполняя каждый раз последовательность действий, указанную выше. Все созданные запросы сохраним в базе данных.
Пример 1. Выведите записи о товарах, произведенных в России, и отсортируйте их по наименованию товаров в алфавитном порядке.
Вид бланка запроса для реализации данного задания приведен на рис. 3.
Помимо основного поля Товар и поля Страна, для которого задано условие отбора записей, в запрос для полноты представления информации включены и некоторые другие поля таблицы Товары. Следует отметить, что текстовые фрагменты в условиях отбора записываются в кавычках.
Рис. 3. Вид бланка запроса для примера 1
Запустите запрос на исполнение и убедитесь в правильности его работы, т. е. проверьте, соответствуют ли отображаемые записи условию задания. Затем в столбце Страна снимите флажок Вывод на экран, еще раз запустите запрос на исполнение и оцените, чем отличается полученный результат от предыдущего.
Пример 2. Выведите записи о любых ручках, цена которых составляет не менее 25 и не более 35 р., в порядке убывания цены.
Вид бланка запроса для решения этого задания представлен на рис. 4. В условии отбора в столбце Товар имеется ключевое слово Like, с помощью которого можно осуществлять поиск записей, содержащих в поле Товар текст, указанный в кавычках после слова Like. При записи текста можно применять звездочку (*), означающую любое количество любых символов, и вопросительный знак (?), означающий один любой символ. В данном примере запись Like "Ручка*" означает, что будут отображены все записи, в которых значение поля Товар начинается со слова «Ручка».
Для реализации комбинированного критерия поиска записей со стоимостью товара в указанном диапазоне в столбце Цена в строке Условие отбора применена логическая операция And, означающая «и». Ввод любого условия отбора можно выполнять непосредственно в ячейке бланка или с помощью построителя выражений. Создайте указанный запрос, запустите его на исполнение и проверьте результат работы.
|
|
|
Рис. 4. Вид бланка запроса для примера 2
Пример 3. Выведите записи о любых карандашах, произведенных в России или Чехии. Упорядочите записи по названию страны и фирмы-производителя.
Вид бланка запроса для решения этого задания приведен на рис. 5. При этом в условии отбора по полю Страна применена логическая операция Оr, означающая «или», а в условии отбора по полю Товар – ключевое слово Like (аналогично предыдущему примеру). Запустите запрос на исполнение и убедитесь в правильности его работы.
Рис. 5. Вид бланка запроса для примера 3
Для отработки навыков применения конструктора запросов создайте и сохраните в базе данных запросы на выборку, реализующие следующие задания:
1) представьте записи таблицы Товары, в которых товар измеряется
поштучно, при этом на экран выведите только поля Товар и Цена, упорядочив записи по уменьшению цены;
2) выведите записи таблицы Реестр продаж, отражающие продажи товаров летом 2009 года в количестве более 1000 единиц, упорядочив записи по
дате продажи.
2.2.1.2. Запрос с вычисляемым полем
Во многих случаях в запросе на выборку данных возникает необходимость выполнить ряд вычислений, используя значения имеющихся полей таблицы, и в отдельном столбце вывести полученные результаты. Для этого в запрос вводится специальное поле, содержимое которого является результатом расчета по заданной формуле. Такое поле называется вычисляемым полем. В исходной таблице базы данных вычисляемое поле не создается и его значения не влияют на содержимое этой таблицы.
Для того чтобы создать вычисляемое поле в запросе на выборку данных в режиме конструктора, нужно в свободном столбце бланка запроса в строке Поле ввести расчетную формулу, в состав которой могут входить следующие элементы:
– имена полей таблиц, заключенные в квадратные скобки;
– встроенные функции СУБД;
– знаки арифметических операций;
– числовые константы.
Для удобства просмотра значений вычисляемых полей целесообразно задать формат их вывода, например, денежные суммы обычно представляют с точностью до сотых долей. Выполняется эта процедура в следующем порядке.
1) Установить курсор в ячейку бланка с формулой.
2) На вкладке ленты Конструктор в группе Показать или скрыть нажать пиктограмму 
или выбрать опцию Свойства в контекстном меню.
3) В появившемся Окне свойств на вкладке Общие в строке Формат поля из списка выбрать нужную опцию (обычно выбирают формат С разделителями разрядов или Фиксированный). При необходимости можно увеличить ширину окна свойств, протягивая его левую границу с помощью мыши. Для некоторых форматов в окне свойств после строки Формат поля появляется строка Число десятичных знаков, где можно задать точность округления результата, например, для округления до сотых долей – два знака.
4) Закрыть окно свойств.
Рассмотрим особенности создания запроса с вычисляемым полем на конкретном примере.
Пример 4. Выведите все записи таблицы Товары, дополнительно указав цену каждого товара в долларах при курсе 1$ = 31,26 р. Установите сортировку записей по наименованию товара в алфавитном порядке.
Для решения этого задания сначала аналогично примерам 1 – 3 создайте запрос на выборку данных из таблицы Товары, включив в бланк запроса поля
Товар (по условию задания установите для него сортировку по возрастанию), Страна, Фирма, Ед_изм, Цена.
Для того чтобы вывести в отдельном столбце цену товара в долларах, нужно создать вычисляемое поле, значения которого определяются делением цены товара в рублях на курс доллара. Для этого выполните следующие действия.
1) В бланке запроса в свободном столбце после столбца Цена установите курсор в ячейку в строке Поле.
2) Запустите построитель выражений, в нижней части окна на левой панели откройте таблицу Товары, на средней панели выберите поле Цена (см. рис. 2) и нажмите кнопку <Вставить>. Введите знак деления, а за ним – заданный курс доллара – 31,26 (разделителем между целой и дробной частями числа является запятая). В результате в рабочем окне построителя будет записана расчетная формула в виде: [Товары]![Цена]/31,26. Нажмите кнопку <ОК> – указанная формула будет помещена в текущую ячейку бланка.
3) Щелкните любую другую ячейку бланка. При этом в начале ячейки с формулой появится текст Выражение1, который служит приглашением для ввода имени вычисляемого поля. Увеличьте ширину столбца с формулой (лучше это сделать двойным щелчком, как указано в п. 2.2.1.1) и вместо имени
Выражение1 введите имя вычисляемого поля, которое будет отображаться при выводе таблицы, например: Цена, $.
4) В соответствии с рекомендациями, приведенными выше, задайте формат вывода результатов расчета с разделителями разрядов.
5) Установите для вычисляемого поля флажок в строке Вывод на экран.
Окончательный вид бланка запроса для примера 4 представлен на рис. 6. Запустите запрос на исполнение и проверьте правильность его работы.
Рис. 6. Вид бланка запроса для примера 4
2.2.2. Многотабличный запрос
В тех случаях, когда одной таблицы БД недостаточно для получения исчерпывающей информации, запрос составляют на основе нескольких или всех таблиц БД. Создание многотабличного запроса выполняется в режиме конструктора аналогично однотабличному запросу за исключением того, что на первом этапе работы в окне Добавление таблицы по очереди выбирают все таблицы, поля которых необходимы для решения задачи. При этом в окне конструктора автоматически показываются межтабличные связи, установленные в схеме данных в процессе проектирования БД. Рассмотрим создание многотабличного запроса на примере запроса на выборку данных с вычисляемым полем.
Пример 5. Выведите записи о всех продажах товара в магазине «Степлер» с указанием полученного дохода в порядке его убывания.
Сведения о продажах товара во всех магазинах с указанием его количества представлены в таблице Реестр продаж, поэтому она будет являться основным источником информации при создании запроса. Доход от продажи товара определяется умножением количества проданного товара на стоимость единицы товара. В связи с этим помимо таблицы Реестр продаж для выполнения
задания понадобится поле Цена из таблицы Товары. Кроме того, в таблице
Реестр продаж хранятся лишь коды товара и магазина, для расшифровки которых потребуются соответствующие поля таблиц Товары и Магазины.
Активизируйте конструктор и добавьте в схему данных запроса по очереди таблицы Магазины, Реестр продаж и Товары. Заполните бланк запроса в соответствии с рис. 7. При этом вычисляемое поле Доход создайте аналогично полю Цена, $ в примере 4. При вводе расчетной формулы выбирайте поля Количество и Цена в списках полей соответствующих таблиц в окне построителя выражений. Обратите внимание на то, что нет необходимости включать в запрос коды магазинов и товаров из таблицы Реестр продаж, поскольку можно сразу вывести их наименования из других таблиц благодаря установленным связям.
Запустите запрос на исполнение и проверьте соответствие результата его работы условию задания (рис. 8). Обратите внимание на то, что заданная сортировка по убыванию значений поля Доход позволяет быстро выявить продажи товаров, принесшие наибольший доход.
Рис. 7. Вид окна конструктора запроса для примера 5
Рис. 8. Начальный фрагмент результирующей таблицы для примера 5
2.2.3. Запрос с параметром
В частном случае, проектируя запрос, можно создать из него своеобразную микропрограмму, которая будет осуществлять отбор записей из таблицы в зависимости от вводимого пользователем параметра, т. е. значения некоторого поля. Для этого при создании запроса на выборку данных в бланке запроса в столбце соответствующего поля в строке Условие отбора в квадратных скобках нужно набрать текст, который будет отображаться в диалоговом окне ввода параметра после запуска запроса на исполнение.
Пример 6. Создайте запрос для вывода данных о продажах товара в определенном магазине, название которого вводится пользователем с клавиатуры. В результирующей таблице отсортируйте записи по товарным группам и товарам.
Схема данных и бланк запроса для решения этого задания представлены на рис. 9. Запустите запрос на исполнение, введите в диалоговое окно название магазина Степлер и проверьте полученный результат.
Рис. 9. Вид окна конструктора запроса для примера 6
2.2.4. Запрос с групповыми операциями
Групповые операции позволяют объединить в группы записи с одинаковыми значениями в указанных полях и вычислить для них итоги с помощью встроенных статистических функций, основными из которых являются следующие:
Sum – сумма значений некоторого поля для группы;
Avg – среднее арифметическое всех значений поля в группе;
Max, Min – максимальное и минимальное значения поля в группе;
Count – количество значений поля в группе (без учета пустых значений).
Для создания запроса с применением групповых операций формируется запрос на выборку. В бланк запроса включаются поля, по которым производится группировка, и поля, по которым выполняются статистические вычисления.
Пример 7. Создайте запрос, отражающий статистику продаж по видам товара: вычислите общее, среднее арифметическое и максимальное количество продаж по каждому виду товара.
Процесс решения этого задания вручную очень трудоемок и выполняется в следующем порядке. Сначала необходимо создать вспомогательную таблицу, в которую нужно полностью переписать поля Код (Товар) и Количество из таблицы Реестр продаж; потом добавить в созданную таблицу поле Код группы, в котором в каждой строке проставить код товарной группы, соответствующий коду товара (по данным таблицы Товары); затем в созданную таблицу добавить поле Код вида и заполнить его данными в соответствии с таблицей Товарные группы; после этого упорядочить (отсортировать) записи по коду вида товара и выполнить для каждого кода требуемые статистические вычисления. На завершающем этапе работы по таблице Виды товара согласно кодам определить наименования видов товара и сформировать результирующую таблицу, аналогичную представленной на рис. 10.
Рис. 10. Вид результирующей таблицы для примера 7
На аналогичных принципах основано и построение запроса в Microsoft Access 2007, который позволяет легко выполнить данное задание. В режиме конструктора добавьте в схему данных запроса таблицы Реестр продаж, Товары, Товарные группы и Виды товара. Обратите внимание на то, что поля из таблиц Товары и Товарные группы непосредственно в результирующей таблице не нужны, однако наличие этих таблиц в схеме данных необходимо, поскольку именно через их поля связаны между собой таблицы Реестр продаж и Виды товара и выстраивается логическая цепочка Код товара → Код группы → Код вида → Вид. Так как по условию задания необходима систематизация данных по видам товара, используйте групповые операции: объедините записи таблицы Реестр продаж в группы в соответствии с кодами и наименованиями видов товара в таблице Виды товара и вычислите заданные статистические характеристики по полю Количество. Для этого заполните бланк запроса аналогично бланку, приведенному на рис. 11. Ввод групповых операций для поля в столбце бланка осуществите в следующем порядке: на ленте инструментов конструктора в группе Показать или скрыть щелкните пиктограмму ∑ Итоги или выберите опцию Итоги в контекстном меню, щелкнув правой кнопкой мыши в любом месте данного столбца. В результате в бланке запроса появится строка Групповые операции, в которой из раскрывающегося списка выберите необходимую опцию.
Для вычисляемого поля среднего количества продаж в свойствах поля задайте округление полученного результата до целых единиц (см. п. 2.2.1.2).
После запуска запроса на исполнение на экран выводится результирующая таблица (рис. 12), при необходимости можно изменить заголовки ее полей. Для этого перейдите в режим конструктора запросов, установите курсор в
нужном столбце бланка, откройте окно свойств и на вкладке Общие в строке Подпись введите требуемое название заголовка.
Рис. 11. Вид окна конструктора запроса для примера 7
Рис. 12. Результат запроса с групповыми операциями для примера 7
2.2.5. Создание запроса на основе другого запроса
В качестве источника информации при построении запроса в Microsoft Access могут выступать не только исходные таблицы, но и ранее созданные и сохраненные другие запросы, например, при создании сложного многоэтапного запроса можно сначала сформировать и сохранить один или несколько простых промежуточных запросов и уже на их основе получить результирующий
запрос. В этом случае на первом этапе работы в режиме конструктора в окне Добавление таблицы нужные объекты выбираются на вкладках Запросы или Таблицы и запросы, в остальном процесс создания запроса ничем не отличается от обычного порядка, рассмотренного в приведенных выше примерах.
2.2.6. Создание простого запроса на расчет итоговых значений
с помощью мастера
В Microsoft Access имеется возможность создавать часто встречающиеся на практике типовые запросы с помощью мастера. Этот режим используют и для получения простого запроса на расчет итоговых значений по числовым полям таблиц или других запросов. Рассмотрим процесс формирования такого запроса на конкретных примерах.
Пример 8. Выведите общее количество продаж всех товаров по данным таблицы Реестр продаж.
Для создания простого итогового запроса выполните следующие действия.
1) На ленте активизируйте вкладку Создание и в группе Другие нажмите пиктограмму 
(Мастер запросов). В списке объектов в окне Новый запрос выберите Простой запрос и нажмите кнопку <ОК>. В следующем окне в соответствии с рис. 13 выберите исходные данные для выполнения задания – поле Количество из таблицы Реестр продаж – и нажмите кнопку <Далее>.
Рис. 13. Вид окна выбора исходных данных при создании
простого запроса
В очередном окне в соответствии с рис. 14 выберите вид отчета итоговый и нажмите кнопку <Итоги…>.
Рис. 14. Вид окна выбора итогового отчета
В окне Итоги установите флажок в слоте Sum (см. п. 2.2.4), как показано на рис. 15. При необходимости в правой нижней части этого окна можно включить флажок Подсчет числа записей, обеспечивающий вывод в итоговый отчет количества записей, по которым вычислены итоги. Нажмите кнопку <ОК> (в результате будет выполнен возврат в предыдущее окно, представленное на рис. 14), а затем – кнопку <Далее>.
Рис. 15. Выбор статистических функций
для расчета итогов
В следующем окне задайте имя запроса Общее количество продаж и
нажмите кнопку <Готово>, в результате на экран будет выведено искомое значение.
Пример 9. Выведите максимальную и минимальную цену товара в долларах.
Для выполнения задания произведите действия, аналогичные перечисленным в примере 8. При этом в качестве источника данных укажите запрос, созданный и сохраненный в примере 4, и выберите в нем поле Цена, $.
