Access 多対多の結合数のカウント方法（複数テーブル）

Question

お世話になります。

先日類似の相談をさせていただいたのですが、それの応用方法についてご相談があります。

以下Table1とTable2をインプットとして、Table3を出力したいです。
「項目1」同士が、それぞれ同じ「項目2」「項目3」にいくつ関連しているかをカウントし出力したいです。

Table1からTable3の一部、Table2からTable3の一部を出すことは可能なのですが、
あわせ技で一括で出す方法があればご教示いただきたいです。
クエリ、またSQLで実現したいと考えております。
ご教示の程よろしくお願いいたします。

【Table1】        
項目1 項目2
sys1 A
sys2 A
sys3 A
sys1 B
sys2 B
sys1 C
sys3 C
sys1 D
sys3 D

【Table2】        
項目1 項目3
sys1 a
sys2 a
sys4 a
sys1 b
sys4 b

【Table3】        
項目4 項目5 項目6 項目7
sys1 sys2 2 1
sys1 sys3 3
sys2 sys3 1
sys1 sys4 2

Comments

[sazi]

そもそも、多対多とは特定できない事を意味しますので、そのような設計はするべきではありません。

[s_tom]

@saziさん
おっしゃる通りだと思います。
ただ、複雑に関連している状態のものがあり、結合度合いを算出する必要があるため質問させていただきました。

Answer 1

Access - Access 多対多の結合数のカウント方法｜teratail
上記の質問の続きですね。

上記の回答のSQLでそれぞれのテーブルからクエリを作成します。

Q1

SELECT a.項目1 AS 項目4, b.項目1 AS 項目5, Count(*) AS 項目6
FROM Table1 AS a INNER JOIN Table1 AS b ON a.項目2 = b.項目2
WHERE a.項目1<b.項目1
GROUP BY a.項目1, b.項目1;

Q2

SELECT a.項目1 AS 項目4, b.項目1 AS 項目5, Count(*) AS 項目7
FROM Table2 a INNER JOIN Table2 b ON a.項目3 = b.項目3
WHERE a.項目1<b.項目1
GROUP BY a.項目1, b.項目1;

この２つのクエリを完全外部結合 (FULL OUTER JOIN)で結合すれば希望の結果になりますが、
残念ながら、AccessはFULL OUTER JOINをサポートしてません。

そこで、工夫が必要です。いろいろ方法はありますが、ユニオンクエリとクロス集計クエリを使う例を出しておきます。

QUnion

SELECT 項目4, 項目5, "項目6" AS 列名, 項目6 AS 件数
FROM Q1
UNION ALL
SELECT 項目4, 項目5, "項目7" AS 列名, 項目7 AS 件数 
FROM Q2;

QCross

TRANSFORM Sum(QUnion.件数) AS 件数の合計
SELECT QUnion.項目4, QUnion.項目5
FROM QUnion
GROUP BY QUnion.項目4, QUnion.項目5
PIVOT QUnion.列名;

クエリ４つになりましたが、サブクエリを使って一つに纏めることはできます。
が、メンテナンスとか考えたら分けておいた方がいいと思いますが、一応、纏めたものの提示しておきます。

TRANSFORM Sum(QUnion.件数) AS 件数の合計
SELECT QUnion.項目4, QUnion.項目5
FROM (
  SELECT 項目4, 項目5, "項目6" AS 列名, 項目6 AS 件数
  FROM (
    SELECT a.項目1 AS 項目4, b.項目1 AS 項目5, Count(*) AS 項目6
    FROM Table1 AS a INNER JOIN Table1 AS b ON a.項目2 = b.項目2
    WHERE a.項目1<b.項目1
    GROUP BY a.項目1, b.項目1
  ) Q1
  UNION ALL
  SELECT 項目4, 項目5, "項目7" AS 列名, 項目7 AS 件数 
  FROM (
    SELECT a.項目1 AS 項目4, b.項目1 AS 項目5, Count(*) AS 項目7
    FROM Table2 a INNER JOIN Table2 b ON a.項目3 = b.項目3
    WHERE a.項目1<b.項目1
    GROUP BY a.項目1, b.項目1
  ) Q2
) QUnion
GROUP BY QUnion.項目4, QUnion.項目5
PIVOT QUnion.列名;

Comments

[s_tom]

@hatena19さん
先日に続きご回答いただきありがとうございます。
（実装と、データ量が多く処理に時間を要していました）
解決しました！
大変助かりました。
ありがとうございました！