Сравнение товаров в Microsoft Access

Кратко: покажу, как в Microsoft Access сравнить записи двух таблиц по коду товара, получить итоговый столбец Results с именем товара из первой таблицы, а если его нет — из второй. Разберём типы JOIN, варианты с IIF и Nz, поддельный FULL OUTER через UNION, практические рекомендации по производительности и чек‑листы для проверки.

sql-queries-access

Изображение: Общий вид SQL-запросов в Microsoft Access, пример дизайнерского интерфейса и результатов.

Вопрос читателя

У меня проблема с написанием запроса в Microsoft Access. У меня есть база с двумя таблицами продуктов, содержащими общий столбец с числовым кодом продукта и связанное имя продукта. Я хочу узнать, какие продукты из Таблицы A встречаются в Таблице B. Хочу добавить столбец Results, который содержит имя продукта из Таблицы A, если оно присутствует, и имя из Таблицы B, если в Таблице A его нет. Какие советы можете дать?

Краткий ответ (в две строки)

Используйте JOINы для сопоставления по ProductID и вычисляемое поле (IIF или Nz) для создания столбца Results. Если нужен полный охват обеих таблиц, создайте «фейковый» FULL OUTER JOIN через LEFT JOIN, UNION ALL и RIGHT JOIN, либо используйте функции Access для замены NULL.

Что такое Microsoft Access и зачем это знать

Microsoft Access — это система управления базами данных (DBMS) под Windows и Mac с графическим интерфейсом поверх движка Jet/ACE. Она упрощает создание таблиц, связей и запросов, но при этом использует SQL-подобный язык. Если вы знакомы с Excel, Access даёт те же преимущества, но для реляционных данных: нормализация, индексы, JOINы и устойчивое хранение.

Определение термина: SQL — язык запросов для добавления, обновления, удаления и выборки данных из базы, а также для изменения структуры таблиц и индексов.

Отправная точка — что посмотреть перед началом

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

Что такое база данных и отношения таблиц (реляционная модель).
Как создаются таблицы и ключи (первичный ключ/внешний ключ).
Основы запросов SELECT и работы с условием JOIN.

Полезные темы для чтения: вводные по таблицам, запросам и нормализации (поиск ресурсов на русском или официальная документация Microsoft Access).

Зачем нормализовать данные (быстрое напоминание)

Представьте таблицу продаж в одном листе Excel: повторяющиеся имена клиентов, адреса, товары и т. д. Нормализация предполагает разбиение информации по логическим таблицам — клиенты, товары, заказы — чтобы избежать дублирования. Это облегчает обновление (например, при смене фамилии клиента), уменьшает объём хранения и снижает риск расхождений в данных.

normalization

Изображение: Идея нормализации — вынесение ClientID, имени и адреса в отдельную таблицу клиентов.

Типы JOIN в SQL и в Access

SQL обычно определяет INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER и CROSS JOIN. Access поддерживает INNER (по умолчанию), LEFT JOIN, RIGHT JOIN и CROSS. FULL OUTER не поддерживается напрямую; его можно симулировать через комбинацию LEFT JOIN + RIGHT JOIN и UNION ALL.

Ниже приведены краткие определения и практическое руководство по выбору:

INNER JOIN — возвращает только совпадающие строки, где ключи равны в обеих таблицах.
LEFT JOIN — возвращает все строки из левой таблицы, а из правой — только совпадающие; при отсутствии совпадения в правой части появляются NULL.
RIGHT JOIN — зеркально LEFT JOIN — все строки из правой таблицы и совпадающие из левой.
FULL OUTER — возвращает все строки из обеих таблиц; Access не поддерживает напрямую.
CROSS JOIN — декартово произведение; используется редко, в тестах или для генерации комбинаций.

Выбор зависит от цели: чтобы увидеть только пересечение — INNER; чтобы получить все из A, дополнив из B при наличии — LEFT; чтобы получить объединение обеих таблиц с приоритетом одной из них — комбинации JOIN/UNION.

