В начало

Access SQL

Вычисления

Для получения, обработки и редактирования информации в Access предусмотрена возможность построения выражений. Выражения могут быть как простыми, например, при сравнении, так и сложными, когда для создания выражения требуется использование специальных инструментов (см. раздел «Выражения: Построитель выражений»).

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

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

Выражения: Обработка пустых записей

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

(См. также раздел «Условие отбора: Пустое поле» главы «Запросы и фильтры»).

Шаг за шагом

1. Откройте нужный запрос, форму или отчет в режиме конструктора.
2. В форме и отчете выделите элемент управления, содержащий выражение, нажмите кнопку Свойства (Properties) и на вкладке Данные (Data) открывшегося окна диалога свойств перейдите к свойству Данные (Control Source). В запросе перейдите к столбцу таблицы конструктора, в строке Поле (Field) которого находится нужное выражение.
3. Измените выражение:

• Если выражение предназначено для определения числового значения, нужно воспользоваться функцией NZ. Функция NZ позволяет преобразовать Null в 0 или любое другое число. Например, если поле [ИмяПоля] пустое и входит в операцию сложения или вычитания, его нужно заменить выражением NZ[ИмяПоля]), что в результате дает 0, а если в операцию умножения — выражением NZ([ИмяПоля],1), что в результате дает 1.
• Если выражение предназначено для обработки текстовых данных, вместо оператора сложения (+), позволяющего объединять текстовые поля, для тех же целей нужно использовать оператор конкатенации (&). Например, [Поле1]&" "&[Поле2].

4. Нажмите кнопку Сохранить (Save) на панели инструментов Access.

СОВЕТ

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

[Имя] & " " & IIf (IsNull([Отчество]),"", [Отчество] & " ") & [Фамилия].

ПРИМЕЧАНИЕ

Функции, применяемые в групповых операциях (например, Sum, Count, Avg), игнорируют значения Null, поэтому использование описанных выше приемов для них не требуется.

Выражение: Объединение текстовых строк

Для объединения текстовых данных используется оператор сложения (+) или оператор конкатенации (&). Например, [Имя] & " " + [Фамилия].

СОВЕТ

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

Шаг за шагом

1. Откройте нужный запрос, форму или отчет в режиме конструктора.
2. В форме и отчете выделите элемент управления, в котором будет содержаться нужное выражение, нажмите кнопку Свойства (Properties) и в открывшемся окне диалога свойств выделенного элемента управления на вкладке Данные (Data) в поле свойства Данные (ControlSource) введите знак равенства (=).
3. В таблице конструктора запроса установите курсор в строке Поле (Field) столбца того поля, в котором будет содержаться нужное выражение, введите имя нового поля и двоеточие (:).
4. Введите имя поля источника данных и заключите его в квадратные скобки.
5. Введите оператор сложения (+) или оператор конкатенации (&).
6. Введите пробел в кавычках (" ").
7. Повторите шаги 2-5 требуемое число раз.
8. Нажмите кнопку Сохранить (Save) на панели инструментов Access.

Выражения: Операторы

К операторам в Access относятся арифметические операторы — сложение, вычитание, умножение, деление (+, -, *, /), операторы сравнения — больше, меньше, равно (>,<, =), логические операторы — отрицание, логическое И, логическое ИЛИ (NOT, OR, AND) и оператор конкатенации (&) использующийся для объединения текстовых строк.

Основные места, в которых используются операторы это поля свойств различных объектов в режиме конструктора: Значение по умолчанию (Default Value) и Условие на значение (Validation Rule) в таблице, Поле (Field), Условие отбора (Criteria) в запросе, Данные (Control Source) в отчете и форме.

Шаг за шагом

1. Перейдите в поле, в котором требуется построить выражение.
2. Нажмите кнопку Построить (Build) на панели инструментов Access или кнопку (...) в поле нужного свойства, чтобы запустить построитель выражений (см. раздел «Выражения: Построитель выражений»).
3. Чтобы вставить в выражение требуемый оператор, используйте кнопки под полем построения, а если искомого оператора там нет, в первом нижнем поле щелкните два раза на папке Операторы (Operators), во втором поле выберите раздел, содержащий нужный оператор, в третьем поле выберите сам оператор и нажмите кнопку Вставить (Paste).

Выражения: Построитель выражений

Построитель выражений (Expression Builder) — это инструмент Access, с помощью которого можно создавать выражения любой сложности для разных типов данных: чисел, текстов, логических значений, даты, времени и т. д.

