Создание запросов на удаления записей в Access

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

Если две таблицы связаны отношением "один-ко-многим", нельзя удалять запи- си из таблицы "один" если в таблице "многие" присутствуют соответствующие им записи. Сначала должны быть удалены записи в таблице "многие" и только потом – соответствующие им записи в таблице "один". Для того чтобы упростить этот процесс, Access позволяет при определении связей между таблицами установить флажок Каскадное удаление связанных записей (Cascade Delete Related Records). Такой флажок установлен, например, для связи таблиц "Заказы" (Orders) и "Заказано" (Order Details). Действительно, если требуется удалить из базы данных какой-то заказ, должны быть удалены не только запись об этом заказе в таблице "Заказы", но и все позиции данного заказа в таблице "Заказано".

Для создания запроса на удаления мы воспользуемся уже имеющимся запросом на добавление записей "Копирование заказов".

1. Откройте этот запрос в режиме Конструктора.
2. Чтобы преобразовать запрос на добавление в запрос на удаление записей, выберите команду Запрос → Удаление (Query → Delete Query). В бланке запроса появится строка Удаление (Delete) (рис. 1). В первом столбце строки Удаление (Delete) показывается значение Из (From), которое указывает, что будут удаляться записи из таблицы "Заказы". Во втором столбце строки Удаление (Delete) вы видите значение Условие (Where), что указывает на использование этого столбца для определения критерия отбора удаляемых записей.

   
   Рис. 1. Запрос на удаление записей из таблицы "Заказы"

3. Нажмите кнопку Запуск (Run). Выводятся сообщение с приглашением подтвердить удаление записей и информация о количестве записей, которые будут удалены. Их будет столько же, сколько было отобрано в соответствующем запросе на добавление. В этот момент еще можно отменить удаление, для чего достаточно нажать кнопку Нет (No) в окне сообщения. Нажмите кнопку Да (Yes), подтверждая удаление. Будут удалены все заказы за 1996 год из таблицы "Заказы" (Orders) и все позиции заказов из таблицы "Заказано" (Order Details). Вы можете убедиться в этом, если откроете таблицу "Заказано".
4. Сохраните запрос, дав ему имя: Удаление заказов.

Однако такое каскадное удаление записей не всегда возможно. Например, для рассмотренной нами в предыдущем разделе связи таблиц "Сотрудники" и "Заказы" нельзя устанавливать флажок Каскадное удаление связанных записей (Cascade Delete Related Records), т. к. удаление записи о сотруднике из таблицы "Сотрудники" не должно повлечь за собой удаление всех его заказов. Если вы попытаетесь удалить какую-либо запись о сотруднике и в таблице "Заказы" (Orders) окажутся заказы, принятые этим сотрудником, Access выдаст сообщение об ошибке (рис. 2).

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

Замечание
Для того чтобы выполнялась автоматическая проверка целостности данных, не забудьте для всех связей в базе данных установить флажок Обеспечение целостности данных (Enforce Referential Integrity).

Анализ данных с помощью запросов. Перекрестные запросы.

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

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

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

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

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

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

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

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

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

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

1. Создайте новый запрос в режиме Конструктора и добавьте в него таблицы "Товары" (Products), "Заказы" (Orders) и "Заказано" (Order Details).
2. Перетащите поля "КодТовара" (ProductlD) и "Марка" (ProductName) таблицы "Товары", а затем поле "ДатаРазмещения" (OrderedDate) таблицы "Заказы" в первые три столбца бланка запроса.
3. Выберите команду меню Запрос → Перекрестный (Query → Crosstab Query). Заголовок окна запроса Запрос1:на выборку (Query1:Select Query) изменится на Запрос1: перекрестный запрос (Query1: Crosstab Query). Кроме того, в бланк запроса будут добавлены строки Перекрестная таблица (Crosstab) и Групповая операция (Total), в которую во всех столбцах автоматически вводится операция Группировка (Group By).
4. Выберите в списке ячейки Перекрестная таблица столбца "КодТовара" значение Заголовки строк (Row Heading). Выполните то же самое для столбца "Марка". Эти столбцы являются требуемыми заголовками строк перекрестной таблицы.
5. Выберите в списке ячейки Групповая операция столбца "ДатаРазмещения" значение Условие (Where). В ячейке Условие отбора (Criteria) этого столбца введите выражение:

   ← #31.12.97# And >=#01.01.97#  

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

