Сравнение двух таблиц в Access с целью выявления только совпадающих данных

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

Если вам нужно сравнить две таблицы Access и найти совпадающие данные, возможны два варианта действий.

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

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

В этой статье

Сравнение двух таблиц с помощью объединений
Сравнение двух таблиц с использованием поля в качестве условия

Сравнение двух таблиц с помощью объединений

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

Допустим, вы работаете в университете и хотите узнать, как недавние изменения в учебном плане по математике повлияли на оценки учащихся. В частности, вас интересуют оценки тех студентов, у которых профилирующим предметом является математика. У вас уже есть таблица, содержащая данные о профилирующих предметах, и таблица, содержащая данные о студентах, которые их изучают. Данные об оценках хранятся в таблице "Учащиеся", а данные о профилирующих предметах — в таблице "Специализации". Чтобы увидеть, как после недавних изменений в учебном плане изменились оценки у тех, кто специализируется на математике, вам нужно просмотреть записи из таблицы "Учащиеся", соответствующие записям в таблице "Специализации".

Подготовка примера данных

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

Access предоставляет несколько способов добавления этих примеров таблиц в базу данных. Вы можете ввести данные вручную, скопировать каждую таблицу в электронную программу (например, Microsoft Office Excel 2007 ), а затем импортировать листы в Access или вставить данные в текстовый редактор, например Блокнот, а затем импортировать данные из полученных текстовых файлов.

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

Специализации

Код учащегося	Год	Специализация
123456789	2005	МАТЕМ
223334444	2005	АНГЛ
987654321	2005	МАТЕМ
135791357	2005	ИСТ
147025836	2005	БИОЛ
707070707	2005	МАТЕМ
123456789	2006	МАТЕМ
223334444	2006	АНГЛ
987654321	2006	ПСИХОЛ
135791357	2006	ИСТ ИССК
147025836	2006	БИОЛ
707070707	2006	МАТЕМ

Учащиеся

Код учащегося	Год	Семестр	Учебный план	Номер предмета	Оценка
123456789	2005	3	МАТЕМ	221	A
123456789	2005	3	АНГЛ	101	B
123456789	2006	1	МАТЕМ	242	С
123456789	2006	1	МАТЕМ	224	С
223334444	2005	3	АНГЛ	112	A
223334444	2005	3	МАТЕМ	120	С
223334444	2006	1	ПОЛИТ	110	A
223334444	2006	1	АНГЛ	201	B
987654321	2005	3	МАТЕМ	120	A
987654321	2005	3	ПСИХОЛ	101	A
987654321	2006	1	МАТЕМ	221	B
987654321	2006	1	МАТЕМ	242	С
135791357	2005	3	ИСТ	102	A
135791357	2005	3	ИСТ ИССК	112	A
135791357	2006	1	МАТЕМ	120	B
135791357	2006	1	МАТЕМ	141	С
147025836	2005	3	БИОЛ	113	B
147025836	2005	3	ХИМ	113	B
147025836	2006	1	МАТЕМ	120	D
147025836	2006	1	СТАТ	114	B
707070707	2005	3	МАТЕМ	221	B
707070707	2005	3	СТАТ	114	A
707070707	2006	1	МАТЕМ	242	D
707070707	2006	1	МАТЕМ	224	С

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

Ввод примеров данных вручную

Откройте новую или существующую базу данных.
на вкладке Создание в группе Таблицы нажмите кнопку Таблица.

Access добавит в базу данных новую пустую таблицу.

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

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

По мере ввода данных Access определяет их тип для каждого поля. Для каждого поля таблицы задается тип данных, например "Число", "Текст" или "Дата/время". Это обеспечивает точный ввод данных и помогает предотвратить ошибки, например использование цифр номера телефона в вычислениях. Для этих примеров таблиц можно определить тип данных автоматически, но не забудьте проверить результаты.
Завершив ввод данных, нажмите кнопку Сохранить или клавиши CTRL+S.

Откроется диалоговое окно Сохранение документа.
В поле Имя таблицы введите имя примера таблицы и нажмите кнопку ОК.

Используйте имена образцов таблиц (например, "Специализации"), поскольку они также используются в разделах с описанием процедур в этой статье.

Завершив ввод примера данных, можете перейти к сравнению двух таблиц.

Если вас не интересует создание листа на основе примера данных, пропустите следующий раздел ("Создание листов с примерами данных").

Создание листов с примерами данных

Запустите редактор электронных таблиц и создайте пустой файл. Если вы используете Excel, при его запуске по умолчанию создается пустая книга.
Скопируйте первый пример таблицы из предыдущего раздела и вставьте его на первый лист, начиная с первой ячейки. Не забудьте скопировать строку заголовка, которая содержит имена полей примера таблицы.
Задайте для листа такое же имя, как и у примера таблицы, с помощью функций редактора электронных таблиц. Например, при вставке примера данных из таблицы Учащиеся назовите лист "Учащиеся".
Повторите шаги 2 и 3, чтобы скопировать второй пример таблицы на пустой лист и переименовать этот лист.

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

Создание таблиц базы данных на основе листов

В новой или существующей базе данных:

На вкладке Внешние данные в группе Импорт щелкните Excel.

-или-

Нажмите кнопку Дополнительно, а затем выберите редактор электронных таблиц из списка.

Откроется диалоговое окно Внешние данные — лист <имя программы>.
Нажмите кнопку Обзор, найдите и откройте файл электронной таблицы, который вы создали на предыдущих этапах, и нажмите кнопку ОК.

Откроется окно мастера импорта электронных таблиц.

