Пример 8. Перекрестные запросы

Раздел 4. Создание запросов

4. 1 Основные виды запросов

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

Тип запроса	Описание
Запрос на выборку	Выбирает данные из одной или нескольких таблиц. При этом данные в процессе выполнения запроса могут быть отсортированы, выбраны в соответствии с заданными условиями, вычислены.
Групповой запрос	Производит вычисления для данных некоторой группы записей, выбираемых из записей указанного для обработки поля по уникальному значению другого поля, в котором при этом устанавливается операция – «группировка». При разработке группового запроса необходимо добавить строку «групповые операции» на стандартный бланк конструктора запроса, нажав на значок ∑.
Перекрестный запрос	Выводит результаты статистических расчетов (например, среднее значение, сумма, количество записей) для данных, которые находятся в одной или нескольких таблицах. Для выполнения такого запроса в бланк запроса добавляются поля «групповая операция» и «перекрестная таблица». Результаты запроса выводятся в виде таблицы, очень похожей на сводную таблицу Excel.
Запрос на изменение(модификацию)	Эти запросы влияют на содержимое БД. Они позволяют за одну операцию внести изменения в одну или несколько таблиц. Запросы используются для создания новых таблиц из результатов запроса и внесения изменений в существующие таблиц. Различают запросы на удаление, добавление и обновление данных, а также запрос на создание таблиц.
Параметрический запрос	Запросы, параметры которых изменяются пользователем при каждом выполнении запроса. Выполнение этого запроса сопровождается выводом одного или нескольких диалоговых окон, предназначенных для ввода пользователем конкретных значений параметров запроса.
SQL-запрос	Запрос на языке SQL.

Для создания нового запроса необходимо:

1. На ленте выбрать вкладку Создание.

2. В данной вкладке выбрать кнопку Создание запросов в режиме конструктора.

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

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

5. Закрыть окно добавления таблиц.

6. Установить параметры запроса, а именно:

· Поле – имя поля;

· Имя таблицы – имя таблицы, в которой находится данное поле;

· Сортировка – указывает вид сортировки (по убыванию, по возрастанию);

· Вывод на экран – определяет вывод на экран поля, указанного в бланке запроса;

Или — другие условия ограничения вывода записи.

7. Сохранить запрос.

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

9. Результат будет представлен в виде новой таблицы.

4. 2 Примеры создания запросов

Разберем несколько типовых запросов к разработанным таблицам Продажи и Агенты.

Пример 1. Запрос выводит название товара и дату продажи у конкретного агента. В строке Условие отбора вводится текст “Дворова У. Щ. ”. Это условие позволит отобрать все продажи, произведенные данным агентом (рис. 1, 2).

Рис. 1 Окно конструктора запросов в примере 1

Рис. 2 Результат выполнения запроса в примере 1

Примечание. Существует возможность получить список всех агентов, начинающихся на букву 'Д'. Для этого в строке Условие отбора нужно записать следующее: 'Д*'. При формировании условия отбора могут быть использованы операции отношения, например, условия '> ', '> =', '< ', '< =', а также логические операции AND, OR, NOT. Например, условие отбора '> =Д*' позволит выбрать всех агентов, фамилии которых начинаются с буквы 'Д' и следующие за ней по алфавиту. Условие отбора '> =Д* AND < =Ж*' позволит выбрать агентов, фамилии которых начинаются на буквы 'Е' и 'Ё'. Данное примечание справедливо для полей числового, денежного типа и полей типа 'дата/время'.

Пример 2. Сформируем запрос с параметром, позволяющий получить необходимую информацию о продажах конкретного товара. Для этого в строке отбора необходимо ввести “[Введите название товара]”. Текст в квадратных скобках может быть произвольным, но не совпадающим с названием поля. В дальнейшем этот текст будет выводиться в диалоговом окне ( см. рис. 3, 4, 5).

Рис. 3 Диалоговое окно в примере 2

Рис. 4 Окно конструктора запросов в примере 2

Рис. 5 Результат выполнения запроса в примере 2

Пример 3. Для того, чтобы выбрать данные по продажам за конкретный интервал времени, в строке условие отбора необходимо задать интересующий интервал времени, например, BETWEEN #04. 10. 2009# AND #11. 10. 2009# (рис. 6, 7).

Рис. 6 Окно конструктора запросов в примере 3

Рис. 7 Результат выполнения запроса в примере 3

Пример 4. Запрос с вычислением. Необходимо определить, сколько агент получает с каждой сделки, если его прибыль составляет 10% (рис. 8, 9, 10).

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

Рис. 9 Окно конструктора запросов в примере 4

Рис. 10 Результат выполнения запроса в примере 4

Пример 5. Требуется вывести сумму, на которую продал каждый агент (рис. 11, 12).

Рис. 11 Окно конструктора запросов в примере 5

Рис. 12 Результат выполнения запроса в примере 5

Пример 6. Требуется найти среднюю сумму сделок (рис. 13, 14).

Рис. 13 Окно конструктора запросов в примере 6

Рис. 14 Результат выполнения запроса в примере 6

Пример 7. Показать всех агентов, у которых суммарные продажи ниже средней суммы (рис. 15, 16).

Рис. 15 Окно конструктора запросов в примере 7

Рис. 16 Результат выполнения запроса в примере 7

Пример 8. Перекрестные запросы

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

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

2. Перетащите поля " Товары", " ФИО Агента”, “Стоимость”.

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

4. Выберите в списке ячейки Перекрестная таблица столбца " Товары" значение Заголовки строк.

5. Выберите в списке ячейки Перекрестная таблица столбца " ФИО Агента" значение Заголовки столбцов.

6. Выберите в списке ячейки Перекрестная таблица столбца " Стоимость" значение Значение.

7. Выберите в списке ячейки Групповая операция столбца " Стоимость" Sum.