Об'єднання файлів з кількома доступами: повний посібник з об'єднання та консолідації даних

Коли ви працюєте з кількома бази даних de Microsoft Access, рано чи пізно виникає потреба об'єднати їх без втрати інформації чи розриву зв'язків. У реальному світі поширеною є ситуація, коли різні копії однієї й тієї ж бази даних, окремі таблиці для кожного проекту або періодичний імпорт із зовнішніх систем. Знання того, як їх добре об'єднати, є ключовим фактором між стабільною системою та купою дублікатів.

У цьому практичному посібнику ми крок за кроком розберемо все, що вам потрібно: від того, як приєднання в Access та його типах, до хитрощів із запитами UNION, вирішення колізій автоматичної нумерації, виявлення конфліктів та рекомендацій, коли два файли .accdb розвивалися окремо. Ми також включаємо корисний огляд поєднувати PDF з VBA гріх Adobe Acrobat, якщо цього вимагає ваш робочий процес.

Що насправді означає об'єднання кількох файлів Access?

Злиття — це не просто копіювання та вставка. На практиці це означає об'єднання гомологічних таблиць, підтримку цілісності посилань та визначення критеріїв для вирішення конфліктів. дублікати або конфліктуючі записиЦе також передбачає розуміння того, як Access пов’язує таблиці та як об’єднуються дані з різних джерел, що робиться за допомогою JOIN у запитах або за допомогою запитів UNION, коли потрібно об’єднати сумісні набори результатів.

Перш ніж почати, варто переглянути структуру, ключі та зв'язки кожного файлу. Знання того, які таблиці є головними, які залежними, а які поля діють як ключі (первинні та зовнішні), заощадить вам багато клопоту. Невелика підготовка, така як активація каскадні оновлення У чітко визначених зв'язках це може запобігти зависанню дочірніх записів під час зміни ідентифікатора.

Зв'язки та об'єднання в Access: основа всього

База даних створюється з таблиць, пов'язаних одна з одною спільними полями. В Access ці зв'язки перетворюються на лінії з'єднання в режимі конструктора запитів: лінія з'єднує поля, які діють як зв'язки, і визначає спосіб об'єднання даних. У зв'язках «один до багатьох» Access навіть відображає 1 та символ ∞ на лінії, якщо ви застосували посилальну цілісність.

Об'єднання працюють як розумний фільтр: вони визначають, які рядки з кожного боку збігаються та як. Окрім фільтрації, об'єднання робить кожну пару, яка відповідає умові, окремим рядком у результаті. Ось чому важливо знати тип об'єднання, який ви використовуєте, і, якщо необхідно, відкрити властивості комбінації (двічі клацніть на лінії), щоб налаштувати поведінку.

Типи об'єднань в Access

Внутрішнє з'єднання

Внутрішнє об'єднання повертає лише рядки з обох таблиць, які мають співпадаючі значення в полях об'єднання. Це найпоширеніший тип, і Access створює його автоматично, коли виявляє сумісні зв'язки або поля. Якщо тип не вказано, Access припускає ВНУТРІШНЄ З'ЄДНАННЯ за замовчуванням.

Коли ви додаєте дві таблиці до запиту, якщо вони мають спільне поле сумісних типів (і одна з них є первинним ключем), Access, як правило, створює об'єднання самостійно. Якщо ви працюєте із запитами як джерелами, вам часто доводиться створювати зв'язок вручну, перетягуючи одне поле поверх іншого в сітці конструктора, що призводить до відображення лінія з'єднання.

Синтаксис SQL INNER JOIN у реченні FROM з будь-яким допустимим оператором реляції (дорівнює, нерівно, більше/менше ніж тощо):

FROM tabla1 INNER JOIN tabla2
  ON tabla1.campo1 = tabla2.campo2

Зверніть увагу, що, за винятком дуже специфічних випадків, найпоширенішим оператором є рівність; використання інших операторів створює те, що відомо як нерівні комбінації (ми побачимо це нижче).

Ліве та праве зовнішнє з'єднання (LEFT JOIN/RIGHT JOIN)

Зовнішнє об'єднання включає всі рядки з одного боку, навіть якщо вони не збігаються з іншого. При лівому об'єднанні перераховуються всі рядки з лівої таблиці, а з правого - лише відповідні рядки; при правому об'єднанні відбувається навпаки. Це дуже корисно для збереження «осиротілі» записи з одного боку, коли ви перевіряєте охоплення або повноту.

