Оффисные приложения Ms Access

Использование форм для ввода и просмотра данных, создание отчетов в Microsoft Access

связанные с помощью арифметических операторов. Для изменения порядка вычислений и группировки данных в выражениях используются круглые скобки, а имена полей таблицы должны быть заключены в квадратные скобки. Если в запросе используется несколько таблиц, во избежание ошибок следует указывать полное имя поля, помещая перед именем поля имя таблицы. В этом случае для разделения имен объектов используется !. Таким образом, запись полного имени поля таблицы выглядит следующим образом: [Имя таблицы]![Имя поля].

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

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

Поскольку выражение начинается с текста Стоимость:, новый столбец получит имя Стоимость. Это имя часто называется "псевдоним". Если псевдоним не указан, он будет создан автоматически, например такой: Expr1.

При выполнении запроса в Access выполняется расчет каждой строки. Создание вычисляемого поля в режиме конструктора запроса

1.В области навигации щелкните правой кнопкой мыши запрос, который необходимо изменить, и выберите в контекстном меню пункт Конструктор.

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

3.Чтобы создать выражение вручную, просто введите его.

4.Чтобы создать выражение с помощью построителя выражений, на вкладке

Конструктор в группе Настройка запроса нажмите кнопку Построитель.

Создание вычисляемого поля в таблице

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

Чтобы создать вычисляемое поле, выполните указанные ниже действия.

1.Откройте таблицу, дважды щелкнув ее в области навигации.

2.Прокрутите таблицу по горизонтали до крайнего правого столбца и нажмите в заголовке столбца кнопку Щелкните для добавления.

3.В появившемся списке выберите пункт Вычисляемое поле и укажите тип данных результата. Откроется построитель выражений Access.

4.Начните вводить формулу расчета, которая требуется для этого поля, например:

[Количество] * [Цена]

5.Нажмите кнопку ОК.

6.Введите имя вычисляемого поля и нажмите клавишу ВВОД.

71

Использование форм для ввода и просмотра данных, создание отчетов в Microsoft Access

Добавление условия на значение в поле таблицы или запись

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

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

>=0

Чтобы ввести условие на значение поля или записи, выполните указанные ниже действия.

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

2.При вводе условия на значение поля выделите поле, которое необходимо изменить.

3.На вкладке Поля в группе Проверка полей нажмите кнопку Проверка и выберите пункт Условие на значение поля или Условие на значение записи.

Откроется построитель выражений Access.

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

>=0

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

Ввод сообщения проверки правильности

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

1.В области навигации дважды щелкните таблицу, которую нужно изменить.

2.При создании сообщения для условия на значение поля выделите соответствующее поле.

3.На вкладке Поля в группе Проверка полей нажмите кнопку Проверка и выберите пункт Сообщение проверки поля или Сообщение проверки записи.

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

ОК.

72

Использование форм для ввода и просмотра данных, создание отчетов в Microsoft Access

УПРАЖНЕНИЕ 21. СОЗДАНИЕ ВЫЧИСЛЯЕМЫХ ПОЛЕЙ. РАБОТА С ПОСТРОИТЕЛЕМ ВЫРАЖЕНИЙ

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

Стоимость туров в Германию с учетом количества путевок.

1.Откройте запрос Клиенты, заказавшие тур в Германию в режиме конструктора.

2.Добавьте в запрос вычисляемое поле для расчета стоимости тура: в пустую ячейку строки Поле бланка запроса введите выражение: [Стоимость]*[Количество]; (после нажатия на Enter вычисляемому полю автоматически присваивается имя типа

Выражение 1, 2 и т. д.).

3.Укажите, что вычисляемое поле должно выводиться на экран.

4.Выполните запрос и просмотрите его результаты в режиме таблицы.

5.Вернитесь в режим конструктора.

6.Присвойте вычисляемому полю имя Стоимость тура: щелчком мыши установите текстовый курсор в строку Поле для вычисляемою поля запроса; удалите старое имя вычисляемого поля (Выражение 1); введите новое имя: Стоимость тура (в

результате получится: Стоимость тура: [Стоимость]*[Количество]).

