Запросы на выборку не изменяют содержимое базы данных, служат только для отображения данных, отвечающих заданным условиям. Запросы на выборку могут быть следующих видов:
- простой запрос на выборку;
- запрос с параметром;
- запрос с итогами;
- запрос перекрестный;
- запрос с вычисляемым полем.
Простой запрос на выборку предназначен для извлечения данных из одной или нескольких таблиц и отображения их в режиме таблицы.
Бланк простого запроса содержит шесть строк:
- имя поля;
- имя таблицы;
- сортировка;
- вывод на экран (указывает, будет ли поле присутствовать в динамическом наборе данных);
- условие отбора (содержит первое условие, ограничивающее набор данных);
- или (содержит другие условия ограничения данных).
- Разработка простого запроса выполняется в несколько этапов:
- выбор таблицы;
- выбор полей (добавление полей в запрос);
- установление критериев отбора;
- задание порядка расположения записей (сортировка).
Перекрестный запрос вычисляет сумму, среднее значение, число элементов и значения других статистических функций, группируя данные и выводя их в компактном виде, напоминающем сводную электронную таблицу.
Перекрестный запрос создается с помощью соответствующего мастера или в конструкторе запросов. В бланке запроса указывается, значения каких полей будут использоваться в вычислениях или в качестве заголовков строк и столбцов.
Перекрестный запрос – это специальный тип группового запроса. Строка Групповая операция обязательно должна быть включена. В запросе обязательно должны быть установлены как минимум три параметра – поле заголовка строк, поле заголовка столбцов и поле для выбора значений. Поля, используемые в качестве строк и столбцов, должны содержать функцию Группировка в строке Групповая операция. Для создания запроса необходимо выполнить следующие действия:
- создать новый запрос для таблицы (таблиц), включив в макет нужные поля;
- выполнить команду ЗАПРОС/Перекрестный;
- в строке Перекрестная таблица указать, какое поле используется в качестве заголовков строк, какое – в качестве заголовков столбцов и какое - для выполнения вычислений в соответствии с выбранной групповой операцией;
- в строке Групповая операция поля значений необходимо выбрать итоговую функцию.
Запрос с параметром - это запрос, при выполнении которого в диалоговом окне пользователю выдается приглашение ввести данные, например условие для возвращения записей или значение, которое должно содержаться в поле. Можно создать запрос, в результате которого выводится приглашение на ввод нескольких данных, например, двух дат. В результате будут возвращены все записи, находящиеся между указанными двумя датами.
Запросы с параметрами удобно использовать в качестве основы для создания форм и отчетов. Например, на основе запроса с параметрами можно создать месячный отчет о доходах. При выводе данного отчета, на экране появится приглашение ввести месяц, доходы которого интересуют пользователя. После ввода месяца на экране будет представлен требуемый отчет.
Чтобы создать запрос с параметром, необходимо в строку Условия отбора для заданного поля ввести текст приглашения для ввода данного, заключив его в прямоугольные скобки. Можно задать параметры для нескольких полей или для одного поля определить несколько параметров для отбора, используя запись условия в несколько строк совместно с логической операцией «ИЛИ».
Если в запрос вводится несколько параметров, то порядок их ввода через диалоговое окно определяется порядком расположения полей с параметром в бланке запроса.
Запрос с итогами позволяют производить выборку данных одновременно с их группировкой и вычислением групповых итогов с использованием различных статистических функций. В Access предусмотрено 9 статистических функций:
Sum - сумма значений некоторого поля для группы;
Avg - среднее значение некоторого поля для группы;
Max, Min - максимальное или минимальное значение поля для группы;
Count - число значений поля в группе (пустые значения поля не учитываются);
StDev - среднеквадратическое отклонение от среднего;
Var - дисперсия значений поля в группе;
First, Last - значение поля из первой или последней записи.
Для выполнения групповой операции необходимо в режиме конструктора включить в таблицу описания запроса строку Групповая операция и указать в том поле, то поле, по которому должна быть группировка, а в тех полях, по которым должно вестись вычисление заменить слово "группировка" на требуемую статистическую функцию.
Результат запроса с использованием групповых операций содержит по одной записи (строке) для каждой группы, исходные строки групп отсутствуют.
В запросах с итогами можно выполнять следующие операции:
1. Групповые операции над всеми записями. Для этого в полях указываются итоговые функции. Результат запроса представляет собой одну запись, заголовки столбцов соответствуют названию функции и имени поля.
2. Групповые операции для некоторых групп записей. Функция Группировка указывается для полей, по которым будут группироваться данные. В полях, по которым будут выполняться вычисления, должны быть выбраны соответствующие вычислениям функции.
3. Группировку записей, которые соответствуют условию отбора. Для этого необходимо указать условие отбора для поля, где выбрана функция Группировка.
4. Вывод только тех результатов, которые удовлетворяют условию отбора. Для этого условие отбора задается для тех полей, по которым в строке Групповая операция выбрана итоговая функция (например, вывести те группы, средний балл студентов которых больше 4,5).
Запрос с вычисляемым полем позволяет произвести вычисления на основе данных, имеющихся в таблицах. В такой запрос вводятся новые дополнительные поля, содержащие формулы вычислений, причем эти поля создаются только в самом запросе, а не в таблицах базы данных.
Значения новых вычисляемых полей определяются в соответствии с выражениями над числовыми, строковыми значениями или над значениями дат, которые выполняются для всех записей или для записей, удовлетворяющих условиям отбора.
Для ввода выражения следует создать новое вычисляемое поле непосредственно в бланке запроса.
Наряду с выводом результатов вычислений на экран допускается использование вычисляемых полей:
- для определения условий отбора записей в запросе или для определения записей, над которыми производятся действия;
- для обновления данных в запросе на обновление.
Вычисляемые поля позволяют:
- рассчитывать числовые значения и даты.
- комбинировать значения в текстовых полях.
- создавать подчиненные запросы.
- рассчитывать значения итоговых полей с помощью групповых функций.
Для вычисляемых полей допускается сортировка, задание условий отбора и расчет итоговых значений, как и для любых других полей. Для расчета итоговых значений следует выбрать в ячейке строки Групповая операция элемент Выражение.
Выражение – это набор элементов, объединенных операторами. Выражения могут задавать условия отбора в запросах. При этом Access вычисляет выражение при каждом его использовании, т.е. при каждом выполнении отбора.
Выражения могут содержать следующие элементы: константы, литералы, функции, имена объектов.
Константы – это величины, которые не могут быть изменены: Yes, No, Null, False, True.
Литералы – это фактические значения, которые водятся в виде чисел, текстовых строк или дат. Они используются в том виде, как их ввели. Числовой литерал – это обычное число; текстовый литерал – это последовательность символов, заключенных в кавычки; литералы даты и времени – это значения, заключенные в # ... #.
Ввод данных в вычисляемые поля или их изменение в режимах таблицы или формы невозможен. При изменении значений полей, используемых в выражении, значение вычисляемого поля обновляется автоматически.
Формула, помещаемая в вычисляемое поле, всегда начинается со знака равенства (=).
Правила создания вычисляемого поля
1. В режиме конструктора запроса щелкнуть по пустой ячейке в строке Поле. Затем для ввода выражения следует либо щелкнуть по кнопке инструментальной панели Построить, либо выбрать одноименную команду в контекстном меню, щелкнув по пустому полю правой клавишей.
2. В окне построителя выражений необходимо создать выражение путем последовательного добавления в него имен полей таблиц и запросов, функций, констант и операторов, которые выбираются из соответствующих списков.
3. После создания в окне построителя выражения щелкнуть по кнопке OK и вернуться в окно конструктора запроса. В поле появится выражение. По умолчанию после нажатия клавиши ENTER или перемещения в другую ячейку полю будет автоматически присвоено имя ВыражениеN, где N является целым числом, возрастающим на единицу для каждого нового поля, содержащего выражение, в запросе.
Пример:
Выражение1:[Специальность]![Стоимость обучения]*0.2
Пользователь имеет возможность переименовать вычисляемое поле, выделив имя этого поля и введя новое имя, например, заменить имя Выражение1. на имя НДС.
4. При делении денежного значения на любое число возвращается числовое значение со значением свойства Размер поля "С плавающей точкой (8 байт)". Для того чтобы возвращалось значение типа "Денежный", следует указать этот тип в свойстве запроса Формат поля.
5. При вводе выражения в окно свойств, в поле бланка запроса Access выполняет автоматическую вставку определенных символов при изменении положения фокуса. В зависимости от типа поля, в которое вводится выражение, автоматически добавляются:
- прямые скобки ([ ]) вокруг имен форм, отчетов, полей и элементов управления;
- символы (#) вокруг дат;
- прямые кавычки вокруг (" ") строковых значений.
Имена объектов
Имена объектов в формульном выражении - это идентификаторы таблиц, форм, отчетов, элементов управления, полей. При вводе имени объекта в Access его нужно заключать в квадратные скобки [ ]. Если в имени объекта нет пробелов и знаков пунктуации, то скобки можно не вводить, однако их вставит сам Access.
При указании имени поля конкретного объекта используются операторы идентификации.
! – оператор идентификации используется для разделения ссылок на объекты. Например, для обращения к полю Дата рождения в форме СТУДЕНТ следует написать:
FORMS![СТУДЕНТ]![ДАТА РОЖДЕНИЯ].
Вывод на экран задает ссылку на значение поля элемента управления или свойства.
Элементы выражений
Элементами выражения могут быть операторы, имена полей, функции, литералы и константы.
Оператор =, <>, <, <=, >, >=, &, And, Or, Not, Like, Between, In определяет операцию, выполняемую над одним или несколькими элементами, и используются главным образом в условиях отбора записей.
Использование стандартных функций в выражениях
Функция – это ранее определенная последовательность действий (программа). В Access можно использовать функции двух видов: стандартные, входящие в состав Access или VBA (140 функций) и пользовательские, которые пользователь пишет сам на языке VBA. Все функции сгруппированы по категориям:
1. Функции даты и времени. Предназначены для управления значениями типа Дата и Время.
Date() – возвращает текущее системное время и дату;
Day() – возвращает целое число от 1 до 31 – день месяца;
Month() – месяц от 1 до 12 значения даты;
Weekday() – возвращает день недели (целое число, воскресенье соответствует 1);
Year() – возвращает год (целое число).
2. Функции преобразования типов данных. Позволяют назначить наиболее подходящий тип данных:
str() – возвращает число в виде строки;
Val() – возвращает число из строки;
Format() – возвращает строку в формате, определенном пользователем.
3. Математические и тригонометрические функции. Выполняют вычисления над числовыми значениями.
Abs() – возвращает абсолютное значение числа;
Sqr() –вычисляет квадратный корень числа;
Fix() – возвращает целую часть числа;
Int() – возвращает первое целое число, меньшее аргумента.
4. Текстовые (строковые) функции. Выполняют операции над текстовыми значениями.
Asc() – возвращает числовой код символа;
Chr() – возвращает символ по числовому коду;
Instr() – возвращает номер позиции знака в тексте;
Left() – возвращает указанное число знаков текста слева;
Right() – возвращает указанное число знаков справа;
Mid() – возвращает указанное число знаков, начиная с указанной позиции.
5. Финансовые функции. Эти функции аналогичны имеющимся финансовым функциям в Excel.
6. Статистические функции. Выполняют групповые операции над набором данных, который содержится в поле формы, отчета или запроса.
7. Логические функции
IIF(условие, значение_если_истина, значение_если_ложь)