Щоб перетворити внутрішнє об'єднання на зовнішнє, у режимі конструктора двічі клацніть лінію об'єднання, виберіть параметр включення всіх записів з одного боку, натисніть кнопку OK, і ви побачите, що лінія відображає стрілка напрямку що вказує, з якої таблиці включено всі рядки.

Ви можете поєднати LEFT JOIN з INNER JOIN в одному запиті, і Access не знатиме, який з них виконати першим. У цьому випадку з'являється помилка неоднозначності. Рішення таке змінити порядок запиту, розділити його на підзапити або уточнити порядок обчислення, переупорядкувавши об'єднання, щоб не було двозначностей.

Синтаксис SQL-операцій LEFT/RIGHT JOIN у реченні FROM:

FROM tabla1 LEFT JOIN tabla2
  ON tabla1.campo1 = tabla2.campo2

FROM tabla1 RIGHT JOIN tabla2
  ON tabla1.campo1 = tabla2.campo2

Повне зовнішнє з'єднання (FULL OUTER) з використанням UNION

Access не включає ПОВНЕ ЗОВНІШНЄ ОБ'ЄДНАННЯ як таке, але ви можете імітувати його, використовуючи UNION для об'єднання результату ЛІВОГО ОБ'ЄДНАННЯ з результатом ПРАВОГО ОБ'ЄДНАННЯ та фільтруючи, щоб уникнути дублікатів збігів рядків. Ідея проста: об'єднати всі ряди з обох боків і зіставляти їх там, де є збіг, залишаючи нулі там, де його немає.

Типова схема роботи:

1. Створіть запит з LEFT JOIN у полі об'єднання.
2. Скопіюйте SQL-запит, змініть LEFT на RIGHT та вставте під UNION.
3. У частині RIGHT додайте речення WHERE, яке перевіряє, чи ключове поле лівої таблиці є IS NULL щоб не дублювати збіги.

Таким чином, ви відтворюєте ефект ПОВНОГО ЗОВНІШНЬОГО елемента в Access навіть без вбудованої підтримки, зберігаючи послідовний та повний результат в обох напрямках.

Перехресні з'єднання

Перехресне з'єднання поєднує кожен рядок однієї таблиці з кожним рядком іншої (декартів добуток). Зазвичай це трапляється, коли ви додаєте таблиці до запиту та забуваєте визначити їхній зв'язок, що призводить до тисяч беззмістовних з'єднань та тривалий час виконанняТим не менш, є корисні сценарії: наприклад, вивчення всіх можливих комбінацій клієнтів з різними теоретичними відсотками прибутковості (91 клієнт помножити на 5 відсотків = 455 гіпотетичних рядків).

Якщо ви бачите надмірну кількість записів у вашому запиті, перевірте, чи всі таблиці мають явне об'єднання. Зазвичай перетягування правильних полів достатньо, щоб уникнути випадкового перехресного об'єднання та повернутися до стабільний результат.

Нерівні об'єднання (non-equi JOIN)

Об’єднання не обов’язково мають базуватися на рівності. Ви можете використовувати такі оператори, як >, <, >=, <= або <>, для об’єднання діапазонів, прапорців або складніших умов. В Access ці типи об’єднань не створюються у візуальному режимі: потрібно перейти до Перегляд SQL, знайдіть оператор = та замініть його потрібним. Важливо: Якщо ви зміните його на нерівність, ви не зможете повторно відкрити запит у конструкторі, доки не повернете оператор до =.

Запити UNION: об'єднання результатів та гармонізація типів

Об'єднання (UNIONs) – ідеальний спосіб об'єднати результати кількох запитів в одну віртуальну таблицю, якщо вони мають однакову кількість стовпців та сумісні типи. Вони є ключовими, коли потрібно імітувати ПОВНЕ ПРИЄДНАННЯ або коли ви хочете об’єднати транзакції різного характеру в одному поданні.

Типовий випадок: у вас є запит на транзакції з продуктом, і ви хочете розділити кількість на два стовпці: «Купити» та «Продати». Перший підхід полягає у використанні 0 у стовпці, який не застосовується, та об'єднанні частини продажів з частиною покупок. Якщо пізніше ви заміните 0 на Null Щоб уникнути відображення нулів, може виникнути побічний ефект: Access визначає тип даних кожного стовпця з першого оператора SELECT в UNION.

Якщо перший SELECT повертає Null (не числове значення), Access може «вирішити», що стовпець є текстовим або null, що впливає на решту. Якщо ви спробуєте помістити порожні рядки «» у початковий SELECT, Access обробляє їх як текст і, перетягуючи, перетворює весь стовпець на текст (ви помітите це, оскільки в технічному описі вони вирівняні по лівому краю).