7.Определите для вычисляемого поля денежный формат: щелчком правой клавиши мыши в области маркировки столбца выделите вычисляемое поле; в контекстном меню выберите команду Свойства; вкладка Общие диалогового окна Окно свойств; в строке Формат поля выберите из списка Евро (не закрывайте диалоговое окно Свойства поля).

8.Выполните запрос и просмотрите его результаты в режиме таблицы.

9.Вернитесь в режим конструктора.

10.Измените число десятичных знаков для вычисляемого поля: в диалоговом окне Окно свойств в строке Число десятичных знаков выберите из списка 0; Закройте диалоговое окно Окно свойств.

11.Выполните запрос и просмотрите его результаты в режиме таблицы.

12.Сохраните измененный запрос под новым именем Стоимость туров в Германию с учетом количества путевок.

13.Закройте запрос.

УПРАЖНЕНИЕ 22. СОЗДАНИЕ ВЫЧИСЛЯЕМЫХ ПОЛЕЙ. РАБОТА С ПОСТРОИТЕЛЕМ ВЫРАЖЕНИЙ

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

1.Создайте новый запрос в режиме конструктора.

2.Выберите таблицу Клиенты.

3.С использованием построителя выражений создайте вычисляемое поле для объединения значений полей Фамилия, Имя и Отчество: установите текстовый курсор в пустую ячейку строки Поле бланка запроса; на вкладке Конструктор в группе Настройка запроса нажмите кнопку Построитель ;

4.В левом списке нижней части окна построителя выражений Элементы выражений двойным щелчком откройте папку Турагенство.accdb и затем Таблицы; щелчок по таблице Клиенты (при этом в среднем списке отобразятся поля выбранной таблицы); выделите поле Фамилия; двойной щелчок мышью по нолю Фамилия (в верхнем поле окна построителя выражений появится ссылка на поле Фамилия таблицы Клиенты: [Клиенты]![Фамилия]);

5.В окне Элементы выражений откройте папку Операторы и выберите строковый оператор & (либо наберите этот символ на клавиатуре); введите пробел: " "; снова оператор &; в среднем поле выделите поле Имя; кнопка Вставить; оператор &;

73

Использование форм для ввода и просмотра данных, создание отчетов в Microsoft Access

введите: " "; оператор &; в среднем поле выделите поле Отчество; кнопка Вставить (в результате в верхнем поле построителя получится: [Клиенты]![Фамилия]&” "&[Клиенты]![Имя]&” "&[Клиенты]![Отчество]); OK (созданное выражение будет вставлено в ячейку строки Поле, из которой и был вызван построитель выражений);

Enter.

6.Присвойте вычисляемому полю имя Клиент: (в результате получилось: Клиент: [Клиенты]![Фамилия]&” “&[ Клиенты]![Имя]&” “[ Клиенты]![Отчество]).

7.Закройте окно Область ввода: ОК.

8.Укажите, что вычисляемое поле должно выводиться на экран.

9.Присвойте вычисляемому полю имя Общая стоимость.

10.Добавьте поле Контактный телефон из таблицы Клиенты.

11.Выполните запрос и просмотрите его результаты в режиме таблицы.

12.Сохраните запрос под именем Контактные телефоны клиентов.

УПРАЖНЕНИЕ 23. СОЗДАНИЕ ВЫЧИСЛЯЕМЫХ ПОЛЕЙ. РАБОТА С ПОСТРОИТЕЛЕМ ВЫРАЖЕНИЙ

Создайте запрос общей стоимости тура с учетом скидок от количества заказанных путевок. Скидки определяются следующим образом: нет скидки если заказоно менее 5 путевок, скидка в 5% если заказано от 5 до 9 путевок и 10% скидки если заказано 10 и более путевок.

Сохраните запрос под именем Общая стоимость тура с учетом скидок от количества

заказанных путевок.

1.Создайте новый запрос в режиме конструктора.