Кнопку Построить (Build), которая приводит к выводу на экран окна диалога Построитель выражений (Express Builder), можно найти на панели инструментов Access и полях некоторых свойств, в которых эта кнопка выглядит как троеточие (...) и становится доступной после щелчка в соответствующем поле.

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

Набор кнопок под большим полем построителя позволяет вводить в выражение операторы (см. раздел «Выражения: Операторы»).

ПРИМЕЧАНИЕ

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

Шаг за шагом

1. Нажмите кнопку Построить (Build).
2. Постройте выражение, используя описанные ниже приемы:

• Чтобы вставить в выражение элемент управления, в первом нижнем поле щелкните два раза на имени папки с нужным объектом и выберите объект, а во втором поле выберите нужный элемент управления этого объекта и нажмите кнопку Вставить (Paste).
• Чтобы вставить в выражение требуемый оператор, используйте кнопки под полем построения, а если искомого оператора там нет, в первом нижнем поле щелкните на папке Операторы (Operators), во втором поле выберите раздел, содержащий нужный оператор, в третьем поле выберите сам оператор и нажмите кнопку Вставить (Paste).
• Чтобы вставить в выражение константу, в первом нижнем поле щелкните на папке Константы (Constants), в третьем поле выберите константу и нажмите кнопку Вставить (Paste).
• Чтобы вставить в выражение функцию, в первом нижнем поле щелкните два раза на папке Функции (Functions), во втором поле выберите раздел, содержащий нужную функцию, в третьем поле выберите функцию и нажмите кнопку Вставить (Paste).

3. Построив и отредактировав выражение, нажмите кнопку ОК.

СОВЕТ

Если вы не помните раздела, содержащего нужную функцию или оператор, раскройте раздел Все (All).

ПРИМЕЧАНИЕ

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

ПРИМЕЧАНИЕ

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

Выражения: Процент

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

ВНИМАНИЕ

В выражениях допускается использование только десятичного эквивалента процентов. Например, для увеличения стоимости на 15% нужно ввести выражение [Стоимость]*1.15.

Шаг за шагом

1. Выберите объект (форму или отчет), для одного из полей которого нужно рассчитать долю в процентах значения каждой записи от общей суммы значений этого поля.
2. Нажмите кнопку Конструктор (Design) на панели инструментов окна базы данных. Откроется окно конструктора объекта.
3. Создайте свободный элемент управления Поле (Text Box) (см. разделы «Элемент управления: Свободный» и «Элемент управления: Поле» главы «Формы и отчеты»).
4. Нажмите кнопку Свойства (Properties) на панели инструментов Access.
5. На вкладке Данные (Data) открывшегося окна диалога в поле свойства Данные (ControlSource) введите выражение = [ИмяПоля]/Sum([ИмяПоля]), где ИмяПоля — имя поля источника данных, для которого нужно рассчитать долю в процентах значения каждой записи от общей суммы значений этого поля.
6. На вкладке Макет (Format) в раскрывающемся списке свойства Формат поля (Format) выберите пункт Процентный (Percent).
7. На вкладке Макет (Format) в раскрывающемся списке свойства Число десятичных знаков (Decimal Places) выберите нужное число знаков после запятой или оставьте значение Авто (Auto).
8. Нажмите кнопку Сохранить (Save) на панели инструментов Access.

Выражения: Редактирование

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

Основные места, где используются выражения, — это поля свойств различных объектов в режиме конструктора: Значение по умолчанию (Default Value) и Условие на значение (Validation Rule) в таблице, Поле (Field), Условие отбора (Criteria) в запросе, Данные (ControlSource) в отчете и форме.

Шаг за шагом

1. Откройте объект (запрос, таблицу, форму или отчет) в режиме конструктора.
2. Поместите курсор в ячейку с нужным выражением.
3. Нажмите клавиши Shift+F2.
4. В открывшемся окне диалога Область ввода (Zoom) произведите необходимые изменения и нажмите кнопку ОК.
5. Нажмите кнопку Сохранить (Save) на панели инструментов Access.

Выражения: Ссылка на поле

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

Шаг за шагом

1. Выберите нужное выражение.
2. Переместите курсор в то место выражения, где требуется ссылка.
3. Введите имя поля в квадратных скобках, например [ПервоеПоле].

ВНИМАНИЕ

Если вы забыли заключить имя поля в квадратные скобки, Access сделает это автоматически. Однако, если в именах полей имеются пробелы, автоматической расстановки скобок не произойдет.

Выражения: Ссылка на элемент управления

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

При ссылке на элемент управления текущего объекта достаточно задать имя элемента управления, которое заключается в квадратные скобки. При ссылке на элемент управления другого объекта помимо имени элемента управления требуется задать тип и имя объекта. Имена и тип объекта, а также имя элемента управления отделяются друг от друга восклицательным знаком (!). Например, Forms![Именa и адреса]![Сумма долгов].

