ユニオン クエリを使って複数のクエリを 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];

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

上記のクエリは、共用体クエリの最初のクエリから出力フィールドの名前のみを読み取 Access ので、さらに削減できます。 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

フィールドごとに、 Access は定義したデータ型の固定値を返します。 もちろん、このクエリの出力は結果に影響してはいけないので、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 サンプル データベースの両方のレコードを示す簡単な選択クエリの結果を次に示します。

前述の共用体クエリでは、[発注書 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

   Access は、前に示した 9 つのレコードの和集合を実行し、固定フィールド値は <All> と "*" です。

   この union 句には UNION ALL が含まれていないため、 Access は個別のレコードのみを返します。つまり、各市区町村は固定の同じ値で 1 回だけ返されます。

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

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

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

ページの先頭へ