Основные операции в Microsoft Access

Часть 3. Создание и редактирование запросов
Данный документ предназначен для использования в самостоятельной работе студентов с приложением Microsoft Office Access при выполнении своего проекта. Рисунки и управляющие последовательности относятся к Microsoft Office Access 2003.

Содержание

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

Запросы на выборку и перекрестные запросы предназначены для выборки из базы данных интересующей нас информации, которая была накоплена в результате работы по сбору и вводу данных.
Приведем примеры на основе рассмотренной ранее (в первой и второй частях данного руководства) базы данных, содержащей таблицы «Клиенты», «Операции», «Детали клиента». Ниже эти примеры реализуются в виде запросов. Примеры 1 и 2 используются в разделах 1.1 и 1.3 (запросы на выборку), а пример 3 – в разделах 1.2 и 1.3 (итоговые запросы).
Пример 1.

Нас интересуют операции, которые определенный клиент провел за текущий год. Для ответа на этот вопрос необходимы данные из таблиц «Клиенты» и «Операции», причем будут использованы следующие свойства схемы данных:

• 
  для каждой операции должна быть указана дата ее проведения (в каком-либо поле таблицы «Операции»);
  
• 
  таблица «Операции» должна иметь связь с таблицей «Клиенты», которая позволит узнать, какой клиент проводил каждую операцию.
  

Пример 2.

Мы интересуемся объемами (общими денежными суммами) операций клиентов, которые являются физическими лицами. Если узнать о том, что клиент является физическим лицом можно только из таблицы «Детали клиента», то для ответа на такой вопрос понадобятся данные из всех трех таблиц («Клиенты», «Операции», «Детали клиента»), причем будут использованы следующие свойства схемы данных:

• 
  для каждой операции должна быть указана ее сумма (в каком-либо поле таблицы «Операции»);
  
• 
  в таблице «Детали клиента» должно быть поле, по которому можно определить, является ли клиент физическим лицом;
  
• 
  таблица «Операции» должна иметь связь с таблицей «Клиенты», которая позволит узнать, какой клиент проводил каждую операцию.
  
• 
  таблица «Клиенты» должна иметь связь с таблицей «Детали клиента», которая позволит узнать, какие дополнительные свойства приписаны каждому клиенту.
  

Пример 3.

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

• 
  для каждой операции должна быть указана дата ее проведения (в каком-либо поле таблицы «Операции»);
  
• 
  для каждой операции должна быть указана ее сумма (в каком-либо поле таблицы «Операции»);
  
• 
  таблица «Операции» должна иметь связь с таблицей «Клиенты», которая позволит узнать, какой клиент проводил каждую операцию.
  

Чтобы удовлетворить всем этим требованиям, в таблицы, которые использовались ранее (см. часть 2 – рис.23-25), добавим денежное поле «Операции.Сумма», а текстовое поле «[Детали клиента].[Дополнительные сведения]» используем для определения статуса клиента. Эти поля необходимо заполнить данными – см. рис.1.

Рисунок 1. Таблицы базы данных, к которой будут делаться запросы
Для работы с запросами перейдите на вкладку «Запросы» окна базы данных. Здесь находятся ярлыки, позволяющие запустить процедуры создания запроса в режиме конструктора и с помощью мастера (рис.2).


Рисунок 2. Вкладка «Запросы» окна базы данных – запросы еще не созданы.

1.1. Создание запроса на выборку (подробного запроса) в режиме конструктора

Для примера 1 сформируем запрос на выборку операций, которые клиент Сидоров провел в 2009 году. Анализируя таблицы (рис.1) можно видеть, что по такому запросу должно быть выбрано две операции. Сформируем такой запрос в режиме конструктора. Для этого необходимо решить три вопроса.

1. 
   Какие таблицы будут участвовать в запросе.
   
2. 
   Какие поля этих таблиц нам необходимо узнать.
   
3. 
   На какие поля будут наложены ограничения.
   

Для нашего примера ответы звучат следующим образом