ВНИМАНИЕ

Для получения доступа к элементу управления в процессе выполнения приложения содержащий его объект должен быть открыт.

ВНИМАНИЕ

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

Шаг за шагом

1. Выберите нужное выражение.
2. Переместите курсор в то место выражения, где требуется ссылка.
3. Введите тип объекта (Forms для форм или Reports для отчетов) и восклицательный знак (!).
4. Введите имя объекта (формы или отчета) в квадратных скобках и восклицательный знак (!).
5. Введите имя элемента управления в квадратных скобках.

Выражения: Суммирование с накоплением

Суммирование с накоплением позволяет вставлять в отчет не значение определенной записи поля, а сумму этой и всех предыдущих записей.

Шаг за шагом

1. Нажмите кнопку Отчеты (Reports) на левой панели окна базы данных.
2. Выберите нужный отчет.
3. Нажмите кнопку Конструктор (Design) на панели инструментов окна базы данных. Откроется окно конструктора отчета.
4. Нажмите кнопку Список полей (Field List) на панели инструментов Access, чтобы вывести на экран список полей источника данных.
5. Перетащите поле, для которого нужно подсчитать сумму с накоплением, в нужную область. Это может быть область данных, а также заголовок или примечание группы.
6. Нажмите кнопку Свойства (Properties) на панели инструментов Access. Откроется окно диалога свойств выделенного элемента управления.
7. На вкладке Данные (Data) в раскрывающемся списке свойства Сумма с накоплением (Running Sum) выберите пункт Для группы (Over Group), чтобы накапливать сумму значений поля только в пределах группы, или пункт Для всего (Over All), чтобы накапливать сумму значений поля во всем отчете.
8. Нажмите кнопку Сохранить (Save) на панели инструментов Access.

Выражения: Тестирование

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

Шаг за шагом

1. Введите выражение в нужное поле.
2. Щелкните за пределами поля. При наличии синтаксических ошибок в выражении Access выдаст соответствующее предупреждение и предложит его исправить. Наиболее частые ошибки: пропущена одна из круглых скобок, определяющих приоритет выполнения операций, имя поля не заключено в квадратные скобки, пропущен оператор.
3. Проверьте имена и синтаксис введенных функций.
4. При использовании в выражении других полей введите в эти поля тестовые значения и убедитесь в правильности результата.

Вычисляемое поле: В запросе

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

Шаг за шагом

1. Нажмите кнопку Запросы (Queries) на левой панели окна базы данных.
2. Выберите нужный запрос.
3. Нажмите кнопку Конструктор (Design) на панели инструментов окна базы данных. Откроется окно конструктора запроса.
4. В строке Поле (Field) свободного столбца таблицы конструктора введите имя создаваемого поля, знак двоеточия (:) и выражение. Если выражение строится с использованием других полей, их имена должны быть заключены в квадратные скобки.
5. Нажмите кнопку Сохранить (Save) на панели инструментов Access.

ПРИМЕЧАНИЕ

Если на шаге 4 не вводить имена вычисляемых полей, Access самостоятельно назначит им имена Выражение1 (Expr1), Выражение2 (Expr2) т. д.

ПРИМЕЧАНИЕ

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

Вычисляемое поле: В форме и отчете

Для удобства представления информации в формах и отчетах очень часто возникает необходимость изменить исходные значения или вычислить на их основе какую-либо функцию.

(См. также раздел «Элемент управления: Вычисляемый» главы «Формы и отчеты».)

Шаг за шагом

1. Нажмите кнопку Формы (Forms) или Отчеты (Reports) на левой панели окна базы данных.
2. Выберите нужный объект (форму или отчет).
3. Нажмите кнопку Конструктор (Design) на панели инструментов окна базы данных. Откроется окно конструктора формы или отчета.
4. Создайте свободный элемент управления Поле (Text Box) (см. разделы «Элемент управления: Свободный» и «Элемент управления: Поле» главы «Формы и отчеты»).
5. Нажмите кнопку Свойства (Properties) на панели инструментов Access.
6. На вкладке Данные (Data) открывшегося окна диалога в поле свойства Данные (Control Source) введите знак равенства (=) и нужное выражение. Если выражение строится с использованием других полей, то их имена должны быть заключены в квадратные скобки ([]). Аргументы функций должны быть заключены в круглые скобки.
7. Нажмите кнопку Сохранить (Save) на панели инструментов Access.

ПРИМЕЧАНИЕ

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

ПРИМЕЧАНИЕ

