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

• Глава 8. Редактирование и анализ данных с помощью запросов.
  • Создание таблиц с помощью запроса
    • Преобразование запроса на выборку в запрос на изменение
    • Выполнение запроса на изменение
  • Создание запросов на добавление данных
  • Создание запросов на обноачение таблиц
  • Создание запросов на удаления записей
  • Анализ данных с помощью запросов
    • Перекрестные запросы
      • Использование фиксированных заголовков столбцов в перекрестных запросах
      • Изменение уровня детализации в запросе
    • Статистические функции для выполнения расчетов
      • Вычисления на всем диапазоне записей
      • Вычисления на выделенных записях таблицы
    • Отбор повторяющихся записей и записей без подчиненных
    • Сводные таблицы
      • Создание сводной таблицы
      • Фильтрация данных сводной таблицы
      • Изменение сводной таблицы
      • Сортировка в сводной таблице
      • Изменение уровня детализации
      • Общие и промежуточные итоги
      • Форматирование сводной таблицы
    • Сводные диаграммы
      • Создание сводной диаграммы
      • Изменение вида диаграммы
      • Форматирование элементов сводной диаграммы
  • Настройка свойств полей в запросе и свойств самого запроса
  • Создание запросов SQL
    • Сравнение ANSI и Jet SQL
      • Зарезервированные слова Jet SQL
      • Функции и операторы Access, используемые вместо ключевых слов ANSI SQL
      • Зарезервированные слова Jet SQL и функции, не входящие в ANSI SQL
      • Эквивалентные типы данных Jet SQL и ANSI SQL
      • Подстановочные знаки при сравнении строк
      • Зарезервированные слова DISTINCTROWu. DISTINCT
      • Использование разделителей и символов SQL
    • Использование инструкций SQL для создания запросов
      • Создание запросов на объединение записей
      • Создание запроса к серверу
      • Управляющие запросы
    • Создание подчиненных запросов
  • Выводы

Глава 8.

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

В первой части книги мы познакомили вас с одним из важнейших объектов базы данных Access — запросами. При этом рассматривался только один вид запросов — запросы на выборку данных из таблиц. Microsoft Access, однако, позволяет не только выбирать нужные данные, но и изменять их с помощью специальных запросов. Такие запросы называются запросами на изменение (Action Query). Существует четыре типа запросов на изменение.

• Запросы на создание таблицы (Make-Table Query) создают таблицы на основе данных, содержащихся в результирующем множестве запроса. Чаще всего этот тип используется для экспорта информации в другие приложения. Кроме того, запросы на создание таблицы удобно применять для копирования таблиц в другую базу данных. В некоторых случаях с их помощью можно повысить быстродействие форм и отчетов, базирующихся на одном сложном запросе.
  
• Запросы на добавление записей (Append Query) позволяют добавить в таблицу создаваемые запросом записи.
  
• Запросы на обновление (Update Query) изменяют значения полей в записях таблицы, отобранных с помощью запроса.
  
• Запросы на удаление (Delete Query) удаляют из таблицы записи, соответствующие результирующему множеству запроса.
  

Если таблицы содержат большое количество данных, важно не только извлечь нужные данные, но и как-то обработать их, т. е. представить в виде, удобном для анализа. В предыдущих версиях Access для этого существовал специальный вид запросов — перекрестные запросы. В Access 2002 для анализа данных можно применять сводные таблицы — мощное и удобное средство анализа, уже давно прекрасно зарекомендовавшее себя в Microsoft Excel. Сводные данные можно теперь представить не только в табличном, но и в графическом виде с помощью сводных диаграмм.

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

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

Итак, в этой главе описывается:

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

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

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

1. Создать запрос на выборку.
   
2. Преобразовать запрос на выборку в запрос на изменение, задав параметры размещения новой таблицы.
   
3. Выполнить запрос на изменение, тем самым поместив отобранные записи в новую таблицу.
   

Созданию различных запросов на выборку мы уделили достаточно внимания в гл. 4. Оставшиеся два этапа описаны ниже на примере запроса на выборку "Холодные клиенты", созданного в базе данных "Борей" при описании внешнего соединения в гл. 4.

1. Откройте запрос "Холодные клиенты" в режиме Конструктора, выделив его имя в списке запросов в окне базы данных и нажав кнопку Конструктор (Design).
   
2. Добавьте в бланке запроса критерий выборки: Is Null в столбце "КодЗаказа".
   
3. Сбросьте флажок Вывод на экран (Output) для этого поля.
   

Замечание

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

Преобразование запроса на выборку в запрос на изменение

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

1. Выберите команду меню Запрос, Создание таблицы (Query, Make Table) [Меню Запрос доступно только в режиме Конструктора запроса.] или щелкните по стрелке на кнопке Тип запроса (Query Type) на панели инструментов и выберите из списка тип Создание таблицы (Make-Table Query). Диалоговое окно Создание таблицы (Make Table) показано на рис. 8.1.
   
2. В текстовом поле имя таблицы (Table Name) введите описательное имя для новой таблицы, например: список холодных клиентов.
   

Внимание

В базе данных Access не должно быть таблицы и запроса с одинаковыми именами.

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

3. Нажмите кнопку ОК. Запрос на выборку преобразуется в запрос на создание таблицы.
   

Рис. 8.1. Диалоговое окно Создание таблицы

Рис. 8.2. Запрос на изменение в окне базы данных

4. Нажмите стрелку на кнопке Вид (View) панели инструментов и выберите из списка элемент SQL View (Режим SQL). Вы увидите инструкцию SQL, которая генерируется автоматически, когда вы в графическом режиме создаете запрос. Она отличается от обычной инструкции SELECT тем, что содержит еще одно предложение INTO...
   
5. Сохраните и закройте запрос. Теперь в окне базы данных ему соответствует другой значок, который сопровождается восклицательным знаком (рис. 8.2), это означает, что запрос является запросом на изменение.
   

Выполнение запроса на изменение

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

Для выполнения запроса:

1. Запустите запрос двойным щелчком мышки на его имени в окне базы данных (см. рис. 8.2). При этом он не отображает результирующий набор записей в режиме таблицы, как это делает запрос на выборку, а выполняет нужное действие. Перед его выполнением появляется сообщение, которое предупреждает, что в новую таблицу будут внесены изменения.[Хотя таблица еще не создана.]
   

Внимание

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

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

Рис. 8.3. Предупреждающее сообщение о числе новых записей

3. Нажмите кнопку Да (Yes). Поскольку запрос запускается впервые, будет создана новая таблица "Список холодных клиентов".
   
4. Щелкните по ярлыку Таблицы (Tables) в окне базы данных и убедитесь, что в /t. списке появилось новое название таблицы (рис. 8.4).
   

Рис. 8.4. Новая таблица в окне оазы данных

5. Дважды щелкните мышью по значку этой таблицы, чтобы ее открыть. Она содержит те же данные, что и запрос на создание таблицы в режиме Таблицы (рис. 8.5).
   

Рис. 8.5. Таблица, созданная при помощи запроса

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

Создание запросов на доьавление данных

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

В качестве примера мы рассмотрим запрос, который будет выбирать из базы данных все заказы за 1996 год и переносить их в другую таблицу так, чтобы таблица "Заказы" (Orders) содержала только актуальные данные. Для этого сначала создадим таблицу, которая будет хранить устаревшие данные:

1. Раскройте список таблиц в окне базы данных и выделите таблицу "Заказы" (Orders).
   
2. Скопируйте таблицу в буфер обмена, нажав комбинацию клавиш <Ctrl>+<C>.
   
3. Вставьте таблицу из буфера обмена, нажав комбинацию клавиш <Ctrl>+<V>. Появляется диалоговое окно Вставка таблицы (Paste Table As).
   
4. В группе Параметры вставки (Paste Options) выберите переключатель Только структура (Structure Only).
   
5. В поле имя таблицы (Table Name) введите строку: Заказы (архив). Нажмите кнопку ОК или клавишу <Enter>.
   

В списке появляется новая таблица, пока пустая, т. к. мы скопировали только структуру таблицы "Заказы" (Orders). Теперь создадим запрос на добавление, который позволит перенести в новую таблицу данные из таблицы "Заказы".

1. Раскройте список таблиц в окне базы данных и выделите в нем таблицу "Заказы".
   
2. Щелкните левой кнопкой мыши по стрелке на кнопке Новый объект (New Object) инструментальной панели и выберите из раскрывшегося списка элемент Запрос (Query). В диалоговом окне Новый запрос (New Query) выберите значение Конструктор (Design View). Появляется окно Конструктора запроса с таблицей "Заказы" в верхней части.
   
3. Сначала создайте запрос на выборку нужных записей. Перетащите из списка полей таблицы "Заказы" все поля в строку бланка запроса Поле (Field).
   
4. В строке Условие отбора (Criteria) столбца "ДатаРазмещения" введите выражение
   Between 101.01.961 And 131.12.961
   
5. Запустите запрос на выборку, чтобы проверить, правильно ли отобраны записи. Оказались отобранными 152 записи.
   
6. Выберите команду Запрос, Добавление (Query, Append) или щелкните левой кнопкой мыши по стрелке на кнопке Тип запроса (Query Type) и выберите из списка элемент Добавление (Append Query). Появляется диалоговое окно Добавление (Append), аналогичное диалоговому окну Создание таблицы (Make Table) (рис. 8.6).
   
7. В поле имя таблицы (Table Name) необходимо выбрать из списка имя таблицы, в которую будут добавляться записи. В данном случае по умолчанию в нем указана таблица "Заказы (архив)", которую мы только что создали. Нажмите кнопку ОК. (Если бы таблица, в которую нужно добавить записи, находилась в другой базе данных, необходимо было бы выбрать переключатель в другой базе данных (Another Database) и затем в поле имя файла (File Name) указать имя файла MDB, который содержит требуемую таблицу.)
   

Рис. 8.6. Диалоговое окно Добавление

8. В бланке запроса на добавление появляется дополнительная строка Добавление (Append To), содержащая названия полей таблицы, в которую добавляются записи (рис. 8.7). В данном случае названия полей запроса и названия полей таблицы совпадают, поэтому по умолчанию вся эта строка заполнена. Если имена полей не совпадают, необходимо выбрать из раскрывающегося списка в строке Добавление (Append To) имя поля результирующей таблицы, соответствующего полю в исходной таблице.
   
9. Нажмите кнопку Запуск (Run), чтобы выполнить запрос. Прежде чем новые записи будут добавлены в таблицу, Access выдает сообщение о числе записей, которые предполагается добавить. Вы можете нажать кнопку Да (Yes), и только после этого записи будут добавлены. Если вы нажмете кнопку Нет (No), вставка записей будет отменена.
   