Простий трюк для примусово обробляти стовпець як числовий і дозволити значення Null: Використовуйте такий вираз, який завжди повертає Null, але примусово обчислює числовий вивід:

IIf(False, 0, Null)

Застосовується до ОБ'ЄДНАННЯ купівель та продажів, a дійсна структура може бути:

SELECT , , , ,
       IIf(False, 0, Null) AS Buy,  AS Sell
FROM   
UNION
SELECT , , , ,
        AS Buy, Null AS Sell
FROM   
ORDER  BY  DESC;

Розширений варіант: додайте «шаблон» SELECT, який визначає типи та не повертає рядки. Це шаблон SELECT з ДЕ Хибно, який встановлює тип кожного стовпця, не впливаючи на результат:

SELECT 0 AS , Date() AS ,
       "" AS , "" AS ,
       0 AS Buy, 0 AS Sell
FROM   
WHERE  False
UNION
SELECT , , , , Null AS Buy,  AS Sell
FROM   
UNION
SELECT , , , ,  AS Buy, Null AS Sell
FROM   
ORDER  BY  DESC;

Такий підхід дозволяє Access правильно розпізнавати числові типи з Null, уникаючи небажаних перетворень на текст, коли в будь-якій секції UNION є порожні рядки.

Посібник: Об'єднання двох розгалужених баз даних Access

Класичний сценарій: Двоє людей почали вносити дані до резервної копії, і тепер у вас є два майже ідентичні файли з різними змінами. Перше, що потрібно усвідомити, це те, що Access, якщо ви цього не планували, не зберігає поле «останні зміни» автоматично для кожного запису. Ви можете додати його зараз за допомогою Now() та подій форми, але якщо розгалуження вже відбулося, вам знадобляться правила, щоб визначити, яка версія кожного запису залишитися.

Рекомендовані кроки для безпечного продовження:

1. Резервне копіювання та стиснення/відновленняСкопіюйте все та запустіть команди «Стиснути» та «Виправити» для кожного файлу, щоб почати з чистого аркуша.
2. Структура аудитуПорівняйте таблиці та поля, визначте первинний ключ для кожної таблиці та зверніть увагу на зв’язки. Якщо можливо, додайте поле «Дата останньої редакції» та заповніть його найточнішою інформацією, яку ви маєте (дати замовлення, дата створення тощо).
3. Вирішує колізії автоматичної нумераціїЯкщо обидві бази даних мають спільні ідентифікатори AutoNumber, які можуть конфліктувати, застосуйте трюк "shift". Змініть поле AutoNumber в одній з баз даних на Numeric (Long Integer) та запустіть оновлення, додавши максимальний ідентифікатор з іншої бази даних. Таким чином, ви гарантуєте, що на ідентифікатор не наступають.

Прикладом оновлення для переміщення ідентифікаторів у головній таблиці може бути щось на кшталт: додати до максимального значення, виявленого в іншій базі даних. Це дозволяє уникнути дублікатів і, завдяки каскадні оновлення У зв'язках дочірні таблиці посилаються на новий ідентифікатор.

Потім імпортуйте всі таблиці з налаштованої бази даних до «цільової» бази даних та створіть запит доданих даних щоб об’єднати набори. Якщо є конфлікти (однаковий ідентифікатор з різним вмістом), визначте політику: зберігати запис з найновішою датою або зберігати обидва з суфіксом і позначити один як «потребує перегляду».

Для виявлення дублікатів та конфліктів дуже корисні шаблони запитів на збіги/незбіги записів. Наприклад, щоб знайти незбіги записів на іншому боці:

SELECT A.*
FROM   TablaA AS A
LEFT JOIN TablaB AS B
  ON A.ID = B.ID
WHERE  B.ID IS NULL;

А щоб порівняти дві версії з однаковим ідентифікатором, можна перерахувати рядки, де відрізняється стовпець, що цікавить. На практиці зручно створювати представлення «очікується узгодження» та вирішувати кожен випадок окремо, коли немає однозначного поля дати.

Нарешті, перевірте зв'язки у вікні "Зв'язки" (меню "Інструменти"). Включіть усі таблиці та зв'яжіть їх за ключами. Активуйте Каскадне оновлення коли це має сенс: тобто, якщо ви зміните ідентифікатор на первинному, він поширюватиметься на вторинний і не розриватиме посилання.

Імпортуйте та об'єднуйте кілька таблиць одночасно