Если какой-либо элемент управления имеет свойство Данные (Control Source), то он допускает возможность построения выражения в поле этого свойства.

Групповые операции: В запросе

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

• Функции StDev и Var предназначены для оценки статистического распределения значений. Данные функции применимы только к числовым типам данных и к типу данных Денежный (Currency).
• Функция Count позволяет определить количество записей в группе. Функция применяется ко всем типам данных.
• Функции Мах и Min позволяют определить минимальное и максимальное значения записи в поле. Эти функции применимы к числовым и текстовым типам данных, а также к типу данных Дата/время (Date/Time).
• Функции First и Last предназначены для получения первой и последней записи в группе и применимы ко всем типам данных.
• Функция Avg вычисляет среднее значение поля.
• Функция Sum складывает все значения поля.

(См также раздел «Групповые операции: В форме и отчете».)

Шаг за шагом

1. Нажмите кнопку Запросы (Queries) на левой панели окна базы данных.
2. Выберите нужный запрос.
3. Нажмите кнопку Конструктор (Design) на панели инструментов окна базы данных.
4. Нажмите кнопку Групповые операции (Totals) на панели инструментов Access.
5. Оставьте в таблице конструктора только те столбцы, к полям которых нужно применить групповую операцию, а остальные столбцы удалите, в противном случае записи соответствующих полей будут сгруппированы (см. раздел «Групповые операции: Группировка в запросе»).
6. В строке Условие отбора (Criteria) таблицы конструктора введите при необходимости условие, на основе которого будут отобраны соответствующие записи.
7. В раскрывающихся списках строки Групповые операции (Totals) каждого поля выберите нужную операцию.
8. Нажмите кнопку Запуск (Run) на панели инструментов Access.
9. Нажмите кнопку Сохранить (Save) на панели инструментов Access.

ПРИМЕЧАНИЕ

Результаты вычислений, выполненных в запросах, не записываются в файл базы данных, а определяются при выполнении запроса. Чтобы сохранить результат выполнения групповой операции, требуется создать специальную таблицу (см. раздел «Запрос на изменение: Создание таблицы» главы «Запросы и фильтры»).

Групповые операции: В форме и отчете

Групповыми операциями называются действия над всеми записями или группой записей поля. К групповым операциям относятся функции оценки статистических распределений, определение среднего и количества элементов в списке, поиск минимума и максимума и т. д. (См. также раздел «Групповые операции: В запросе»).

В форме, в отличие от запроса или отчета, возможность группировки отсутствует, поэтому групповые операции могут выполняться только над всеми записями поля. Для выполнения групповой операции над группой записей надо использовать запрос (см. раздел «Групповые операции: Группировка в запросе») или отчет (см. раздел «Групповые операции: Группировка в отчете»).

Шаг за шагом

1. Нажмите кнопку Формы (Forms) или Отчеты (Reports) на левой панели окна базы данных.
2. Выберите форму или отчет.
3. Нажмите кнопку Конструктор (Design) на панели инструментов окна базы данных. Форма или отчет откроется в режиме конструктора.
4. Выделите нужное поле. Оно должно находиться либо в области заголовка или примечания (для форм и отчетов), либо в области заголовка или примечания группы (только для отчетов).
5. Нажмите кнопку Свойства (Properties) на панели инструментов. Откроется окно диалога свойств выделенного элемента управления.
6. Перейдите на вкладку Данные (Data) и щелкните в поле свойства Данные (ControlSource).
7. Введите знак равенства (=), функцию групповой операции и имя поля. Например, =Sum([ИмяПоля]).
8. Нажмите кнопку Сохранить (Save) на панели инструментов Access.

СОВЕТ

При создании выражения для групповой операции можно использовать построитель выражений (см. раздел «Выражения: Построитель выражений»).

Групповые операции: Группировка в запросе

(Для получения предварительной информации см. раздел «Групповые операции: В запросе».)

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

Шаг за шагом

1. Нажмите кнопку Запросы (Queries) на левой панели окна базы данных.
2. Выберите нужный запрос.
3. Нажмите кнопку Конструктор (Design) на панели инструментов окна базы данных. Откроется окно конструктора запроса.
4. Нажмите кнопку Групповые операции (Totals) на панели инструментов Access.
5. В раскрывающемся списке строки Групповая операция (Total) таблицы конструктора выберите групповые операции для требуемых полей (например, это может быть функция Sum для поля Цена). В строке Групповая операция (Total) тех полей, которые предназначены для формирования групп записей (например, это может быть поле Категория товара), должен быть выбран пункт Группировка (Group By).
6. Удалите столбцы всех полей, которые не предназначены для формирования групп записей или выполнения групповых операций.
7. В строке Условие отбора (Criteria) таблицы конструктора введите при необходимости условия, на основе которых будут отобраны соответствующие записи.
8. Нажмите кнопку Запуск (Run) на панели инструментов Access. На экран будет выведены записи с результатами выполнения групповой операции для каждой группы записей (для нашего примера в одном поле будут перечислены все категории товара, а в другом указана суммарная цена товаров каждой категории).
9. Нажмите кнопку Сохранить (Save) на панели инструментов Access.