Замечание

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

Рис. 8.7. Бланк запроса на добавление

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

Создание запросов на обновление таблиц

Запросы на обновление используются главным образом для того, чтобы внести изменения сразу в большое количество записей с помощью одного запроса. Классическим примером такого запроса является изменение цены в таблице "Товары" (Products) на некоторый постоянный коэффициент, например можно увеличить цену одной из категорий товара на 20%. Чтобы создать такой запрос:

1. Раскройте список таблиц, щелкнув мышью по ярлыку Таблицы (Tables) окна базы данных.
   
2. Выделите таблицу "Товары" (Products). Щелкните левой кнопкой мыши по стрелке на кнопке Новый объект (New Object) на панели инструментов и выберите из списка значение Запрос (Query). Появится окно Конструктора запросов с таблицей "Товары" в верхней части. Перенесите в бланк запроса поля "Цена" и "КодТипа".
   
3. Введите условие отбора записей: например, в столбец "КодТипа" введите значение 1.
   
4. Выполните запрос, чтобы убедиться, что отбираются все записи, содержащие напитки.
   
5. Теперь изменим запрос, превратив его в запрос на обновление. Для этого выполните команду меню Запрос, Обновление (Query, Update). Изменяется заголовок запроса и появляется дополнительная строка Обновление (Update To). При этом исчезают строки Сортировка (Sort) и Вывод на экран (Show) (рис. 8.8).
   
6. Теперь нужно в строку Обновление (Update To) ввести выражение, по которому будет вычисляться новая цена: [Цена]* 0, 2. В других случаях можно вводить константу, например, если нужно поменять дату во многих записях на текущую.
   
7. Теперь можно выполнить запрос. Для этого нажмите кнопку Запуск (Run) на панели инструментов. Так же, как и при добавлении записей в таблицу, Access выдаёт сообщение о количестве обновляемых записей и запрашивает подтверждение на обновление. Вы можете подтвердить обновление записей или отвергнуть.
   

Рис. 8.8. Запрос на обновление записей

Совет

Прежде чем выполнять запрос на обновление, щелкните левой кнопкой мыши по стрелке на кнопке Вид (View) и выберите Режим таблицы (Datasheet View). Вы увидите все записи, которые будут обновлены, хотя данные в обновляемых полях будут еще старые. И только убедившись, что отобраны нужные записи, можно выполнять запрос.

В приведенном примере изменялись значения в поле, которое не является первичным ключом в таблице "Товары" (Products). Особый случай возникает, когда требуется обновить значение первичного ключа в таблице. Если эта таблица связана отношением "один-ко-многим" с другими таблицами, то при изменении первичного ключа записи должны одновременно измениться значения внешних ключей во всех связанных записях подчиненных таблиц. Access обеспечивает выполнение такого изменения автоматически, т. к. поддерживает каскадное обновление записей. При определении связи между таблицами можно установить флажок каскадное обновление связанных полей (Cascade Update Related Fields) (см. разд. "Поддержка целостности данных" гл. 2).

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

Убедиться в том, как работает такой запрос, можно на примере таблиц "Сотрудники" (Employees) и "Заказы" (Orders). Для этого нам придется сначала сделать копии этих таблиц и установить связь между ними:

1. Раскройте список таблиц в окне базы данных и выделите таблицу "Сотрудники".
   
2. Скопируйте таблицу в буфер обмена, нажав комбинацию клавиш <Ctrl>+<C>.
   
3. Вставьте таблицу из буфера обмена, нажав комбинацию клавиш <Ctrl>+<V>. Появится диалоговое окно Вставка таблицы (Paste Table As).
   
4. В поле Имя таблицы (Table Name) введите строку: Сотрудники (копия). Оставьте предлагаемое по умолчанию значение переключателя в группе Параметры вставки (Paste Options). Нажмите кнопку ОК или клавишу <Enter>. В списке появится новая таблица.
   
5. Выполните шаги 1—4 для таблицы "Заказы", создав таблицу "Заказы (копия)".
   
6. Для выполнения примера нам придется немного изменить таблицу "Заказы (копия)". Поле "КодСотрудника" в таблице "Заказы" является полем подстановки, т. е. хотя в этом поле содержатся коды сотрудников, при отображении таблицы в этом поле показываются фамилия и имя сотрудника. Мы можем временно удалить поле подстановки, чтобы видеть результаты каскадного обновления данного поля. Для этого достаточно открыть таблицу "Заказы (копия)" в режиме Конструктора, выделить строку "КодСотрудника", раскрыть в панели Свойства поля (Field Properties) вкладку Подстановка (Lookup) и установить значение свойства Тип источника строк (Row Source Type) равным Поле (Text box). Сохраните изменение.
   
7. Еще одно изменение потребуется ввести в таблицу "Сотрудники (копия)". Поле "КодСотрудника" в ней имеет тип Счетчик (AutoNumber), поэтому Access не позволит изменить значение в этом поле. Откройте эту таблицу в режиме Конструктора и замените тип данных для поля "КодСотрудника" на Числовой (Number).
   
8. Теперь нужно установить связи между новыми таблицами. Нажмите кнопку Схема данных (Relationships) на панели инструментов для вывода окна Схема данных (Relationships).
   
9. Нажмите кнопку Очистить макет (Clear Layout), чтобы очистить окно схемы данных. Подтвердите свое намерение в окне сообщения, нажав кнопку Да (Yes).
   
10. Добавьте таблицы "Сотрудники (копия)" и "Заказы (копия)" с помощью диалогового окна Добавление таблицы (Show Table). Для этого нажмите соответствующую кнопку на панели инструментов.
    
11. Чтобы установить связь "один-ко-многим" между таблицами, перетащите поле "КодСотрудника" таблицы "Сотрудники (копия)" в соответствующее поле таблицы "Заказы (копия)". Появится диалоговое окно Изменение связей (Edit Relationship).
    
12. В этом окне необходимо установить флажки Обеспечение целостности данных (Enforce Referential Integrity) и каскадное обновление связанных полей (Cascade Update Related Fields) (рис. 8.9). Нажмите кнопку ОК.
    
13. Закройте окно Схема данных (Relationships).
    

Рис. 8.9. Диалоговое окно Изменение связей

Для выполнения каскадного обновления:

1. Откройте таблицы "Сотрудники (копия)" и "Заказы (копия)" и разместите их на экране так, чтобы были видны обе таблицы (рис 8.10).
   
2. Отсортируйте таблицу "Заказы (копия)" по столбцу "Сотрудник". Для этого выделите данный столбец, щелкнув левой кнопкой мыши по его заголовку, и нажмите кнопку Сортировка по возрастанию (Sort Ascending) на панели инструментов.
   
3. Измените значение в столбце "Код сотрудника" первой записи таблицы "Сотрудники (копия)", введя число 10, и нажмите клавишу <Ф>, чтобы перейти к следующей записи. Изменение кода сразу же будет отображено в столбце "Сотрудник" таблицы "Заказы (копия)".
   

Рис. 8.10. Каскадное обновление записей

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

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

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

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

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

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

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

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

Рис. 8.12. Сообщение о невозможности удаления записей

Замечание

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

Анализ данных с помощью запросов

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

7. Установите курсор в ячейку Поле (Field) следующего (пустого) столбца и введите выражение Format([ДатаРазмещения];"mmm"). Функция Format О в данном случае будет возвращать три первые буквы месяца от значения даты в столбце "ДатаРазмещения". С помощью этой функции мы создали еще одно вычисляемое поле в запросе, причем после окончания ввода этого выражения Access автоматически создает имя этого поля "Выражение!" (Exprl). Так происходит всякий раз, когда мы не определяем имя вычисляемого поля. Выберите из списка в ячейке Перекрестная таблица этого столбца значение Заголовки столбцов (Column Heading) (рис. 8.14). Это означает, что данный столбец запроса будет преобразован в процессе выполнения запроса в строку, содержащую заголовки столбцов результирующей таблицы.
   

Рис. 8.14. Перекрестный запрос в режиме Конструктора

8. Нажмите кнопку Запуск (Run). Появится результирующее множество записей, представленное на рис. 8.15.
   

Рис. 8.15. Результирующее множество перекрестного запроса

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

Использование фиксированных заголовков столбцов в перекрестных запросах

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

1. Переключитесь в режим Конструктора и выведите на экран свойства запроса. Для этого нажмите кнопку Свойства (Properties) на панели инструментов и щелкните левой кнопкой мыши на свободном поле панели, в которой отображаются таблицы запроса. В диалоговом окне свойств запроса выводится свойство Заголовки столбцов (Column Headings), которое имеется только у перекрестных запросов.
   
2. Введите краткие названия каждого месяца года в поле свойства Заголовки столбцов (рис. 8.16). Необходимо воспроизвести названия так, как они возвращаются функцией Format () и выглядят при стандартном отображении заголовков столбцов (см. рис. 8.15), в противном случае, данные для неправильно заданных месяцев не отображаются. Названия требуется заключить в кавычки и разделить символом точки с запятой. Пробелы не нужны. По завершении ввода названий всех 12 месяцев закройте окно свойств.
   
3. Перейдите в режим просмотра SQL (рис. 8.17). Обратите внимание на использование выражений в вычисляемых полях и критерии запроса, а также ключевых слов TRANSFORM И PIVOT. Инструкция SQL содержит операцию TRANSFORM, в которой определяются данные, содержащиеся в таблице. В операции PIVOT задаются заголовки столбцов.
   

После ключевого слова IN указываются, если они есть, фиксированные названия столбцов.

Рис. 8.16. Задание значения свойства Заголовки столбцов

Рис. 8.17. Перекрестный запрос на SQL

Замечание

В ANSI SQL слова TRANSFORM и PIVOT не являются зарезервированными словами, а ключевое слово IN интерпретируется по-разному в Access и ANSI SQL. Обратите также внимание на формат даты в инструкции SQL: сначала пишется номер месяца, а потом число месяца, разделителем является косая черта, и все выражение содержится между значками #. Именно в таком формате должны быть всегда написаны даты в предложении SQL, хотя, как вы видели, в бланке запроса можно писать даты в привычном нам европейском формате. Нужное преобразование Конструктор выполняет автоматически.

4. Нажмите кнопку Запуск (Run) для вывода результатов запроса. Теперь заголовки столбцов перекрестной таблицы соответствуют календарной последовательности (рис. 8.18).
   
