Access 2007

Вы научитесь создавать все эти типы запросов с помощью Конструктора.

oМастер Создание простых запросов (Simple Query Wizard) позволяет вам создать обычный запрос, отображающий подмножество данных таблицы. Этот тип запроса вы создали в предыдущем разделе.

oМастер Создание перекрестных запросов (Crosstab Query Wizard) создает перекрестный запрос, который позволяет анализировать большие объемы данных с помощью разных вычислений. Один такой запрос рассматривается в разд. "О перекрестных запросах" главы 9.

oМастер запросов Поиск повторяющихся записей (Find Duplicates Query Wizard) похож на мастер Создание простых запросов, за исключением того, что он включает условие отбора, отображающее только те записи, в которых используются совпадающие значения. Если вы забыли создать первичный ключ или создать уникальный индекс в вашей таблице (см. разд. "Предотвращение дублирования значений с помощью индексов "главы 4), такой запрос поможет удалить возникший беспорядок.

oМастер запросов Поиск записей, не имеющих подчиненных (Find Unmatched Query Wizard) похож на мастер Создание простых запросов, за исключением того, что он содержит условие отбора, извлекающее несвязанные записи из подчиненных таблиц. Его можно применить для поиска заказа, который не связан ни с одним конкретным клиентом. Вы узнаете, как он работает в разд. "Поиск несвязанных записей" далее в этой главе.

Рис. 6.10. На первом этапе выполнения Мастера запроса вы выбираете из небольшого набора основных типов запросов

3.Щелкните мышью кнопку ОК.

На экране появляется первое окно мастера запросов.

4.В. раскрывающемся списке Таблицы и запросы (Tables/Queries) выберите таблицу, содержащую нужные вам данные. Затем добавьте поля, которые вы хотите видеть в окне результатов запроса, как показано на рис. 6.11.

221

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

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

Рис. 6.11. Для добавления поля выберите его в списке Доступные поля и щелкните мышью кнопку со стрелкой > (или дважды щелкните его мышью). Можно добавить все поля сразу, если щелкнуть мышью кнопку с двойной стрелкой » и удалить поля, выбрав их в списке Выбранные поля и щелкнув мышью кнопку <. В данном примере в запрос включены три поля

5.Щелкните мышью кнопку Далее (Next).

Если в вашем запросе есть числовое поле, Мастер запроса предложит создать итоговый запрос, объединяющий строки в группы и вычисляющий итоги или средние значения. Вы узнаете больше об итоговых запросах в главе 7. Сейчас, если у вас есть такой выбор, отметьте переключатель подробный (вывод каждого поля каждой записи) (Detail), a затем нажмите кнопку Далее (Next).

На экране появляется завершающее окно Мастера запроса (рис. 6.12).

6.Введите имя запроса в поле Задайте имя запроса (What title do you want for your query?).

7.Если вы хотите подкорректировать запрос, выберите переключатель Изменить макет запроса (Modify the query design). Если же вы довольны тем, что получилось, для выполнения запроса выберите переключатель Открыть запрос для просмотра данных (Open the query to view information).

Одна из причин, по которой вам может понадобиться переход в режим Конструктора - вставка условий отбора или фильтрации (см. разд. "Фильтрация" главы 3) для извлечения

222

определенных строк. К сожалению, вы не можете задать условия отбора в Мастере запроса. 8. Щелкните мышью кнопку Готово (Finish).

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

Работа с запросами | Конструктор → Результаты → Выполнить (Query Tools | Design → Results → Run).

Рис. 6.12. На последнем этапе вы выбираете имя для вашего запроса и немедленный вывод результатов или дальнейшее усовершенствование запроса в Конструкторе

Малоизвестная или недооцененная возможность. Запросы на базе запросов

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

Чаще всего запрос строится на другом запросе, если вы хотите повторно использовать плоды вашего напряженного труда и упростить сложные запросы. Например, вы хотите создать запрос к БД Boutique Fudge, который находит клиентов, поместивших заказ в текущем месяце, и извлекает всю информацию об этих клиентах. На основе этого запроса, возможно, вам захочется создать более специализированный итоговый запрос (см. разд. "Итоговые данные" главы 7), который объединяет клиентов в группы с учетом города, в котором они живут, и подсчитывает, сколько у вас недавних покупателей в каждом регионе.

223

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

Режим SQL

За кадром каждый запрос в действительности — текстовая команда, написанная на экзотическом языке, именуемом SQL (Structured Query Language, язык структурированных запросов). Язык SQL— один из главных компонентов мира БД, он поддерживается всеми основными программными продуктами для управления БД, хотя и с незначительными вариациями и индивидуальными отличительными особенностями.

Примечание

Гуру БД все еще спорят о том, как произносить название языка: "Эс-ку-эль" (что исторически корректно) или "Сиквэл" (именно это название применяется в программном обеспечении корпорации Microsoft SQL Sever). В этой книге мы полагаем, что вы пользуетесь более продвинутым вариантом "Сиквэл".