Групповые операции: Группировка в отчете

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

Шаг за шагом

1. Нажмите кнопку Отчеты (Reports) на левой панели окна базы данных.
2. Выберите отчет, в котором выполнена группировка (см. раздел «Отчет: Группировка» главы «Формы и отчеты»),
3. Нажмите кнопку Конструктор (Design) на панели инструментов окна базы данных. Откроется окно конструктора отчета.
4. В области заголовка или примечания группы создайте свободный элемент управления Поле (Text Box), который будет использоваться для групповой операции (см. разделы «Элемент управления: Свободный» и «Элемент управления: Поле» главы «Формы и отчеты»).
5. Нажмите кнопку Свойства (Properties) на панели инструментов Access. Откроется окно диалога свойств созданного элемента управления.
6. На вкладке Данные (Data) в поле свойства Данные (Control Source) введите знак равенства (=), функцию групповой операции и имя соответствующего поля. Например, = Sum ([ИмяПоля]). При необходимости измените другие свойства элемента управления, например свойство Формат поля (Format) на вкладке Макет (Format).
7. Нажмите кнопку Сохранить (Save) на панели инструментов Access.

Перекрестный запрос: Модификация

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

Шаг за шагом

1. Нажмите кнопку Запросы (Queries) на левой панели окна базы данных.
2. Выберите нужный перекрестный запрос.
3. Нажмите кнопку Конструктор (Design) на панели инструментов окна базы данных.
4. Щелкните в свободной области верхней панели окна конструктора запроса и нажмите кнопку Свойства (Properties) на панели инструментов Access. Откроется окно диалога свойств запроса.
5. В поле свойства Заголовки столбцов (Column Headings) введите в нужной последовательности названия только тех заголовков, отделяя их точкой с запятой (;), которые необходимы.
6. Нажмите кнопку Запуск (Run) на панели инструментов Access.
7. Нажмите кнопку Сохранить (Save) на панели инструментов Access.

ВНИМАНИЕ

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

Перекрестный запрос: Создание

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

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

Возможности перекрестного запроса по анализу данных схожи с возможностями сводной таблицы (см. раздел «Сводная таблица: Создание»).

ПРИМЕЧАНИЕ

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

Шаг за шагом

1. Нажмите кнопку Запросы (Queries) на левой панели окна базы данных.
2. Нажмите кнопку Создать (New) на панели инструментов окна базы данных.
3. В списке открывшегося окна диалога выберите пункт Перекрестный запрос (Crosstab Query Wizard) и нажмите кнопку ОК.
4. В первом окне Мастера создания перекрестных таблиц (Crosstab Query Wizard) выберите таблицу или запрос на основе которого будет создан перекрестный запрос, и нажмите кнопку Далее (Next).
5. Выберите поля записи которых нужно использовать в качестве имен строк. Таких полей должно быть не более трех. Нажмите кнопку Далее (Next).
6. Выберите поле, записи которого нужно использовать в качестве заголовков столбцов. Нажмите кнопку Далее (Next).
7. Выберите вычисляемое поле и функцию, по которой будет определяться итоговое значение. Для вычисление итогового значения по каждой строке установите флажок Да (Yes, include row sums). Нажмите кнопку Далее (Next).
8. Введите имя нового запроса и нажмите кнопку Готово (Finish).

СОВЕТ

При использовании в качестве заголовков столбцов или имен строк полей типа Дата/время (Date/Time) на шаге 7 мастер предложит сгруппировать данные в этих полях по месяцам или годам. То же самое можно сделать в готовом перекрестном запросе в режиме конструктора. Для этого в таблице конструктора выделите столбец с соответствующим полем и в строке Поле (Field) вместо существующего имени, например ПолеДаты, введите выражение DatePart (формат, ПолеДаты). Аргумент формат может принимать значения "уууу" для группировки по годам или "mmmm" для группировки по месяцам.

Построение выражений: В запросе

В запросах выражения используются для создания условий отбора или сложных фильтров.

Шаг за шагом

