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

В предыдущем примере мы объединили данные только из двух таблиц базы данных "Борей". Однако в запрос на объединение очень легко добавить больше таблиц. Например, в результаты приведенного выше запроса может также потребоваться включить имена сотрудников. Для этого добавьте третий запрос и объедините его с существующей инструкцией 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];

В запросе на объединение порядок записей разрешено задавать только один раз, но каждый запрос можно отфильтровать по отдельности. В этом примере мы отфильтровали каждый запрос, добавив предложение 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];

В режиме таблицы вы увидите примерно такие результаты:

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

Чтобы понять, как это работает, воспользуемся запросом Операции с товарами в образце базы данных "Борей". Откройте в этой базе данных запрос "Операции с товарами" в режиме таблицы. Последние 10 записей должны выглядеть примерно так:

Предположим, что вы хотите разделить поле "Количество" на два — для закупок и продаж. Также допустим, что вместо пустых полей вы хотите использовать статическое значение 0. Вот какой код 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; 

В режиме таблицы 10 последних записей теперь выглядят следующим образом:

Но что если мы хотим, чтобы вместо нулей отображались пустые поля? Можно попробовать изменить код 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 извлекает значения Buy, но преобразует их в текст. Вы можете определить, что это текстовые значения, так как они выровнены по левому краю в представлении таблицы. Пустая строка в первом запросе не является числом, поэтому эти результаты отображаются. Вы также заметите, что значения Продажи также преобразуются в текст, так как записи покупки содержат пустую строку.

Так как же решить эту проблему?

Решение заключается в том, чтобы запрос ожидал, что значение поля будет числом. Это можно сделать с помощью выражения:

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;

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

Рассмотрим на примере базы данных "Борей", как получить итоговое значение в запросе на объединение.

1. Создайте простой запрос, который выводит закупки пива (ИД товара = 34 в базе данных "Борей"), используя следующий синтаксис 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. В режиме таблицы теперь должна отображаться только одна запись:

   Максимум_Дата получения	Сумма_Количество
   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 по умолчанию включают только уникальные записи. Но что делать, если вы хотите вывести все записи? Рассмотрим еще один пример.

В предыдущем разделе мы показали, как добавить итоговое значение в запрос на объединение. Измените ИД товара в нем на 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];

В режиме таблицы отобразится странный результат:

Конечно же, одна запись не может возвращать двойное итоговое количество.

Если обратиться к таблице "Сведения о заказе на приобретение", можно заметить, что в один день было дважды продано одинаковое количество шоколада. Вот результат простого запроса на выборку, который выводит обе записи из базы данных "Борей":

В запрос на объединение не было включено поле "ИД заказа на приобретение", поэтому записи не считались уникальными.

Если вы хотите вывести все записи, используйте UNION ALL вместо UNION. Скорее всего, это повлияет на сортировку результатов, поэтому советуем также добавить предложение 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];

В режиме таблицы теперь выводятся все сведения в дополнение к итоговой записи:

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

Рассмотрим это на примере базы данных "Борей".

1. Создайте простой запрос на выборку, используя следующий синтаксис SQL:

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

2. В режиме таблицы должны отображаться следующие результаты:

   Город	Фильтр
   Псков	Псков
   Томск	Томск
   Самара	Самара
   Сочи	Сочи
   Псков	Псков
   Самара	Самара
   Псков	Псков
   Самара	Самара
   Псков	Псков

3. На первый взгляд кажется, что это ничего не дает. Но попробуйте расширить запрос и преобразовать его в запрос на объединение, используя следующий код SQL:

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

4. В режиме таблицы должны появиться следующие результаты:

   Город	Фильтр
   <Все>	*
   Томск	Томск
   Сочи	Сочи
   Самара	Самара
   Псков	Псков

   Access объединяет указанные выше девять записей со статическими значениями полей <Все> и "*".

   Так как это предложение не содержит UNION ALL, Access возвращает только уникальные записи, то есть каждый город выводится только один раз.

5. Теперь полученный запрос, в котором есть все уникальные названия городов, а также вариант, выбирающий все города, можно использовать как источник записей для поля со списком в форме. В этом примере можно создать поле со списком в форме, задать запрос в качестве его источника записей, указать для ширины столбца "Фильтр" значение 0 (нуль), чтобы скрыть его, а затем установить для свойства "Связанный столбец" значение 1, чтобы указать индекс второго столбца. После этого в свойство "Фильтр" самой формы можно добавить следующий код, чтобы активировать фильтр формы с учетом значения, выбранного в поле со списком:

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

   Благодаря этому пользователь формы сможет фильтровать записи, выбирая конкретный город или значение <Все>.

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