Извлечение информации при помощи запросов

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

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

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

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

Выбор данных из одной таблицы

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

На рис. 23 показан запрос в режиме конструктора. Окно конструктора запросов разделено на две части. В верхней находятся списки полей таблиц или запросов, на основе которых создается новый запрос. В нижней располагается бланк QBE (Query By Example – запрос по образцу), в котором выполняется вся работа по созданию запроса. Каждый столбец бланка представляет одно поле, используемое в запросе. Поле может просто принадлежать одной из таблиц, быть вычисляемым (иными словами, его значение рассчитывается на основе одного или нескольких полей таблицы), или итоговым, то есть использующим одну из встроенных функций Microsoft Access.

 

Рис. 23. Пример запроса на основе таблицы «Должностные оклады» в режиме конструктора

 

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

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

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

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

Включение полей в запрос

Первым шагом при создании запроса является выбор полей, включаемых в набор записей. Это можно сделать несколькими способами. Чтобы увидетьсписокдоступных полей, надо перейти с помощью клавиши табуляции к нужномустолбцубланка запроса и нажать клавиши Alt+ «стрелка вниз»(для перевода курсора изверхнейчасти окна в бланк запроса, нажмите клавишу F6). Чтобы включить полев бланк запроса, надо переместить выделение к этому полю, пользуясь клавишами «стрелка вниз» или «стрелка вверх» и нажать клавишу Enter.

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

В самом начале списка полей, находящегося в верхней половине окна запроса (а также в раскрывающихся списках в первой строке бланка QBE), находится специальный символ «*», означающий «Все поля». Если необходимо включить в запрос все поля таблицы, не надо определять каждое по отдельности в бланке QBE. Достаточно перетащить «*» из списка полей в бланк QBE. Имеется возможность повторно включить столбец поля таблицы в бланк запроса, чтобы определить условия отбора для них. В этом случае необходимо снять флажки Вывод на экран (такие флажки можно видеть на рис. 23) в столбцах бланка, содержащих повторяющиеся поля, чтобы поля дважды не выводились в наборе записей запроса.

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

Ввод условий отбора

Ввод условия отбора в запросе аналогичен заданию условия на значение для поля таблицы. Когда введено условия отбора для нескольких полей, то все выражения в строке Условие отбора либо в строке или должны принимать значение Истина для любой записи, включаемой в набор записей запроса. Это означает, что Асcess выполняет логическую операцию AND над условиями отбора, находящимися в одной строке. В таблице приведены значения выражения, состоящего из двух условий, связанных оператором AND. Очевидно, чтобы результат операции AND имел значение Истина, оба условия должны быть истинными; только в этом случае запись отбирается запросом.

Когда для некоторого поля задано несколько условий отбора, соединенных логическим оператором OR, то для того, чтобы запись была отобрана, истинным должно быть хотя бы одно из них. Есть два способа задать несколько связанных оператором OR условий для одного поля. Можно ввести все условия в одну ячейку строки Условие отбора, соединив их оператором OR. Другой вариант: ввод каждого условия в отдельную ячейку строки или. При использовании нескольких строк или для отбора записи достаточно выполнения всех условий в одной из строк или.

Таблица 10

Результат применения к двум условиям логических операций AND

Условия отбора Условия отбора	Истина	Ложь
Истина	Истина (Отбирается)	Ложь (Отвергается)
Ложь	Ложь (Отвергается)	Ложь (Отвергается)

 

Таблица 11

Результат применения к двум условиям логических операций OR

Условия отбора Условия отбора	Истина	Ложь
Истина	Истина (Отбирается)	Истина (Отбирается)
Ложь	Истина (Отбирается)	Ложь (Отвергается)

Поясним сказанное на примере еще одного простого запроса, выполненного на основе таблицы «Сведения о сотрудниках». На рис. 24 представлена информация о сотрудниках в режиме таблицы. На рис. 25 и 26 приведены запрос в режиме конструктора и результат его выполнения, соответственно. Мы видим, что по запросу: «Выбрать всех сотрудников, начальные буквы фамилий которых находятся между " Б " и " К ", ИЛИ код должности больше " 05 "» выбрано 12 записей. Если немного изменить этот запрос – вместо ИЛИ поставить И – как показано на рис. 27, то будут выбраны всего две записи, представленные на рис. 28.

 