5. Выберите команду Файл, Сохранить как (File, Save As) и сохраните запрос под именем "Ежемесячная выручка от продаж в 1997 году".
   

Рис. 8.18. Перекрестная таблица с фиксированными заголовками столбцов

Изменение уровня детализации в запросе

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

1. Добавьте в уже созданный запрос таблицу "Типы" (Categories).
   
2. Замените столбцы "КодТовара" и "Марка" в запросе столбцами "КодТипа" и "Категория" таблицы "Типы". Для этого сначала удалите лишние столбцы, а затем перенесите нужные столбцы в бланк заказа. В ячейках Перекрестная таблица (Crosstab) обоих столбцов выберите значение Заголовки строк (Row Heading) (рис. 8.19).
   
3. Измените выражение в столбце Выражение! (Expl) Format([ДатаРазмещения];"""Квартал ""q") , которое определяет заголовки столбцов "Квартал 1", "Квартал 2", "Квартал 3", "Квартал 4". Несколько кавычек требуется для указания того, что слово Квартал и пробел рассматриваются как строка, a q — как формат.
   
4. Откройте бланк свойств и очистите содержимое свойства Заголовки столбцов (Column Headings), а затем закройте бланк. Если не удалить фиксированные заголовки, то в перекрестной таблице не будет данных. Чтобы ускорить работу запроса, можно задать в поле свойства Заголовки столбцов четыре фиксированных названия: "Квартал 1", "Квартал 2", "Квартал 3", "Квартал 4".
   
5. Нажмите кнопку Запуск (Run) для вывода результатов запроса (рис. 8.20).
   

Рис. 8.19. Запрос для вывода выручки от продаж по типам товаров

Рис. 8.20. Перекрестная таблица с итогами по кварталам

Статистические функции для выполнения расчетов

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

Статистические функции, которые можно использовать в запросах Access, входят в состав Access SQL. Описание этих функций, а также типов полей, к которым они могут быть применены, приведено в табл. 8.1.

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

Замечание

Все статистические функции Access SQL поддерживаются стандартом ANSI SQL, но не все из них поддерживаются другими СУБД. Например, не все серверные базы данных поддерживают функции StDev(), StDevP(), Var () и VarP (). Поэтому, если вы планируете в дальнейшем перенести свою базу данных на сервер, прежде чем использовать эти функции, нужно убедиться, поддерживаются ли они сервером. Иначе потом придется изменять все объекты, в которых встречаются эти функции.

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

Вычисления на всем диапазоне записей

Первый запрос, который мы предлагаем построить, должен ответить на вопрос: "Сколько заказов и на какую сумму разместил каждый клиент?". Для этого нам придется построить многотабличный запрос. Воспользуемся уже существующим в базе данных "Борей" запросом "Сведения о заказах" (Oreder Details Extended), а также таблицами "Клиенты" (Customers) и "Заказы" (Orders):

1. Создайте новый запрос и добавьте в него таблицы "Клиенты", "Заказы" и запрос "Сведения о заказах".
   
2. Перетащите в бланк заказа поле "Название" из таблицы "Клиенты", поле "КодЗаказа" из таблицы "Заказы", а затем поле "Отпускная цена" из запроса "Сведения о заказах".
   
3. Нажмите кнопку Групповые операции (Totals) на панели инструментов. В бланк запроса добавляется строка Групповая операция (Total), содержащая по умолчанию в каждой ячейке операцию Группировка (Group By).
   
4. Теперь нужно в строке Групповая операция (Total) задать необходимые статистические функции. Для поля "Название" оставьте значение Группировка (Group By), для поля "КодЗаказа" выберите из списка функцию count, а для поля "Отпускная цена" — Sum (рис. 8.21).
   
5. Нажмите кнопку Запуск (Run) на панели инструментов, чтобы посмотреть результаты запроса. Вы получите таблицу, которая содержит список клиентов, и для каждого клиента указывается количество заказов, которое он сделал, и общая сумма этих заказов (рис. 8.22).
   
6. Закройте запрос, сохранив era под именем "Итоги по клиентам".
   

Рис. 8.21. Использование статистических функций в запросе

Рис. 8.22. Запрос "Итоги по клиентам"

Вычисления на выделенных записях таблицы

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

1. Откройте запрос "Итоги по клиентам", который мы построили в предыдущем разделе, в режиме Конструктора.
   
2. Перетащите поле "Страна" (Country) из таблицы "Клиенты" (Customers) в бланк запроса. В строке Групповая операция (Total) для этого поля появляется элемент Группировка (Group by).
   
3. Замените элемент Группировка (Group by) на элемент Условие (Where), выбрав его из списка (рис. 8.23). При этом автоматически сбрасывается флажок (Show) в данном столбце. Это означает, что в результат запроса поле "Страна" не попадет. Если вы попытаетесь установить этот флажок, то при выполнении запроса будет выведено сообщение об ошибке (рис. 8.24). Действительно, если мы хотим, чтобы поле "Страна" отобразилось в результате запроса, его нужно добавить в бланк запроса второй раз, при этом в строке Групповая операция (Total) данного столбца нужно оставить элемент Группировка (Group by) и установить флажок Вывод на экран (Show).
   
4. Введите слово Германия в ячейку Условие отбора (Criteria) первого столбца "Страна" (того, который не выводится на экран).
   
5. Выберите команду Вид, Режим SQL (View, SQL View) или щелкните по стрелке на кнопке Вид (View) и выберите значение Режим SQL (SQL View) для вывода инструкции SQL. Она будет выглядеть следующим образом: SELECT Клиенты.Название, Count(Заказы.КодЗаказа) AS [CountOfKoд3a каза], Sum([Сведения о заказах].ОтпускнаяЦена) AS [SumOfОтпускная Цена], Клиенты.Страна, Клиенты.Страна FROM Клиенты INNER JOIN ([Сведения о заказах] INNER JOIN Заказы ON [Сведения о заказах].КодЗаказа = Заказы.КодЗаказа) ON Клиенты.КодКлиента = Заказы.КодКлиента WHERE (((Клиенты.Страна)="Германия")) GROUP BY Клиенты.Название, Клиенты.Страна;
   

   В ней используются две статистические функции— Count () и Sum () — и предложение WHERE, содержащее условие отбора записей.
   

Рис. 8.23. Задание условия отбора записей в запросе "Итоги по клиентам"

Рис. 8.24. Сообщение об ошибке при попытке отобразить поле, для которого задано условие

6. Измените это предложение, заменив названия полей, в которых ведутся расчеты: AS [Число заказов] вместо AS [CountOfКодЗаказа], AS [Общая сумма] вместо AS [ЗитО£ОтпускнаяЦена].
   
7. Нажмите на панели инструментов кнопку Запуск (Run) для вывода результата. Результирующий набор записей представлен на рис. 8.25.
   

Рис. 8.25. Результаты запроса "Итоги по клиентам в Германии"

Статистические функции можно использовать также в вычисляемых полях запроса. Например, для создания запроса "Итоги по клиентам" мы могли бы нместо запроса "Сведения о заказах" использовать прямо таблицу "Заказано". На рис. 8.26 показано вычисляемое поле "Стоимость". В выражение для этого поля включена функция Sum ():

Стоимость: Sum(CCur([Заказано].[Цена]*[Количество]*(1-[Скидка])/100) *100)

Тогда в строке Групповая операция (Total) для этого столбца должен быть выбран элемент Выражение (Expression). При использовании группировки по полю "КодЗаказа", поле "Стоимость" будет содержать общую стоимость заказа.

Можно создать и другое вычисляемое поле:

Стоимость: CCur([Заказано].[Цена]*[Количество]*(1-[Скидка])/100) *100,

а функцию Sum () использовать в строке Групповая операция (Total). Оба варианта будут правильны.

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

Рис. 8.26. Использование статистической функции в выражении для вычисляемого поля

Внимание

Статистические функции при вычислениях не обрабатывают записи, которые имеют значение Null в том поле, к которому они применяются. Поэтому нужно быть осторожным при использовании функции Count (). Она будет считать только те записи, которые не содержат пустых значений. Если нужно сосчитать все записи в результирующем наборе или группе, используйте функцию Nz (<имя поля>), которая преобразует пустое значение в 0, и только потом суммируйте.

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

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

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

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

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

4. Данный запрос имеет сложное выражение в строке Условие отбора (Criteria) поля "Название":
   

   In (SELECT [Название] FROM [Клиенты] As Tmp GROUP BY [Название],[Адрес] HAVING Count(*)>l And [Адрес] = [Клиенты].[Адрес])
   

   Это выражение включает еще один запрос. Такой запрос называется подчиненным запросом (подробнее о подчиненных запросах см. разд. "Создание подчиненных запросов" в конце данной главы). Чтобы посмотреть, как полностью выглядит инструкция SQL, соответствующая этому запросу, нужно перейти в режим SQL.
   

Совет

Мы рекомендуем при работе с запросами в режиме Конструктора чаще открывать окно Режим SQL (SQL View). Изучая, как Access создает инструкции SQL, можно постепенно выучить этот язык. Правда, помните, что в данном случае мы имеем дело с диалектом стандартного языка SQL — Access SQL. Ниже мы расскажем, чем он отличается от стандартного языка SQL (см. разд. "Сравнение ANSI и Jet SQL" данной главы).

Такой поиск разумно выполнять также в таблицах, которые экспортированы из другой среды, например Excel. Эти таблицы могут содержать дублирующие записи, которые нужно найти и удалить. Для удаления дубликатов можно воспользоваться процедурой, которая описана в справке Access в разделе Работа с запросами, Работа с запросами на изменение, Удаление повторяющихся записей из таблицы.

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

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

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

В бланке запроса в столбце "КодКлиента" задано условие — Is Null. Это означает, что выбираются все клиенты, код которых отсутствует в таблице "Заказы" (Orders). Вы можете использовать это условие всякий раз, когда нужно отыскать записи, у которых одно из полей пусто.

Рис. 8.29. Запрос для поиска записей, не имеющих подчиненных записей

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

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

Замечание

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

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

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

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

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

Рис. 8.31. Запрос "Счета" в режиме сводной таблицы

5. Чтобы определить столбцы таблицы, переместите поле "Дата размещения по месяцам" (OrderDate By Month) в область столбцов, которая имеет надпись Перетащите сюда поля столбцов (Drop Column Fields Here), В таблице появятся три столбца, которые будут содержать данные по годам — это самый верхний уровень группировки, — и один итоговой столбец "Общие итоги" (Grand Total).
   
6. Мы определили заголовки строк и столбцов таблицы, теперь остается определить, что будет отображаться в самой таблице. Переместитесь в самую большую область таблицы — поле "Отпускная цена" (ExtendedPrice). Таблица приобретет вид, представленный на рис. 8.31. По умолчанию в ней отображаются все данные по всем товарам и странам.
   

Когда таблица отображается в режиме Сводная таблица, в верхней части окна приложения видна специальная панель инструментов Сводные таблицы (Pivot Table). Эта панель инструментов представлена на рис. 8.32.

Рис. 8.32. Панель инструментов Сводные таблицы

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

Таблица 8.2. Описание кнопок панели инструментов Сводные таблицы

Рис. 8.33. Отображение итоговых данных в режиме сводной таблицы

Определим итоговые столбцы.

1. Щелкните левой кнопкой мыши на заголовке столбца "Отпускная цена" (ExtendedPrice) — все столбцы (кроме итогового) окажутся выделенными. Теперь щелкните по кнопке Автовычисления (AutoCalc) на панели инструментов и выберите из списка имя функции — Sum(). Появляется дополнительная строка для каждого товара, содержащая итоговое значение по каждому товару за год, а в итоговом столбце будет отображена итоговая цифра продаж каждого товара по всем годам.
   
2. Теперь можно скрыть детальные данные, оставив только итоговые. Нажмите кнопку Скрыть подробности (Hide Details) на панели инструментов.
   

Окончательный вид таблицы представлен на рис. 8.33.

Фильтрация данных сводной таблицы

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

Справа от названия каждого из этих полей видна небольшая стрелка вниз. Щелкните по этой стрелке в поле "Страна" (Country) в области фильтра. Появится небольшое окно со списком значений поля, которые присутствуют в исходных данных. По умолчанию у каждой страны установлен флажок (рис. 8.34).

Рис. 8.34. Выпадающий список значений поля

Сбросьте флажок у первой строки — Все (АН), щелкнув по нему мышкой. Все флажки одновременно сбросятся. Затем установите его только у одной страны, например Бельгия, и нажмите кнопку ОК. Теперь ваша таблица отображает данные только по Бельгии. Под названием поля "Страна" (Country) отображается название выбранной страны.

Если нужно отобразить данные сразу для нескольких стран (но не всех), просто установите в списке значений флажки у этих стран. Данные в таблице опять изменятся, только, к сожалению, не видно, какие страны участвуют в выборке — под названием поля в области фильтра указывается просто несколько элементов (Multiple Items).

Однако таблицу можно настроить таким образом, чтобы она не позволяла делать множественный выбор в полях фильтра.[Слово "полях" мы употребили не случайно, т. к. в этой области может быть несколько полей.]

Для этого:

1. Нажмите кнопку Свойства (Properties) на панели инструментов. При этом область фильтра с полем "Страна" должна быть выделенной. Появится диалоговое окно Свойства (Properties), которое отображает свойства поля "Страна". Оно содержит несколько вкладок.
   
2. Раскройте вкладку Фильтр и группировка (Filter and Group) и сбросьте флажок Разрешить выделять несколько элементов в области фильтра (Allow selecting multiple items when in filter area) (рис. 8.35).
   

Рис. 8.35. Диалоговое окно Свойства

3. Раскройте список значений поля "Страна" (Country). Теперь в списке значений полей отсутствуют флажки, и вы можете выбрать только одну страну (рис. 8.36).
   

Рис. 8.36. Окно значений поля с отключением множественного выбора

Теперь рассмотрим, как можно фильтровать отображаемые в таблице данные по значениям поля "Марка" (ProductName), которое находится в области строк.

Раскройте список значений поля, щелкнув по стрелке справа от названия поля. Сбросьте флажок Все (Аll) и установите флажки напротив нужных товаров. Нажмите кнопку ОК. Таблица стала намного короче, а в названии столбца появился значок фильтра (рис. 8.37).

Рис. 8.37. Сводная таблица с фильтром по товарам

Чтобы сбросить установленный фильтр, нажмите кнопку Автофильтр (AutoFilter) на панели инструментов. Обратите внимание, что при этом сбрасываются все фильтры, в том числе установленные в области фильтра. Последний установленный фильтр сохраняется, и если вы нажмете эту кнопку еще раз, то снова увидите отфильтрованные данные.

Можно установить фильтр и другими • способами. Например, можно отобразить только те товары, которые принесли больше всего или меньше всего дохода. Проще всего для этого воспользоваться специальной кнопкой на панели инструментов.

1. Сначала сбросьте все установленные фильтры, т. к. в противном случае фильтр будет накладываться на уже отфильтрованные данные.
   
2. Выделите столбец "Марка", щелкнув по его заголовку.
   
3. Нажмите кнопку Показать верхние и нижние элементы (Show Top/Bottom Items) на панели инструментов и выберите из списка элемент Показать только верхний элемент (Show Only the Top) и затем в поле Элементы — 10, как изображено на рис. 8.38. В таблице будет показано 10 строк, содержащих максимальное значение в поле "Общие итоги" (Grand Total).
   

Чтобы убрать этот фильтр, достаточно нажать ту же кнопку еще раз и выбрать элемент Показать все (Show All).

Кнопка Показать верхние и нижние элементы (Show Top/Bottom Items) позволяет фильтровать данные, основываясь на значениях в области детальных и итоговых данных. Более гибко устанавливать фильтр можно, задавая значения в окне Свойства (Properties).

1. Выделите область строк, щелкнув мышью по имени поля "Марка", затем раскройте вкладку Фильтр и группировка (Filter and Group) окна Свойства (Properties). Верхняя часть этой вкладки содержит поля, относящиеся к фильтрации данных в таблице, а нижняя — к группировке.
   

Рис. 8.38. Товары — лидеры продаж

2. По умолчанию в таблице отображаются все значения (All items). Выберите из списка в поле Отображать (Display the) значение первые (Тор). В поле Элементы (Items) можно указать, какое количество значений выводить, а в поле На основе (Based on) — на основе какого поля отбирать записи. В списке предлагается на выбор два варианта: либо по значениям в поле "Сумма "Отпускная цена"" (Sum of ExtendedPrice), либо по порядку в исходном запросе. Во втором случае будут выведены строки с первыми по алфавиту названиями товаров, как показано на рис. 8.39.
   

Рис. 8.39. Фильтр для первых элементов сводной таблицы

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

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

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

Например, можно добавить в область фильтра поле "Клиенты. Название" (CompanyName), что позволит фильтровать данные не только по странам, но и по клиентам. Перетащите поле "Клиенты. Название" (CompanyName) из списка полей в область фильтра и поместите его рядом с полем "Страна" (Country). Устанавливая флажки против нужных клиентов, вы сможете получать сводные данные по счетам для каждого клиента.

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

Вы можете легко поменять местами поля из области фильтра и из области столбцов или строки поменять местами со столбцами. Переместите поле "Клиенты.Название" (CompanyName) в область столбцов, а поле "Годы" (Year) — в область фильтра. Теперь в столбцах таблицы будут отображаться данные по продажам для каждого клиента (рис. 8.40), а, используя поле "Дата размещения по месяцам" (Order Date By Month), вы сможете фильтровать эти данные.

Рис. 8.40. Отображение в сводной таблице данных по клиентам

Совет

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

Сортировка в сводной таблице

В отличие от аналогичных таблиц в перекрестном запросе, данные в сводной таблице легко сортировать. Для этого используются стандартные кнопки Сортировать по возрастанию (Sort Ascending) и Сортировать по убыванию (Sort Descending) на панели инструментов.

Нужно выделить поле, по которому вы хотите отсортировать данные, и нажать соответствующую кнопку на панели инструментов. Если выделить поле в области строк, то данные будут отсортированы в порядке возрастания или убывания значений этого поля (в нашем примере названия товаров будут отсортированы в алфавитном порядке). Если выделить поле в области столбцов, то столбцы в таблице будут отсортированы в порядке возрастания значений в заголовках столбцов (в данном примере клиенты будут выведены в алфавитном порядке). Если выделить область детальных и итоговых данных таблицы, то она будет отсортирована в порядке возрастания (или убывания) значений в столбце "Общие итоги" (Grand Total).

Изменение уровня детализации

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

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

1. Щелкните по этому маркеру в столбце, соответствующем 1997 году. Появится еще одна строка в заголовке, и вместо одного столбца отобразятся пять: четыре соответствуют кварталам, а последний столбец представляет собой итог за год (рис. 8.41). Поскольку крайний правый столбец таблицы содержит общий итог по всем годам, то данные в этом столбце будут представлять собой промежуточный итог Итоги (Total). Маркер у цифр года изменился: появился значок минуса в квадратике. Его можно использовать потом для того, чтобы снова "свернуть" данные. Справа от поля "Годы" (Year) появилось еще одно поле — "Кварталы" (Quarters).
   
2. Слева в заголовке кварталов также виден маркер развертывания. Щелкнув по нему, вы можете дальше развернуть данные и просматривать данные по месяцам. Но если вы хотите развернуть данные сразу по всем кварталам, воспользуйтесь специальной кнопкой на панели инструментов. Выделите поле "Кварталы" (Quarters) и нажмите кнопку "Развернуть" (Expand) на панели инструментов. Число столбцов в таблице увеличится и для каждого месяца будет отведен отдельный столбец. Кроме того, будут отображаться промежуточные итоги по кварталам и годам.
   

Замечание

Обратите внимание, что, выполняя все эти операции, мы не создавали никаких вычисляемых полей. Так, Access 2002 умеет работать с данными типа Дата (Date/Time).

3. Если вы хотите теперь снова свернуть таблицу и отобразить только верхний уровень иерархии, выделите поле "Годы" (Year), щелкнув по его названию, и нажмите кнопку Свернуть (Collapse) на панели инструментов. Таблица вновь приобретет первоначальный вид.
   

Рис. 8.41. Детализация данных по столбцам сводной таблицы

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

1. Подведите указатель мыши к кнопке со знаком плюс в строке с названием первого товара. Появится подсказка Показать/скрыть детали (Show/Hide Details). Нажмите эту кнопку. Строка расширится, и в ней будут выведены как детальные данные по всем счетам, в которых участвовал этот товар, так и итоговые данные за год (рис. 8.42). Это как раз тот режим, который не удается получить, если воспользоваться группировкой в обычных запросах на выборку.
   

Рис. 8.42. Итоговые данные по строкам сводной таблицы

2. Чтобы снова скрыть детальные данные, нажмите кнопку со знаком минус.
   
3. Чтобы отобразить детальные данные сразу по всем строкам, выделите весь столбец с наименованиями товаров и нажмите кнопку Подробности (Show Details) на панели инструментов.
   
4. Чтобы снова скрыть детальные данные по всем строкам, нажмите кнопку Скрыть подробности (Hide Details) на панели инструментов.
   

Аналогично отображаются и скрываются данные и для столбцов таблицы.

Детализация данных может быть изменена также при добавлении новых полей в область строк или столбцов. Например, можно добавить в область строк поле "Категория" (Category) и сгруппировать все товары по категориям.

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

1. Перетащите поле "Категория" (CategoryName) из списка полей в область строк, поместив его перед полем "Марка" (ProductName). Сводная таблица приобретет вид, показанный на рис. 8.43. Теперь в левой части таблицы — два поля, причем все товары автоматически сгруппировались по категориям. Последняя строка в каждой категории представляет собой промежуточный итог продаж по категории.
   

Рис. 8.43. Добавление поля в область строк

2. Выделите поле "Категория" (CategoryName) и нажмите кнопку Свернуть (Collapse) на панели инструментов. Таблица быстро перестроится и будет иметь вид, приведенный на рис. 8.44. Маркеры развертывания справа указывают на то, что представлен верхний уровень детализации, и данные могут быть детализированы.
   

Рис. 8.44. Отображение товаров, сгруппированных по категориям

Общие и промежуточные итоги

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

Чтобы скрыть общий итог по строкам, необходимо выделить крайнее левое поле в области строк (в нашем примере "Категория" (CategoryName)) и нажать кнопку Итоги (Subtotal) на панели инструментов. Аналогично, чтобы скрыть общий итог по всем столбцам, нужно выделить самое левое поле в области столбцов и нажать ту же кнопку. На рис. 8.45 показана таблица нашего примера со скрытыми значениями общих итогов.

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

Если вы выделите любое другое поле в области строк или столбцов и нажмете кнопку Итоги (Subtotal), будут скрыты промежуточные итоги для этого поля.

Для создания итоговых значений мы использовали команду Автовычисления (AutoCalc). Эта команда в зависимости от типа данных в поле, по которому подводятся итоги, сама выбирает подходящую функцию. Например, если поле числового типа, обычно используется функция Sum (), если текстового — Count (). Вообще говоря, для расчета итоговых значений можно использовать почти те же статистические функции, что и при группировке в запросах на выборку. Список этих функций приведен в табл. 8.1 (см. разд. "Статистические функции для выполнения расчетов" выше в этой главе). В данном случае отсутствуют только функции First () и Last ().

Рис. 8.45. Сводная таблица со скрытыми общими итогами

Например, если сводная таблица содержит данные с оценками по всем предметам учеников в школьном классе, то итоговым значением, скорее всего, будет средний бал, для вычисления которого можно использовать функцию Avg (). В одной сводной таблице может быть представлено несколько типов итоговых значений. Например, в нашей таблице можно в качестве итоговых значений отображать не только общие суммы, вырученные за проданные товары, но и количество выписанных счетов. Для того чтобы добавить еще одно итоговое поле, выделите поле "Марка" (ProductName) в области строк и нажмите кнопку Автовычисления (AntoCalc). Выберите из списка функцию Count (). Через несколько секунд в сводной таблице для каждого года будет отображено два столбца:

• "Сумма "Отпускная цена"" (Sum of ExpendingPrice);
  
• "Количество значений "Марка"" (Count of ProductName).
  

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

1. Выделите столбец "Количество значений "Марка"" (Count of ProductName) (одновременно будут выделены все столбцы данного типа).
   
2. Откройте диалоговое окно Свойства (Properties) и раскройте вкладку Отчет (Report).
   
3. В группе Отображать итоги в (Display total as) установите переключатель заголовках строк (Row headings). Таблица будет трансформирована и примет вид, представленный на рис. 8.46.
   

Рис. 8.46. Сводная таблица с несколькими итоговыми полями

Рис. 8.47. Создание вычисляемого поля

Для отображения итоговых значений могут быть использованы также вычисляемые поля. Для добавления вычисляемого поля нажмите кнопку Итоги и вычисляемые поля (Calculated Total and Fields) на панели инструментов и выберите из раскрывшегося меню значение Создание вычисляемого итога (Create Calculated Total). В области данных появятся дополнительные строки или столбцы, и будет открыта вкладка Вычисление (Calculated) диалогового окна Свойства (Properties) (рис. 8.47).

Вы должны ввести имя нового поля в поле Имя (Name), а формулу для расчета значения — в большое поле, расположенное ниже. Если для составления выражения нужно сослаться на какое-то поле, выберите имя этого поля в списке в нижней части вкладки и нажмите кнопку Добавить ссылку на (Insert Reference to). Когда выражение будет готово, нажмите кнопку Изменить (Change).

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

Форматирование сводной таблицы

Для форматирования полей сводной таблицы используется диалоговое окно Свойства (Properties). Можно задать формат для полей области фильтра, строк, столбцов и детальных данных. Для этого сначала нужно выделить поле, а затем раскрыть вкладку Формат (Format) диалогового окна свойств. Эта вкладка показана на рис. 8.48.

Рис. 8.48. Диалоговое окно Свойства сводной таблицы, вкладка Формат

Из этого рисунка видно, что для поля можно определить:

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

Если в области данных отображаются числовые поля, даты или время,-то можно задать формат отображения данных в этих полях — либо выбрать из списка стандартный формат, либо задать нужный формат с помощью маски. Используйте для этого поле Число (Number).

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

Рис. 8.49. Диалоговое окно Свойства сводной таблицы, вкладка Заголовки

В поле Заголовок (Caption) можно задать текст, который будет отображен в названии поля, и затем формат этого текста.

Сводные диаграммы

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

Создание сводной диаграммы

Создать диаграмму на основе сводной таблицы очень просто. Переключитесь в режим Сводная диаграмма (PivotChart View), и на экране появится сводная диаграмма, примерно такая, как на рис. 8.50.

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

Рис. 8.50. Сводная диаграмма, построенная на основе сводной таблицы

Но можно создать диаграмму и непосредственно на базе запроса или таблицы. При этом одновременно будет создаваться и сводная таблица. Сводная таблица и сводная диаграмма — это две формы представления одних и тех же данных.

В качестве примера предлагается построить сводную диаграмму для запроса "Продажи по сотрудникам и странам" (Employee Sales by Country).

1. Откройте этот запрос в режиме Конструктора.
   
2. Запрос имеет два параметра: [Начальная дата] и [Конечная дата], которые используются для фильтрации данных. Для сводной диаграммы эти параметры не нужны, поэтому сначала удалите выражение из строки Условие отбора (Criteria), затем откройте диалоговое окно Параметры (Query Parameters) (см. разд. "Запросы с параметрами" гл. 4) и удалите оба параметра.
   
3. Щелкните по стрелке на кнопке Вид (View) панели инструментов и выберите из меню пункт Сводная диаграмма (PivotChart View). Появится окно, основную часть которого занимает область отображения диаграммы (рис. 8.51), ограниченная осями координат и размеченная линиями сетки. Кроме этого, видны область фильтра, которая играет ту же роль и расположена так же, как и в сводной таблице, область категорий и область рядов, которые соответствуют строкам и столбцам сводной таблицы. В область категорий переносятся поля, значения которых должны откладываться по оси X (горизонтальной), а в область рядов — поля, каждое значение которых соответствует одной серии точек или столбцов на диаграмме (в зависимости от типа диаграммы). Эти поля соответствуют полям столбцов на сводной диаграмме. В область данных помещаются поля, значения которых будут отображаться по оси Y (вертикальной) диаграммы.
   

Рис. 8.51. Макет сводной диаграммы

4. Перетащите из списка полей в область фильтра поле "Страна" (Country), в область категорий — поля "Фамилия" (Last Name) и "Имя" (First Name), в область рядов — поле "Дата исполнения по месяцам" (Shipped Date By Month). Следите, как будет меняться область диаграммы.[Если вы не видите диалоговое окно со списком полей, щелкните по кнопке Список полей (Fields List) панели инструментов.]
   
5. Перенесите поле "СуммаПродаж" (Sale Amount) в область данных — и диаграмма готова. Нажмите кнопку Добавить легенду (Show Legend), чтобы отобразить легенду, после чего вы получите диаграмму, представленную на рис. 8.52.
   
6. Можно еще ввести надписи у осей диаграммы. Щелкните по надписи Название оси (Axis Title) под осью X. Выведите на экран окно Свойства (Properties) и раскройте вкладку Формат (Format). Введите в поле Заголовок (Caption): Сотрудники. Аналогично введите надпись Объем продаж для оси Y.
   

Рис. 8.52. Сводная диаграмма "Продажи по сотрудникам и странам"

Рис. 8.53. Сводная таблица "Продажи по сотрудникам и странам"

Примечание

Каи уже говорилось выше, одновременно со сводной диаграммой создается и сводная - таблица.

7. Щелкните по стрелке на кнопке Вид (View) и переключитесь в режим сводной таблицы. Вы увидите сводную таблицу, показанную на рис. 8.53.
   

Изменение вида диаграммы

Так же, как и сводная таблица, сводная диаграмма является очень гибкой, и ее можно легко трансформировать, добавляя и удаляя поля, перемещая их из одной области в другую. Кроме того, можно изменить тип диаграммы, например, превратить ее из гистограммы в график (рис. 8.54). В этом случае каждая ломаная линия соответствует серии столбцов на столбчатой диаграмме. Каждая линия выделяется своим цветом и видом точек. Легенда описывает, какому значению поля "Годы" (Year) соответствует каждая линия.

Рис. 8.54. Диаграмма в виде графика

Для того чтобы изменить тип диаграммы, щелкните мышью в любом месте области диаграммы и убедитесь, что эта область выделена. Тогда становится доступна кнопка Тип диаграммы (Chart Type) на панели инструментов (рис. 8.55). Нажмите эту кнопку. В диалоговом окне Свойства (Properties) раскроется вкладка Тип (Туре). На ней представлены все типы диаграмм, которые могут быть построены. Выберите нужный тип из списка, а затем подтип справа и остановитесь на том типе, который вам больше нравится.

Рис. 8.55. Изменение типа сводной диаграммы

Можно очень легко поменять местами поля рядов и категорий. Для этого достаточно нажать кнопку По строке или по столбцу (By Row/By Column) на панели инструментов. И диаграмма мгновенно трансформируется (рис. 8.56). Остальные кнопки панели инструментов, такие как Свернуть (Collapse), Развернуть (Expand), Автовычисления (AutoCalc), работают так же, как и в сводной таблице.

Можно поворачивать диаграмму по часовой или против часовой стрелки. Для этого, выделив область диаграммы, необходимо открыть вкладку Общие (General) диалогового окна Свойства (Properties) и использовать четыре кнопки: Отразить справа налево (Flip Horizontal), Отразить сверху вниз (Flip Vertical), Повернуть на 90° по часовой стрелке (Rotate Clockwise) и Повернуть на 90° против часовой стрелки (Rotate Counter Clockwise).

Рис. 8.56. Трансформация сводной диаграммы

Форматирование элементов сводной диаграммы

Форматирование элементов сводной диаграммы выполняется с помощью различных вкладок диалогового окна Свойства (Properties):

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

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

Настройка свойств полей в запросе и свойств самого запроса

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

К вычисляемому полю в запросе применимы только свойства, заданные в запросе, т. к. данные, отображаемые в вычисляемом поле, не хранятся в исходной таблице. Например, если значение в поле вычисляется путем перемножения чисел, которые не являются целыми, следует установить свойство Формат (Format) для этого поля. Можно также, вместо того, чтобы устанавливать свойство Формат, применить функцию Format (<имя поля>, <формат>) или подходящую функцию преобразования типов данных, чтобы получить отображение данных в столбце в требуемом формате.

Существует несколько способов вывода на экран диалогового окна, содержащего свойства запроса или свойства поля в запросе. Сначала нужно щелкнуть левой кнопкой мыши в нужном столбце или на свободном поле в верхней панели запроса (для вывода свойств самого запроса), затем нажать кнопку Свойства (Properties) на панели инструментов или выполнить команду Вид, Свойства (View, Properties). Появится диалоговое окно, содержащее соответствующий список свойств. Кроме того, можно щелкнуть правой кнопкой мыши в столбце бланка запроса и выбрать в контекстном меню команду Свойства (Properties). Самый быстрый способ вывода окна свойств запроса — это дважды щелкнуть левой кнопкой мыши по свободной области верхней части окна запроса.

В окне свойств поля можно задать значения четырех свойств:

• свойство Описание (Description) позволяет задать текст, который выводится в строке состояния, когда курсор помещается в это поле в результирующем множестве запроса;
  
• свойство Формат поля (Format) позволяет задать формат отображения данных в результирующем множестве запроса;
  
• свойство Маска ввода (Input Mask) позволяет указать формат для ввода данных, если результирующее множество запроса является обновляемым. Для создания маски ввода, соответствующей типу данных поля, можно нажать кнопку Построителя справа от поля свойства, чтобы запустить Мастер по созданию масок ввода;
  
• свойство Подпись (Caption) позволяет изменить заголовок столбца результирующего множества запроса. Это свойство мы уже рассматривали в разд. "Создание запроса с помощью Конструктора" гл. 4.
  

Каждое из перечисленных свойств соответствует соглашениям о свойствах полей таблиц. Присваивание значения свойству Подпись (Caption) эквивалентно указанию префикса в строке Поле (Field) в бланке запроса для этого поля. Значение свойства Маска ввода (Input Mask) необязательно соответствует значению свойства Формат поля (Format). Например, поле с типом данных Дата/время (Date/Time) может иметь средний формат даты для вывода, а в маске ввода можно задать короткий формат.

Диалоговое окно Свойства запроса (Query Properties) представлено на рис. 8.57.

Рис. 8.57. Диалоговое окно Свойства запроса

• Свойство Описание (Description) используется, чтобы задать текст, который будет выводиться в строке состояния при отображении результирующего множества запроса.
  
• Свойство Режим по умолчанию (Default View) позволяет указать тот вид, в котором будут представлены результаты запроса при его открытии: таблицы, сводная таблица, сводная диаграмма. То есть теперь вы можете открыть запрос прямо в режиме сводной таблицы или диаграммы.
  
• Свойство Вывод всех полей (Output All Fields) позволяет включить в результирующий набор все поля из всех базовых таблиц запроса. Следует отметить, что при выводе большого количества полей выполнение запроса происходит медленнее, особенно если в результирующем множестве содержится большое количество записей.
  
• Свойство Набор значений (Top Values) позволяет указать, какое количество строк из результирующего набора нужно отобразить на экране. По умолчанию это свойство имеет значение Все (АН), однако, если запрос содержит большое количество записей, чтобы ускорить вывод, можно указать число, которое задает либо количество записей, либо процент записей. В этом случае Access будет выводить информацию на экран сразу, как только найдет заданное количество строк.
  
• Свойства Уникальные значения (Unicue Values) и Уникальные записи (Unique records) позволяют не включать в результирующий набор повторяющиеся записи. Эти свойства мы рассмотрим подробнее в разд. "Зарезервированные слова DISTINCTROW и DISTINCT' данной главы (см. также разд. "Изменение данных в результирующем множестве запроса" гл. 4).
  
• Свойство При запуске предоставлять права (Run Permissions) позволяет ограничить доступ к базовым таблицам других пользователей. Это свойство используется в том случае, если база данных работает в многопользовательском режиме и защищена на уровне пользователей (см. разд. "Администрирование баз данных Access, защищенных на уровне пользователей" гл. 20). Оно позволяет дать пользователю дополнительные права по сравнению с теми, что назначены ему при защите базы данных. Для этого данному свойству должно быть присвоено значение Владельца (Owner's). По умолчанию оно имеет значение Пользователя (User's), при этом пользователи не имеют никаких дополнительных прав.
  
• Свойство Блокировка записей (Record Locks) также используется в многопользовательском режиме и позволяет установить разные типы блокировки. По умолчанию это свойство имеет значение Отсутствует (No Locks). Это означает, что записи не блокируются в процессе их редактирования пользователем. Если данное свойство имеет значение Изменяемой записи (Edited Records), то когда пользователь редактирует запись, она блокируется и не может быть изменена другим пользователем. Значение Всех записей (All Records) позволяет блокировать все записи, отобранные запросом, до тех пор, пока пользователь не закроет запрос. (Подробно о многопользовательском режиме работы и типах блокировок в Access см. разд. "Организация совместного доступа к данным"гл. 16.)
  
• Свойства База данных-источник (Source Database) и Строка нодключения источник (Source Connect Str) используются для доступа к внешней базе данных, которая не является присоединенной к текущей. В этом случае они содержат имя и тип внешней базы данных.
  
• Свойство Время ожидания ODBC (ODBC Timeout) указывает число секунд, в течение которых Microsoft Access будет повторять попытки выполнения запроса к базе данных, подключенной к текущей базе посредством драйверов ODBC. Если по истечении заданного времени ответа от сервера базы данных не поступает, выдается сообщение об ошибке.
  
• Свойство Максимальное число записей (Max Records) определяет максимальное количество записей, которое возвращается запросом из внешней базы данных.
  
• Свойство Тип набора записей (RecordsetType) позволяет определить тип результирующего набора записей. Это свойство может иметь три значения:
  
  • Динамический набор (Dynaset) — если запрос базируется на одной таблице или таблицах, связанных отношением "один-к-одному", то все поля запроса могут редактироваться, а если таблицы в запросе связаны отношением "один-ко-многим", то редактироваться данные могут только в таблице на стороне "один";
    
  • Динамический набор (несогл.) (Dynaset (Inconsistent updates)) — все поля запроса могут редактироваться;
    
  • Статический набор (Snapshot) — ни одно поле запроса не может редактироваться.
    
• Свойство Фильтр (Filter) — позволяет задать фильтр, который можно применить к результирующему набору запроса. Значением свойства является строковое выражение, которое создается в соответствии с правилами составления предложения WHERE. Этот фильтр сохраняется вместе с запросом.
  
• Свойство Порядок сортировки (OrderBy) — позволяет задать порядок сортировки записей в результирующем наборе. Значением этого свойства является строковое выражение, содержащее имена полей, перечисленные через запятую.
  
• Ориентация (Orientation) — позволяет выводить поля запроса как в обычном, так и в обратном порядке. По умолчанию задается тот порядок следования полей, который определен в запрос: "Слева направо" (Left-to-Right).
  

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

Создание запросов SQL

Все запросы, которые мы рассматривали до сих пор, создавались либо с помощью мастера, либо с помощью Конструктора запросов. Конструктор запросов представляет собой графический инструмент для создания запросов по образцу (QBE — Query By Example). Однако на самом деле любой запрос хранится в базе данных в формате SQL (Structured Query Language — язык структурированных запросов). Основное достоинство этого языка состоит в том, что он является стандартом для большинства реляционных СУБД. SQL имеет унифицированный набор инструкций, которые можно использовать во всех СУБД, поддерживающих этот язык. Действующим на данный момент стандартом языка SQL является принятая Американским национальным институтом стандартов (American National Standards Institute — ANSI) версия SQL-92. Фирмы — разработчики СУБД при реализации языка SQL могут вносить в него расширения, но обязаны реализовать базовый набор команд ASNSI SQL.

Процессор обработки данных Jet является составной частью Access и выполняет инструкции Access SQL (Jet SQL), который отличается от ANSI SQL существенно (как правило, настольные СУБД, совместимые со стандартом SQL, реализуют не все инструкции ANSI SQL).

Замечание

В дальнейшем для обозначения используемого в Access диалекта языка SQL мы будем применять термин Jet SQL. Это будет правильнее, т. к. процессор обработки данных используется не только в приложениях, созданных в среде Access, но и в приложениях, разработанных с помощью Microsoft Visual Basic.

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

Сравнение ANSI и Jet SQL

Язык Jet SQL почти соответствует стандарту ANSI SQL-89. В реализацию языка SQL для Microsoft Jet 4.x (используемого, начиная с версии Microsoft Access 2000) внесены несколько расширений, которые приближают его к стандарту ANSI SQL-92 и

Transact-SQL — диалекту языка SQL для Microsoft SQL Server. Для тою чтобы обеспечить совместимость с предыдущими версиями Microsoft Jet, эти расширения можно использовать только в специальном режиме — ANSI SQL-92.

Замечание

Режим ANSI SQL-92 доступен только при использования программы Microsoft OLE DB Provider для Jet.

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

Основные различия языков Jet SQL и ANSI SQL состоят в следующем:

• они имеют разные наборы зарезервированных слов и типов данных;
  
• разные правила применимы к оператору Between. . .And, используемому для определения условий выборки записей;
  
• подстановочные знаки ANSI и Microsoft Jet, которые используются в операторе Like, взаимно исключают друг друга;
  
• язык Jet SQL обычно предоставляет пользователю большую свободу, например разрешается группировка и сортировка по выражениям;
  
• язык Jet SQL позволяет использовать более-сложные выражения.
  

Зарезервированные слова Jet SQL

Приведенные здесь таблицы предназначены для сравнения зарезервированных слов ANSI SQL и Jet SQL.

Ниже перечисляются зарезервированные слова Jet SQL, которые идентичны зарезервированным словам ANSI SQL (знаком звездочки помечены слова, которые доступны только в режиме ANSI SQL-92):

Зарезервированные слова, обозначающие типы данных, не включены в этот список, т. к. соответствие типов данных ANSI SQL и Jet SQL приводится ниже, в табл. 8.6. Большинство операторов сравнения в ANSI SQL и Jet SQL совпадают: =, <, <=, > и =>. Исключение составляет оператор неравенства. Оператору неравенства ! = в ANSI SQL соответствует оператор <> в Jet SQL.

Как и в ANSI SQL, зарезервированное слово Jet SQL IN может быть использовано для задания списка значений в предложении WHERE или списка, созданного подчиненным запросом. Оператор IN также может использоваться для идентификации таблицы в другой базе данных.

Хотя в последней версии Jet SQL появились новые инструкции, связанные с обработкой транзакций (раньше они реализовывались с помощью процедур VBA), тем не менее существует отличие в механизме выполнения транзакции от рекомендуемого ANSI SQL:

• автоматический запуск транзакции невозможен. Чтобы начать транзакцию, ее необходимо явно запустить с помощью инструкции BEGIN TRANSACTION;
  
• допускается пять уровней вложения транзакций. Чтобы запустить вложенную транзакцию, воспользуйтесь инструкцией BEGIN TRANSACTION в контексте существующей транзакции;
  
• для присоединенных (связанных) таблиц транзакции не поддерживаются.
  

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

Таблица 8.3. Зарезервированные слова ANSI SQL, не поддерживаемые Access SQL

Часть зарезервированных слов ANSI SQL заменяется в Jet SQL операторами или функциями. В табл. 8.4 перечислены функции и операторы Jet SQL, которые соответствуют зарезервированным словам ANSI SQL.

Таблица 8.4. Функции и операторы Access, используемые вместо зарезервированных слов ANSI SQL

Функция Access IsNull(), возвращающая значение True (-1), если ее аргумент равен лустому значению, и False (0) — в обратном случае, не имеет эквивалента в ANSI SQL и не является заменой модификаторов is Null или is Not Null в предложении WHERE. Кроме того, Jet SQL не поддерживает статистические функции по подмножеству, аргументом которых является любое выражение с параметром DISTINCT, например AVG (DISTINCT имя__поля). В таких случаях требуется использовать параметр DISTINCTROW в инструкции SELECT. Выражения, в которых применяются операторы, такие как, например, And или Or, заключаются в инструкциях Jet SQL в скобки.

Оператор Between. . .And, который имеет синтаксис

<выражение> [NOT] Between <значение_1> And <значение_2>

по-разному трактуется в языках Jet SQL и ANSI SQL. В языке Jet SQL значение_1 может превышать значение_2; в ANSI SQL значение_1 должно быть меньше или равно значение_2.

Зарезервированные слова Jet SQL и функции, не входящие в ANSI SQL

Существует несколько зарезервированных слов Jet SQL, которые не имеют аналогов в ANSI SQL. Это слова:

• DISTINCTROW — позволяет исключить из результирующего множества запроса повторяющиеся строки;
  
• PIVOT — используется в перекрестных запросах;
• TRANSFORM — создание перекрестного запроса.
  

Кроме того, Jet позволяет использовать четыре статистические функции по подмножеству, не включенные в ANSI SQL (табл. 8.5).

Таблица 8.5. Дополнительные статистические функции Access SQL

Эквивалентные типы данных Jet SQL и ANSI SQL

В табл. 8.6 перечислены типы данных ANSI SQL, эквивалентные им типы данных языка Jet SQL и допустимые синонимы.

Таблица 8.6. Эквивалентные типы данных Jet SQL и ANSI SOL

Реализация SQL процессора Jet 4.x имеет существенные отличия в типах данных от предыдущей версии Jet SQL, а именно:

• тип TIMESTAMP теперь не является синонимом типа DATETIME;
  
• тип NUMERIC теперь используется как синоним типа DECIMAL, а не FLOAT или DOUBLE, как это было раньше;
  
• поля типа LONGTEXT и CHAR всегда хранятся в формате представления знаков Unicode, который эквивалентен такому типу данных ANSI SQL, как NATIONAL CHAR;
  
• если имя типа данных TEXT используется без указания необязательной длины, то создается поле типа LONGTEXT. В результате появляется возможность применять инструкции CREATE TABLE для создания типов данных, совместимых с Microsoft SQL Server;
  
• если имя типа данных TEXT используется с указанием необязательной длины, например TEXT(25), то тип данных соответствующего поля будет эквивалентен типу CHAR. В результате сохраняется обратная совместимость с большинством приложений, использующих процессор Microsoft Jet, а также согласованность типа данных TEXT (без указания длины) с Microsoft SQL Server;
  
• тип данных BIT стандарта ANSI SQL не соответствует типу данных BIT языка Jet SQL. Он совпадает с типом данных BINARY. Для типа данных BIT языка Jet SQL нет эквивалентного типа в ANSI SQL.
  

Подстановочные знаки при сравнении строк

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

Таблица 8.7. Соответствие подстановочных знаков

Язык SQL Microsoft Jet поддерживает использование в операторе Like как подстановочных знаков ANSI SQL (_ и #), так и подстановочных знаков ядра Microsoft Jet (? и *). Однако подстановочные знаки ANSI SQL доступны только в режиме ANSI SQL-92, в противном случае они воспринимаются просто как строковые константы. И наоборот, в режиме ANSI SQL-92 подстановочные знаки Jet SQL трактуются как строковые константы. Таким образом, нельзя в одном запросе смешивать эти знаки.

Ниже перечислены важные правила установления совпадения оператором Like.

• Восклицательный знак (!) в начале списка_знаков означает, что совпадение наступит, если в выражении будет найден любой знак, отсутствующий в списке. Восклицательный знак вне квадратных скобок соответствует самому себе.
  
• Знак дефиса (-) можно использовать в начале (после восклицательного знака, если он есть) или в конце списка_знаков для установления соответствия с самим собой. В любом другом месте знак дефиса означает диапазон символов ANSI.
  
• Если указан диапазон знаков, его границы должны следовать в возрастающем порядке (А— Я или 0—100). [А— Я] является правильным образцом, а [Я— А] — неправильным.
  
• Квадратные скобки ([ ]) игнорируются; они рассматривается как пустая строка ("").
  

Зарезервированные слова DISTINCTROW w DISTINCT

Зарезервированные слова DISTINCTROW и DISTINCT в инструкции SELECT Jet SQL позволяют исключить из результирующего множества повторяющиеся строки. Разница между этими словами состоит в том, как при их использовании определяются повторяющиеся записи.

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

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

Замечание

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

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

1. Создайте новый запрос в базе данных "Борей". Для этого раскройте список запросов окна базы данных и нажмите кнопку Создать (New Query). В списке диалогового окна Новый запрос (New Query) выделите значение Конструктор (Design View) и нажмите кнопку ОК.
   
2. Добавьте в запрос таблицы "Клиенты" (Customers), "Заказы" (Orders), "Заказано" (Orders Details) и "Товары" (Products). Access автоматически создает необходимые связи между таблицами.
   
3. Перетащите поле "Название" (CompanyName) из списка полей таблицы "Клиенты" в первый столбец бланка запроса. Выделите ячейку Сортировка (Sort) и выберите значение По возрастанию (Ascending).
   
4. Перетащите поле "Марка" (ProductName) из списка полей таблицы "Товары" (Products) во второй столбец бланка запроса. Задайте в этом столбце также сортировку по возрастанию.
   
5. Нажмите на панели инструментов кнопку Запуск (Run) для выполнения запроса.
   

Вы должны получить результирующее множество, которое содержит 2169 записей. При этом те клиенты, которые не сделали ни одного заказа, в результирующее множество .не попадут, однако клиенты, которые заказывали один и тот же товар несколько раз, могут попасть в таблицу несколько раз. Теперь добавим в инструкцию SQI, SELECT слово DISTINCTROW для зтого:

1. Выберите команду меню Вид, Режим SQL (View, SQL View) или щелкните по стрелке на кнопке Вид (View) на панели управления и выберите элемент Режим SQL (SQL View). Откроется диалоговое окно, в котором выведена инструкция SQL, соответствующая построенному запросу.
   
2. Введите слово DISTINCTROW сразу после слова SELECT. Запрос должен выглядеть гак, как показано на рис. 8.58.
   
3. Нажмите кнопку Запуск (Run). Результирующее множество запроса будет включать 1695 записей.
   
4. Снова выполните команду Вид, Режим SQL (View, SQL Mode) и замените в инструкции SQL СЛОВО DISTINCTRTOW СЛОВОМ DISTINCT.
   
5. Нажмите кнопку Запуск (Run). Результирующее множество запроса содержит те же 1695 строк, что и при использовании ключевого слова DISTINCTROW.
   

Рис. 8.58. Инструкция SQL с модификатором DISTINCTROW

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

Вместо того чтобы вносить эти модификаторы вручную в режиме SQL, можно просто установить соответствующие свойства запроса. Чтобы открыть диалоговое окно свойств запроса, щелкните правой кнопкой мыши по свободному полю в верхней панели окна Конструктора запроса и выберите из контекстного меню команду Свойства (Properties). Появляется диалоговое окно, представленное на рис. 8.57.

Два свойства в этом окне определяют использование модификаторов DISTINCTROW и DISTINCT: Уникальные значения (Unique Values) и Уникальные записи (Unique Rows). Свойство Уникальные значения соответствует модификатору DISTINCT, a свойство Уникальные записи — модификатору DISTINCTROW. Если вы попробуете установить эти значения, то увидите, что Access не позволит установить значения Да (Yes) для обоих свойств. Если одно из них имеет значение Да (Yes), то для второго автоматически устанавливается значение Нет (No). Если оба свойства имеют значение Нет (No), запрос будет включать в результирующий набор все записи.

Замечание

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

Использование разделителей и символов SQL

Разделителями в SQL также являются запятая, точка, точка с запятой и двоеточие. Описанные ниже разделители применяются с небольшими отличиями в ANSI SQL и в Jet SQL. Так в Jet SQL:

• запятые используются для разделения членов списков, например имен полей: Имя, Фамилия, Отчество, [Год рождения}, Адрес, Город, Индекс;
  
• для задания имен полей, которые содержат недопустимые символы (например, пробелы) применяются квадратные скобки: [Дата размещения];
  
• если в запрос включены поля нескольких таблиц, то включается полное имя поля, которое состоит из двух частей: имени таблицы и имени поля, между которыми используется разделитель — точка: Заказы. КодЗаказа;
  
• строки в ANSI SQL заключаются в одиночные кавычки ('). Строки в Jet SQL можно заключать как в одиночные, так и в двойные кавычки ("). При использовании инструкций SQL в процедурах VBA рекомендуется ставить одиночные кавычки;
  
• в конце инструкции Jet SQL обязательно ставится точка с запятой; П в инструкциях Jet SQL нельзя использовать переменные.
  

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

Использование инструкций SQL для создания запросов

Есть несколько типов запросов, которые невозможно создать с помощью Конструктора запросов. Их можно создать, вводя соответствующую инструкцию в окне SQL. Для этого нужно:

1. Создать новый запрос с использованием Конструктора запросов.
   
2. Закрыть диалоговое окно Добавление таблицы (Show Table), не добавляя таблиц.
   
3. Выбрать команду Вид, Режим SQL (View, SQL View) для вывода окна SQL.
   
4. Удалить весь текст, который может быть выведен в окне SQL (обычно по умолчанию ВЫВОДИТСЯ SELECT DISTINCTROW;).
   
5. Ввести в окне инструкцию SQL, используя для ввода новой строки комбинацию клавиш <Ctrl>+<Enter>.
   
6. Нажать на панели инструментов кнопку Запуск (Run), чтобы вывести результирующее множество записей.
   

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

Создание запросов на объединение записей

Этот тип запроса позволяет объединить в одном результирующем наборе результаты нескольких запросов, таблиц и инструкций SELECT. Синтаксис этого запроса следующий:

[TABLE] запрос_1 UNION [ALL] [TABLE] запрос_2 [UNION [ALL] [TABLE] запрос_п [ ... ]],

где

запрос_1, запрос_2, ... запрос_п — инструкция SELECT, имя сохраненного запроса или имя сохраненной таблицы, перед которым стоит зарезервированное слово TABLE.

В каждом аргументе запрос_1 допускается применение предложения GROUP BY или HAVING для группировки возвращаемых данных. В конец последнего аргумента запрос_п можно включить предложение ORDER BY, чтобы отсортировать возвращенные данные. Таким образом, группировка записей может выполняться в каждом из объединяемых запросов или таблиц, а сортировка выполняется только в результирующем множестве записей запроса на объединение. В качестве поля для сортировки нужно указывать имена полей из первого запроса или таблицы, т. к. именно эти имена будут выступать в качестве имен полей в результирующем множестве запроса.

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

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

Для создания запроса на объединение существует специальная команда меню Запрос, Запрос SQL, Запрос на объединение (Query, SQL Specific, Union), которая доступна в режиме Конструктора запросов. При выполнении этой команды открывается пустое окно Режим SQL, в которое нужно ввести инструкцию.

На рис. 8.59 показана инструкция SQL, содержащая операцию UNION, для запроса "Клиенты и поставщики по городам" (Customers and Suppliers by City), который содержится в базе данных "Борей". Чтобы открыть окно SQL, откройте список запросов в окне базы данных, выделите запрос "Клиенты и поставщики по городам" и нажмите кнопку Конструктор (Design). При этом кнопка и команда меню Вид, Конструктор (View, Design View) становятся недоступными.

Результат выполнения запроса на объединение "Клиенты и поставщики по городам" приведен па рис. 8.60.

Рис. 8.59. Запрос на объединение

Рис. 8.60. Результирующее множество запроса "Клиенты и поставщики по городам"

Создание запроса к серверу

Еще одним типом запроса SQL является запрос к серверу. Он обрабатывается не процессором Jet, как все остальные запросы, а непосредственно передается на сервер базы данных, к которому выполняется обращение, например Microsoft SQL

Server. Главной особенностью этого запроса является то, что он должен использовать синтаксис языка SQL сервера базы данных (для Microsoft SQL Server это — Transact-SQL). Создается этот запрос аналогично запросу на объединение с помощью команды меню Запрос, Запрос SQL, К серверу (Query, SQL Specific, Pass-Through). Запросы к серверу применяются в приложениях, имеющих архитектуру "клиент-сервер".

Управляющие запросы

Управляющие запросы относятся к третьему типу запросов, которые создаются в окне Режим SQL и используют инструкции Jet SQL. Такие запросы позволяют работать с таблицами и индексами — создавать, изменять и удалять таблицы, создавать индексы.

Инструкции SQL, которые при этом используются, относятся к подмножеству SQL, называющемуся DDL — язык определения данных. Набор этих инструкций в Jet SQL существенно сокращен по сравнению со стандартом ANSI SQL, т. к. многие операции можно выполнить с помощью других средств (команд меню или окна базы данных).

В обычном режиме можно использовать следующие инструкции SQL:

• CREATE TABLE — создает таблицу;
  
• CONSTRAINT — создает уникальный индекс, в том числе по первичному ключу, а также применяется для установления отношений между таблицами;
  
• ALTER TABLE — изменяет структуру таблицы, созданной с помощью инструкции CREATE TABLE;
  
• DROP — удаляет таблицу из базы данных или удаляет индексы в таблице;
  
• CREATE INDEX — создает индекс для поля или группы полей.
  

В режиме ANSI SQL-92 возможны дополнительно следующие инструкции:

• CREATE PROCEDURE — создает хранимую процедуру; П CREATE VIEW — создает новое представление;
  
• CREATE USER или GROUP — создает одного или нескольких новых пользователей или группу;
  
• ADD USER — добавляет одного или нескольких существующих пользователей к существующей группе;
  
• DROP USER или GROUP — удаляет одного или нескольких существующих пользователей или групп или исключает одного или нескольких существующих пользователей из существующей группы;
  
• ALTER USER или DATABASE — изменяет пароль существующего пользователя или базы данных;
  
• GRANT — предоставляет конкретные привилегии существующему пользователю или группе;
  
• REVOKE — отменяет конкретные привилегии существующего пользователя или группы.
  

Замечание

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

В рамках данной книги мы не описываем подробно синтаксис всех инструкций SQL. Заинтересованному читателю рекомендуется обратиться к разделу Справочник Microsoft Jet SQL Справочной системы Access.

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

Инструкции Jet SQL, такие как SELECT, SELECT. .. INTO, INSERT...INTO, DELETE или UPDATE позволяют для вычисления предиката в предложении WHERE использовать другой запрос. Этот запрос называется подчиненным запросом.

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

• сравнение [ANY | ALL | SOME] (инструкцияSQL);
• выражение [NOT] IN (инструкцияSQR);
• [NOT] EXISTS (инструкцияSQL) ,

где:

сравнение — выражение и оператор сравнения, который сравнивает это выражение с результатами подчиненного запроса;

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

инструкцияSQL — инструкция SELECT, которая представляет подчиненный запрос.

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

SELECT * FROM Товары WHERE Товары.Цена > ANY

(SELECT ТоварыКонкурентов.Цена FROM ТоварыКонкурентов)

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

SELECT DISTINCTROW

Заказы.КодЗаказа

FROM Заказы

WHERE Year(Заказы.ДатаРазмещения) = 1998

AND Заказы.СуммаЗаказа < ALL (SELECT Заказы.СуммаЗаказа FROM Заказы WHERE Year(Заказы.ДатаРазмещения) = 1997);

Предикат IN используется для выбора в главном запросе только тех записей, которые содержат значения, совпадающие с одним из значений, выбранных подчинен-ным_ запросом. Например, чтобы выбрать клиентов, которые разместили заказы в январе 1998 года, можно написать следующий запрос:

SELECT * FROM Клиенты WHERE КодКлиента IN

(SELECT КодКлиента FROM Заказы

WHERE ДатаРазмещения

BETWEEN #l/l/98# AND #31/1/981;

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

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

SELECT DISTINCTROW Поставщики.Название FROM Поставщики WHERE Exists (SELECT Заказано.КодТовара FROM Заказано

WHERE КодЗаказа = 121 AND Заказано.КодТовара = Поставщики.КодТовара); В подчиненном запросе можно-использовать псевдонимы таблиц для ссылки на таблицы, перечисленные в предложении FROM, расположенном вне подчиненного запроса. В следующем примере выбираются фамилии и имена сотрудников, чья зарплата равна или больше средней зарплаты сотрудников, имеющих ту же должность. В предыдущем примере можно присвоить таблице "Сотрудники" (Employees) псевдоним Т1, и тогда запрос будет выглядеть следующим образом:

SELECT DISTINCTROW Поставщики.Название

FROM Поставщики As П1

WHERE Exists

(SELECT Заказано.КодТовара

FROM Заказано

WHERE КодЗаказа = 121 AND Заказано.КодТовара = П1.КодТовара); Некоторые подчиненные запросы можно использовать в перекрестных запросах как предикаты в предложении WHERE. Подчиненные запросы, применяемые для вывода результатов (в списке SELECT), нельзя применять в перекрестных запросах.

В отличие от запросов на объединение, подчиненный запрос можно создать в режиме Конструктора. В строке Условия отбора (Criteria) в качестве условия отбора следует указать, например, зарезервированное слово IN, а затем ввести инструкцию SELECT. А можно сначала создать в режиме Конструктора подчиненный запрос, переключиться в режим SQL, скопировать получившуюся инструкцию SQL в буфер

обмена, а затем создать главный запрос и скопировать в строку Условия отбора (Criteria) подчиненный запрос из буфера.

Выводы

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

Мы говорили о новых режимах, которые появились в Microsoft Access 2002 для просмотра содержимого таблиц и результатов запросов. И хотя сводные таблицы и диаграммы, получаемые в Access, немного отличаются от своих аналогов в Microsoft Excel, тем не менее, это уже большой шаг в усовершенствовании средств для анализа данных.

В последних разделах главы мы привели описание языка Jet SQL. Возможно, это изложение показалось вам недостаточно полным. Однако применение языка Jet SQL в приложениях Access все-таки является достаточно ограниченным. В большинстве случаев для создания запросов можно использовать графический интерфейс Конструктора запросов. И даже при написании программ на VBA, в которых используются инструкции SQL, можно сначала построить нужный запрос в Конструкторе, а потом просто скопировать его через буфер обмена из окна Режим SQL Конструктора запросов. Читателю, желающему основательно изучить SQL, мы рекомендуем обратиться к специальной литературе.

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