Когда вы создаете запрос в Конструкторе (или с помощью Мастера запроса), программа Access генерирует соответствующую команду SQL. Когда вы сохраняете запрос, Access просто сохраняет в вашей БД текст этой команды. Это все, что нужно программе для выполнения запроса в дальнейшем.

Чаще всего вы не будете тратить много времени на обдумывание SQL, прячущегося за вашими запросами. Но иногда нужно посмотреть на него повнимательнее. Далее перечислены возможные причины.

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

■Вы хотите выучить язык SQL. Это неплохая идея, если вы хотите делать карьеру администратора БД, но это лишнее, если вы привязаны к программе Access.

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

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

■Вы — суперспециалист по написанию кода на языке SQL, и Конструктор запросов только тормозит вашу работу.

Для просмотра команды SQL для вашего запроса щелкните заголовок вкладки правой кнопкой мыши и выберите команду Режим SQL (SQL View). На рис. 6.13 показана команда, которую вы увидите.

224

Рис. 6.13. На экране команда SQL для запроса TopProducts, который находит товары, стоящие больше 50 долларов. Если вас испугал этот режим, в любой момент можно вернуться в другой, щелкнув правой кнопкой мыши заголовок вкладки и выбрав Конструктор или Режим таблицы

Анализ запроса

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

1.SELECT Products.ID, Products.ProductName, Products.Price

2.FROM Products

3.WHERE (((Products.Price)>50))

4.ORDER BY Products.Price;

Проанализируем первые две строки.

■Строка 1 начинается со слова SELECT, означающего, что перед вами запрос, который выбирает записи (как и все запросы, с которыми вы имели дело в этой главе).

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

Строка 2 начинается со слова FROM, указывающего на таблицу (таблицы), которую вы исследуете. В данном случае нужные вам записи есть в таблице Products. В этих двух строках представлен законченный действующий запрос. Но часто в вашей команде будут дополнительные строки, задающие параметры фильтрации и сортировки.

■Строка 3 начинается со слова WHERE, указывающего на начало ваших условий отбора. В данном случае есть только одно условие — цена продукта должна быть больше 50 долларов. Если вы задали несколько условий отбора в разных полях, здесь будут представлены все они, объединенные с помощью оператора AND.

Примечание

Программа Access несколько странным образом применяет скобки в условиях отбора. Предложение языка SQL WHERE ( ( (Products . Price) >50) ) можно упростить до следующего WHERE Products. Price>50. Access использует скобки, поскольку они облегчают анализ сложных запросов с множественными условиями отбора.

225

■Строка 4 начинается со слов ORDER BY, которые определяют порядок сортировки.

Вданном случае записи отсортированы по возрастанию значений в поле Price (цена). Если задана сортировка по убыванию, вы увидите сокращение DESC после имени поля. Если сортируется несколько полей, вы увидите разделенный запятыми список полей.

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

Резюме приведенного урока состоит в том, что любой запрос, который вы создаете, формируется из нескольких общих ингредиентов, представленных разделами SELECT, FROM, WHERE и ORDER BY.

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

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

SELECT Products.ID, Products.ProductName, Products.Price,

Products.Description

FROM Products

WHERE (((Products.Price)>100))

ORDER BY Products.Price, Products.ProductName;

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

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

Конструктор не распознает некоторые редкие методы языка SQL. Их можно применить, только откорректировав команду SQL в Режиме SQL, и после внесения этих изменений вы больше не сможете просмотреть ваш запрос в Конструкторе (пока позже не удалите неподдерживаемое изменение).

Запрос на объединение (union query) — один из примеров запросов, временами очень полезных, но не поддерживаемых в Конструкторе запросов. Запрос на объединение объединяет результаты из нескольких таблиц и затем представляет их на общем листе данных.

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

Далее приведен запрос на объединение, который представляет список имен и фамилий, полученный из двух таблиц — Customers и Employees:

SELECT Customers.FirstName, Customers.LastName FROM Customers

226

UNION

SELECT Employees . FirstName, Employees . LastName FROM Employees

Этот запрос функционирует, несмотря на то, у таблиц Customers и Employees разная структура. Но гораздо важнее то, что структура результатов запросов к обеим таблицам, в данном случае поля FirstName и LastName, совпадает.

ПРИМЕЧАНИЕ

Создать запрос на объединение можно, даже если имена столбцов отличаются — если в таблице Employees содержатся столбцы с именами F_Name и L_Name, запрос все равно будет выполняться. Программа Access просто использует имена столбцов из первого запроса при выводе результатов на лист данных.

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

Программа помещает запросы па объединение в группу Несвязанные объекты (Unrelated Objects) в области переходов и применяет для их обозначения пиктограмму, отличающуюся от пиктограммы обычного запроса (рис. 6.14).