Рис. 24. Содержимое таблицы «Сведения о сотрудниках»

 

Рис. 25. Запрос на выборку с использованием ИЛИ в режиме конструктора

 

Рис. 26. Результат выполнения запроса на выборку с использованием ИЛИ

 

Рис. 27. Запрос на выборку с использованием И в режиме конструктора

 

Рис. 28. Результат выполнения запроса на выборку с использованием И

 

Вычисляемые поля

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

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

Таблица 12

Операторы, применяемые в арифметических выражениях

Оператор	Выполняемое действие
+	складывает два арифметических выражения
–	вычитает из первого арифметического выражения второе
*	перемножает два арифметических выражения
/	делит первое арифметическое выражение на второе
\	округляет два арифметических выражения до целых значений и делит первое на второе. Результат округляется до целого
^	возводит первое арифметическое выражение в степень, задаваемую вторым арифметическим выражением
MOD	округляет оба арифметических выражения до целых значений, делит первое на второе и возвращает остаток
&	создает текстовую строку как результат присоединения второй строки к концу первой. Если один из операндов является числом, то оно автоматически преобразуется в строку символов

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

Если в выражении используются имена других полей, то они заключаются в квадратные скобки. Например, Стоимость: [ Цена ] * [ Количество ].

В таблице 13 перечислены статистические функции, которые могут использоваться в вычисляемых полях.

Таблица 13

Статистические функции Access

Функция	Результат (возвращаемое значение)	Тип поля
Sum	Сумма значений поля внутри каждой группы	Числовой, Дата/время, Денежный и Счетчик
Avg	Среднее от значений поля внутри каждой группы	Числовой, Дата/время, Денежный и Счетчик
Min	Наименьшее значение поля внутри каждой группы	Текстовый, Числовой, Дата/время, Денежный и Счетчик
Max	Наибольшее значение поля внутри каждой группы	Текстовый, Числовой, Дата/время, Денежный и Счетчик
Count	Число значений поля без учета пустых (Null) значений внутри каждой группы	Текстовый, Числовой, Дата/время, Денежный, Счетчик, Логический и Поле объекта OLE
StDev	Среднеквадратичное отклонение от среднего значения поля	Числовой, Дата/время, Денежный и Счетчик
Var	Дисперсия значений поля	Числовой, Дата/время, Денежный и Счетчик
First	Первое значение поля в группе	Числовой, Дата/время, Денежный и Счетчик
Last	Последнее значение поля в группе	Числовой, Дата/время, Денежный и Счетчик

 

Из всего многообразия встроенных функций Access рассмотрим наиболее часто используемые функции: CCur(), IIf(), IsNull().

Функция CCur () относится к функциям преобразования типа. Данные функции используются, когда требуется выполнить «недопустимые» действия над полями определенного типа, например, сложить значения двух полей, содержащих цифры в текстовом формате. Функции преобразования типа данных применяются, чтобы показать, что результат некоторой операции должен выражаться в определенном типе данных, а не в типе данных, используемом по умолчанию. Функция CCur() используется для принудительного выполнения арифметических действий с денежными единицами в тех случаях, когда обычно используются действия с числами с обычной точностью, двойной точностью или с целыми числами. Это связано с особенностями округления чисел.

Функция IIf () напоминает логический оператор IF. Ее синтаксис

IIf(term; termT; termF),

где term – выражение, которое требуется оценить;

termT – значение или выражение, которое будет возвращено, если term будет оценено как Истина;

termF – значение или выражение, которое будет возвращено, если term будет оценено как Ложь.

Функция IsNull () возвращает значение Истина, если выражение, записанное в скобках, содержит недопустимые данные Null, и Ложь – в противном случае. Ее синтаксис

IsNull (term),

где term – значение типа Variant, содержащее числовое или строковое выражение.

 

Итоговые запросы