Демонстрация: таблицы ProdA и ProdB

Создадим две таблицы с простым дизайном: Autonumber ProductID и поле ProductName (Текст, 255 символов). Заполним примерами. В интерфейсе Access это легко сделать через таблицы данных.

access-tables

Изображение: Таблицы ProdA и ProdB в режиме таблицы с примерами записей.

Для показа разницы я удалил некоторые записи в ProdA, чтобы в таблицах были несовпадающие идентификаторы.

Создание запроса через Query Design

В Ribbon: Create > Query Design.
Добавьте обе таблицы в диалог Show Table и нажмите Close.
Перетащите поле ProductID из ProdA на ProductID в ProdB, чтобы создать связь.
Щёлкните правой кнопкой по линии связи и выберите Join Properties, чтобы изменить тип соединения.

new-query

Изображение: Добавление таблиц в конструктор запросов.

design_view

Изображение: Перетаскивание полей между таблицами для создания связи.

join_properties

Изображение: Свойства соединения — выбор INNER, LEFT или RIGHT.

Пример INNER JOIN

Запрос выбирает имена из обеих таблиц только при совпадающем ProductID.

SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA INNER JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

При запуске будут показаны только строки, у которых ProductID встречается в обеих таблицах.

inner_join

Изображение: Результаты INNER JOIN — только совпадающие ProductID.

LEFT JOIN — приоритет левой таблицы

SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA LEFT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

LEFT JOIN вернёт все строки из ProdA; если для строки нет пары в ProdB, поля из ProdB будут NULL.

left_outer_join

Изображение: Результаты LEFT JOIN — все строки из ProdA, пустые поля из ProdB там, где совпадений нет.

RIGHT JOIN — приоритет правой таблицы

SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

RIGHT JOIN вернёт все строки из ProdB; там, где нет совпадений в ProdA, значения ProdA будут NULL.

right_outer_join

Изображение: Результаты RIGHT JOIN — все строки из ProdB, пустые поля ProdA при отсутствии совпадений.

Как получить столбец Results — IIF и Nz

Нам нужно одно результирующее поле Results: если продукт есть в ProdA — взять его имя оттуда, иначе — из ProdB.

В Access удобно использовать IIF (Immediate IF) или встроенную функцию Nz.

IIF принимает три параметра: условие, значение если True, значение если False.

IIF(ProdA.ProductID Is Null, ProdB.ProductName, ProdA.ProductName)

Важно: NULL нельзя сравнивать оператором =. Для проверки NULL в Access и VBA используют конструкцию “Is Null” или “Is Not Null”.

В SQL Access с алиасом поля:

SELECT ProdA.ProductName, ProdB.ProductName, IIF(ProdA.ProductID Is Null, ProdB.ProductName, ProdA.ProductName) AS Results FROM ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Этот запрос создаст столбец Results с ожидаемым поведением.

Альтернативный и часто более компактный вариант — функция Nz, которая возвращает заданное значение, если выражение NULL:

SELECT ProdA.ProductName, ProdB.ProductName, Nz(ProdA.ProductName, ProdB.ProductName) AS Results FROM ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Nz(ProdA.ProductName, ProdB.ProductName) означает: если ProdA.ProductName NULL — вернуть ProdB.ProductName, иначе вернуть ProdA.ProductName.

Примечание: Nz работает именно с нулевым значением (Null). Пустая строка (“”) не равна Null и должна обрабатываться отдельно, если это важно.

right_outer_with_aliased_results

Изображение: Результаты RIGHT JOIN с вычисленным полем Results.

Как получить FULL OUTER JOIN в Access (поддельный FULL OUTER)

Поскольку Access не поддерживает FULL OUTER напрямую, можно смоделировать его комбинацией LEFT JOIN и RIGHT JOIN с UNION (или UNION ALL, если нужны дубликаты):

SELECT ProdA.ProductID, ProdA.ProductName, ProdB.ProductID, ProdB.ProductName
FROM ProdA LEFT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID
UNION ALL
SELECT ProdA.ProductID, ProdA.ProductName, ProdB.ProductID, ProdB.ProductName
FROM ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID
WHERE ProdA.ProductID IS NULL;

Описание: первый запрос берёт все из ProdA и совпадения из ProdB; второй добавляет строки из ProdB, которые не встречаются в ProdA (фильтр WHERE ProdA.ProductID IS NULL гарантирует неповтор). Вместо UNION ALL можно использовать UNION для удаления дубликатов, но UNION выполняет сортировку и может быть медленнее.

И тот же подход с Results:

SELECT Nz(ProdA.ProductName, ProdB.ProductName) AS Results, ProdA.ProductID, ProdB.ProductID
FROM ProdA LEFT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID
UNION ALL
SELECT Nz(ProdA.ProductName, ProdB.ProductName) AS Results, ProdA.ProductID, ProdB.ProductID
FROM ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID
WHERE ProdA.ProductID IS NULL;

Этот приём полезен, если вам нужно увидеть все уникальные ProductID из обеих таблиц и приоритетное имя продукта.

Частые ошибки и когда подходы не работают

Смешивание типов данных: сравнивайте идентичные типы (Long/Integer, Text). Если ProductID в одной таблице хранится как текст, в другой — как число, JOIN не найдёт совпадений.
NULL vs пустая строка: Nz обрабатывает только Null; пустые строки остаются пустыми.
Дубликаты в ключах: если в таблице нет уникальности по ProductID, JOIN даст множественные строки. Убедитесь, что ProductID — уникальный идентификатор или используйте GROUP BY при необходимости.
Производительность: UNION и сложные JOINы могут быть медленными на больших таблицах. Индексируйте поля соединения.

Производительность и надёжность

Добавьте индекс на ProductID в обеих таблицах (если это поле используется в JOIN и часто в WHERE). Это значительно ускорит выполнение.
Для очень больших таблиц избегайте UNION без ALL (UNION выполняет сортировку и удаление дубликатов).
Для временных объединений больших наборов данных рассмотрите создание промежуточных таблиц (Make Table query) и индексацию их перед последующими операциями.
Регулярно выполняйте Compact and Repair Database в Access, чтобы уменьшить фрагментацию и восстановить размеры файла.

Проверка результата: тесты и критерии приёмки

Критерии приёмки

Все ProductID, которые присутствуют и в ProdA, и в ProdB, отображаются один раз с именем из ProdA в поле Results.
Если ProductID присутствует только в ProdA, Results соответствует ProdA.ProductName.
Если ProductID присутствует только в ProdB, Results соответствует ProdB.ProductName.
Нет неожиданных NULL в поле Results (если только обе таблицы не содержат NULL для имени).

Минимальные тесты (пример данных)

ProdA: (1, “Яблоко”), (2, “Груша”) ProdB: (1, “Apple”), (3, “Банан”) Ожидаем Results для ID=1 — “Яблоко”, для ID=2 — “Груша”, для ID=3 — “Банан”.
Тест с NULL: ProdA: (4, NULL), ProdB: (4, “Персик”) Ожидаем Results для ID=4 — “Персик”.
Тип данных: ProdA.ProductID как число, ProdB.ProductID как текст: JOIN не найдёт совпадений. Ожидаемый результат — проверка типов и корректировка.

Практическая методика: как шаг за шагом построить такой запрос

Убедитесь, что поля ProductID имеют согласованный тип данных.
Добавьте индексы на ProductID в обеих таблицах.
В Query Design добавьте обе таблицы и создайте связь по ProductID.
Выберите нужный тип соединения (LEFT/RIGHT) в зависимости от приоритета данных.
Добавьте вычисляемое поле Results: используйте Nz(ProdA.ProductName, ProdB.ProductName) или IIF(…).
Запустите запрос, проверьте результаты на тестовых примерах.
При необходимости комбинируйте LEFT JOIN и RIGHT JOIN через UNION ALL для полного охвата.
Добавьте WHERE/ORDER BY для финальной фильтрации и порядка вывода.

