Создание простого запроса с помощью Мастера запросов

Запросы и фильтры

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

Основные отличия запросов и фильтров заключаются в следующем.

· Фильтры не позволяют в одной строке отображать данные из нескольких таблиц, т. е. объединять таблицы.

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

· Фильтры не могут быть сохранены как отдельный объект в окне базы данных (они сохраняются только в виде запроса).

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

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

· для просмотра подмножества записей таблицы без предварительного открытия этой таблицы или формы;

· для того чтобы объединить в виде одной таблицы на экране данные из нескольких таблиц;

· для просмотра отдельных полей таблицы;

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

Создание и изменение запроса с помощью Конструктора запросов

Для изменения уже существующих запросов и для создания новых запросов используется Конструктор запросов. Для того чтобы открыть запрос в режиме Конструктора, выделите в списке один из существующих запросов, например только что созданный запрос "Сотрудники Запрос", и нажмите кнопку Конструктор (Design) на панели инструментов окна База данных (Database).

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

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

В области панелей инструментов Access отображается панель инструментов Конструктор запросов (Queries Design). Эта панель представлена на рис. 4.6, а в табл. 4.1 приведено описание кнопок этой панели инструментов и соответствующие им команды меню.

Рис. 4.5. Запрос "Сотрудники Запрос" в режиме Конструктора запросов

Рис. 4.6. Панель инструментов Конструктор запросов

Таблица 4.1. Описание кнопок на панели инструментов

Конструктор запросов

	Кнопка	Описание	Команда меню
	Вид (View)	Отображение запроса в различных режимах. Чтобы изменить режим отображения запроса, нажмите стрелку справа от кнопки и в появившемся списке выделите нужный элемент. Если просто нажать эту кнопку, запрос будет отображен в режиме Таблицы	Вид, Режим таблицы (View, Datasheet View)
	Сохранить (Save)	Сохранение активного запроса	Файл, Сохранить (File, Save)
	Печать (Print)	Печать результатов запроса без открытия диалогового окна Печать	Нет вЬ
	Предварительный просмотр (Print Preview)	Предварительный просмотр запроса перед печатью	Файл, Предварительный просмотр (File, Print Preview)
	Орфография (Spelling)	Проверка орфографии в текстовых полях запроса	Сервис, Орфография (Tools, Spelling)

 

	Кнопка	Описание	Команда меню
	Вырезать (Cut)	Удаление выделенных объектов из запроса в буфер обмена Windows	Правка, Вырезать (Edit, Cut)
	Копировать (Сору)	Копирование выделенных объектов запроса в буфер обмена	Правка, Копировать (Edit, Copy)
	Вставить (Paste)	Вставка содержимого буфера обмена в запрос	Правка, Вставить (Edit, Paste)
	Формат по образцу (Format Painter)	Копирование параметров форматирования из одного выделенного объекта в другой такого же типа	Нет
	Отменить (Undo)	Отмена последнего изменения запроса	Правка, Отменить (Edit, Undo)
	Тип запроса (Query Туре)	Изменение типа запроса. Для изменения типа запроса щелкните по стрелке справа от кнопки и выберите из списка нужный тип запроса	Запрос, Выборка (Query, Select Query)
	Запуск (Run)	Выполнение запроса	Запрос, Запуск (Query, Run)
	Отобразить таблицу (Show Table)	Выводится диалоговое окно Добавление таблицы (Show Table)	Запрос, Добавить таблицу (Query, Show Table)
	Групповые операции (Totals)	Группировка записей в запросе и расчет итоговых значений	Вид, Групповые операции (View, Totals)
	Набор значений (Top Values)	Отобразить только первые записи запроса. Количество отображаемых записей указывается в поле ввода либо в штуках, либо в процентах	Нет
	Свойства (Properties)	Открытие окон свойств выделенных объектов: запроса или поля запроса	Вид, Свойства (View, Properties)
	Построитель (Build)	Вызов построителя для создания выражения. Кнопка доступна только тогда, когда активизировано свойство запроса или поля запроса, которое допускает ввод выражения, например Условие отбора (Criteria)	Нет
	Окно базы данных (Database Window)	Отображение окна базы данных	Окно, 1 (Window, 1)
	Новый объект (New Object)	Создание нового объекта базы данных. Чтобы выбрать тип создаваемого объекта, нажмите стрелку справа от кнопки	Нет
	Справка по Microsoft Access (Microsoft Access Help)	Вызов помощника и получение справки по Access 2002	Справка, Справка по Microsoft Access (Help, Microsoft Access Help)

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

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