1. Нажмите кнопку Запросы (Queries) на левой панели окна базы данных.
2. Выберите нужный запрос.
3. Нажмите кнопку Конструктор (Design) на панели инструментов окна базы данных.
4. В столбце таблицы конструктора, для поля которого необходимо создать условие отбора, щелкните в строке Условие отбора (Criteria) и нажмите кнопку Построить (Build) на панели инструментов Access.
5. Постройте требуемое выражение (см. раздел «Выражения: Построитель выражений»).

Построение выражений: В таблице

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

Шаг за шагом

1. Нажмите кнопку Таблицы (Tables) на левой панели окна базы данных.
2. Выберите нужную таблицу.
3. Нажмите кнопку Конструктор (Design) на панели инструментов окна базы данных.
4. Выберите поле, для которого нужно задать значение по умолчанию или условие на значение.
5. Установите курсор в поле свойства Значение по умолчанию (Default Value) или Условие на значение (Validation Rule) и нажмите кнопку Построить (Build) на панели инструментов Access.
6. Постройте требуемое выражение (см. раздел «Выражения: Построитель выражений»).

Построение выражений: В форме и отчете

Выражения в формах и отчетах строятся для изменения исходных данных.

Шаг за шагом

1. Нажмите кнопку Формы (Forms) или Отчеты (Reports) на левой панели окна базы данных.
2. Выберите форму или отчет с нужным элементом управления.
3. Нажмите кнопку Конструктор (Design) на панели инструментов окна базы данных.
4. Выделите элемент управления, для которого нужно построить выражение.
5. Нажмите кнопку Свойства (Properties) на панели инструментов Access, чтобы открыть окно диалога свойств выделенного элемента управления.
6. На вкладке Данные (Data) перейдите в поле свойства Данные (ControlSource) и нажмите кнопку Построить (Build) на панели инструментов Access.
7. Постройте требуемое выражение (см. раздел «Выражения: Построитель выражений»).

Сводная таблица: Модификация

(Для получения предварительной информации см. раздел «Сводная таблица: Создание».)

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

Шаг за шагом

1. Нажмите кнопку Формы (Forms) на левой панели окна базы данных.
2. Выберите форму со сводной таблицей.
3. Нажмите кнопку Открыть (Open) на панели инструментов окна базы данных.
4. На форме нажмите кнопку Изменить объект сводной таблицы (Edit PivotTable Object). Откроется окно программы Microsoft Excel.
5. Выберите команду Данные (Data) → Обновить данные (Refresh Data).
6. Измените сводную таблицу:

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

7. Выберите команду Файл (File) → Закрыть и вернуться в Сводная форма (Close & Return to PivotTable Form).
8. Нажмите кнопку Сохранить (Save) на панели инструментов Access.

Сводная таблица: Создание

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

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

ВНИМАНИЕ

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

Шаг за шагом

1. Нажмите кнопку Формы (Forms) на левой панели окна базы данных.
2. Нажмите кнопку Создать (New) на панели инструментов окна базы данных.
3. В списке открывшегося окна диалога Новая форма (New Form) выберите пункт Сводная таблица (Pivot Table Wizard) и нажмите кнопку ОК.
4. В первом окне Мастера создания сводных таблиц (Pivot Table Wizard) ознакомьтесь с предлагаемой информацией и нажмите кнопку Далее (Next).
5. В раскрывающемся списке Таблицы и запросы (Tables/Queries) выберите таблицу или запрос, из которого нужно отобрать данные.
6. Выберите нужное поле и нажмите кнопку (>). Повторите действие для всех необходимых полей.
7. Повторяйте шаги 5 и 6 для следующих таблиц или запросов, пока в сводной таблице не окажутся все необходимые поля, затем нажмите кнопку Далее (Next). Откроется окно программы Microsoft Excel с открытым окном Мастера сводных таблиц.
8. В окне мастера нажмите кнопку Макет (Layout).
9. Перетащите кнопки с именами полей, которые расположены справа, в области, находящейся в центре окна. В область Данные (Data) нужно перетащить поля, по которым будут подводиться итоги, в область Столбец (Column) — поля, записи которых будут столбцами таблицы, в область Строка (Row) — поля, записи которых будут строками таблицы. Нажмите кнопку ОК.
10. Нажмите кнопку Параметры (Options) и установите флажки у требуемых параметров. Нажмите кнопку ОК.
11. Нажмите кнопку Готово (Finish).
12. Нажмите кнопку Сохранить (Save) на панели инструментов Access, введите имя новой формы и нажмите кнопку ОК.

ВНИМАНИЕ

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

СОВЕТ

Чтобы работать с данными сводной таблицы в программе Excel, возможности которой по анализу данных гораздо шире, чем Access, просто дважды щелкните на области данных сводной таблицы.