Чек-листы по ролям

Для аналитика данных:

Проверить, что ProductID соответствует бизнес-идентификатору.
Подготовить контрольный набор тестовых записей.
Проверить результаты на соответствие ожиданиям по выборке.

Для разработчика баз данных:

Установить индексы на поля соединения.
Проверить типы данных и привести при необходимости.
Добавить комментарии/описания к запросу в проекте Access.

Для администратора/оператора:

Выполнить Compact and Repair перед массовыми обновлениями.
Создать резервную копию базы перед изменениями структур таблиц.

Дополнительные подсказки и приёмы

Если нужно, чтобы Results брал значение из ProdB только при отсутствии значения в ProdA или если значение в ProdA — пустая строка, используйте выражение:

IIF(Trim(Nz(ProdA.ProductName, '')) = '', ProdB.ProductName, ProdA.ProductName) AS Results

Для агрегирования результатов по продукту (например, сумм продаж) используйте GROUP BY и агрегатные функции, но сначала определите правила приоритета имени.
Если ProductID не уникален, рассмотрите создание агрегированного представления, где вы явно выбираете MIN/Max или First/Last имя.

Решение реальных ошибок и отладка

Симптом: пустой Results при наличии ожидаемого имени

Проверьте, не равны ли типы ProductID.
Убедитесь, что в ProdA действительно есть значение (не NULL) в поле ProductName.
Используйте запросы с SELECT DISTINCT ProductID … чтобы обнаружить дубликаты.

Симптом: неожиданные дубликаты строк

Проверьте, есть ли несколько строк с одинаковым ProductID в одной из таблиц.
Используйте GROUP BY ProductID, Nz(…) и агрегатные функции для контролируемого вывода.

Безопасность и конфиденциальность данных

Если база содержит персональные данные клиентов, применяйте ограничения доступа к файлу .accdb и резервные копии.
Для соответствия локальным требованиям по защите данных (например, GDPR) минимизируйте количество дублируемых персональных данных и храните только необходимые поля.

Модель принятия решений (схема)

flowchart TD
  A[Нужно ли видеть только пересечение?] -->|Да| B[Использовать INNER JOIN]
  A -->|Нет| C[Нужно показать все из одной таблицы?]
  C -->|Да, из A| D[LEFT JOIN + IIF/Nz для Results]
  C -->|Да, из B| E[RIGHT JOIN + IIF/Nz для Results]
  C -->|Нужно всё| F[Симулировать FULL OUTER через LEFT JOIN + RIGHT JOIN + UNION ALL]

Схема: выбор подхода в зависимости от задачи: INNER / LEFT / RIGHT / поддельный FULL OUTER.

Часто задаваемые вопросы — кратко

Q: Можно ли использовать COALESCE как в других СУБД?

A: В Access нет COALESCE; вместо него используйте Nz или IIF. В некоторых версиях можно эмулировать через пользовательские функции.

Q: Что быстрее — IIF или Nz?

A: Nz обычно компактнее и понятнее для простого замещения NULL; производительность для небольших таблиц сопоставима. Для больших наборов проверьте на тестовом объёме.

Итог и рекомендации

Для вашей задачи лучше всего подходит вариант с JOIN и вычисляемым полем Results через Nz(ProdA.ProductName, ProdB.ProductName).
Если нужно покрыть обе таблицы полностью — используйте комбинацию LEFT JOIN и RIGHT JOIN через UNION ALL, чтобы получить поддельный FULL OUTER.
Индексируйте поля соединения, приведите типы данных к единому формату и проверяйте поведение NULL.

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

Короткое резюме

JOINы решают проблему сопоставления.
IIF и Nz формируют итоговый столбец Results.
FULL OUTER можно симулировать через UNION.
Индексы и корректные типы данных — ключ к производительности.

Источники для дальнейшего чтения

Официальная документация Microsoft Access (руководства по JOIN и функциям IIF/Nz).
Учебные материалы по нормализации и проектированию реляционных баз.

Сравнение товаров из двух таблиц в Microsoft Access