複数InnerJoin時の処理落ち回避法

質問

DB：Access97

30個のテーブルがあり、
それぞれ1000件のレコードがあります。

全テーブルを内部結合の上、データの抽出をしたいのですが、
Inner Joinで内部結合を行うとAccessの応答が無くなってしまいます。

5分～10分程度で、
30個のテーブルを内部結合の上データの抽出を
行えるようにするには、SQLをどのように変えれば良いのでしょうか。
アドバイスをお待ちしております。

*** SQL ***

Select
　　　Table01.製品コード
　　　, Table01.シリアルNO
　　　, Table01.基準値 as 製品性能01
　　　, IIF(Table02.最大値 LIKE "NULL" or Table02.最小値 LIKE "NULL" , 0 , (CINT(Table02.最大値) + CINT(Table02.最小値)) / 2) as 製品性能02
　　　, IIF(Table03.基準値 LIKE "NULL" , 0 , CDBL(CCur(Table03.基準値))) as 製品性能03
　　　, IIF(Table04.基準値 LIKE "NULL" , 0 , CINT(Table04.基準値)) as 製品性能04
　　　, IIF(Table05.最大値 LIKE "NULL" , IIF(Table05.基準値 LIKE "NULL" , 0 , CINT(Table05.基準値)) , (CINT(Table05.最大値) + CINT(Table05.最小値)) /2) as 製品性能05
　　　　　　　　　　・
　　　　　　　　　　・（以下Table30まで同様）
　　　　　　　　　　・
From
　　　((Table01
　　　Inner Join
　　　Table02
　　　Table01.製品コード = Table02.製品コード
　　　And Table01.シリアルNO = Table02.シリアルNO)
　　　Inner Join
　　　Table03
　　　Table01.製品コード = Table03.製品コード
　　　And Table01.シリアルNO = Table03.シリアルNO)
　　　Inner Join
　　　Table04
　　　Table01.製品コード = Table04.製品コード
　　　And Table01.シリアルNO = Table04.シリアルNO)
　　　　　　　　　　・
　　　　　　　　　　・（以下Table30まで同様）
　　　　　　　　　　・

回答No.2

venzou · ベストアンサー

主キーは（製品コード,シリアルNO,製品性能名）ですよね？

製品性能名によって、製品性能の計算が異なっているようなので、
まず、製品性能の計算を先に済ませ、作業用のテーブルに保存する。

INSERT INTO 作業用 ( 製品コード, シリアルNO, 製品性能名, 製品性能 )
SELECT 製品コード, シリアルNO, 製品性能名, 基準値 AS 製品性能
FROM システムA
WHERE システムA.製品性能名="製品性能01";
---------------------------------------------
INSERT INTO 作業用 ( 製品コード, シリアルNO, 製品性能名, 製品性能 )
SELECT 製品コード, シリアルNO, 製品性能名, IIf(最大値 Like "NULL" Or 最小値 Like "NULL",0,(CInt(最大値)+CInt(最小値))/2) AS 製品性能
FROM システムA
WHERE システムA.製品性能名="製品性能02";
---------------------------------------------
　・
　・
　・

作業用のテーブルを元にクロス集計をする。

TRANSFORM Sum(作業用.製品性能) AS 製品性能の合計
SELECT 作業用.製品コード, 作業用.シリアルNO
FROM 作業用
GROUP BY 作業用.製品コード, 作業用.シリアルNO
PIVOT 作業用.製品性能名;

これでシステムＢのテーブル構成になると思います。

補足：
Sumを使っていますが、合計ではありません。
（製品コード,シリアルNO,製品性能名）が主キーの場合、該当するデータは１件ずつしかないので。

お礼

> 主キーは（製品コード,シリアルNO,製品性能名）ですよね？
仰るとおりです。
クロス集計は存じておりませんでした。
SQLはある程度分かっているつもりでしたが、奥が深いです。
ありがとうございました。

回答No.3

noname#182251

＃１です。２点ばかり。

１．IIFを使用しない
UPDATE テーブル1 SET テーブル1.新基準値 = ([最大値]+[最小値])/2
WHERE (((テーブル1.製品性能名)="製品性能02"));
このようなクエリの方が楽だし早いと思います。
留意点
・作業テーブルを作らず、作業フィールド「新基準値」を利用しています
・丸め処理はしていません
・最大値、最小値の少なくとも一方がnullだと、計算をスキップします。全ての性能に関し計算後、まとめてnullを０で更新すればよいでしょうか？

以上で新基準値が全て埋まった後、クロス集計をすればよいのですが、クロス集計が一般的ＳＱＬで扱えるのか疑問です。その場合はピボットテーブルを自作する方法があります。

２．ピボットテーブル
この場合はフィールドとして
製品性能名（文字列）、製品性能０１～３０（整数値）
の３１フィールド。レコードとして
製品性能０１～３０
の３０レコード。対角線上に1それ以外に0をセットします。名称を仮に「ピボットテーブル」とします。イメージは下記

