ユニオン クエリを使って複数のクエリを 1 つの結果に結合する

Northwind データベースを使用する前のセクションの例では、2 つのテーブルのデータのみを結合しました。 ところが、3 つ以上のテーブルを結合して簡単にユニオン クエリを作成することもできます。 たとえば、前の例に基づいて、クエリ出力に employees の名前を含める場合もあります。 このタスクを実行するには、次のように、3 番目のクエリを追加し、UNION キーワードをもう 1 つ使用して前の SQL ステートメントと結合します。

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];

データシート ビューに表示される結果を次に示します。 次の 5 つのレコード例が表示されます。

ユニオン クエリの最初のクエリから出力フィールドの名前を読み取るだけなので、上記のクエリは、さらに短縮できます。 2 番目と 3 番目のクエリ セクションから出力を削除したものを次に示します。

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 のユニオン クエリでは、並べ替えは 1 回しか許可されませんが、フィルター処理は各クエリで個別に行うことができます。 前のセクションのユニオン クエリを基にして、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 サンプル データベースの Product Transactions ユニオン クエリを使用します。 そのサンプル データベースを開き、Product Transactions クエリをデータシート ビューで開きます。 最後の 10 個のレコードは、次の出力のようになります。

[数量] フィールドを [購入] と [販売] の 2 つに分割するとします。 また、値のないフィールドに固定値 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 個のレコードは次のように表示されます。

この例を続けて、0 のフィールドを空にする場合はどうでしょうか。 次のように Null キーワード (keyword)を追加することで、SQL を変更してゼロではなく何も表示するように変更できます。

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 値が取得されますが、値がテキストに変換されていることがわかります。 データシート ビューで左揃えになっているため、これらはテキスト値であることを示すことができます。 最初のクエリの空の文字列は数値ではないため、これらの結果が表示されます。 また、購入レコードに空の文字列が含まれているため、Sell 値もテキストに変換されます。

では、これはどのように解決するのでしょうか?

解決するには、クエリでフィールド値が数字になるように強制します。 これには、次の式を使用します。

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;

2 番目のクエリを変更する必要はないことに注意してください。

データシート ビューに切り替えると、必要な結果が表示されます。

別の方法で同じ結果を得るには、ユニオン クエリ内のクエリの先頭にさらに別のクエリを追加します。

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

フィールドごとに、定義したデータ型の固定値が返されます。 もちろん、このクエリの出力は結果に影響してはいけないので、False に設定した WHERE 句を含めるテクニックを使用すると、これを回避できます。

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 つのセットのレコードを 1 つ以上のフィールドの合計が含まれている 1 つのレコードと結合することがあります。

ユニオン クエリで合計を取得する方法を説明するために、Northwind サンプル データベースで作成できる別の例を示します。

1. 新しい簡単なクエリを作成して、ビール (Northwind データベースでは Product ID = 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. データシート ビューに切り替えると、4 つの購入が表示されます。

   受領日	数量
   1/22/2006	100
   1/22/2006	60
   4/4/2006	50
   4/5/2006	300

3. 合計を取得するには、次の SQL を使用して、簡単な集計クエリを作成します。

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

4. データシート ビューに切り替えると、1 つのレコードのみが表示されます。

   最新受領日	数量合計
   4/5/2006	510

5. 次の 2 つのクエリをユニオン クエリに結合して、合計数量を含むレコードを購入レコードに追加します。

   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. データシート ビューに切り替えると、それぞれの合計を含む 4 つの購入とその後に数量を合計したレコードが表示されます。

   受領日	数量
   1/22/2006	60
   1/22/2006	100
   4/4/2006	50
   4/5/2006	300
   4/5/2006	510

これには、ユニオン クエリに合計を追加する基本が含まれます。 両方のクエリに “Detail” や “Total” などの固定値を含めて、合計レコードを他のレコードと視覚的に区別する場合もあります。 固定値の使用については、「3 つ以上のテーブルまたはクエリを結合してユニオン クエリにする」セクションで確認できます。

Access の共用体クエリには、既定では個別のレコードのみが含まれます。 ただし、すべてのレコードを含める場合はどうでしょうか。 別の例は、ここで役立つ場合があります。

前のセクションでは、ユニオン クエリで合計を作成する方法を示しました。 そのユニオン クエリ SQL を変更して、Product ID = 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];

データシート ビューに切り替えると、少し誤解を招く結果が表示されます。

もちろん、1 つのレコードが合計で 2 回数量を返すことはありません。

この結果が表示される理由は、Purchase Order Details テーブルに記録されているように、1 日に同じ数量のチョコレートが 2 回販売されたためです。 Northwind サンプル データベースの両方のレコードを示す簡単な選択クエリの結果を次に示します。

上記のユニオン クエリでは、Purchase Order ID フィールドが含まれていないことと、2 つのフィールドでは 2 つの異なるレコードが作成されないことがわかります。

すべてのレコードを含める場合は、SQL で UNION の代わりに UNION ALL を使用します。 これは結果の並べ替えに影響を与える可能性が高いため、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. データシート ビューに切り替えると、次の結果が表示されます。

   都市	フィルター
   <All>	*
   Bellevue	Bellevue
   Kirkland	Kirkland
   Redmond	Redmond
   Seattle	Seattle

   既に示した 9 つのレコードが結合され、<All> と "*" の固定フィールド値が付けられています。

   この union 句には UNION ALL が含まれていないため、異なるレコードのみが返されます。つまり、各都市が 1 回のみ返され、同じ固定値が付けられます。

5. 各都市名を 1 回のみ表示するユニオン クエリと共にすべての都市を効果的に選択するオプションが完成したので、このクエリをフォームのコンボ ボックスのレコード ソースとして使用できます。 この具体的な例をモデルとして使用すると、フォームのコンボ ボックス コントロールを作成し、このクエリをそのレコード ソースとして設定し、Filter 列の Column Width プロパティを 0 (ゼロ) に設定して非表示にしてから、Bound Column プロパティを 1 に設定して 2 番目の列のインデックスを指定することができます。 次に、フォーム独自の Filter プロパティで、次のようなコードを追加して、コンボ ボックス コントロールで選択した項目の値を使用するフォーム フィルターを使用可能にすることができます。

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

   これで、フォームのユーザーはフォーム レコードを特定の都市名でフィルター処理したり、<All> を選択してすべての都市に関してすべてのレコードを一覧表示したりすることができます。

ページの先頭へ