2.Выберите таблицы Клиенты, Заказы и Маршруты.

3.Из таблицы Клиенты выберите поля Фамилия, Имя, Отчество; из таблицы Заказы

выберите поле Количество; из таблицы Маршруты выберите Стоимость.

4.С использованием построителя выражений создайте вычисляемое поле для определения

общей стоимости тура с учетом скидок	от количества заказанных путевок:
установите текстовый курсор в пустую ячейку	строки Поле бланка запроса; на вкладке

Конструктор в группе Настройка запроса нажмите кнопку Построитель ;

5.В левом списке нижней части окна построителя выражений Элементы выражений двойным щелчком откройте папку Турагенство.accdb и затем Таблицы; из таблицы Заказы выберите поле Количество; двойной щелчок мышью по полю Количество (в верхнем поле окна построителя выражений появится [Заказы]![ Количество]); оператор * (умножение); из таблицы Маршруты выберите поле Стоимость; двойной щелчок мышью по нолю Стоимость (в верхнем поле окна построителя выражений появится [Заказы]![Количество]*[Маршруты]![Стоимость]); оператор *.

6.Продолжаете работу с построителем выражений: папка Функции; папка Встроенные функции; категория Управление; функция IIf; в верхнем поле окна построителя выражений вместо выражения «expression» строите выражение [Заказы]![Количество]; оператор <; цифра 5; вместо «truepart» ставите цифру 1; вместо «falsepart» опять выбираете функцию IIf; снова в верхнем поле окна построителя выражений вместо выражения «expression» строите выражение [Заказы]![Количество]; оператор <; цифра 10; вместо «truepart» ставите число 0,95; вместо «falsepart» число 0,9 (должно получиться следующее выражение Выражение 1:

[Заказы]![Количество]*[Маршруты]![Стоимость]*IIf([Заказы]![Количество]<5;1;IIf ([Заказы]![Количество]<10;0,95;0,9)).

7.Закройте окно Построитель выражений: ОК.

8.Укажите, что вычисляемое поле должно выводиться на экран.

9.Определите для вычисляемого поля денежный формат: щелчком правой клавиши мыши в области маркировки столбца выделите вычисляемое поле; в контекстном меню

74

Использование форм для ввода и просмотра данных, создание отчетов в Microsoft Access

выберите команду Свойства; вкладка Общие диалогового окна Окно свойств; в строке Формат поля выберите из списка Евро (не закрывайте диалоговое окно Свойства поля).

10.Измените число десятичных знаков для вычисляемого поля: в диалоговом окне Окно свойств в строке Число десятичных знаков выберите из списка 0; Закройте диалоговое окно Окно свойств.

11.Укажите, чтобы поле Стоимость не выводилось на экран.

12.Выполните запрос и просмотрите его результаты в режиме таблицы.

13.Сохраните запрос под именем Общая стоимость тура с учетом скидок от

количества заказанных путевок.

3.6. СОЗДАНИЕ ИТОГОВЫХ ЗАПРОСОВ.

Запросы на выборку можно также использовать для группировки записей и вычисления итоговых значений по группам данных. Подсчет итоговых значений выполняется с помощью так называемых групповых функций. Групповая функция Sum вычисляет сумму всех значений заданного поля в каждой группе; Avg вычисляет среднее арифметическое всех значений данного поля в каждой группе; Min (Max) возвращает наименьшее (наибольшее) значение, найденное в этом поле внутри каждой группы; Count определяет количество записей в каждой группе.

Для создания итогового запроса в бланк запроса на вкладке Работа с запросами в группе

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

УПРАЖНЕНИЕ 24. СОЗДАНИЕ ИТОГОВЫХ ЗАПРОСОВ

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

1.Создайте новый запрос в режиме конструктора.

2.Добавьте запрос Общая стоимость тура с учетом скидок от количества заказанных путевок.

3.Определите поля, необходимые для создания итогового запроса: поля Фамилия, Имя,

Отчество и Общая стоимость.

4.Включите отображение строки Групповая операция в бланке запроса: на вкладке

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

5.Укажите, что для каждого клиента должна быть вычислена общая стоимость туров по всем маршрутам: в строке Групповая операция бланка запроса для поля Общая стоимость выберите из списка функцию Sum.

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

7.Присвойте вычисляемому полю имя Общая стоимость туров: щелчком мыши установите текстовый курсор перед именем поля Общая стоимость и введите:

Общая стоимость туров.

8.Выполните запрос и просмотрите его результаты в режиме таблицы.

9.Сохраните запрос под именем Общая стоимость туров по всем маршрутам и

закройте его.

УПРАЖНЕНИЕ 25. СОЗДАНИЕ ИТОГОВЫХ ЗАПРОСОВ

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

75

Использование форм для ввода и просмотра данных, создание отчетов в Microsoft Access

1.Создайте новый запрос в режиме конструктора.

2.Добавьте таблицу Сотрудники.

3.Добавьте в бланк запроса поле Должность.

4.Включите отображение строки Групповая операция в бланке запроса.

5.Добавьте в бланк запроса еще поле Должность.

6.Укажите количество сотрудников по каждой должностной категории: для второго поля Должность в строке Групповая операция бланка запроса выберите из списка функцию Count.

7.Закройте и сохраните запрос под именем Штатная структура турагентства.

3.7.СОЗДАНИЕ ЗАПРОСОВ С ПАРАМЕТРАМИ.

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

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

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

Добавление параметра запроса

1.Создайте запрос на выборку и откройте его в режиме конструктора.

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

[Начальная дата]

3.При запуске запроса с параметрами текст отображается в диалоговом окне без скобок. Можно также использовать выражение с предложениями ввести параметр, например:

Between [Начальная дата] And [Конечная дата]

4.Повторите действие 2 для каждого поля, к которому необходимо добавить параметры.

Можно использовать предыдущие действия для добавления параметра к запросам любого из следующих типов:

на выборку;

перекрестный;

на добавление;

76

Использование форм для ввода и просмотра данных, создание отчетов в Microsoft Access

на создание таблицы;

на обновление.

Сопоставление части значения поля со строкой параметра

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

1.Создайте запрос на выборку и откройте его в режиме конструктора.

2.В строке Критерий поля, для которого нужно применить параметр, введите Like "*"&[, текст, который должен отображаться в сообщении, а затем ]&"*". При выполнении запроса с параметрами на экран будет выведено диалоговое окно, сообщение в котором будет отображаться без квадратных скобок, ключевого слова Like и подстановочных знаков.

3.Когда запрос получит значение параметра, он сравнит его со значениями, которые содержат строку параметра. Например, для строки параметра си будут найдены строки, в которых поле параметра имеет значение «Сингапур» и «Россия».

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

Сопоставление на основе неравных значений

Может потребоваться, чтобы запрос принимал параметр, но возвращал не строки с совпадающими значениями, а строки, для которых истинно другое выражение условия. Например, можно предложить ввести год и возвратить все записи, в которых значение года больше, чем введенное в строке параметра. Для этого введите оператор сравнения слева от квадратных скобок, в которые заключено предложение ввести параметр, например >[Год:].

Указание типов данных для параметров

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

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

1.Когда запрос открыт в режиме конструктора, на вкладке Конструктор в группе

Показать или скрыть щелкните элемент Параметры запроса.

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

3.В столбце Тип данных выберите тип данных для каждого параметра.

77

Использование форм для ввода и просмотра данных, создание отчетов в Microsoft Access

УПРАЖНЕНИЕ 26. СОЗДАНИЕ ПАРАМЕТРИЧЕСКИХ ЗАПРОСОВ

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

1.Создайте новый запрос в режиме конструктора на основе таблицы Заказы с полями

Кол клиента, Код маршрута, Дата заказа.

2.Задайте сортировку записей динамической таблицы по возрастанию по полю Дата заказа.

3.Укажите, что на экран должны выводиться все поля запроса.

4.Определите условие выбора данных: в строку Условие отбора бланка запроса для поля Дата заказа введите выражение: Between [Начальная дата] And [Конечная дата]; Enter.

5.Определите тип данных для параметра Начальная дата: на вкладке Конструктор в

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

6.Определите тип данных для параметра Конечная дата

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

29.12.2010; для параметра Конечная дата введите: 12.01.2011;ОК.

8.Сохраните запрос под именем Выбор заказов по дате и закройте его.

УПРАЖНЕНИЕ 27. СОЗДАНИЕ ПАРАМЕТРИЧЕСКИХ ЗАПРОСОВ

Создайте параметрический запрос для поиска Клиентов по нескольким первым буквам фамилии. Сохраните запрос под именем Поиск клиентов по первым буквам фамилии.

1.Создайте новый запрос в режиме конструктора на основе таблицы Клиенты с полями

Фамилия, Имя, Отчество, Место работы и Должность.

2.Определите условие выбора данных: в строку Условие отбора бланка запроса для поля

Фамилия введите выражение: Like [Начальные буквы фамилии] & "*"; Enter.

3.Определите тип данных для параметра: на вкладке Конструктор в группе Показать или скрыть щелкните элемент Параметры запроса; в первую строку столбца

Параметр введите имя параметра: Начальные буквы фамилии; в столбце Тип данных

для этого параметра выберите из списка Текстовый; ОК.

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

5.Сохраните запрос под именем Поиск клиентов по первым буквам фамилии и закройте его.

3.8. СОЗДАНИЕ ПЕРЕКРЕСТНЫХ ЗАПРОСОВ.

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

Перекрестная таблица используется для определения роли поля в перекрестном запросе

(Заголовки строк, Заголовки столбцов, Значение). Строка Групповая операция

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

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

78

Использование форм для ввода и просмотра данных, создание отчетов в Microsoft Access

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

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

Создание перекрестных запросов

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

Способы создания перекрестного запроса

Использование мастера создания перекрестных запросов

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

Преимущества использования мастера указаны ниже.

Простота применения. Достаточно запустить мастер и затем ответить на ряд вопросов.

Автоматическая группировка дат по интервалам. Если в качестве заголовков столбцов используется поле с данными типа дата/время, мастер позволяет сгруппировать даты по интервалам, например по месяцам или кварталам.

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

Однако мастер не позволяет делать следующее:

использовать более одной таблицы или запроса в качестве источника записей;

использовать выражение для создания полей;

добавлять запросы на ввод параметров;

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

Работа в режиме конструктора.

Режим конструктора позволяет более тщательно контролировать структуру запроса. В этом режиме доступны возможности, не поддерживаемые мастером.

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

лучше контролировать весь процесс (мастер принимает некоторые решения независимо от пользователя);

использовать в качестве источника записей более одной таблицы или запроса;

включить в запрос приглашение ввести параметр;

использовать выражения как поля в запросе;

79

Использование форм для ввода и просмотра данных, создание отчетов в Microsoft Access

задать список фиксированных значений для использования в качестве заголовков столбцов;

поупражняться в работе с бланком запроса.

Составление запроса в режиме SQL

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

Создание перекрестного запроса с помощью мастера

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

1.На вкладке Создание в группе Запросы нажмите кнопку Мастер запросов.

2.В диалоговом окне Новый запрос выберите пункт Перекрестный запрос и нажмите кнопку ОК. Будет запущен мастер создания перекрестных запросов.

3.На первой странице мастера выберите таблицу или запрос, которые следует использовать для создания перекрестного запроса.

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

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

6.Если для заголовков столбцов выбрано поле типа «Дата/время», на следующей странице мастера будет предложено задать интервал для группировки дат.

Возможные варианты: Год, Квартал, Месяц, Дата и Дата/время. Если поле,

выбранное для формирования заголовков столбцов, не относится к типу «Дата/время», эта страница мастера пропускается.

7.На следующей странице выберите поле и функцию для расчета сводных значений. Набор доступных функций зависит от типа данных выбранного поля.

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

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

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

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

80