|製品性能名|製品性能01|製品性能02|製品性能03|
|---------+---------+----------+---------+---------+---・・・
|製品性能01　　　　1|　　　　 0|　　　　0|　
|製品性能02　　　　0|　　　　 1|　　　　0|
|製品性能03　　　　0|　　　　 0|　　　　1|

これを利用して製品性能０３までを展開したＳＱＬ文は
SELECT テーブル1.製品コード, テーブル1.シリアルＮＯ, Sum([テーブル1].[基準値]*[ピボットテーブル].[製品性能０１]) AS 製品性能０１値, Sum([テーブル1].[基準値]*[ピボットテーブル].[製品性能０2]) AS 製品性能０2値, Sum([テーブル1].[基準値]*[ピボットテーブル].[製品性能０3]) AS 製品性能０3値
FROM テーブル1 INNER JOIN ピボットテーブル ON テーブル1.製品性能名 = ピボットテーブル.製品性能名
GROUP BY テーブル1.製品コード, テーブル1.シリアルＮＯ;

お礼

こちらのやり方で無事希望通りのデータを作成することができました。
ありがとうございました。

回答No.1

noname#182251

直感的にはＤＢの設計を変更した方が良いと思われますが、提示されたＳＱＬから「やりたいこと」を追いかける根気がありません。テーブルの内容とやりたいことの概要を説明されてはいかがでしょうか。

補足

ご指摘ありがとうございます。

本SQL作成の目的は、システム間のデータコンバート準備です。

システムＡ（コンバート元）のデータをシステムＢ（コンバート先）へ持って行きたいのですが、
コンバートするには、データの加工が必要です。

***　システムＡ(コンバート元)のテーブル構成　***

|製品コード|シリアルNO|製品性能名|最大値　　|基準値　　|最小値　　|
|---------+---------+----------+--------+--------+--------|
|　　　　1234|　　　　　　01|製品性能01|　　　NULL|　　　1200|　　　NULL|
|　　　　1234|　　　　　　02|製品性能01|　　　NULL|　　　1300|　　　NULL|
|　　　　1234|　　　　　　01|製品性能02|　　　NULL|　　　NULL|　　　NULL|
|　　　　1234|　　　　　　02|製品性能02|　　　　　80|　　　　　40|　　　　　30|
|　　　　9876|　　　　　　01|製品性能02|　　　　　90|　　　　　60|　　　　　30|
|　　　　0864|　　　　　　01|製品性能02|　　　　　90|　　　　　70|　　　　　30|
|　　　　9876|　　　　　　01|製品性能03|　　　NULL|　　　NULL|　　　NULL|
|　　　　0864|　　　　　　01|製品性能03|　　　　9.12|　　　　8.00|　　　　6.89|
　　　　　　　　　　　　・
　　　　　　　　　　　　・
　　　　　　　　　　　　・
***　システムＢ(コンバート先)のテーブル構成　***

|製品コード|シリアルNO|製品性能01|製品性能02|製品性能03|
|---------+---------+----------+---------+---------+---・・・(製品性能30まで同様)
|　　　　1234|　　　　　　01|　　　　1200|　　　　　　00|　　　　0.00|
|　　　　1234|　　　　　　02|　　　　1300|　　　　　　55|　　　　0.00|
|　　　　9876|　　　　　　01|　　　　　　00|　　　　　　60|　　　　0.00|
|　　　　0864|　　　　　　01|　　　　　　00|　　　　　　60|　　　　8.00|
　　　　　　　　　　　　・
　　　　　　　　　　　　・
　　　　　　　　　　　　・

やり方は、以下の３ステップです。
１．システムＡのデータを製品性能名別に分ける。（完了済み）

　　***　製品性能名別テーブル構成　***
　　　<Table01>
　　　|製品コード|シリアルNO|製品性能名|最大値　　|基準値　　|最小値　　|
　　　|---------+---------+----------+--------+--------+--------|
　　　|　　　　1234|　　　　　　01|製品性能01|　　　NULL|　　　　1200|　　　NULL|
　　　|　　　　1234|　　　　　　02|製品性能01|　　　NULL|　　　　1300|　　　NULL|

　　　<Table02>
　　　|製品コード|シリアルNO|製品性能名|最大値　　|基準値　　|最小値　　|
　　　|---------+---------+---------+--------+--------+--------|
　　　|　　　　1234|　　　　　　01|製品性能02|　　　NULL|　　　NULL|　　　NULL|
　　　|　　　　1234|　　　　　　02|製品性能02|　　　　　80|　　　　　40|　　　　　30|
　　　|　　　　9876|　　　　　　01|製品性能02|　　　　　90|　　　　　60|　　　　　30|
　　　|　　　　0864|　　　　　　01|製品性能02|　　　　　90|　　　　　70|　　　　　30|
　　　　　　　　　　　　　　　　　・
　　　　　　　　　　　　　　　　　・
　　　　　　　　　　　　　　　　　・

２．製品性能別のデータを連結する。（ここで本問題が発生）
　　連結後のデータ構成は、上記「システムＢ(コンバート先)のテーブル構成」の通り。

３．２．の連結データをシステムＢのテーブルへ挿入する。

他、不明点等ございましたら、ご指摘ください。
よろしくお願い致します。