1. 
   В запросе участвуют таблицы «Клиенты» и «Операции»
   
2. 
   Необходимо узнать значения полей «Операции.Наименование  операции», «Операции.Сумма».
   
3. 
   Ограничения должны быть наложены на поля «Клиенты.ФИО» и «Операции.Дата».
   

В соответствии с этим разложим построение запроса на три шага

Шаг 1. Выбор таблиц для запроса

Используйте ярлык «Создание запроса в режиме конструктора» для запуска процедуры создания запроса. Появится диалог «Добавление таблицы» (рис.3). Из всего списка таблиц выберите те, которые будут участвовать в запросе (информация из этих таблиц потребуется для выполнения запроса). Например, если нас не интересуют детали клиента (пример 1), то таблицу «Детали клиента» можно не включать в запрос.
Обратите внимание (рис.3), что запрос может обращаться к результатам других запросов. Это возможно потому, что результат любого запроса тоже представляется в виде таблицы.


Рисунок 3. Диалог «Добавление таблицы»
После выбора двух таблиц мы получим доступ к окну «Запрос на выборку» (рис.4), где можно формировать запрос.


Рисунок 4. Окно «Запрос на выборку» - запрос еще не сформирован

Шаг 2. Выбор полей для запроса

В окне «Запрос на выборку» (рис.4) двойными щелчками выберите поля «Операции.Наименование операции», «Операции.Сумма», «Клиенты.ФИО» и «Операции.Дата». Результат показан на рис.5.


Рисунок 5. Окно «Запрос на выборку» - выбраны поля для запроса
Сначала посмотрим, как выглядит ответ на запрос, пока ограничения еще не заданы. Для этого переведем окно «Запрос на выборку» из режима «Конструктор» в режим таблицы с помощью кнопки выбора (см. рис.6)


Рисунок 6. Выбор режима для окна «Запрос на выборку»
После перевода запроса в режим таблицы мы получим следующий результат (рис.7). Сравнивая полученный ответ с исходными данными (рис.1) видим, что отобраны все операции без ограничений. При этом учтен параметр объединения, заданный для связи между таблицами «Клиенты» и «Операции» (см. часть 1, раздел 2.2.2), т.к. клиент Петров, который не провел ни одной операции, не попал в выборку.


Рисунок 7. Окно «Запрос на выборку» в режиме таблицы – ограничения не заданы
Для полноты картины посмотрим текст запроса, переведя его в режим «SQL» (способ перевода в этот режим можно найти на рис.6). Результат показан на рис.8. Обратите внимание, что в тексте запроса перечисляются выбранные поля

SELECT Операции.[Наименование операции],
Операции.Сумма,

Клиенты.ФИО,

Операции.Дата

указаны выбранные таблицы и способ их объединения

FROM Клиенты INNER JOIN Операции

а также указывается, какие поля реализуют связь между таблицами

ON Клиенты.[Код клиента] = Операции.[Код клиента]


Рисунок 8. Окно «Запрос на выборку» в режиме «SQL» – ограничения не заданы

Шаг 3. Задание ограничений

Чтобы добавить ограничения, вернемся в режим конструктора (способ перевода в этот режим можно найти на рис.6). Если ограничения будут заданы, то из выборки (рис.7) будут изъяты записи, которые не удовлетворяют ограничениям. Запрос на выборку операций, которые клиент Сидоров провел в 2009 году, можно составить так, как показано на рис.9. Обратите внимание на изменения по сравнению с рис.5:

1. 
   Для поля «Клиенты.ФИО» задано условие отбора "Сидоров". Кавычки можно не вводить – Access их установит сам.
   
2. 
   Добавлено поле с выражением Year(Дата), которое вычисляет номер года и для этого поля задано условие отбора 2009. Квадратные скобки и имя выражения задавать не надо – Access их установит сам.
   

Рисунок 9. Окно «Запрос на выборку» в режиме «SQL» – ограничения заданы
Результат отбора показан на рисунке 10. Он совпадает с нашими ожиданиями.


