access １対１と１対多のテーブルをクエリで集計したい…

質問

受注管理のデータベースを作っています。
売り上げを計算するクエリを作ったのですがどうもうまくいかないので質問します。

集計したいテーブルですが

受注マスタテーブル
注文番号（主キー　テキスト型）
注文日（日付型）
氏名
：
：

受注明細マスタテーブル
注文番号　（重複あり）受注マスタの注文番号と１対多のリレーションシップ
商品名
品番
販売単価
購入個数

経費・返品金額テーブル
注文番号（主キー）受注マスタの注文番号と１対１のリレーションシップ
経費
返品金額

これを、販売単価×購入個数-経費＋返品金額というようにして１ヵ月ごとに集計しようとして、下のようなクエリを作ったのですが経費が何回も計算されてうまくいきませんでした…
よろしくお願いします。

SELECT Format(注文者マスタ!注文日,"yyyy/mm") AS 月, Sum([受注明細マスタ]![販売単価]*[受注明細マスタ]![購入数量]-[経費・返品金額テーブル]![経費]+[経費・返品金額テーブル]![返品金額]) AS 売上
FROM (注文者マスタ RIGHT JOIN 経費・返品金額テーブル ON 注文者マスタ.注文番号 = 経費・返品金額テーブル.注文番号) LEFT JOIN 受注明細マスタ ON 注文者マスタ.注文番号 = 受注明細マスタ.注文番号
GROUP BY Format(注文者マスタ!注文日,"yyyy/mm");

回答No.1

chairwarmer · ベストアンサー

受注明細が注文番号だけでは1件に絞られず複数件抽出されるので
これと結合している時点で受注、経費のテーブルからは
同じレコードを何回も使って結合を行ってしまいます。

１：１：１になるように、先に受注明細だけ集計を取っておいて
サブクエリとするのが適切かと。

SELECT Format([注文者マスタ].[注文日],"yyyy/mm") AS 月, Sum(A.[購入金額] - [経費・返品金額テーブル].[経費] + [経費・返品金額テーブル].[返品金額]) AS 売上
FROM (注文者マスタ LEFT JOIN 経費・返品金額テーブル ON 注文者マスタ.注文番号 = 経費・返品金額テーブル.注文番号) LEFT JOIN
(SELECT 注文番号, Sum(販売単価 * 購入数量) AS 購入金額 FROM 受注明細マスタ GROUP BY 注文番号) A
ON 注文者マスタ.注文番号 = A.注文番号
GROUP BY Format(注文者マスタ.注文日,"yyyy/mm");

↑こんな感じでお望みの結果が得られませんでしょうか。

(SELECT 注文番号, Sum(販売単価 * 購入数量) AS 購入金額 FROM 受注明細マスタ GROUP BY 注文番号) A

SQLが煩雑になるのを避けたいならば、この部分だけをあらかじめ一つのクエリとして作っておいて
そのクエリと結合する形でもできますよ。

お礼

できました！ありがとうございます！
すばやいご回答ありがとうございます！
サブクエリやクエリを分けて考えるということはまったく知りませんでした。
いくら悩んでも解決しないわけです…
いつも何か問題にぶつかるたびに親切なご回答を頂いて本当に助かります。
ありがとうございました。