Лекция 10. Модификация данных с помощью запросов-действий

ЛЕКЦИЯ 10. ПЕРЕКРЕСТНЫЕ ЗАПРОСЫ

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

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

Для этого мы должны сделать запрос (вкладка Запросы/Создать/Конструктор) и изменить тип запроса на перекрестный запрос.

Поле	Код товара	Дата продажи	Продано
Имя таблицы	Заказано	Заказы	Заказы
Гр. Операции	Группировка	Группировка	Sum
Перекрестная таблица	Заголовок строки	Заголовок столбца	Значения
Сортировка	По возрастанию
Условие

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

ü заголовки строк

ü заголовки столбцов

ü значение (выводится в ячейках таблицы)

ü не выводить (предназначена для ввода условий отбора)

Результаты выполнения данного запроса будет таблица ниже:

Код товара	3.01.00	5.01.00	2.02.00	4.03.00	10.01.00

Можно в строке Условие поле Дата продажи ввести *.03.*, тогда таблица будет состоять из заголовков строк и двух столбцов (04.03.00 10.03.00).

Пусть нас интересует суммарное значение по каждому месяцу.

Тогда, вместо поля [Дата продажи], в качестве заголовка столбцов необходимо ввести вычисляемое поле.

Format ([Заказы][Дата продажи];”mmm-yyyy”)

Данная функция одновременно определяет

- формат ввода даты:

mmm – месяц апрель, май, июнь

mmmm – полное название месяца

уу – год 00, 99

уууу – год 1999, 2000

- уровень группировки.

Например:

- если в данной функции ввести format ([Дата продажи];”dd-mmmm-yyyy”), то группировка будет выполняться по дням (так же как получилось в первоначальном варианте запроса);

- если ввести Format ([Дата продажи];”уууу”), то будут суммироваться продажи по каждому товару за год.

В нашем варианте запрос будет иметь вид:

Код товара	Январь- 00	Декабрь - 00	Март – 00

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

Если мы хотим указать, что данный запрос отражает только те заказы, которые выполнены (то есть [Заказано]=[Продано]), то в бланк запроса нужно добавить поле З аказано дважды (для поля, являющегося Значением перекрестной таблицы, Условие отбора задавать нельзя). В повторно добавленном с запрос поле Заказано в строке Перекрестная таблица нужно выбрать режим (не отображаются), так в строке Условие - [Заказано]=[Продано]. Тогда на экран выведутся только те товары, по которым выполняются заказы.

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

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

ü запросы создания таблиц;

ü запросы удаления;

ü запросы добавления;

ü запросы обновления.

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

Запрос на удаление

Например, мы хотим удалить из таблицы все Заказы, совершенные до 1 апреля 1999 года. Для этого необходимо:

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

Имя таблицы   ОК   Параметры вставки О только структура О структура и данные О добавление данных в таблицу

Напишем, например, имя Временная.

2) Далее следует перейти во вкладку Запросы и открыть окно Конструктора запросов. В окне Добавление таблицы добавить таблицу Заказы.

3) В бланке запроса добавить все поля таблицы Заказы (знак *, находящийся в верхней части списка полей таблицы), а затем отдельно поля Дата заказа, задать условие: <#01.04.99#.

4) Проверить выполнение запроса, убедившись, что условие задано верно (кнопка Вид).

5) Выполнить преобразование запроса-выборки в запрос-действие. Для этого необходимо вызвать пункт меню Запрос или кнопку Тип запроса:

Выборка Перекрестный Создание таблицы Обновление Добавление Удаление

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

Поле	Заказы. *	Дата заказа
Имя таблицы	Заказы	Заказы
Удаление	Из	Условие
Условие отбора		<#01.04.99#

6) Запустить запрос с помощью кнопки!. Будет выдано сообщение о том, сколько строк будет удалено.

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

Выполнение запроса-обновления

Запросы-обновления позволяют изменить значение выбранного поля по всем записям таблицы, удовлетворяющим определенным условиям.

Например, есть таблица:

Дата добавления: 2014-01-06