В простейшем случае построение и использование запроса в Access заключается в выборе требуемых полей из таблицы, применении условий (если они нужны) и просмотре результатов запроса. Но чаще необходимо использовать данные, которые находятся в разных таблицах. К счастью, вы можете создавать запросы, объединяющие сведения из нескольких источников. В этой статье объясняется, когда следует получать данные сразу из нескольких таблиц и как это делать.
Выберите нужное действие
Уточнение данных в запросе с помощью данных из связанной таблицы
В некоторых случаях запрос, построенный на основе одной таблицы и предоставляющий необходимые сведения, может стать более информативным и полезным благодаря данным из другой таблицы. Например, предположим, что у вас есть список кодов сотрудников, которые выводятся в результатах запроса. Вы понимаете, что было бы более удобнее, если бы в этом списке были видны и имена сотрудников, но они находятся в другой таблице. Чтобы в результатах запроса отображались имена сотрудников, следует включить в него обе таблицы.
Использование мастера запросов для построения запроса на основе главной и связанной таблицы
-
Убедитесь, что для таблиц задано отношение в окно отношений.
Инструкции
-
На вкладке Работа с базами данных в группе Показать или скрыть выберите пункт Отношения.
-
На вкладке Конструктор в группе Связи нажмите кнопку Все связи.
-
Выберите таблицы, которые нужно связать.
-
Если таблицы отображаются в окне схемы данных, убедитесь, что отношение между ними уже установлено.
Отношение отображается в виде линии, соединяющей общие поля двух таблиц. Чтобы узнать, какие поля таблиц связаны отношением, дважды щелкните линию связи.
-
Если таблицы не отображаются в окне схемы данных, следует добавить их.
На вкладке Конструктор в группе Показать или скрыть нажмите кнопку Имена таблиц.
Дважды щелкните каждую из таблиц, которые вы хотите отобразить, а затем нажмите кнопку Закрыть.
-
-
Если между таблицами не установлено отношение, создайте его, перетащив поле из одной таблицы на поле другой. Поля, по которым создается отношение, должны иметь одинаковый тип данных.
Примечание: Создать отношение между полем с типом Тип данных "Счетчик" и полем, имеющим тип данных Числовой тип данных, можно в том случае, если это поле имеет размер "длинное целое". Это часто бывает так при создании отношение "один-ко-многим".
Откроется диалоговое окно Изменение связей.
-
Нажмите кнопку Создать для создания связи.
Дополнительные сведения о параметрах, используемых при создании отношения, см. в статье Создание, изменение и удаление отношения.
-
Закройте окно схемы данных.
-
-
На вкладке Создание в группе Запросы нажмите кнопку Мастер запросов.
-
В диалоговом окне Новый запрос выберите пункт Простой запрос и нажмите кнопку ОК.
-
В поле со списком Таблицы и запросы выберите таблицу, содержащую основные сведения, которые вы хотите включить в запрос.
-
В области Доступные поля щелкните первое поле, которое вы хотите включить в запрос, и нажмите кнопку с одинарной стрелкой вправо, чтобы переместить это поле в список Выбранные поля. Повторите то же самое для каждого поля этой таблицы, которое вы хотите включить в запрос. Это могут быть поля, данные из которых должны выводиться в результатах запроса, или поля, используемые для наложения ограничений на выводимые строки путем задания определенных условий.
-
В поле со списком Таблицы и запросы выберите таблицу, содержащую дополнительные сведения, с помощью которых вы хотите уточнить результаты запроса.
-
Добавьте поля, которые следует использовать для уточнения результатов запроса, в список Выбранные поля и нажмите кнопку Далее.
-
В группе Выберите подробный или итоговый отчет выберите вариант Подробный или Итоговый.
Если не требуется использовать в запросе какие-либо агрегатные функции (Sum, Avg, Min, Max, Count, StDev или Var), выберите подробный запрос. В противном случае выберите вариант "Сводка". Выбрав параметры, нажмите кнопку Далее.
-
Нажмите кнопку Готово для просмотра результатов.
Пример на основе базы данных "Борей"
В приведенном ниже примере используется мастер запросов, с помощью которого строится запрос, отображающий список заказов, стоимость доставки каждого заказа и фамилию сотрудника, выполнившего заказ.
Примечание: Этот пример подразумевает изменение учебной базы данных "Борей". Рекомендуем сделать ее резервную копию и выполнять инструкции, используя резервную копию.
Построение запроса с помощью мастера запросов
-
Откройте учебную базу данных "Борей". Закройте форму входа.
-
На вкладке Создание в группе Запросы нажмите кнопку Мастер запросов.
-
В диалоговом окне Новый запрос выберите пункт Простой запрос и нажмите кнопку ОК.
-
В поле со списком Таблицы и запросы выберите пункт Таблица: Заказы.
-
В списке Доступные поля дважды щелкните пункт ИД_заказа, чтобы переместить это поле в список Выбранные поля. Дважды щелкните пункт Цена доставки, чтобы переместить это поле в список Выбранные поля.
-
В поле со списком Таблицы и запросы выберите пункт Таблица: Сотрудники.
-
В списке Доступные поля дважды щелкните пункт Имя, чтобы переместить это поле в список Выбранные поля. Дважды щелкните пункт Фамилия, чтобы переместить это поле в список Выбранные поля. Нажмите кнопку Далее.
-
Так как вы создаете список всех заказов, следует использовать подробный запрос. Если нужно суммировать стоимость доставки заказов, выполненных сотрудником, или применить другую агрегатную функцию, следует использовать итоговый запрос. Выберите вариант Подробный (вывод каждого поля каждой записи) и нажмите кнопку Далее.
-
Нажмите кнопку Готово для просмотра результатов.
Запрос вернет перечень заказов, для каждого из которых будет указана стоимость доставки, а также имя и фамилия сотрудника, выполнившего его.
Объединение данных в двух таблицах с помощью их связей с третьей таблицей
Часто данные в двух таблицах связаны друг с другом через третью таблицу. Это может быть в том случае, когда данные в первых двух таблицах связаны отношение "многие-ко-многим". Хорошим приемом при проектировании баз данных является разбиение одной связи с отношением "многие-ко-многим" между двумя таблицами на две связи с отношением "один-ко-многим", в которых участвуют три таблицы. Это делается путем создания третьей (связующей) таблицы, в которой есть первичный ключ и внешний ключ для каждой из таблиц. Затем создается связь "один-ко-многим" между каждым внешним ключом связующей таблицы и соответствующим первичным ключом связуемой таблицы. В таких случаях следует включать в запрос все три таблицы, даже если вы хотите получить данные только из двух.
Создание запроса на выборку с использованием таблиц, связанных отношением "многие-ко-многим"
-
На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов.
-
Дважды щелкните две таблицы, содержащие данные, которые нужно включить в запрос, а также таблицу соединения, которая их связывает, и нажмите кнопку Закрыть.
Все три таблицы появятся в рабочей области конструктора запросов, связанные по соответствующим полям.
-
Дважды щелкните поля, которые вы хотите использовать в запросе. Каждое поле появится в бланк запроса.
-
В бланке запроса укажите условия для полей в строке Условия отбора. Чтобы поле, по которому задаются условия, не отображалось в результатах запроса, снимите флажок в строке Показать для него.
-
Чтобы отсортировать результаты по значениям поля, в бланке запроса в строке Сортировка для него выберите значение По возрастанию или По убыванию (в зависимости от того, в каком направлении вы хотите выполнить сортировку записей).
-
На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.
Access выведет результаты запроса в Режим таблицы.
Пример на основе базы данных "Борей"
Примечание: Этот пример подразумевает изменение учебной базы данных "Борей". Рекомендуем сделать ее резервную копию и выполнять инструкции, используя резервную копию.
Предположим, что у вас появилась новая возможность: поставщик из Рио-де-Жанейро нашел ваш веб-сайт и хочет с вами сотрудничать. Однако он работает только в Рио-де-Жанейро и Сан-Паулу. Компания поставляет все интересующие вас категории пищевых продуктов. Являясь довольно крупным предприятием, поставщик хочет, чтобы вы гарантировали достаточно большой рынок сбыта, который обеспечил бы ему годовые продажи объемом не менее 20 000 бразильских реалов (около 9 300 долларов США). Можете ли вы обеспечить требуемый рынок сбыта?
Данные, необходимые для ответа на этот вопрос, находятся в двух местах: в таблице "Клиенты" и в таблице "Сведения о заказе". Эти таблицы связаны друг с другом через таблицу "Заказы". Отношения между этими таблицами уже заданы. В таблице "Заказы" для каждого заказа может быть указан только один клиент, связанный с таблицей "Клиенты" по полю "ИДКлиента". Каждая запись в таблице "Сведения о заказе" связана только с одним заказом в таблице "Заказы" по полю "ИД_заказа". Таким образом, у каждого клиента может быть множество заказов, для каждого из которых есть несколько записей со сведениями.
В данном примере следует построить перекрестный запрос, в котором будут отображены годовые продажи в городах Рио-де-Жанейро и Сан-Паулу.
Открытие запроса в Конструкторе
-
Откройте базу данных "Борей". Закройте форму входа.
-
На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов.
-
Дважды щелкните Клиенты, Заказы, а затем— Сведения о заказе.
Все три таблицы появятся в рабочей области конструктора запросов.
-
В таблице "Клиенты" дважды щелкните поле "Город", чтобы добавить его в бланк запроса.
-
В бланке запроса в строке Условие отбора столбца Город введите In ("Рио-де-Жанейро","Сан Паулу). Это позволяет включить в запрос только записи о заказах клиентов из этих городов.
-
В таблице "Сведения о заказе" дважды щелкните поля "ДатаИсполнения" и "Цена".
Поля добавляются в бланк запроса.
-
В столбце бланка запроса ДатаИсполнения выберите строку Поле. Замените [ДатаИсполнения] на Год: Format([ДатаИсполнения],"yyyy"). При этом будет создан псевдоним поля (Год), позволяющий использовать только значение года из даты, указанной в поле "ДатаИсполнения".
-
В столбце бланка запроса Цена выберите строку Поле. Замените [Цена] на Продажи: [Сведения о заказе].[Цена]*[Количество]-[Сведения о заказе].[Цена]*[Количество]*[Скидка]. При этом будет создан псевдоним поля (Продажи), вычисляющий сумму продаж для каждой записи.
-
На вкладке Конструктор в группе Тип запроса щелкните элемент Перекрестная таблица.
В бланке запроса появятся две новые строки: Итоги и Перекрестная таблица.
-
В столбце бланка запроса Город щелкните строку Перекрестная таблица, а затем щелкните Заголовки строк.
Названия городов будут использоваться в качестве заголовков строк (т. е. запрос будет возвращать одну строку для каждого города).
-
В столбце Год щелкните строку Перекрестная таблица, а затем щелкните Заголовки столбцов.
Значения годов будут использоваться в качестве заголовков столбцов (т. е. запрос будет возвращать один столбец для каждого года).
-
В столбце Продажи щелкните строку Перекрестная таблица, а затем щелкните элемент Значение.
Значения продаж будут отображаться на пересечениях строк и столбцов (т. е. запрос будет возвращать одно значение продаж для каждого сочетания города и года).
-
В столбце Продажи щелкните строку Итоги, а затем щелкните элемент Sum.
Запрос будет суммировать все значения столбца.
В строке Итоги для других двух столбцов можно оставить значение по умолчанию Группировка, так как в этих столбцах требуется отобразить отдельные значения, а не агрегированные показатели.
-
На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.
Теперь у вас есть запрос, возвращающий общие годовые продажи по Рио-де-Жанейро и Сан-Паулу.
Просмотр всех записей из двух похожих таблиц
Иногда требуется объединить данные из двух таблиц, которые имеют одинаковую структуру, но расположены в разных базах данных. Рассмотрим следующий сценарий.
Предположим, вы являетесь аналитиком и занимаетесь обработкой сведений об учащихся. Вы начинаете работу над новым проектом совместной обработки данных по вашей и другой школе с целью улучшения их учебных планов. По некоторым из исследуемых вопросов удобнее просматривать записи по обеим школам вместе, словно бы они находились в одной таблице.
Вы можете импортировать данные другой школы в новые таблицы в своей базе данных, но в этом случае изменения, внесенные в базу данных другой школы, не будут отражаться в вашей базе данных. Лучшим решением было бы установить связь с таблицами другой школы, а затем создать запросы, объединяющие эти данные во время выполнения. При этом вы сможете анализировать данные в едином наборе вместо того, чтобы выполнять два отдельных анализа, а затем пытаться объединить их в один.
Чтобы просмотреть все записи из двух таблиц с одинаковой структурой, используйте запрос на объединение.
Запросы на объединение невозможно отобразить в Конструкторе. Они создаются с помощью команд SQL, которые нужно вводить на вкладке объекта в режим SQL.
Создание запроса на объединение двух таблиц
-
На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов.
-
На вкладке Конструктор в группе Тип запроса нажмите кнопку Объединение.
Запрос переключится из Конструктора в режим SQL. На данном этапе вкладка объекта в режиме SQL будет пуста.
-
В режиме SQL введите SELECT и список полей первой таблицы, которые вы хотите включить в запрос. Имена полей должны быть заключены в квадратные скобки и разделены запятыми. Когда вы закончите вводить имена полей, нажмите клавишу ВВОД. Курсор переместится на одну строку вниз в окне режима SQL.
-
Введите FROM и имя первой таблицы, включаемой в запрос. Нажмите клавишу ВВОД.
-
Если вы хотите указать условие для поля первой таблицы, введите WHERE, имя поля, оператор сравнения (обычно знак равенства =) и условие. Можно добавлять дополнительные условия к концу предложения WHERE, используя ключевое слово AND и такой же синтаксис, как и для первого условия (например, WHERE [Уровень]="100" AND [Часов]>2). После завершения ввода условий нажмите клавишу ВВОД.
-
Введите слово UNION и нажмите клавишу ВВОД.
-
Введите SELECT и список полей второй таблицы, которые вы хотите включить в запрос. Следует указать те же поля, что для первой таблицы, и в том же порядке. Имена полей должны быть заключены в квадратные скобки и разделены запятыми. Когда вы закончите вводить имена полей, нажмите клавишу ВВОД.
-
Введите FROM и имя второй таблицы, включаемой в запрос. Нажмите клавишу ВВОД.
-
Если вы хотите, добавьте предложение WHERE, как описано в шаге 6.
-
Введите точку с запятой (;), чтобы обозначить конец запроса.
-
На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.
Результаты будут отображены в режиме таблицы.