Для вычисления итоговых значений надо установить групповые операции. Это можно сделать двумя способами: 1) в тот момент, когда указатель мыши находится внутри бланка QBE, нажать правую кнопку, а затем в появившейся вкладке нажать кнопку Групповые операции ( ), чтобы в бланке QBE появилась строка Групповые операции; 2) в главном меню выбрать Вид→Групповые операции. Access использует установку Группировка в строке Групповая операция для любого поля, внесенного в бланк запроса. Теперь записи по каждому полю группируются, но итог не подводится. При выполнении запроса выдается набор записей, включающий по одной строке для каждого уникального значения поля запроса – но без итогов. Для получения итогов необходимо заменить установку Группировка в строке Групповая операция на конкретную итоговую функцию. Эти функции приведены в таблице 13. Задать требуемую функцию можно, если ввести ее имя с клавиатуры в строке Групповая операция бланка запроса или выбрав ее в раскрывающемся списке.

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

Запрос «Стаж» строится на основе таблицы «Сведения о сотрудниках» - для получения необходимой нам информации надо вычесть из даты приема на работу текущую дату, используя при этом функцию Year(), возвращающую количество лет. Запрос должен включать все поля исходной таблицы плюс вычисляемое поле Stag. На рис. 29 приведен данный запрос в режиме конструктора.

 

Рис. 29. Запрос «Стаж» в режиме конструктора

 

Запрос «Дней болезни» строится на основе таблицы «Больничный лист» и запроса «Стаж». В режиме конструктора нажимаем кнопку Отобразить таблицу ()  на панели инструментов. Открывается окно Добавление таблицы (см. рис. 21). Добавляем требуемые объекты: таблицу «Больничный лист» из списка таблиц и запроса «Стаж» из списка запросов, после чего окно Добавление таблицы закрываем. Другой способ добавления объектов к проектируемому запросу состоит в перетаскивании левой кнопкой мыши требуемых объектов из окна базы данных. В верхнем поле бланка запроса появляются две таблицы, пока не связанных между собой. Связь устанавливается так же, как при составлении схемы данных, – перетаскиванием одноименных полей из одной таблицы в другую. Далее следует установить вид объединения, поскольку по умолчанию действует «один-к-одному», в то время как возможна ситуация, когда один сотрудник может за рассматриваемый период времени иметь более одного больничного листа. Для выбора вида объединения устанавливаем указатель мыши на линию связи и щелкаем. Затем выбираем параметры объединения, как показано на рис. 30. Появляется окно Параметры объединения, представленное на рис. 31. Выбираем вид связи «2».

 

Рис. 30. Изменение связи в запросе «Дней болезни»

 

Рис. 31. Выбор параметра объединения для запроса «Дней болезни»

 

Окно Параметры объединения можно также вызвать из главного меню, выбрав Вид→Параметры объединения.

Запрос «Дней болезни включает поля: Tab_N, FIO, K_Dolg, Stag, D_Beg, D_End и вычисляемое поле Dn_Bol. На рис. 32 данный запрос приведен в режиме конструктора.

 

Рис. 32. Запрос «Дней болезни» в режиме конструктора

 

Запрос «Сумма дней болезни» строится на основе запроса «Дней болезни» с использованием групповых операций. Открываем запрос «Дней болезни» в режиме конструктора. В строке Сортировка бланка запроса устанавливаем сортировку табельных номеров по возрастанию. Для этого в поле Tab_N наводим указатель мыши на строку Сортировка, после щелчка левой кнопкой появляется треугольник, нажимая на который выбираем способ сортировки, как показано на рис. 33. Затем устанавливаем Групповые операции и для поля Dn_Bol (дней болезни) выбираем функцию Sum. Выбор функции Sum осуществляется точно так же, как и выбор режима сортировки.

Запрос «Оплата» использует данные запроса «Сумма дней болезни» (в бланк запроса включаются поля: Tab_N, FIO, K_Dolg, Sum_Dn_Bol) и таблиц: «Должностные оклады» (включаются все поля) и «Процент оплаты Б/Л» (включается только поле Proc). Первым добавляется запрос, затем таблицы. Как и в предыдущем случае создаем связи и устанавливаем для каждой связи параметр объединения «2». В бланк запроса включаем вычисляемое поле Opl. Запрос в режиме конструктора приведен на рис. 34.

 

Рис. 33. Выбор режима сортировки

 

Рис. 34. Запрос «Оплата» в режиме конструктора

 

Пусть таблица «Больничные листы» содержит данные, приведенные на рис. 35. Тогда при выполнении запроса «Оплата» будут выведены записи, представленные на рис. 36.

Рис. 35. Содержимое таблицы «Больничные листы»

 

Рис. 36. Информация, выводимая при выполнении запроса «Оплата»