По умолчанию мастер выбирает первый лист в книге (в этом примере — лист "Специализации"), и данные из этого листа появляются в нижней части страницы мастера.
Нажмите кнопку Далее.
На следующей странице мастера установите флажок Первая строка содержит названия столбцов, а затем нажмите кнопку Далее.
Вы можете изменить имена полей и типы данных или пропустить некоторые поля, воспользовавшись текстовыми полями и списками в группе Параметры поля. В этом примере вам не нужно ничего изменять. Нажмите кнопку Далее.
На следующей странице выберите вариант Не создавать ключ и нажмите кнопку Далее.
По умолчанию Access использует имя листа для новой таблицы. Оставьте имя в поле Импорт в таблицу, а затем нажмите кнопку Готово.
На странице Сохранение шагов импорта нажмите кнопку Закрыть, чтобы завершить работу мастера.
Повторите шаги с 1 по 7 для каждого листа, чтобы создать для него таблицу.

Сравнение образцов таблиц и поиск соответствующих записей с использованием объединений

Теперь все готово для сравнения таблиц "Учащиеся" и "Специализации". Так как связи между двумя таблицами не определены, вам необходимо создать объединения соответствующих полей в запросе. Таблицы содержат по несколько полей, и вам потребуется создать объединение для каждой пары общих полей: "Код учащегося", "Год", а также "Учебный план" (в таблице "Учащиеся") и "Специализация" (в таблице "Специализации"). В данном случае нас интересует только математика, поэтому можно ограничить результаты запроса с помощью условия поля.

Откройте базу данных, в которой вы сохранили примеры таблиц.
На вкладке Создание нажмите кнопку Конструктор запросов.
Дважды щелкните таблицу, содержащую записи, которые вы хотите отобразить ( в этом примере таблица Регистрация классов ), а затем дважды щелкните таблицу, с которой вы сравниваете ее . В этом примере таблица Student Majors .
Перетащите поле Код учащегося из таблицы Учащиеся в поле Код учащегося таблицы Специализации. В бланке запроса между двумя таблицами появится линия, которая показывает, что создано объединение. Дважды щелкните линию, чтобы открыть диалоговое окно Параметры объединения.
Обратите внимание на три варианта в диалоговом окне Параметры объединения. По умолчанию выбран вариант 1. В некоторых случаях требуется добавить в параметры объединения дополнительные строки из одной таблицы. Так как вам нужно найти только совпадающие данные, оставьте для объединения значение 1. Закройте диалоговое окно Параметры объединения, нажав кнопку Отмена.
Вам нужно создать еще два объединения. Для этого перетащите поле Год из таблицы Учащиеся в поле Год таблицы Специализации, а затем — поле Учебный план из таблицы Учащиеся в поле Специализация таблицы Специализации.
В таблице Учащиеся дважды щелкните звездочку (*), чтобы добавить все поля таблицы в бланк запроса.

Примечание: При использовании звездочки для добавления всех полей в бланке отображается только один столбец. Имя этого столбца включает имя таблицы, за которым следуют точка (.) и звездочка (*). В этом примере столбец получает имя Учащиеся.*.
В таблице Специализации дважды щелкните поле Специализация, чтобы добавить его в бланк.
В бланке запроса снимите флажок в строке Показать столбца Специализация.
В строке Условие отбора столбца Специализация введите МАТЕМ.
На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.

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

К началу страницы

Сравнение двух таблиц с использованием поля в качестве условия

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

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

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

Чтобы проиллюстрировать этот способ, мы используем примеры таблиц из предыдущего раздела, но в поле "Код учащегося" таблицы "Специализации" изменим числовой тип данных на текстовый. Так как нельзя создать объединение двух полей с разными типами данных, нам придется сравнить два поля "Код учащегося", используя одно поле в качестве условия для другого.

Изменение типа данных в поле "Код учащегося" таблицы "Специализации"

Откройте базу данных, в которой вы сохранили примеры таблиц.
В области навигации щелкните таблицу "Специализации" правой кнопкой мыши и выберите пункт Конструктор.

Таблица "Специализации" откроется в режиме конструктора.
В столбце Тип данных измените для поля Код учащегося тип данных Число на Текст.
Закройте таблицу "Специализации". Нажмите кнопку Да, когда вам будет предложено сохранить изменения.

Сравнение примеров таблиц и поиск соответствующих записей с использованием условия поля

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

На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.
Дважды щелкните Пункты регистрации в классах, а затем дважды щелкните Пункты обучения.
Перетащите поле Год из таблицы Учащиеся в поле Год таблицы Специализации, а затем — поле Учебный план из таблицы Учащиеся в поле Специализация таблицы Специализации. Эти поля содержат данные одного типа, поэтому для их сравнения можно использовать объединения. Для сравнения полей с данными одного типа рекомендуется использовать объединения.
Дважды щелкните звездочку (*) в таблице Учащиеся, чтобы добавить все поля таблицы в бланк запроса.

Примечание: При использовании звездочки для добавления всех полей в бланке отображается только один столбец. Имя этого столбца включает имя таблицы, за которым следуют точка (.) и звездочка (*). В этом примере столбец получает имя Учащиеся.*.
В таблице Специализации дважды щелкните поле Код учащегося, чтобы добавить его в бланк.
В бланке запроса снимите флажок в строке Показать столбца Код учащегося. В строке Условие отбора столбца Код учащегося введите Like [Учащиеся].[Код учащегося].
В таблице Специализации дважды щелкните поле Специализация, чтобы добавить его в бланк.
В бланке запроса снимите флажок в строке Показать столбца Специализация. В строке Условие отбора введите МАТЕМ.
На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.

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