Рисунок 10. Окно «Запрос на выборку» в режиме таблицы – ограничения заданы
Текст запроса с ограничениями можно увидеть в режиме «SQL» (рис.11). Единственное изменение – это добавление директивы WHERE, которая отражает наши ограничения:

WHERE (((Клиенты.ФИО)="Сидоров") AND ((Year([Дата]))=2009))


Рисунок 11. Окно «Запрос на выборку» в режиме «SQL» – ограничения заданы
При сохранении запроса Access предложит назвать его имя. Давайте имена в соответствии со смыслом запроса. Для нашего примера 1 уместно имя «Операции Сидорова в 2009 году».

Построитель выражений

Если необходимо построить сложное условие отбора, используйте построитель выражений (рис.13). Он вызывается из контекстного меню для поля и условия отбора (см. на рис.12 элемент «Построить» в контекстном меню). В построителе запросов можно найти доступные функции (см. рис.13) и поля таблиц.


Рисунок 12. Окно «Запрос на выборку» – вызов построителя выражений


Рисунок 13. Окно «Построитель выражений»

Запрос с параметрами

Модифицируйте последний запрос (рис.9), заменив в нем значение «Сидоров» на переменную [Назовите ФИО клиента], как показано на рис.14. При выполнении такого запроса Access обнаружит, что поле с таким именем отсутствует и предложит ввести значение как параметр запроса (см. рис.15). Выполните запрос с параметром несколько раз, задавая разных клиентов. Проанализируйте результаты.


Рисунок 14. Окно «Запрос на выборку» – задан запрос с параметром


Рисунок 15. Диалог «Введите значение параметра» для запроса с параметром

Запрос для примера 2

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


Рисунок 16. Окно «Запрос на выборку» с запросом для примера 2

1.2. Создание перекрестного (итогового) запроса в режиме конструктора

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

1. 
   Поля, по которым делается группировка.
   
2. 
   Итоговые значения, которые вычисляются внутри групп.
   

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

Рисунок 17. Окно «Запрос на выборку»
с использованием групповых операций для примера 3
Обратите внимание на строку «Групповая операция», где заданы все нужные для итогового запроса параметры. Чтобы увидеть эту строку, используйте контекстное меню (рис.18).


Рисунок 18. Выбор показа строки групповых операций
в контекстном меню окна «Запрос на выборку»
Результатом выполнения запроса является следующая таблица (рис.19). Из нее видно, что клиент Сидоров в 2009 году провел 2 операции на общую сумму 664 руб., а клиент Иванов – одну операцию на сумму 1230 руб. в 2008 году. Проверьте, соответствует ли этот результат исходным данным (рис.1).


Рисунок 19. Окно «Запрос на выборку» с результатом перекрестного запроса

1.3. Создание запроса на выборку (подробного запроса) с помощью мастера

Рассмотрим другой способ создания запроса для примера 1 – с помощью мастера. Для запуска мастера используйте ярлык «Создание запроса с помощью мастера» на вкладке «Запросы» окна базы данных (рис.2). Мастер создает простой запрос за три шага (рис.20-22).
В диалоге первого шага выбираются поля, которые будут участвовать в запросе (рис.20).


Рисунок 20. Первый шаг мастера «Создание простых запросов» - выбор полей
На втором шаге (рис.21) можно выбрать тип запроса (подробный или итоговый). Мы строим подробный запрос (запрос на выборку), поэтому просто нажимаем кнопку «Далее».


Рисунок 21. Второй шаг мастера «Создание простых запросов» - выбор типа запроса
На третьем шаге (рис.22) дается возможность дать имя запроса, выйти в режим редактирования запроса. Мастер не позволяет задать ограничения, необходимые для примера 1, поэтому нам придется выбрать действие «Изменить макет запроса». В результате по готовности будет выведено окно «Запрос на выборку» (рис. 23).


Рисунок 22. Финальный шаг мастера «Создание простых запросов» - сохранение и выбор режима просмотра запроса