Примечание

Если в результатах запроса на объединение выявляются совпадения, на экран выводится одна копия. Это поведение можно изменить, если заменить слово UNION словосочетанием UNION ALL. В предыдущем примере этот шаг вызовет повторное отображение в объединенных результатах человека, являющегося и клиентом, и сотрудником.

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

227

Запросы на объединение — это хороший способ соединения двух аналогичных таблиц, которые были разделены из соображений производительности, безопасности или способа распространения. (См. в разд. "Подготовка вашей базы данных" главы 18 различные причины деления одного набора данных на несколько разных таблиц.) Эти запросы неудобны для обработки отношений "родитель — потомок". Для этой задачи вам нужны запросы на выборку с объединением таблиц (join queries), описанные в следующем разделе.

Для тех, кто понимает.

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

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

Предположим, что вы переименовываете таблицу Orders (заказы) в таблицу Sales (продажи) и поле DatePlaced (дата размещения) в поле OrderDate (дата заказа). В следующий раз, когда вы запустите запрос FirstQuarterOrders_2007 (см. рис. 6.6), то с удивлением обнаружите, что он все еще действует. Программа Access знает о том, что запрос FirstQuarterOrders_2007 зависит от таблицы Orders. Когда вы изменяете имена в таблице, программа соответствующим образом корректирует запрос.

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

1.Выберите Работа с базами данных → Показать или скрыть → Зависимости объектов (Database Tools → Show/Hide → Object Dependencies). В правой части окна программы Access появляется область Зависимости объектов. (Для того чтобы скрыть ее, выберите ту же последовательность еще раз.)

2.В области переходов выберите объект БД, который вы хотите исследовать.

3.В области Зависимости объектов выберите переключатель Объекты, зависящие от данного (Objects that depend on me), чтобы увидеть объекты, использующие данный, или переключатель Объекты, от которых зависит данный (Objects that I depend on), чтобы увидеть все объекты, которые использует данный объект.

4.В верхней части области Зависимости объектов щелкните кнопкой мыши ссылку Обновить (Refresh). В области Зависимости объектов выводятся все соответствующие объекты, разделенные на категории в зависимости от их типа (рис. 6.15).

Программа Access не может отследить все зависимости, например, если вам нужно проникнуть в Режим SQL для формирования запроса, который нельзя создать в режиме Конструктора. Если создается запрос на объединение (как в предыдущем примере), у Access не хватает сообразительности для того, чтобы выяснить, от каких таблиц зависит ваш запрос. Если вы изменили структуру этих таблиц, то при следующем выполнении запроса получите сообщение об ошибке, говорящее о том, что программа не может найти нужное поле или таблицу. (Для исправления ошибки необходимо снова открыть запрос в Режиме SQL и заменить имена полей или таблиц их новыми значениями.)

228

Рис. 6.15. На рисунке в области Зависимости объектов анализируется таблица Products. Отображены три таблицы, связанные с Products, и четыре запроса, использующие таблицу Products. В любой объект можно углубиться, щелкнув кнопкой мыши квадратик со знаком "плюс" (+), расположенный рядом с именем объекта. (Щелкните кнопкой мыши + рядом с именем TopProducts, чтобы выяснить, используют ли другие объекты БД данный запрос.) В конце списка находится раздел Пропущенные объекты. В нем отображен запрос на объединение CustomersAndEmployees, и это свидетельствует о том, что у программы Access нет данных о его зависимостях

Запросы и связанные таблицы

В главе 5 вы узнали, как делить данные на базовые фрагменты и сохранять их в отдельных хорошо организованных таблицах. У такого проекта есть лишь одна проблема — гораздо труднее представить общую картину, если связанные данные хранятся в разных местах. К счастью, Access обладает чудесным средством — вы можете снова соединить таблицы при выводе на экран с помощью операции объединения (join).

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

229

Далее приведено несколько примеров.

ВБД кукол-болванчиков можно отобразить список кукол (извлеченный из таблицы Dolls) совместно с данными об изготовителе каждой куклы-болванчика (из таблицыродителя Manufacturers).

ВБД школы Cacophone music можно получить список учебных классов, снабженный информацией о преподавателях.

Из БД Boutique Fudge можно извлечь список заказов, дополнив его сведениями о клиенте, сделавшем заказ.

Примечание

Вы уже научились создавать таблицы подстановок для отображений части информации из связанной таблицы. Подстановка может вывести название категории изделия из поля ProductID вместо кода изделия. Но запрос с использованием операции объединения гораздо мощнее. Он может выбрать массу сведений из связанной таблицы — гораздо больше, чем может вместить одно поле.

На рис. 6.16 показано объединение таблиц.

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

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

Access делает удивительно легким объединение двух таблиц. Первый шаг — добавление обеих таблиц в ваш запрос, с помощью диалогового окна Добавление таблицы (Show Table).

230