Функции: Даты и времени

(Для получения предварительной информации см. раздел «Выражения: Построитель выражений».)

Набор функций Access позволяет создавать выражения для полей, имеющих формат Дата/время (Date/Time). Ниже приведены наиболее часто используемые функции:

1. Функция Date. Эта функция выводит значение текущей даты. Как правило, она используется вместе с операторами неравенства в качестве условия отбора, условия на значение или для вычисления разницы между текущей и заданной датами.
2. Функция Now. Функция полностью аналогична функции Date, но выводит текущее время. Эти функции часто используются вместе.
3. Функция DateDiff(интервал, дата1, дата2). Функция позволяет определять разницу между двумя датами в днях недели или месяцах. Аргументами функции являются даты (аргументы дата1 и дата2), между которыми требуется найти разницу. Третий аргумент (интервал) определяет, в каком виде эта разница будет представлена. Различают 9 форматов представления даты: уууу (год), q (квартал), m (месяц), у (день года), d (день), w (день недели), ww (час), n (минута) и s (секунда). Аргумент должен быть заключен в кавычки, например "ww". Обычно эта функция используется при вычислениях в строке Поле (Field) таблицы конструктора запросов и в поле свойства Данные (Control Source) элемента управления формы или отчета.
4. Функция DateAdd(интepвaл, число, дата). Функция позволяет определить будущую дату, отстоящую от заданной даты (аргумент дата) на указанное число (аргумент число) интервалов (аргумент интервал). Аргумент число должен иметь числовой тип, а форматы даты, задаваемые аргументом интервал, были определены выше.
5. Функция DatePart(интервал, дата). Функция применяется для преобразования одного формата даты (аргумент дата) в любой другой, заданный аргументом интервал.

Шаг за шагом

1. Выберите объект, для которого нужно построить выражение (см. разделы «Построение выражений: В таблице», «Построение выражений: В запросе», «Построение выражений: В форме и отчете»), установите курсор в нужное поле и запустите построитель выражений.
2. В окне диалога Построитель выражений (Expression Builder) в первом нижнем поле дважды щелкните на папке Функции (Functions). Раскроется следующий уровень иерархии папок.
3. Щелкните на папке Встроенные функции (Built-in Functions).
4. Во втором поле выберите раздел Дата/время (Date/Time).
5. Используя функции из третьего окна, постройте нужное выражение (см. также разделы «Выражения: Ссылка на элемент управления» и «Выражения: Ссылка на поле»).
6. Построив и отредактировав выражение, нажмите кнопку ОК.

Функции: Обработки фрагментов текста

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

• Функция Left(cтрокa, n) выводит n левых символов из текста строка.
• Функция Right(cтрокa, n) выводит n правых символов из текста строка.
• Функция Mid(cтрокa, начало, длина) выводит фрагмент длиной длина из текста строка, начиная с символа начало.
• Функция InStr(cтрока1, строка2) выводит позицию, с которой в текст строка1 начинает входить текст строка2.
• Функция Trim(строка) удаляет пробелы в начале и в конце текста строка. Для удаления пробелов только в начале используется функция Ltrim, а для удаления пробелов в конце — функция Rtrim.

Шаг за шагом

1. Откройте нужный запрос, форму или отчет в режиме конструктора.
2. В форме и отчете выделите элемент управления, в котором будет содержаться нужное выражение, нажмите кнопку Свойства (Properties) и в окне диалога свойств выделенного элемента управления на вкладке Данные (Data) установите курсор в поле свойства Данные (Control Source). Введите знак равенства.
3. В таблице конструктора запроса установите курсор в строке Поле (Field) столбца того поля, в котором будет содержаться нужное выражение. Введите имя нового вычисляемого поля и двоеточие.
4. Введите требуемую функцию.
5. Нажмите кнопку Сохранить (Save) на панели инструментов Access.

Функции: Пользователя

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

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

Шаг за шагом

1. Нажмите кнопку Модули (Modules) на левой панели окна базы данных.
2. Выберите нужный модуль.
3. Нажмите кнопку Конструктор (Design) на панели инструментов окна базы данных. Откроется окно программы Microsoft Visual Basic с открытым окном выбранного модуля.
4. В раскрывающемся списке Процедура (Procedure), находящемся вверху справа в окне модуля, выберите имя нужной функции.
5. При необходимости измените программу функции и скопируйте модуль в вашу базу данных, чтобы иметь доступ к выбранной функции (см. раздел «Объект базы данных: Копирование» главы «Операции с файлами»).
6. Нажмите кнопку Сохранить (Save) на панели инструментов Visual Basic.