У деяких робочих процесах вікно доступу до даних дозволяє вибрати до п'яти таблиць в одній операції імпорту та визначити парні об'єднання. Процес складається з вибрати столи, перенесіть їх до області підготовки та об’єднайте їх по два, використовуючи спільне ключове поле (наприклад, CustomerID). Якщо значення збігаються, результат збігається з окремими записами.

У схемі з більш ніж двома таблицями «ліва таблиця» є першою в парі, а «права таблиця» – другою. Ви можете об’єднати кілька таблиць у ланцюг (Customer із Orders, а потім Orders з Product). Зверніть увагу, що одна й та сама таблиця може бути лівою в одному об’єднанні та правою в іншому, залежно від сегмента ланцюжка. Ви також можете об'єднання за кількома полями коли одне поле недостатньо унікальне.

Обмеження, яке слід пам’ятати: у деяких джерелах, таких як Apache Drill, візуальний редактор дозволяє об’єднувати лише дві таблиці. Якщо ви хочете об’єднати три або більше, зробіть це в Режим SQL та уникайте дужок в оголошенні з'єднання, оскільки вони не підтримуються в цьому конкретному конекторі. Це дуже специфічна деталь, але корисно знати, коли дані надходять з різнорідних джерел.

Виключіть неправильні комбінації

Якщо Access створює непотрібне автоматичне об’єднання або ви помилилися під час перетягування полів, безпечно видаліть його: у сітці конструктора клацніть рядок і натисніть клавішу Delete або клацніть правою кнопкою миші та виберіть ВидаленняЗбереження лише необхідних комбінацій є ключем до уникнення випадкових перехресних переходів та отримання надійних результатів.

Корисні практики використання INNER, LEFT та RIGHT JOIN

Емпіричне правило полягає в тому, щоб перевірити кожну комбінацію за допомогою простих фільтрів, щоб переконатися, що вона повертає очікувану кількість рядків. Якщо ви змішуєте INNER та LEFT в одному запиті, і Access позначає неоднозначність, розділіть проблему: спочатку створіть частину INNER у збереженому запиті, а потім використовувати це як джерело у консультації, яка застосовуватиме ЛІВІ, що зробить послідовність оцінювання чіткою.

Ще одна практична порада: документуйте в коментарях (властивості запиту), що робить кожне з'єднання та чому. Коли інші колеги відкриють базу даних, вони оцінять причину незвичайного RIGHT JOIN або нерівномірне поєднання що фільтрує за діапазонами.

SQL-хитрощі в Access для щоденного використання

Окрім наведених вище шаблонів (IIf(False,0,Null) та шаблон SELECT з WHERE False), пам’ятайте, що в Access можна використовувати реляційні оператори в об’єднаннях для вирішення сценаріїв перекриття дат або сегментів. Влучно встановлена ​​нерівність часто замінює складні підзапити і дозволяє вам ввести саме той набір рядків, який вам потрібен.

А якщо в UNION ви хочете навмисно зберегти дублікати рядків (наприклад, тому що вам потрібно підрахувати екземпляри), замініть UNION на UNION ALLAccess видаляє дублікати за допомогою UNION, але не за допомогою UNION ALL, що може бути вирішальним залежно від вашого остаточного звіту.

Додатково: Об'єднання PDF з Access за допомогою VBA (без Acrobat)

Деяким людям потрібно витягти кілька PDF-звітів та об’єднати їх в один з Access. Якщо у вас немає Adobe Acrobat, існують утиліти VBA, які вирішують цю проблему лише за кілька рядків. Популярним підходом є підхід розробника. Альберт Каллал, який пропонує простий модуль для об’єднання PDF-файлів з Access без використання Acrobat. Це оптимізоване рішення для автоматизації створення уніфікованих досьє або результатів безпосередньо з процесів вашої бази даних.

Загальна ідея полягає в тому, щоб передати йому список маршрутів PDF-файли і цільовий вивід; модуль піклується про їх зшивання. Таким чином, ви інтегруєте Об'єднання PDF-файлів як подальший крок, так само як запуск запитів або експорт звітів.

З огляду на все вищесказане, ви тепер маєте повну карту: розуміння того, як Access об'єднує дані (INNER/LEFT/RIGHT, нерівні та перехресні), емуляція ПОВНОГО ЗОВНІШНЬОГО об'єднання за допомогою UNION, вимірювання впливу типів даних у запитах UNION, налаштування зв'язків та автонумерації для уникнення конфліктів, а також практична автоматизація, така як об'єднання PDF-файлів. Коли ви освоїте ці блоки, об'єднання кількох файлів Access Це перетворюється з одіссеї на контрольований та повторюваний процес, навіть за умови паралельної роботи команд.