Использование мастера запросов для построения запроса на основе главной и связанной таблицы
Сценарии извлечения данных сразу из нескольких таблиц.
Предполагаемое действие:
Использование данных из связанных таблиц для повышения информативности запроса
Объединение данных из двух таблиц при помощи их связи с третьей таблицей
Просмотр всех записей двух похожих таблиц
Использование данных из связанных таблиц для повышения информативности запроса
В некоторых случаях запрос, построенный на основе одной таблицы и предоставляющий необходимые сведения, может благодаря извлечению данных из другой таблицы стать более информативным и полезным. Предположим, имеется список кодов сотрудников, отображаемых в результатах запроса. Было бы более наглядно, если бы в этом списке были видны и имена сотрудников, но они находятся в другой таблице. Чтобы имена сотрудников отображались в результатах запроса, следует включить в запрос обе таблицы.
Убедитесь, что для таблиц задано отношение (Отношение. Связь, установленная между двумя общими полями (столбцами) двух таблиц. Существуют связи с отношением «один-к-одному», «один-ко-многим» и «многие-ко-многим».) в окне схемы данных (Окно схемы данных. Вкладка объекта, на которой можно просматривать, создавать или изменять связи между таблицами или запросами.).
Инструкции
На вкладке Средства базы данных в группе Скрыть/Отобразить выберите команду Отношения.
На вкладке Конструктор в группе Связи щелкните Все связи.
Выберите таблицы, имеющие связь между собой.
Если таблицы отображаются в окне схемы данных, проверьте, что отношение между ними уже установлено.
Отношение отображается в виде линии, соединяющей две таблицы с общими полями. Чтобы узнать, какие поля таблиц связаны отношением, дважды щелкните линию связи.
Если таблицы не отображаются в окне схемы данных, следует добавить их.
На вкладке Конструктор в группе Скрыть/Отобразить выберите команду Имена таблиц.
Дважды щелкните каждую из таблиц, которые требуется отобразить, а затем нажмите кнопку Закрыть.
Если между таблицами не установлено отношение, создайте его, перетащив поле из одной таблицы на поле другой. Поля, по которым создается отношение между таблицами, должны иметь одинаковый тип данных.
Примечание. Создать отношение между полем, имеющим тип данных «Счетчик» (Тип данных «Счетчик». Тип данных поля в базе данных Microsoft Access, в котором для каждой добавляемой в таблицу записи в поле автоматически заносится уникальное числовое значение. Поле счетчика может содержать значения трех типов: последовательные, случайные и коды репликации.), и полем, имеющим числовой тип данных (Числовой тип данных. Тип данных поля в базе данных Microsoft Access, предназначенный для сохранения чисел, используемых в математических расчетах. Для сохранения денежных значений и для выполнения расчетов с ними рекомендуется использовать тип данных «Денежный».), можно в том случае, если это поле имеет размер «длинное целое». Это часто бывает необходимо при создании отношения «один-ко-многим» (Отношение «один-ко-многим». Объединение двух таблиц, в котором значению ключа каждой записи в главной таблице соответствует значение в связанном поле или полях в нескольких записях подчиненной таблицы.).
Будет открыто диалоговое окно Изменение связей.
Для создания отношения нажмите кнопку Создать.
Дополнительные сведения о параметрах, используемых при создании отношения, см. в статье Создать, изменить или удалить отношения.
Закройте окно схемы данных.
На вкладке Создание в группе Другие щелкните Мастер запросов.
В диалоговом окне Новый запрос выберите вариант Создание простых запросов и нажмите кнопку ОК.
В поле со списком таблицы и запросы выберите таблицу, содержащую основные сведения, которые необходимо включить в запрос.
В списке Доступных полей выберите первое поле, которое необходимо включить в запрос, и нажмите кнопку с одинарной стрелкой вправо. Это поле будет помещено в список Выбранных полей. Повторите то же самое для каждого поля этой таблицы, которое необходимо включить в запрос. Это могут быть поля, данные из которых должны отобразиться в результатах запроса, или поля, используемые для наложения ограничений на выводимые строки путем задания определенных условий.
В поле со списком таблицы и запросы выберите таблицу, содержащую дополнительные сведения, с помощью которых требуется расширить результаты запроса.
Добавьте поля, которые следует использовать для расширения результатов запроса, в список Выбранных полей и нажмите кнопку Далее.
В группе Выберите подробный или итоговый отчет? выберите вариант Сведения или Итоги.
Если не требуется выполнять в запросе какие-либо статистические функции (Sum, Avg, Min, Max, Count, StDev или Var), выберите вариант «Сведения». Выберите вариант «Итоги», если требуется выполнить статистическую функцию. Выбрав требуемые параметры, нажмите кнопку Далее.
Нажмите кнопку Готово, чтобы просмотреть результаты.
Пример, использующий учебную базу данных «Борей»
В приведенном ниже примере используется мастер запросов, с помощью которого строится запрос, отображающий список заказов, стоимость доставки каждого заказа и фамилию сотрудника, обрабатывающего данный заказ.
Примечание. В данном примере происходит изменение сведений в учебной базе данных «Борей». Учитывая это, можно сначала сделать резервную копию учебной базы данных «Борей» и выполнять указания примера уже на резервной копии.
Откройте учебную базу данных «Борей». Закройте форму входа.
На вкладке Создание в группе Другие щелкните Мастер запросов.
В диалоговом окне Новый запрос выберите вариант Создание простых запросов и нажмите кнопку ОК.
В поле со списком таблицы и запросы выберите пункт Таблица: Заказы.
В списке Доступных полей дважды щелкните пункт КодЗаказа, чтобы поместить это поле в список Выбранные поля. Дважды щелкните пункт Стоимость доставки, чтобы поместить это поле в список Выбранные поля
В поле со списком таблицы и запросы выберите пункт Таблица: Сотрудники.
В списке Доступные поля дважды щелкните пункт Имя, чтобы поместить это поле в список Выбранные поля. Дважды щелкните пункт Фамилия, чтобы поместить это поле в список Выбранные поля.Нажмите кнопку Далее.
Поскольку создается перечень всех заказов, следует использовать подробный запрос. Если, например, суммируется стоимость доставки заказов, обрабатываемых одним сотрудником, или выполняются другие статистические функции, следует использовать статистический запрос. Выберите вариант подробный (вывод каждого поля каждой записи) и нажмите кнопку Далее.
Нажмите кнопку Готово, чтобы просмотреть результаты.
Запрос вернет перечень заказов, для каждого из которых будет указана стоимость доставки, а также имя и фамилия сотрудника, обрабатывающего данный заказ.
Объединение данных из двух таблиц при помощи их связи с третьей таблицей
Часто бывает, что данные в двух таблицах связаны друг с другом через третью таблицу. Это может быть в том случае, когда данные в первых двух таблицах связаны отношением «многие-ко-многим» (Отношение «многие-ко-многим». Если две таблицы связаны отношением «многие-ко-многим», то одной записи в каждой таблице могут соответствовать несколько записей в другой таблице. Для установления отношения «многие-ко-многим» необходимо создать третью (связующую) таблицу и добавить в нее ключевые поля из обеих таблиц.). Хорошим приемом при проектировании баз данных является разбиение одной связи с отношением «многие-ко-многим» между двумя таблицами на две связи с отношением «один-ко-многим» (Отношение «один-ко-многим». Объединение двух таблиц, в котором значению ключа каждой записи в главной таблице соответствует значение в связанном поле или полях в нескольких записях подчиненной таблицы.), в которых участвуют три таблицы. Это делается путем создания третьей (связующей) таблицы, имеющей один первичный ключ (Первичный ключ. Одно или несколько полей (столбцов), комбинация значений которых однозначно определяет каждую запись в таблице. Первичный ключ не допускает значений Null и всегда должен иметь уникальный индекс. Первичный ключ используется для связывания таблицы с внешними ключами в других таблицах.) и один внешний ключ (Внешний ключ. Одно или несколько полей (столбцов) в таблице, содержащих ссылку на поле или поля первичного ключа в другой таблице. Внешний ключ определяет способ объединения таблиц.) для каждой из связуемых таблиц. Затем создается связь с отношением «один-ко-многим» между каждым внешним ключом связующей таблицы и соответствующим первичным ключом связуемой таблицы. В этом случае следует включать в запрос все три таблицы, даже если данные извлекаются только из двух.
Построение запроса на выборку с использованием таблиц, связанных отношением «многие-ко-многим»
На вкладке Создание в группе Другие щелкните Конструктор запросов.
Должно быть открыто диалоговое окно Добавление таблицы.
В диалоговом окне Добавления таблицы дважды щелкните две таблицы, содержащие данные, которые следует включить в запрос, а также связующую их таблицу, а затем нажмите кнопку Закрыть.
Все три таблицы появятся в рабочей области конструктора запросов, связанные по соответствующим полям.
Дважды щелкните каждое поле, которое следует использовать в результатах запроса. Все выбранные поля появятся в бланке запроса (Бланк запроса. Бланк, предназначенный для определения запроса или фильтра в режиме конструктора запроса или в окне расширенного фильтра. В предыдущих версиях использовался термин бланк запроса по образцу (QBE).).
В бланке запроса для задания условий полей следует использовать строку Условия. Чтобы условия не отображались в результатах запроса, снимите флажок в строке Показать для данного поля.
Чтобы сортировать результаты по значениям полей, в бланке запроса в строке сортировки для данного поля выберите параметр по возрастанию или по убыванию (в зависимости от того, в каком направлении требуется выполнить сортировку записей).
На вкладке Конструктор в группе Результаты выберите команду Запуск.
Результаты запроса будут отображены в режиме таблицы (Режим таблицы. Представление, в котором данные из таблицы, формы, запроса, представления или хранимой процедуры отображаются в формате строк и столбцов. В режиме таблицы поддерживается изменение значений полей, добавление или удаление данных и поиск данных.).
Пример, использующий учебную базу данных «Борей»
Примечание. В данном примере происходит изменение сведений в учебной базе данных «Борей». Учитывая это, можно сначала сделать резервную копию учебной базы данных «Борей» и выполнять указания примера уже на резервной копии.
Предположим, появилась новая возможность: поставщик из Рио-де-Жанейро посетил веб-узел фирмы и имеет намерение сотрудничать с ней. Однако поставщик работает только в Рио-де-Жанейро и Сан-Паулу, городе расположенном поблизости, и поставляет все интересующие фирму категории продуктов. Являясь достаточно крупным предприятием, поставщик желает получить от фирмы гарантии доступа к достаточно большому рынку сбыта, который обеспечил бы ей годовые продажи на сумму минимум R$20,000.00 (около $9,300.00). Может ли фирма обеспечить поставщика требуемым рынком сбыта?
Данные, необходимые для ответа на этот вопрос, находятся в двух местах: в таблице «Клиенты» и в таблице «Сведения о заказах». Эти таблицы связаны друг с другом таблицей «Заказы». Отношения между этими таблицами уже заданы. В таблице «Заказы» для каждого заказа может быть указан только один клиент, и установлена связь с таблицей «Клиенты» по полю «Код клиента». Каждая запись в таблице «Сведения о заказах» связана только с одним заказом в таблице «Заказы» по полю «Код заказа». Таким образом, каждый клиент может иметь множество заказов, к каждому из которых имеются дополнительные сведения.
В данном примере следует построить перекрестный запрос, в котором будут отображены годовые продажи в городах Рио-де-Жанейро и Сан-Паулу.
