Сравнить товары в Access и объединить имена

Microsoft Access — мощная СУБД в составе Microsoft Office, которая часто кажется сложной пользователям, знакомым с Word и Excel. У неё более крутая кривая обучения, но при этом Access даёт графический дизайнер запросов и SQL-движок Jet, что упрощает работу с таблицами и объединениями данных.

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

У меня есть две таблицы товаров с общим столбцом ProductID и с именами продуктов. Хочу узнать, какие товары из Таблицы A есть в Таблице B. Мне нужен столбец Results: если товар есть в Таблице A — взять имя из A, если нет — взять имя из B. Как это сделать?

Краткий ответ (ключевая идея)

Используйте соединение (join) таблиц по ProductID, затем создайте вычисляемый столбец Results, который проверяет NULL и возвращает имя из нужной таблицы. В Access это удобно сделать с помощью IIf(Is Null…) или функции Nz для замены NULL.

Что нужно знать заранее

Если вы не работали с Access или реляционными СУБД, полезно пройти короткий ввод:

• Что такое база данных: как таблицы связаны и зачем нужен первичный ключ.
• Основы таблиц в Access: поля, типы, AutoNumber как PK.
• Основы запросов: выборка, фильтрация и объединения.

Рекомендуемые материалы для старта (на английском) — названия статей здесь сохранены для ссылки на те же руководства:

• So What is a Database? — ввод в реляционные базы данных через Excel.
• A Quick Guide To Get Started With Microsoft Access 2007 — обзор компонентов Access.
• A Quick Tutorial To Tables in Microsoft Access 2007 — создание первой базы и таблиц.
• A Quick Tutorial On Queries In Microsoft Access 2007 — базовые запросы в Access.

Изучение этих тем упростит понимание примеров ниже.

Отношения таблиц и нормализация

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

В нормализованной модели имя и адрес клиента хранятся в отдельной таблице Clients. В исходной таблице заказов остаётся только ClientID. Тогда изменение фамилии клиента выполняется один раз в таблице Clients и автоматически отражается во всех объединённых отчётах.

Преимущества: меньше ошибок, меньше дублирования, проще отчётность и экономия места.

Типы JOIN в SQL и поддержка в Access

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

• CROSS JOIN: декартово произведение (редко используется в рабочих запросах).
• INNER JOIN: только совпадающие ключи в обеих таблицах.
• LEFT JOIN: все строки левой таблицы + совпадающие справа; где нет совпадения — NULL справа.
• RIGHT JOIN: зеркально LEFT JOIN — все строки правой таблицы + совпадающие слева; где нет — NULL слева.

Ниже приведён подробный пример с двумя таблицами ProdA и ProdB.

AutoNumber — поле с автоматическим увеличением. Text (текст) по умолчанию принимает до 255 символов.

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

Создание запроса в дизайнере

1. Create > Query Design.
2. Выберите таблицы ProdA и ProdB, нажмите Add, затем Close.

3. Перетащите ProductID из ProdA на ProductID в ProdB, чтобы создать связь.

4. Правый клик по линии связи > Join Properties, чтобы выбрать тип соединения.

Опция 1 — INNER, 2 — LEFT JOIN, 3 — RIGHT JOIN.

Примеры результатов JOIN

INNER JOIN показывает только строки, где ProductID есть в обеих таблицах.

SQL, сгенерированный дизайнером для INNER JOIN:

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

LEFT JOIN показывает все строки из ProdA и только совпадающие из ProdB; там, где нет совпадения, в столбце ProdB.ProductName будет NULL.

SQL для LEFT JOIN:

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

RIGHT JOIN наоборот показывает все строки из ProdB и NULL слева там, где нет соответствия в ProdA.

SQL для RIGHT JOIN:

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

Как получить столбец Results с нужным именем

Наша задача: получить столбец Results, где будет имя из ProdA, если запись есть в ProdA, иначе — имя из ProdB.

В Access можно использовать функцию IIf (Immediate IF) и проверку на Null. Конструкция:

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

Важно: NULL нельзя сравнивать как обычные значения (NULL <> NULL и NULL = NULL оба дают неопределённость). Поэтому проверяем с помощью ключевого слова Is или функцией Is Null.

Если хотите, чтобы результирующий столбец назывался Results, используйте алиас в поле запроса:

Results: IIf(ProdA.ProductID Is Null, ProdB.ProductName, ProdA.ProductName)

В SQL это выглядит так:

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, где выбирается имя из ProdA, если оно есть, иначе из ProdB.

Альтернативы и улучшения (подходы, когда стоит применять)

1. Использовать Nz (специфично для Access):

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

Функция Nz(Поле, ЗначениеПоУмолчанию) заменяет NULL на указанное значение. Это короче и часто удобнее.

2. LEFT JOIN с приоритетом ProdA: вместо RIGHT JOIN вы можете зеркально поменять таблицы и использовать LEFT JOIN, если проще считать ProdA «левой» таблицей.

3. FULL OUTER эмуляция: если нужно видеть все записи из обеих таблиц, используйте UNION ALL лево- и правосторонних запросов, исключая дубли.

4. DLookup: для небольших объёмов можно использовать DLookup в выражении, но это медленнее и не рекомендуется для больших наборов.

5. UNION: если таблицы имеют разные структуры, можно собрать их в единый набор и потом агрегировать.

Когда этот подход не годится (ограничения и противпримеры)

• Очень большие таблицы: JOIN + выражение может быть медленным в Access. Для больших объёмов данных лучше перенести логику в серверную СУБД (SQL Server, PostgreSQL).
• Несогласованные ключи: если ProductID не уникален в одной из таблиц, результат потребует дополнительной агрегации или фильтрации.
• Разные семантики имени: если ProdA.ProductName и ProdB.ProductName представляют разные форматы (например, локализованные имена), простая логика «если есть в A, взять из A» может быть неверной.