1. В окне базы данных на панели объектов выбрать ярлык Запросы (Queries).

2. В списке запросов выбрать ярлык Создание запроса в режиме конструктора (Create query in Design view) или нажать кнопку Создать (New), в появившемся окне Новый запрос (New Query) выбрать Конструктор (Design view) и нажать на кнопку ОК.

3. В окне Добавление таблицы (Show Table) (см. рис. 4.5) выбрать одну или несколько таблиц или запросов для построения нового запроса и нажать кнопку Добавить (Add). Для удобства выбора таблиц и запросов в окне существуют следующие вкладки: Таблицы (Tables), на которой отображается список таблиц; Запросы (Queries), на которой отображается список запросов; Таблицы и запросы (Both), на которой отображается список таблиц и запросов вместе.

Рис. 4.7. Окно Добавление таблицы Конструктора запросов

4. После добавления всех необходимых таблиц нажать кнопку Закрыть (Close) в окне Добавление таблицы (Show Table). Все выбранные таблицы оказываются помещенными на верхней панели окна Конструктора запросов. Если таблицы связаны между собой, т. е. связи присутствуют явно на схеме данных, то эти связи также отображаются (рис. 4.8). Если связи на схеме данных не установлены, то Конструктор запросов автоматически устанавливает связи между таблицами, если они содержат поля, которые имеют одинаковые имена и согласованные типы (см. разд. "Связывание таблиц на схеме данных"гл. 2).

Рис. 4.8. Запрос по нескольким связанным таблицам

Внимание

Иногда Конструктор устанавливает лишние связи, основываясь только на именах и типах полей. Это может привести к некорректным результатам запроса, поэтому нужно обязательно проверять, как отображаются связи между таблицами в окне Конструктора запросов, и удалить вручную лишние связи. Для этого выделите лишнюю связь, щелкнув по ней левой кнопкой мыши, и нажмите клавишу <Delete>. Работа со связями в окне Конструктора запросов выполняется точно так же, как в окне Схема данных (Relationship) (см. разд. "Связывание таблиц на схеме данных" гл. 2).

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

o Выделите нужное поле в таблице-источнике (можно выделить несколько полей, пользуясь клавишами <Shift> и <Ctrl>). Если требуется включить в запрос все поля базовой таблицы, выделите поле, обозначенное звездочкой (*). Дважды щелкните левой кнопкой мыши на выделенном поле. При этом в бланке запроса появится столбец, соответствующий выбранному полю. Затем аналогично добавьте другие поля. Столбцы в бланке запроса при этом заполняются слева направо.

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

o И наконец, вместо перетаскивания полей в бланк запроса из таблицы можно просто использовать раскрывающийся список полей в строке Поле (Field) бланка запроса

Примеры разных способов включения полей в результат запроса в режиме Конструктора запросов приведены на рис. 4.9—4.11.

Рис. 4.9. Пример отбора полей для нового запроса в режиме Конструктора

Рис. 4.10. Вид окна Конструктора при выборе всех полей из таблицы

Чтобы добавить в запрос еще одну таблицу или другой запрос, необходимо:

1. Нажать кнопку Добавить таблицу (Show Table) на панели инструментов или выполнить команду меню Запрос, Добавить таблицу (Query, Show Table).

Рис. 4.11. Выбор полей запроса из раскрывающегося списка

2. В окне Добавление таблицы (Show Table) выбрать вкладку, содержащую требуемые объекты.

Замечание

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

3. Выбрать имя объекта, добавляемого в запрос.

4. Нажать кнопку Добавить (Add), а затем кнопку Закрыть (Close).

Замечание

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

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

Чтобы удалить поле из запроса, выделите нужный столбец в бланке запроса, а затем нажмите клавишу <Delete>. Чтобы выделить столбец, пользуйтесь областью выделения столбцов — узкой серой полоской над столбцами в бланке запроса. Когда вы подводите указатель мыши к этой области, он преобразуется в жирную стрелку, направленную вниз.

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

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

Рис. 4.12. Изменение названия поля в запросе

Совет

Чтобы использовать новое имя поля только в заголовках столбцов в режиме таблицы или как подпись полей в формах и отчетах, следует задать для этого поля свойство Подпись (Caption), а не переименовывать поле в бланке. (Об установке свойств поля см. разд. "Настройка свойств полей в запросе и свойств самого запроса" гл. 8.)

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

Замечание

Изменение ширины столбцов в окне Конструктора запросов или в окне расширенного фильтра не влияет на ширину столбцов запроса в режиме Таблицы или объекта, для которого применен фильтр.

В строке Условие отбора (Criteria) и в строке или (Or) указываются условия отбора записей. Такими условиями могут быть логические выражения. Например, (>30), (='Иванов'), (=10) и т. п. (0 создании выражений в условиях запроса см. разд. "Использование выражений" ниже в этой главе.)

Условия, находящиеся в одной строке, но в разных столбцах бланка, объединяются по логическому оператору And (И). Если нужно объединить условия отбора по логическому оператору Or (ИЛИ), разместите эти условия в разных строках бланка запроса. Пример использования нескольких условий отбора приведен на рис. 4.13.

Рис. 4.13. Задание критериев отбора записей в запросе

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

1. Выделить строку, которая должна оказаться под вставляемой строкой. Для этого нужно подвести указатель мыши к левой границе строки (он должен превратиться в жирную стрелку, указывающую вправо) и щелкнуть левой кнопкой мыши.

2. Выполнить команду Вставка, Строки (Insert, Rows) или нажать клавишу <Ins>. Новая пустая строка вставляется над выбранной. В нее можно вносить условия отбора.

Для удаления строки условий отбора:

1. Щелкните левой кнопкой мыши в любом месте строки.

2. Выполните команду Правка, Удалить строки (Edit, Delete Rows).

Чтобы установить порядок сортировки записей в запросе, используйте строку Сортировка (Sort). Для каждого поля, по которому должны сортироваться записи, выберите из списка соответствующий порядок сортировки: по возрастанию (Ascending) или по убыванию (Descending). По умолчанию во всех полях запроса устанавливается значение (отсутствует) (Not sorted). Пример использования поля со списком Сортировка (Sort) приведен на рис. 4.14.

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

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

Рис. 4.14. Установка порядка сортировки записей в запросе

Рис. 4.15. Запрос в режиме Конструктора

Если вы хотите очистить бланк запроса для того, чтобы создать новый, нужно выполнить команду Правка, Очистить бланк (Edit, Clear Grid).

После формирования бланка запроса его можно сохранить, нажав на кнопку Сохранить (Save) на панели инструментов или выполнив команду меню Файл, Сохранить (File, Save). При этом появляется диалоговое окно, в котором нужно ввести имя сохраняемого запроса (рис. 4.16). Запрос можно сохранить и закрыв его.

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

Рис. 4.16. Диалоговое окно Сохранение

Результат выполнения запроса, созданного в режиме Конструктора, приведен на рис. 4.17.

Рис. 4.17. Результат выполнения запроса, созданного в режиме Конструктора

Совет

Если выражение длинное, его неудобно писать в строке Поле (Field). Нажмите комбинацию клавиш <Shift>+<F2>. Появится диалоговое окно Область ввода (Zoom) (рис. 4.19), в котором вводить выражение удобнее. Можно также использовать Построитель выражений, вызвав его щелчком по кнопке Построить (Build) на панели инструментов. (Подробнее об использовании Построителя выражений и о правилах составления выражений в Access см. последний раздел данной главы.)

Рис. 4.19. Диалоговое окно Область ввода

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

Виды соединений

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

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

· в таблицах имеются поля с одинаковыми именами и согласованными типами, причем одно из полей является ключевым;

· соединение было явно задано в окне Схема данных (Relationships).

Замечание

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

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

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

Если необходимо связать данные любым отношением, кроме отношения равенства, используют соединение по отношению или тэта-соединение). Соединение по отношению не отображается в окне Схема данных (Relationships) и не выводится в окне Конструктора запросов.

Для обозначения внутренних соединений в Access используются зарезервированные слова ANSI SQL INNER JOIN, а для указания внешних соединений — слова LEFT JOIN или RIGHT JOIN. Выражение WHERE <поле1>=<поле2> соответствует внутреннему соединению, a WHERE поле1>поле2 — соединению по отношению (одному из таких соединений).

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

Внутреннее соединение

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

· Каждая таблица "один" должна иметь первичный ключ с уникальными.значениями. Отсутствие повторений значений поля или полей первичного ключа в таблице Access устанавливает автоматически.

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

· Необходимо извлечь повторяющиеся данные в новую таблицу и связать ее с таблицей, из которой эти данные были получены, отношением "многие-к-одному". Основная цель — однозначно определить извлеченные данные. Часто для этого приходится использовать первичный ключ, состоящий из нескольких полей. Для автоматизации работы по нахождению и извлечению повторяющейся информации можно использовать Мастер по анализу таблиц Microsoft Access. Этот мастер запускается при выборе команды меню Сервис, Анализ, Таблица (Tools, Analysis, Table).

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

1. Если база данных "Борей" открыта, закройте все окна, кроме окна База данных (Database). Если база данных "Борей" не загружена, загрузите ее.

2. Щелкните по ярлыку Запросы (Queries) на панели объектов окна База данных (Database), чтобы открыть список запросов, а затем нажмите кнопку Создать (New) для создания нового запроса. В появившемся диалоговом окне Новый запрос (New Query) выделите элемент Конструктор (Design View) и нажмите кнопку ОК. Access отображает диалоговое окно Добавление таблицы (Show Table) поверх пустого окна запроса.

3. Выберите таблицу "Поставщики" (Suppliers) и добавьте ее в запрос. Access покажет список полей таблицы в верхней панели окна запроса.

4. Добавьте также таблицу "Товары" (Products) и закройте диалоговое окно Добавление таблицы (Show Table). Access добавляет список полей таблицы "Товары" (Products) в окно запроса и изображает соединение полей "КодПоставщика" (SupplierlD) двух таблиц (рис. 4.20). Соединение создается автоматически, т. к. поле "КодПоставщика" является ключевым в таблице "Поставщики" и в таблице "Товары" найдено поле с тем же именем (внешний ключ). Отображаемое отношение является отношением "один-ко-многим".

Рис. 4.20. Окно Конструктора запроса с изображением соединения полей таблиц

5. Выберите поле "Марка" (ProductName) в таблице "Товары" (Products) и перетащите его в строку Поле (Field) первого столбца бланка запроса.

6. Выберите поле "Название" (CompanyName) в таблице "Поставщики" (Suppliers) и перетащите в строку Поле (Field) второго столбца. Также перетащите поля "ЕдиницаИзмерения" (QuantityPerUnit) и "Цена" (UnitPrice) таблицы "Товары" (Products) в строку Поле (Field) третьего и четвертого столбца бланка запроса, соответственно (рис. 4.21).

Рис. 4.21. Бланк запроса с заполненными полями в режиме Конструктора

Нажмите кнопку Запуск (Run) либо выберите Режим таблицы (Query View) в списке кнопки Вид (View) на панели инструментов для отображения результата запроса (рис. 4.22). Обратите внимание на то, что заголовками полей в созданном наборе записей являются подписи полей таблицы (включающие пробелы), а не их имена, в которых пробелы обычно не используются.

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

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

1. Переключитесь в режим Конструктора запроса.

2. Установите курсор в поле Сортировка (Sort) столбца "Цена", а затем нажмите клавишу <F4> или стрелку справа для вывода содержимого списка.

3. Выберите элемент по убыванию (Descending).

4. Нажмите кнопку Запуск (Run) для вывода нового результирующего множества запроса (рис. 4.23).

5. Сохраните запрос, дав ему название: "Отсортированный список товаров".

Рис. 4.23. Результат запроса с сортировкой по убывающей цене товара

Аналогично создаются запросы, в которых участвует несколько связанных таблиц, образующих цепочку. При этом в результат запроса могут быть включены поля из всех таблиц, участвующих в запросе, или только поля из таблиц, находящихся на концах такой цепочки (рис. 4.24). Во втором случае таблицы на концах цепочки оказываются связанными косвенно, и чтобы правильно построить запрос, соединяющий поля этих таблиц, необходимо включить в запрос каждую таблицу, участвующую в соединении. Например, можно вывести названия стран, товары из которых покупаются клиентами. В данном случае косвенно оказываются связанными таблицы "Клиенты" (Customers) и "Поставщики" (Suppliers), а промежуточными таблицами оказываются таблицы "Заказы" (Orders), "Заказано" (Order Details), "Товары" (Products). Microsoft Access автоматически показывает связи, в том числе и промежуточные, между таблицами.

После добавления в бланк запроса полей "Название" (CompanyName) из таблицы "Клиенты" (Customers) и "Страна" (Country) из таблицы "Поставщики" (Suppliers) выберите команду Вид, Режим SQL (View, SQL View) для того, чтобы просмотреть инструкцию SQL, соответствующую данному запросу (рис. 4.25). Соединения таблиц задаются операцией INNER JOIN...ON... При таком положении косвенные соединения основываются на выражении INNER JOIN... ON... ON... (Подробнее об инструкциях языка SQL см. разд. "Создание запросов SQL" гл. 8.)

Рис. 4.24. Соединение косвенно связанных записей

Рис. 4.25. Эквивалентная запросу инструкция SQL

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

Внешнее соединение

Внешние соединения бывают левыми или правыми. Запрос, в котором участвуют таблицы с левым внешним соединением (LEFT JOIN или *= в SQL), выводит все записи таблицы "один", в независимости от того, имеются ли соответствующие им записи в таблице "многие". И наоборот, запрос, в котором участвуют таблицы с правым внешним соединением (RIGHT JOIN или =* в SQL), выводит все записи таблицы "многие", в независимости от того, имеются ли соответствующие им записи в таблице "один".

Рассмотрим в качестве примера, как с помощью левого внешнего соединения создать запрос в базе данных "Борей" (Northwind), который обнаруживает клиентов, не сделавших ни одного заказа:

1. Создайте новый запрос и добавьте в него таблицы "Клиенты" (Customers) и "Заказы" (Orders). Поскольку эти таблицы связаны отношением "один-ко-многим", между ними сразу появляется соединяющая линия.

2. Выделите и перетащите в бланк запроса поле "Название" (CompanyName) таблицы "Клиенты" и поле "КодЗаказа" (Orderld) таблицы "Заказы".

3. Выполните запрос и посмотрите, сколько записей будет возвращать запрос: 830.

4. Вернитесь в режим Конструктора. Щелкните правой кнопкой мыши на линии, соединяющей таблицы, и выберите из контекстного меню команду Параметры объединения (Join Properties). Появится диалоговое окно, представленное на рис. 4.26. В данном окне показаны имена связанных полей в таблицах. При этом таблица на стороне "один" считается левой (независимо от того, как она расположена в окне Конструктора), а таблица на стороне "многие" — правой. Ниже расположены три переключателя:

o "1" задает внутреннее соединение;

o "2" — левое внешнее соединение;

o "3" — правое внешнее соединение.

Рис. 4.26. Диалоговое окно Параметры объединения

Замечание

Традиционно термин JOIN, который применяется к операции соединения таблиц, переводился как "объединение", хотя на самом деле операция объединения таблиц — это UNION, которая позволяет объединить все записи из одной таблицы и все записи из другой, а потом удалить дублирующиеся записи. Тем не менее, при локализации диалоговых окон Access по-прежнему, как мы видим, используется термин "объединение" по отношению к операции JOIN.

5. Задайте левое внешнее соединение, выбрав значение "2". Нажмите кнопку ОК для закрытия диалогового окна. При этом на конце линии соединения появится стрелка в сторону таблицы "многие", что указывает на левое внешнее соединение (рис. 4.27).

Рис. 4.27. Отображение левого внешнего объединения в окне Конструктора запросов

6. Установите сортировку по возрастанию для поля "КодЗаказа" (Orderld) во втором столбце бланка запроса.

7. Нажмите на панели инструментов кнопку Запуск (Run) для вывода результирующего множества запроса с левым внешним -соединением (рис. 4.28). Количество записей стало равно 832, и первые две записи содержат пустое поле "КодЗаказа".

Рис. 4.28. Результат выполнения запроса с левым внешним соединением

8. Сохраните запрос под именем "Холодные клиенты". Он нам потребуется в дальнейшем. Запросы с правым внешним соединением используются гораздо реже, т. к., если при составлении схемы данных для связи между таблицами установлена ссылочная целостность (см. раздел "Обеспечение целостности данных" гл. 2), записей в таблице "многие" (подчиненной), не связанных с записями в таблице "один" (главной), просто не может не быть.

Рекурсивное соединение

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

Для создания такого запроса мы будем использовать таблицу "Заказы" (Orders):

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

2. Добавьте в запрос копию таблицы, повторно нажав кнопку Добавить (Show Table). Access присваивает копии имя "Заказы_1". Закройте диалоговое окно Добавление таблицы (Show Table).

3. Перетащите поле "ДатаРазмещения" (OrderedDate) исходной таблицы "Заказы" на поле "ДатаИсполнения" (ShippedDate) таблицы "Заказы_1". Между таблицами возникает соединение (рис. 4.29).

4. Перетащите поля "КодЗаказа" (Orderld), "КодКлиента" (Customerld) и "ДатаРазмещения" (OrderedDate) таблицы "Заказы" в первые три столбца бланка запроса соответственно.

Рис. 4.29. Запрос с рекурсивным соединением

5. При использовании рекурсивных соединений требуется задать вывод только уникальных значений. Дважды щелкните по свободной области верхней части окна запроса, а затем в диалоговом окне Свойства запроса (Query Properties) установите значение Да (Yes) для свойства Уникальные значения (Unique Values) (рис. 4.30). Закройте окно Свойства запроса (Query Properties).

6. Установите сортировку по убыванию для поля "ДатаИсполнения" (ShippedDate), чтобы последние обслуженные заказы были отображены первыми.

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

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

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

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