6. Установите курсор в ячейку Поле (Field) следующего (пустого) столбца и введите выражение:

   Объем продаж: Sum([Количество]*[Заказано].[Цена])  

   Это будет поле, значение которого вычисляется с помощью описанного выражения, а название поля – "Объем продаж". В выражении используются поля из таблиц, которые включены в запрос, однако обратите внимание, что сами поля в результат запроса не включены. Это означает, что при создании вычисляемого поля в выражение можно включать ссылки не только на поля самого запроса, но и на поля, которые не включаются в результат запроса. Важно, чтобы они были в исходных таблицах. При ссылке на поле "Цена" в выражении мы указали еще имя таблицы "Заказано", а при ссылке на поле "Количество" не указывали. Указать имя таблицы пришлось потому, что поле с именем "Цена" присутствует и в таблице "Товары" и в таблице "Заказано". Если не указать в выражении имени таблицы, Access не сможет определить, из какой таблицы брать значения, поэтому при выполнении запроса выдаст сообщение об ошибке, как это представлено на рис. 3.

   
   Рис. 3. Сообщение об ошибке в выражении вычисляемого поля

   Выберите в ячейке Групповая операция (Total) того же столбца значение Выражение (Expression), а затем в ячейке Перекрестная таблица – значение Значение (Value). В столбце "Объем продаж" вычисляется общий объем заказов на каждый товар, который будет подставляться в ячейки результирующей таблицы запроса.

Сводные таблицы

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

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

Создание сводной таблицы

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

1. Щелкните по стрелке на кнопке Вид (View) панели инструментов и выберите из списка элемент Сводная таблица (PivotTable View). На экране появится макет будущей таблицы, который состоит из четырех областей. Каждая область имеет надпись, которая показывает, какие данные должны в ней помещаться (рис. 4).
2. Чтобы удобнее было создавать таблицу, необходимо отобразить на экране список полей запроса. Этот список показывается в специальном окне Список полей сводной таблицы (PivotTable Field List) (рис. 4, справа). Если он не виден на вашем экране, нажмите кнопку Поля (Field List) на панели инструментов. Теперь требуется просто перетащить мышью поля в соответствующие области таблицы.
3. Найдите в списке поле "Страна" (Country) и переместите его в верхнюю область таблицы, которая называется областью фильтра. После этого вы сможете фильтровать данные в таблице по странам клиентов. В сводной таблице появляется еще одно измерение – глубина – благодаря которому вы можете просматривать и анализировать данные по странам. Каждый срез таблицы будет отображать данные по выбранной стране.
4. Выделите в списке полей поле "Марка" (ProductName) и перетащите его в крайнюю левую область. Эта область называется областью строк и будет содержать список всех товаров, на которые были выписаны счета. Заметьте, что этот список по умолчанию упорядочивается по наименованию товара и последней строкой является строка "Общие итоги" (Grand Total), содержащая общий итог по всем строкам таблицы.

   
   Рис. 4. Макет сводной таблицы

Отбор повторяющихся записей и записей без подчиненных

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

1. Раскройте список запросов в окне базы данных и нажмите кнопку Создать (New). В диалоговом окне Новый запрос (New Query) выберите из списка элемент Повторяющиеся записи (Find Duplicates Query Wizard). Появится диалоговое окно мастера поиска повторяющихся записей (рис. 5).
2. Выберите из списка таблицу "Клиенты" (Customers) (по умолчанию в окне отображается именно список таблиц, но можно установить флажок и получить список запросов или таблиц и запросов вместе).
3. Следуйте указаниям мастера (они подробно описаны в диалоговых окнах), и в результате вы получите запрос, который в режиме Конструктора может выглядеть так, как представлено на рис. 6.

   
   Рис. 5. Диалоговое окно Мастера поиска повторяющихся записей

   
   Рис. 6. Запрос, отбирающий повторяющиеся записи в таблице "Клиенты"

Рекомендуем в повседневной деятельности: