Об’єднання результатів кількох запитів за допомогою запиту на об’єднання

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

SELECT Customers.Company, Customers.[Last Name], Customers.[First Name]
FROM Customers

UNION

SELECT Suppliers.Company, Suppliers.[Last Name], Suppliers.[First Name]
FROM Suppliers

UNION

SELECT Employees.Company, Employees.[Last Name], Employees.[First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

Коли ви відкриєте результат у вікні табличного подання даних, ви побачите, що всіх працівників указано з назвою зразка компанії, що, очевидно, не дуже зручно. Якщо потрібно, щоб це поле містило відомості про тип зайнятості працівника з таблиці "Постачальники" або "Клієнти", додайте фіксоване значення замість назви компанії. Ось який вигляд матиме інструкція SQL:

SELECT "Customer" As Employment, Customers.[Last Name], Customers.[First Name]
FROM Customers

UNION

SELECT "Supplier" As Employment, Suppliers.[Last Name], Suppliers.[First Name]
FROM Suppliers

UNION

SELECT "In-house" As Employment, Employees.[Last Name], Employees.[First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

Нижче наведено зразок результату у вікні табличного подання даних. Access відображає п’ять зразків записів:

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

SELECT "Customer" As Employment, [Last Name], [First Name]
FROM Customers

UNION

SELECT "Supplier", [Last Name], [First Name]
FROM Suppliers

UNION

SELECT "In-house", [Last Name], [First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

У запиті на об’єднання Access сортування можна виконати тільки один раз, проте кожен запит можна фільтрувати окремо. Узявши за основу запит на об’єднання з попереднього розділу, наведемо приклад фільтрування кожного запиту додаванням речення WHERE.

SELECT "Customer" As Employment, Customers.[Last Name], Customers.[First Name]
FROM Customers
WHERE [State/Province] = "UT"

UNION

SELECT "Supplier", [Last Name], [First Name]
FROM Suppliers
WHERE [Job Title] = "Sales Manager"

UNION

SELECT "In-house", Employees.[Last Name], Employees.[First Name]
FROM Employees
WHERE City = "Seattle"

ORDER BY [Last Name], [First Name];

Перейдіть у вікно табличного подання даних, і результати відобразяться в такому вигляді:

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

Щоб зрозуміти, як це працює, ми скористаємося запитом на об’єднання Транзакції товару в зразку бази даних Northwind. Відкрийте зразок бази даних, а потім відкрийте запит "Транзакції товару" у вікні табличного подання даних. Ось зразок останніх десяти записів:

Припустімо, потрібно розбити поле "Кількість" на два: "Придбання" й "Продаж". Крім того, уявімо, що потрібно призначити фіксоване нульове значення для поля без значення. Ось який вигляд матиме інструкція SQL такого запиту на об’єднання:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], 0 As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, 0 As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC; 

Якщо перейти у вікно табличного подання даних, останні десять записів відобразяться в такому вигляді:

А якщо потрібно зробити поля з нульовими значеннями пустими? Ви можете змінити інструкцію SQL так, щоб не відображати нічого замість нуля, додавши ключове слово Null:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], Null As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

Однак, як ви могли помітити, у вікні табличного подання даних результат запиту може бути неочікуваний. У стовпці "Придбання" кожного поля зазначено таке:

Це відбувається тому, що Access визначає типи даних полів за першим запитом. У нашому прикладі Null не число.

То що відбудеться, якщо ви спробуєте вставити пустий рядок замість пустих значень полів? SQL-інструкція цієї спроби може мати такий вигляд:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], "" As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, "" As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

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

Як вирішити цю проблему?

Потрібно зробити так, що запит очікував отримати числове значення. Цього можна добитися за допомогою такого виразу:

IIf(False, 0, Null)

Умова, яку потрібно перевірити, False (Хибність), ніколи не матиме значення True (Істина), тому вираз завжди повертатиме значення Null, проте Access усе одно оцінює обидва варіанти виводу й призначає одному з них числовий формат або значення Null.

Ось як можна скористатися цим виразом у нашому прикладі:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], IIf(False, 0, Null) As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

Зверніть увагу: змінювати другий запит не потрібно.

Якщо перейти у вікно табличного подання даних, відобразиться потрібний результат:

Ще один спосіб досягти такого ж результату – додати перед запитами в запиті на об’єднання ще один запит.

SELECT 
    0 As [Product ID], Date() As [Order Date], 
    "" As [Company Name], "" As [Transaction], 
    0 As Buy, 0 As Sell
FROM [Product Orders]
WHERE False

Для кожного поля Access поверне фіксовані значення визначених типів даних. Звісно, виводи цього запиту не мають суперечити результатам, тому ми додамо речення WHERE з оператором False:

WHERE False

Через цю маленьку хитрість запит не повертає жодних результатів, адже всі вони мають значення False. Об’єднавши цю інструкцію з наявною інструкцією SQL, ми отримаємо готову інструкцію на кшталт цього:

SELECT 
    0 As [Product ID], Date() As [Order Date], 
    "" As [Company Name], "" As [Transaction], 
    0 As Buy, 0 As Sell
FROM [Product Orders]
WHERE False

UNION

SELECT [Product ID], [Order Date], [Company Name], [Transaction], Null As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

Особливий випадок використання запиту на об’єднання – об’єднання набору записів з одним записом, що містить суму одного або кількох полів.

Нижче наведено інший приклад, який можна відтворити в зразку бази даних Northwind, щоб навчитися підбивати підсумки в запиті на об’єднання.

1. Створіть простий запит, щоб проаналізувати придбання пива (Ідентифікатор продукту=34 в базі даних Northwind), використовуючи такий синтаксис SQL:

   SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   ORDER BY [Purchase Order Details].[Date Received];

2. Перейдіть у вікно табличного подання даних, і ви побачите такі чотири записи про придбання:

   Дата отримання	Кількість
   22.01.2006	100
   22.01.2006	60
   04.04.2006	50
   05.04.2006	300

3. Щоб отримати підсумок, створіть простий агрегатний запит, скориставшись цією інструкцією SQL:

   SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))

4. Перейдіть у вікно табличного подання даних, і ви побачите тільки один запис:

   MaxOfDate Received	SumOfQuantity
   05.04.2006	510

5. Об’єднайте ці два запити в запит на об’єднання, щоб додати запис із загальною кількістю до даних про придбання:

   SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   UNION
   
   SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   ORDER BY [Purchase Order Details].[Date Received];

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

   Дата отримання	Кількість
   22.01.2006	60
   22.01.2006	100
   04.04.2006	50
   05.04.2006	300
   05.04.2006	510

Ми охопили тільки основи додавання підсумків до запиту на об’єднання. Ви також можете додати фіксовані значення до обох типів запитів, як-от "Докладно" та "Усього", щоб візуально відокремити підсумковий запис від решти записів. Дізнатися про використання фіксованих значень можна з розділу Об’єднання трьох і більше таблиць або запитів у запиті на об’єднання.

Запити на об’єднання в Access за замовчуванням містять тільки окремі записи. Та чи можна додати всі записи? Розгляньмо черговий приклад.

У попередньому розділі ми показали вам, як створити підсумковий запис у запиті на об’єднання. Змініть цей запит на об’єднання в режимі SQL, щоб додати запис "Ідентифікатор продукту=48":

SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

UNION

SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

ORDER BY [Purchase Order Details].[Date Received];

Перейдіть у вікно табличного подання даних, і ви побачите дещо незрозумілий результат:

Один запис, звісно, не повертає вдвічі більшу загальну кількість.

Ви бачите такий результат, тому що одного дня одну й ту ж кількість шоколаду було продано двічі, як це зазначено в таблиці "Відомості про замовлення на придбання". Нижче наведено зразок простого вибіркового запиту, що відображає обидва записи в зразку бази даних Northwind:

Ви могли помітити, що в раніше згаданому запиті на об’єднання немає поля "Ідентифікатор замовлення на закупівлю" та два поля не становлять двох окремих записів.

Щоб додати всі записи, скористайтеся ключовими словами UNION ALL замість слова UNION в інструкції SQL. Це, імовірно, змінить спосіб відображення результатів, тож радимо також додати речення ORDER BY, щоб визначити порядок їх сортування. Ось зразок зміненої інструкції SQL із попереднього прикладу:

SELECT [Purchase Order Details].[Date Received], Null As [Total], [Purchase Order Details].Quantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

UNION ALL

SELECT Max([Date Received]), "Total" As [Total], Sum([Quantity]) AS SumOfQuantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

ORDER BY [Total];

Перейдіть у вікно табличного подання даних, і ви побачите всі дані разом із підсумками в останньому записі:

Зазвичай запит на об’єднання може слугувати джерелом для елемента керування "поле зі списком" у формі. Ви можете скористатися цим полем зі списком, щоб вибрати значення, за яким потрібно фільтрувати записи форми. Наприклад, можна відфільтрувати записи працівників за містом.

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

1. Створіть простий вибірковий запис за допомогою цього синтаксису SQL:

   SELECT Employees.City, Employees.City AS Filter
   FROM Employees;

2. Перейдіть у вікно табличного подання даних, і ви побачите такі результати:

   Місто	Фільтр
   Seattle	Seattle
   Bellevue	Bellevue
   Redmond	Redmond
   Kirkland	Kirkland
   Seattle	Seattle
   Redmond	Redmond
   Seattle	Seattle
   Redmond	Redmond
   Seattle	Seattle

3. Мабуть, ви помітили, що ці результати не надто змістовні. Розгорніть запит і перетворіть його на запит на об’єднання за допомогою такого синтаксису SQL:

   SELECT Employees.City, Employees.City AS Filter
   FROM Employees
   
   UNION
   
   SELECT "<All>", "*" AS Filter
   FROM Employees
   
   ORDER BY City;

4. Перейдіть у вікно табличного подання даних, і ви побачите такі результати:

   Місто	Фільтр
   <Усі>	*
   Bellevue	Bellevue
   Kirkland	Kirkland
   Redmond	Redmond
   Seattle	Seattle

   Access об’єднує всі дев’ять раніше виведених записів за допомогою фіксованих значень полів <Усі> та "*".

   Оскільки це речення об’єднання не містить ключових слів UNION ALL, Access повертає тільки окремі записи. Це означає, що кожне місто повертається тільки один раз з однаковими фіксованими значеннями.

5. Виконавши запит на об’єднання, що виводить кожну назву міста тільки один раз і дає змогу швидко вибрати всі міста, ви можете скористатися цим запитом як джерелом записів для поля зі списком у формі. Використовуючи цей особливий зразок як модель, можна створити елемент керування "поле зі списком" у формі, установити цей запит його джерелом записів, установити для властивості "Ширина стовпця" стовпця "Фільтр" значення 0 (нуль), щоб приховати його, а потім установити для властивості "Приєднаний стовпець" значення 1, щоб указати індекс другого стовпця. Для властивості "Фільтр" форми можна додати наведений нижче код, щоб активувати фільтр форми з використанням значення, вибраного в елементі керування "поле зі списком".

   Me.Filter = "[City] Like '" & Me![FilterComboBoxName].Value & "'"
   Me.FilterOn = True

   Користувач форми може потім відфільтрувати записи форми за певною назвою міста або вибрати <Усі>, щоб відобразити всі записи для всіх міст.

На початок сторінки