Практическая мини-методология (шаг за шагом)

1. Проверьте уникальность ключей: убедитесь, что ProductID — ключ или как минимум однозначен в той таблице, где вы ожидаете уникальности.
2. Запустите простой INNER JOIN, чтобы увидеть пересечения.
3. Запустите LEFT JOIN и RIGHT JOIN отдельно, чтобы понять, где NULL появляются.
4. Решите приоритеты: имя из A важнее или из B?
5. Создайте вычисляемый столбец с IIf или Nz и протестируйте на краевых данных.
6. Добавьте индексы по ProductID, если запросы медленные.

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

• Аналитику:

  • Убедиться в корректности данных (нет лишних пробелов, одинаковые форматы кодов).
  • Выполнить выборки INNER, LEFT и RIGHT JOIN для анализа разниц.
  • Подготовить выборку тестовых кейсов для проверки.

• Разработчику/инженеру БД:

  • Проверить индексы по ProductID в обеих таблицах.
  • Оценить объём данных и принять решение об оптимизации (индексы, перенос в серверную СУБД).
  • Написать параметризуемый запрос/вид с вычислением Results.

• Пользователю/отчётчику:

  • Убедиться, что в отображении результатов нет неожиданных NULL.
  • Проверить, что логика выбора имени соответствует требованиям бизнеса.

Критерии приёмки (тесты/acceptance)

1. Для строки, где продукт присутствует в ProdA и ProdB, Results = ProdA.ProductName.
2. Для строки, где продукт отсутствует в ProdA, но присутствует в ProdB, Results = ProdB.ProductName.
3. Для строки отсутствующей в обеих таблицах — её нет в выборке (если JOIN настроен соответствующим образом) или Results = NULL, если ожидаемо.
4. Производительность: выполнение запроса на тестовой базе (N записей) подойдёт под требования SLA.

Технические сниппеты (шпаргалка)

INNER JOIN (пересечение):

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

LEFT JOIN (все из ProdA):

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

RIGHT JOIN (все из ProdB):

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

IIf + алиас Results:

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

Использование Nz для краткости (Access):

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

Эмуляция FULL OUTER JOIN (с осторожностью, может быть медленно):

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

Модель принятия решений (когда что использовать)

• Если нужно только узнать общие товары — используйте INNER JOIN.
• Если хотите приоритет имени из ProdA и хотите все записи ProdA — LEFT JOIN + IIf/Nz.
• Если хотите все записи из ProdB и приоритет у ProdA, но записи в ProdA могут отсутствовать — RIGHT JOIN + IIf/Nz.
• Если нужно увидеть все записи из обеих таблиц — эмулируйте FULL OUTER через UNION.

flowchart TD
  A[Есть ли уникальный ключ ProductID?] -->|Нет| B[Нормализовать данные или создать уникальный идентификатор]
  A -->|Да| C[Определить приоритет имени]
  C -->|A важнее| D[LEFT JOIN 'A left' + IIf/Nz]
  C -->|B важнее| E[RIGHT JOIN 'B right' + IIf/Nz]
  C -->|Все нужны| F[Эмуляция FULL OUTER через UNION]

Риски и меры смягчения

• Риск: медленность на больших данных. Митигирование: добавить индекс по ProductID, переносить в серверную СУБД для больших объёмов.
• Риск: неоднозначные/дублирующиеся ProductID. Митигирование: очистить данные, применить уникальные ключи или агрегировать.
• Риск: NULL воспринимаются как пустые строки. Митигирование: использовать Nz или стандартные проверки Is Null.

Сопутствующие примечания по совместимости и миграции

• Access поддерживает IIf и Nz, но не стандартную SQL-функцию COALESCE. При миграции в SQL Server замените Nz на COALESCE.
• FULL OUTER JOIN доступен в большинстве серверных СУБД, поэтому при переносе логики в SQL Server можно заменить эмуляцию на настоящий FULL OUTER JOIN.
• DLookup удобен для быстрых проверок, но для массовых операций используется JOIN.

Краткий факт-бокс

• Типы JOIN в SQL: 5 (INNER, LEFT, RIGHT, FULL, CROSS).
• Access поддерживает: INNER, LEFT, RIGHT, CROSS. FULL эмулируется.
• Основные Access-функции для работы с NULL: IIf(… Is Null …), Nz(…).

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

• Для задачи читателя достаточно JOIN + IIf или Nz.
• Проверяйте уникальность ключей и используйте индексы при больших объёмах.
• Для больших наборов данных рассмотрите перенос логики в серверную СУБД.

Important: всегда тестируйте на выборке, содержащей граничные случаи: отсутствующие ProductID, дубли, пустые строки и разный формат кодов.

Короткий анонс (100–200 слов)

Нужна быстрая проверка, какие товары из одной таблицы есть в другой и какое имя продукта показать в отчёте? В Microsoft Access это решается сочетанием JOIN и вычисляемого поля Results. Используйте LEFT или RIGHT JOIN для контроля того, чьи строки вы хотите видеть, и функцию IIf или короткую функцию Nz для выбора значения: если ProdA имеет имя — используйте его, иначе подставьте имя из ProdB. Для больших баз применяйте индексы по ProductID или переносите логику в серверный движок. В статье показаны примеры SQL, инструкции по созданию запроса через дизайнер и возможные альтернативы — от DLookup до эмуляции FULL OUTER JOIN через UNION.

Источники для дополнительного чтения: How to Learn Microsoft Access: 5 Free Online Resources.