ВНИМАНИЕ

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

ПРИМЕЧАНИЕ

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

СОВЕТ

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

Функции: Преобразования формата

Иногда возникает необходимость, чтобы данные в запросе, форме или отчете были представлены в ином, чем в исходной таблице, формате. Ниже приведены основные функции преобразования формата: CCur (денежный), CDate (дата), CStr (текст), CDbl (числовой с плавающей точкой, 8 байт), CSng (числовой с плавающей точкой, 4 байта), CInt (числовой, целый).

Шаг за шагом

1. Откройте нужный запрос, форму или отчет в режиме конструктора.
2. В окне конструктора запроса установите курсор в строке Поле (Field) свободного столбца таблицы конструктора запроса, введите имя вычисляемого поля, под которым оно будет фигурировать в запросе, и двоеточие.
3. В форме или отчете создайте элемент управления, в котором будет содержаться нужное выражение, нажмите кнопку Свойства (Properties) и в окне диалога свойств элемента управления на вкладке Данные (Data) установите курсор в поле свойства Данные (Control Source). Введите знак равенства.
4. Введите нужную функцию, указав в качестве аргумента имя поля, данные которого требуется представить в другом формате. Например, Cint([ИмяПоля]).
5. Нажмите кнопку Сохранить (Save) на панели инструментов Access.

Функции: Финансовые

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

• Функция Pmt(cтавкa, периодов, сумма) показывает объем регулярных платежей по кредиту при фиксированной учетной ставке. Аргументами функции являются: ставка (rate) — учетная ставка за период, периодов (nper) - число периодов, сумма (pv) — сумма займа на текущий момент.
• Функция FV(cтавкa, периодов, выплаты) показывает будущие значения суммы регулярных платежей при фиксированной учетной ставке. Аргументами функции являются: ставка (rate) — учетная ставка за период, периодов (nper) — число периодов, выплаты (pmt) — размер выплат за период.
• Функция РV(ставка, периодов, выплаты) показывает выплаты в текущий момент при регулярных платежах и фиксированной учетной ставке. Аргументами функции являются: ставка (rate) — учетная ставка за период, периодов (nper) — число периодов, выплаты (pmt) - размер выплат за период.

ВНИМАНИЕ

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

Шаг за шагом

1. Откройте нужный запрос, форму или отчет в режиме конструктора.
2. В форме и отчете выберите элемент управления, содержащий выражение, нажмите кнопку Свойства (Properties) и на вкладке Данные (Data) выберите свойство Данные (ControlSource). В таблице конструктора запроса выберите строку Поле (Field).
3. Нажмите кнопку Построить (Build) на панели инструментов Access.
4. В открывшемся окне построителя выражения введите функцию вместе с нужными аргументами (см. раздел «Выражения: Построитель выражений»).
5. Завершив создание выражения, нажмите кнопку ОК.
6. Нажмите кнопку Сохранить (Save) на панели инструментов Access.

Функции: Условные

Функция IIf(выражение, если_истинно, если_ложно) позволяет проверить истинность аргумента выражение. Результатом выполнения функции является значение если_истинно, если аргумент выражение имеет значение истина, или значение если_ложно, если аргумент выражение имеет значение ложно. Например, выражение IIf([ЗаказМатериала] = "Да", "Материал заказан", "") позволяет вывести сообщение Материал заказан, если в записи поля стоит значение Да, и вывести пустую строку в любом другом случае.

ПРИМЕЧАНИЕ

В качестве аргументов если_истинно и если_ложно можно использовать другие функции и выражения.

Шаг за шагом

1. Откройте нужный запрос, форму или отчет в режиме конструктора.
2. В окне конструктора запроса установите курсор в строке Поле (Field) свободного столбца таблицы конструктора запроса, введите имя вычисляемого поля, под которым оно будет фигурировать в запросе, и двоеточие. В форме или отчете создайте элемент управления, в котором будет содержаться нужное выражение, нажмите кнопку Свойства (Properties) и в окне диалога свойств элемента управления на вкладке Данные (Data) установите курсор в поле свойства Данные (Control Source). Введите знак равенства.
3. Введите ключевое слово IIf, откройте круглую скобку, введите выражение, которое будет проверяться на истинность, и поставьте запятую.
4. Введите значение или выражение, которое нужно выполнить в случае истинности. Текстовые значения нужно заключать в кавычки. Поставьте запятую.
5. Введите значение или выражение, которое нужно выполнить в случае ложности. Текстовые значения нужно заключать в кавычки. Поставьте закрывающую круглую скобку.
6. Нажмите кнопку Сохранить (Save) на панели инструментов Access.