Перекрёстные запросы

Тема 10. ПЕРЕКРЕСТНЫЕ ЗАПРОСЫ

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

уу – год 00, 99

уууу – год 1999, 2000

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

Нарпимер:

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

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

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

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

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

Тема 10. МОДИФИКАЦИЯ ДАННЫХ С ПОМОЩЬЮ ЗАПРОСОВ-ДЕЙСТВИЙ

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Поставляемые товары

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

Для этого:

1) добавляем в окно Конструктора нового запроса таблицу Поставляемые товары.

2) выбираем команду Запрос/Обновление.

3) создаем следующий бланк запроса.

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

5) вернувшись в режим Конструктора запросов, нажать кнопку Запуск (!).

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

Пример 1: вам нужно в таблице Заказы для записей, данные продажи которых датируется сегодняшним числом, а поле Продано = 0. продано поставить значение поля Заказано.

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

Пример 2.

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

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

Вставка данных из другой таблицы

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

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

Например, в базе данных Тел.-адр. книга хранятся адреса клиентов, некоторые из которых временно не вошли в состав таблицы Клиенты базы данных Заказы. Теперь их нужно добавить из таблицы Тел.-адр. книга в таблицу Клиенты (например, тех кто работает в СП*).

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

1) открыть базу данных Тел.-адр. книга

2) открыть окно Конструктора запросов и добавить в бланк Тел.-адр. книга

3) выполнить команду Запрос/Добавление или кнопка Тип запроса/Добавление:

Поскольку информацию из Тел.-адр. книги мы хотим добавить в таблицу другой базы данных, необходимо выбрать переключатель "В другую базу данных", затем указать имя файла (Заказы.mdb) и выбрать из списка имя таблицы Клиенты.

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

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

5) перейти в режим Таблицы и проверить правильность выбранных данных, и только потом запустить запрос (!).

Будет выдано сообщение о количестве добавляемых записей.

Создание новой таблицы с помощью запроса

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

Тогда:

1) необходимо создать запрос следующего содержания:

2) после этого изменить тип запроса на Запрос/Создание таблицы или Тип  запроса/Создание таблицы. Последует запрос:

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

3) запустить запрос на выполнение.

Преобразуем данное условие следующим образом:

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

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

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

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

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