Рисунок 23. Запрос, построенный мастером, в окне «Запрос на выборку»
В окне «Запрос на выборку» придется задать нужные нам ограничения (см. раздел 1.1, шаг 3, рис.9).
Как видим, процедура создания запроса для примера 1 с помощью мастера ненамного облегчает построение запроса, поскольку не позволяет задать ограничения.
Постройте с помощью мастера запрос для примера 2.

1.4. Создание перекрестного (итогового) запроса с помощью мастера

Создадим с помощью мастера перекрестный запрос для примера 3. Ниже приведены шаги построения запроса (рис.24-27), которые позволяют добиться нужного результата. Как и в предыдущем случае, сначала выбираются поля, участвующие в запросе (рис.24), затем выбирается тип запроса (рис.25) – нам необходим итоговый запрос. При этом в окне второго шага становится доступной кнопка «Итоги…», которая выводит в диалог «Итоги», где можно задать нужные нам групповые операции (рис.26). После этого мастер анализирует выбранные поля и группировки и предлагает задать их детали (рис.26).


Рисунок 24. Первый шаг мастера «Создание простых запросов» - выбор полей


Рисунок 25. Второй шаг мастера «Создание простых запросов» - выбор типа запроса


Рисунок 26. Диалог «Итоги» - задание групповых операций


Рисунок 27. Третий шаг мастера «Создание простых запросов» - выбор интервалов группировки
На финальном шаге (рис.28) задается имя запроса и выбирается режим его просмотра. В данном случае нам удалось с помощью мастера задать все необходимые свойства запроса, поэтому можно не выбирать «Изменить макет запроса». Однако полезно посмотреть полученный результат и сравнить его с тем, что у нас получилось при использовании конструктора (см. раздел 1.2, рис.17). Для этого выйдем в режим конструктора для построенного запроса (рис.29).


Рисунок 28. Финальный шаг мастера «Создание простых запросов» - сохранение и выбор режима просмотра запроса


Рисунок 29. Перекрестный запрос, построенный мастером,
в окне «Запрос на выборку», режим конструктора
На рисунке 29 обратим внимание на следующие моменты. По сути, все поля запроса (кроме второго и четвертого) совпадают с построенным «вручную» запросом (рис.17). Отличия во втором и четвертом поле имеют следующий смысл.

• 
  В четвертом поле используется функция count(*) вместо count(Сумма). Отличие в том, что count(*) считает общее количество записей в группе, а count(Сумма) считает все записи, в которых задано (не оставлено пустым) значение поля «Сумма». Если поле «Сумма» является обязательным (оставить пустым это поле не удастся), то результат в обоих случаях будет одинаковым.
  
• 
  Второе поле содержит формулу Format$([Операции].[Дата];'yyyy'), которая выводит номер года в таблицу результатов запроса. Обратите внимание, что пятое поле, в котором тоже вычисляется номер года, не помечено для «Вывода на экран». Это значит, что оно не появится в таблице результатов запроса, а будет использоваться только для группировки.
  

Группировка по кварталам

Сравнивая второе и четвертое поля на рис.29, можно сделать вывод, что мастер построил запрос, который предусматривает лишние операции – в двух столбцах делается одно и то же. Чтобы понять, почему мастер так делает, попробуйте построить такой же запрос, но с группировкой не по годам, а по кварталам (см. рис.26). Вы увидите, что во втором поле появится формула
Format$([Операции].[Дата];'\К\вq yyyy')
а в четвертом поле будет стоять формула
Year([Операции].[Дата])*4+DatePart('q';[Операции].[Дата])-1
Легко догадаться, что функция DatePart определяет номер квартала. Поэтому если во втором поле мы увидим значение вида «Кв3 2008», то значение в 4 поле будет равно 2008*4+3-1. В этом варианте значения полей 2 и 4 не совпадают. Мы видим, что поле 2 используется для показа пользователю, а поле 4 – для СУБД. При этом второе поле является текстовым, а четвертое – числовым. Так что ответ на наш вопрос сводится к эффективности, т.к. группировка по числовому полю выполняется быстрее, чем по текстовому полю.