Accessに２つのテーブルを結合する方法

テーブル１
販売店名　品名　　金額
Ａ販売店／いちご／５００円
Ａ販売店／みかん／３００円
Ａ販売店／りんご／８００円
Ｂ販売店／いちご／５００円
Ｂ販売店／みかん／３００円
Ｂ販売店／りんご／８００円
Ｂ販売店／ばなな／４００円
Ｃ販売店／いちご／５００円
Ｃ販売店／みかん／３００円
Ｃ販売店／りんご／８００円

テーブル２
販売店名　品名１　品名２　品名３　品名４
Ａ販売店／いちご／みかん／りんご
Ｂ販売店／いちご／みかん／りんご／ばなな
Ｃ販売店／いちご／みかん／りんご

これを下記のように結合したい
販売店名　品名１　金額　　　品名２　金額　　　品名３　金額　　　品名４　金額
Ａ販売店／いちご／５００円／みかん／３００円／りんご／８００円
Ｂ販売店／いちご／５００円／みかん／３００円／りんご／８００円／ばなな／４００円
Ｃ販売店／いちご／５００円／みかん／３００円／りんご／８００円


どのようにすればよいでしょうか？
クエリーだけでも可能ですかね？

No.1 回答者： 30246kiku 回答日時： 2016/05/04 13:53

２つの方法で

金額部分は数値であるとします
なお、希望の最終形表示での、"金額" ･･･
同じ文字列のフィールドは作成できないので 金額1、金額2 ･･･


１）DLookup を使う

SELECT 販売店名,
品名１,
DLookup("金額","テーブル１","販売店名='" & 販売店名
& "' AND 品名='" & 品名１ & "'") AS 金額１,
品名２,
DLookup("金額","テーブル１","販売店名='" & 販売店名
& "' AND 品名='" & 品名２ & "'") AS 金額２,
品名３,
DLookup("金額","テーブル１","販売店名='" & 販売店名
& "' AND 品名='" & 品名３ & "'") AS 金額３,
品名４,
DLookup("金額","テーブル１","販売店名='" & 販売店名
& "' AND 品名='" & 品名４ & "'") AS 金額４
FROM テーブル２;


２）クロス集計を使う

以下内容で、クエリ ★★ を作っておいて

SELECT 1 AS 順, 販売店名, 品名１ AS 品名 FROM テーブル２
WHERE 品名１ Is Not Null
UNION ALL
SELECT 2, 販売店名, 品名２ FROM テーブル２
WHERE 品名２ Is Not Null
UNION ALL
SELECT 3, 販売店名, 品名３ FROM テーブル２
WHERE 品名３ Is Not Null
UNION ALL
SELECT 4, 販売店名, 品名４ FROM テーブル２
WHERE 品名４ Is Not Null;


以下のクロス集計で最終形にする


TRANSFORM First(IIF(S=1,Q2.品名,Q2.金額)) AS 値
SELECT Q1.販売店名 FROM (
SELECT 1 AS S, * FROM ★★
UNION ALL
SELECT 2, * FROM ★★) AS Q1
INNER JOIN テーブル１ AS Q2
ON Q1.販売店名=Q2.販売店名 AND Q1.品名=Q2.品名
GROUP BY Q1.販売店名
PIVOT IIF(S=1,'品名','金額') & Q1.順
IN ('品名1','金額1','品名2','金額2','品名3','金額3','品名4','金額4');


余談）テーブル１だけからでも以下の様な表示は可能

どのように表示されるのかは、実際にやってみてください

TRANSFORM Sum(金額) AS 値
SELECT 販売店名, Sum(金額) AS 計 FROM (
SELECT 1 AS T, 販売店名, 品名, 金額 FROM テーブル１
UNION ALL
SELECT 2, '合計', 品名, 金額 FROM テーブル１) AS Q1
GROUP BY T, 販売店名
PIVOT 品名;

この回答へのお礼

30246kiku様ありがとうございました。
クエリ一つでこんなに出来あがるなんて、嬉しい限りです。

お礼日時：2016/05/04 17:23