access クエリにて在庫数を出したい

質問

お世話になります。

ａｃｃｅｓｓにて在庫管理ＤＢを構築しています。
在庫数を出そうとすると、「あいまいな外部結合が含まれているので、SQLステートメントを実行できません。 いずれかの結合を最初に実行するために、第1次結合を実行する分割クエリを作成し、 SQLステートメントにそのクエリを含めてください。」とエラーが出てしまいます。
なにぶん初心者の為、ちんぷんかんぷんです。
ご教授の程よろしくお願い致します。

t_薬剤マスター
薬剤区分ID、薬剤名、包装内容、包装量、単位、薬価

t_店舗
店舗ID、店舗名

q_入庫数の合計
店舗ID、店舗名、薬剤ID、薬剤名、入庫数の合計、小分入庫数の合計、入庫総合計

q_出庫
店舗ID、店舗名、薬剤ID、薬剤名、出庫数の合計

q_在庫
在庫ID、店舗ID、店舗名、薬剤ID、薬剤名、在庫数

とあります。
t_薬剤マスターとt_店舗から、
それぞれのクエリ、（q_入庫、q_出庫、q_在庫）に
「't_薬剤マスター'の全レコードと'q_入庫'の同じ結合フィールドのレコードだけを
含める。」結合を設定しました。

そして下記の様にクエリを作成

フィールド　店舗ID　　店舗名　　　　薬剤ID　　　　　　　薬剤名
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
テーブル　t_店舗　　t_店舗　　　　　t_薬剤マスター　　t_薬剤マスター

フィールド　在庫数　　　入庫総合計　　　　　出庫数の合計　　　　　　
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
テーブル　q_在庫マスター　q_入庫数総合計　　ｑ_出庫数合計　　　　　　　

フィールド　　在庫数: [在庫数]+[入庫総合計]-[出庫数の合計]　　　
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
テーブル　

と作成しました。。。。が実行するとエラーメッセージが出てきてしまいます。
これは結合が悪いと言う事でしょうか？
よろしくお願い致しますm(__)m

回答No.5

nda23

>t_入庫の伝票IDと、t_出庫の伝票IDにそれぞれリレーションを
ちと、画像が見えませんが、そういうことなら、
正しいのでしょう。
真相はシステムを設計した人の意図を聞かないと
闇の中ですね。仕様書見たり、話を聞いたりは
このコミュニティの範囲外だと思われるので、
止めにしましょう。
まぁ、結果がでて何よりです。

お礼

nda23　様

ありがとうございました。
無事に在庫数を出す事が出来ました。m(__)m
解りやすい説明とても勉強になりました。感謝致します。
と、こんな初心者に根気良くお付き合い下さりまして本当にありがとうございました。

又、質問させて頂く事もあるかと思いますが、その際はこれに懲りずに又よろしくお願い致します。m(__)m

回答No.4

nda23

データが無いとNullになります。計算項目に１個でも
Nullがあると、結果はNullになります。
そこで、「Nullならゼロ」という関数を噛ませます。

在庫の数:[在庫数]+Nz([入庫総合計],0)-Nz([出庫数の合計],0)
あと、結合式を Left Join にすれば完璧です。
テーブル間に張られた線の上をダブルクリックすると、
３個の選択肢を選ぶダイアログが出てきます。
説明すると長くなるので止めますが、２か３を選びます。
これで、線が以下のように矢印になればOKです。
q_在庫マスターの店舗ID → q_入庫数総合計の店舗ID
q_在庫マスターの薬剤ID → q_入庫数総合計の薬剤ID
q_在庫マスターの店舗ID → ｑ_出庫数合計の店舗ID
q_在庫マスターの薬剤ID → ｑ_出庫数合計の薬剤ID
Left Join にするのは全部成功してからでも良いでしょう。

>正しいかどうかの判断がつきません。
伝票テーブルのレイアウトが分からないのですが、
伝票には伝票IDの他に店舗IDと薬剤IDが別に
あるのでは？あるとすると、伝票IDと店舗IDを
結合させているのは間違いです。

補足

nda23 様

ありがとうございます！
ご教授頂いた通り、結合式に2を選択し、Null関数を計算式に入れた所、
見事に在庫数を出させて頂く事が出来ました！

>正しいかどうかの判断がつきません。
伝票テーブルのレイアウトが分からないのですが、
伝票には伝票IDの他に店舗IDと薬剤IDが別に
あるのでは？あるとすると、伝票IDと店舗IDを
結合させているのは間違いです。

え～と。
t_伝票には
伝票ID、処理日、店舗ID、仕入れ区分ID
があります。

t_店舗の店舗IDをt_伝票の店舗IDにリレーションさせています。

それをt_入庫の伝票IDと、t_出庫の伝票IDにそれぞれリレーションをさせています。

それとは別にt_薬剤マスターがあります。

t_薬剤マスターの薬剤IDをt_入庫の薬剤IDとt_出庫の薬剤IDにそれぞれリレーションさせています。

レイアウトを図にし、添付させて頂きました。
※必要そうなものだけ載せてみました。

ほんと～にお手数ばかりおかけしてますが、見て頂けたら幸いです。。。。。m(__)mm(__)mm(__)mm(__)mm(__)mm(__)mm(__)mm(__)mm(__)m

回答No.3

nda23

結合式に試行錯誤のあとが見られますが、SQLを
考える時は理論の組み立てが大事です。
先ず、コアになるものは何かです。先ず間違いなく
q_在庫マスターと思います。で、ここには既に
店舗系情報（IDと名前）、薬剤系情報（IDと名前）が
含まれているので、t_店舗とt_薬剤マスタは不要です。
次に、在庫マスタから見て、入庫数と出庫数が必要と
なるわけですね。このマスタと店舗ID、薬剤IDが一致する
q_入庫数総合計の情報、ｑ_出庫数合計の情報を得る
という考え方です。修正方法は以下の通りです。

(1)t_店舗、t_薬剤マスタを削除する。
(2)q_在庫マスターから店舗ID、店舗名、薬剤ID、薬剤名を
　　登録する。
(3)結合式は以下の通り
　q_在庫マスターの店舗ID - q_入庫数総合計の店舗ID
　q_在庫マスターの薬剤ID - q_入庫数総合計の薬剤ID
　q_在庫マスターの店舗ID - ｑ_出庫数合計の店舗ID
　q_在庫マスターの薬剤ID - ｑ_出庫数合計の薬剤ID

あと、気になったのですが、q_在庫マスターの結合で
「ON t_伝票.伝票ID = t_在庫マスター.店舗ID」
このようになっているんですが、伝票ID=店舗IDで正しい
のでしょうか？

補足

nda23　様

出来ました！
教えて頂いた通りに修正、結合しましたら、在庫数を出す事が出来ました。
解りやすく、なおかつ丁寧なご説明本当にありがとうございます。
組み立て方が、とても勉強になりました。
在庫マスターから何が必要かを考えるべきだったのですね。

お手数でなければ、もうひとつお聞きしたい事があります。
お付き合い願えますでしょうか？

在庫の数: [在庫数]+[入庫総合計]-[出庫数の合計]　
で出庫が入力されているものに関しては合計が出ますが、
出庫数が入力されていないものに関しては
在庫数の数が表示されず、空白のレコードが表示されてしまいます。
別のフィールドを設け、

在庫と入庫の合計を出し

フィールド　在庫と入庫の合計: [在庫数]+[入庫総合計]

その後で

フィールド　現在庫数：[在庫と入庫の合計]-[出庫数の合計]　

で、在庫数を出した方がよろしいのでしょうか？

貴重なお時間頂いて、申し訳ありませんが、もう少しのお付き合いをして頂けたら幸いです。
よろしくお願い致します。

＞あと、気になったのですが、q_在庫マスターの結合で
「ON t_伝票.伝票ID = t_在庫マスター.店舗ID」
このようになっているんですが、伝票ID=店舗IDで正しい
のでしょうか？

ごめんなさい。勉強不足で申し訳ないのですが、
正しいかどうかの判断がつきません。
t_伝票に店舗ID、店舗名をコンボボックスで選択出来るようにしてあります。
なので、入庫の合計、出庫の合計は店舗IDを伝票から抽出出来る様にしました。m(__)m

ご質問の答えになってますでしょうか？

よろしくお願い致します。m(__)m

回答No.2

nda23

>q_入庫数総合計
このクエリは単独で正常に表示されますか？
エラーになるクエリのSQLを提示してください。

補足

nda23　様
ありがとうございます。
お手数おかけ致します。

>q_入庫数総合計
このクエリは単独で正常に表示されますか？

新しくデザインビューを開き、SQLビューに貼り付けた所、正常に表示されました。

今回エラーメッセージが出てしまう、SQLを結合を変えて表示させて頂きます。
補足、足りない点、もしくは理解出来てない点もあるかと思いますので、
その際にはご指摘下さいます様お願い致します。

＜今回、エラーメッセージが出てしまうSQL＞

SELECT t_店舗.店舗ID, t_店舗.店舗名, t_薬剤マスター.薬剤ID, t_薬剤マスター.薬剤名, q_在庫マスター.在庫数, q_入庫数総合計.入庫総合計, ｑ_出庫数合計.出庫数の合計, [q_在庫マスター.在庫数]+[入庫総合計]-[出庫数の合計] AS 在庫の数
FROM ((t_薬剤マスター INNER JOIN (t_店舗 INNER JOIN q_在庫マスター ON t_店舗.店舗ID = q_在庫マスター.店舗ID) ON t_薬剤マスター.薬剤ID = q_在庫マスター.薬剤ID) INNER JOIN q_入庫数総合計 ON (t_薬剤マスター.薬剤ID = q_入庫数総合計.薬剤ID) AND (t_店舗.店舗ID = q_入庫数総合計.店舗ID)) INNER JOIN ｑ_出庫数合計 ON (t_薬剤マスター.薬剤ID = ｑ_出庫数合計.薬剤ID) AND (t_店舗.店舗ID = ｑ_出庫数合計.店舗ID);

お手数おかけ致しますが、よろしくお願い致します。m(__)m

回答No.1

nda23

>「あいまいな～
これは複数のテーブルに同名のフィールドがあり、
テーブル名による修飾が無い場合に、どちらの
データを使えばよいかが分からないという意味です。
恐らく、下の部分で「在庫数」が２回出てきますが、
クエリに由来するものと、自身のフィールドの区別が
つかないためと思います。
在庫数: [在庫数]+[入庫総合計]-[出庫数の合計]

以下の(1)あるいは(2)のようにしてみてください。
(1)名前を変える
在庫の数: [在庫数]+[入庫総合計]-[出庫数の合計]
(2)明示的に修飾する
在庫数: [q_在庫.在庫数]+[入庫総合計]-[出庫数の合計]

これでもエラーになる場合は「店舗ID」のように同名の
フィールドが無修飾で使用されている可能性があります。
この場合はSQLビューを開いてSQL全文を提示してください。

補足

nda23　様
早々のご回答ありがとうございます。

教えて頂いた通り、（1）.（2）とも同じ名前を重複させない様に変えてみましたが、
同じエラーメッセージが出てきてしまいました。

SQL文を提示させて頂きますので、アドバイス頂いてもよろしいでしょうか。
お手数をおかけ致しますが、よろしくお願い致します。

＜q_入庫数総合計＞

SELECT t_伝票.店舗ID, t_店舗.店舗名, t_入庫.薬剤ID, t_薬剤マスター.薬剤名, Sum(([包装量]*[入庫単位])) AS 入庫数の合計, Sum(t_入庫.小分入庫数) AS 小分入庫数の合計, [入庫数の合計]+[小分入庫数の合計] AS 入庫総合計
FROM t_薬剤マスター INNER JOIN ((t_店舗 INNER JOIN t_伝票 ON t_店舗.店舗ID = t_伝票.店舗ID) INNER JOIN (t_入庫 INNER JOIN t_価格マスター ON t_入庫.価格ID = t_価格マスター.価格ID) ON t_伝票.伝票ID = t_入庫.伝票ID) ON t_薬剤マスター.薬剤ID = t_入庫.薬剤ID
GROUP BY t_伝票.店舗ID, t_店舗.店舗名, t_入庫.薬剤ID, t_薬剤マスター.薬剤名;

＜q_出庫数合計＞

SELECT t_伝票.店舗ID, t_店舗.店舗名, t_出庫.薬剤ID, t_薬剤マスター.薬剤名, Sum(t_出庫.出庫数) AS 出庫数の合計
FROM t_薬剤マスター INNER JOIN ((t_店舗 INNER JOIN t_伝票 ON t_店舗.店舗ID = t_伝票.店舗ID) INNER JOIN t_出庫 ON t_伝票.伝票ID = t_出庫.伝票ID) ON t_薬剤マスター.薬剤ID = t_出庫.薬剤ID
GROUP BY t_伝票.店舗ID, t_店舗.店舗名, t_出庫.薬剤ID, t_薬剤マスター.薬剤名;

＜q_在庫マスター＞

SELECT t_在庫マスター.在庫ID, t_在庫マスター.店舗ID, t_店舗.店舗名, t_在庫マスター.薬剤ID, t_薬剤マスター.薬剤名, t_在庫マスター.在庫数
FROM t_薬剤マスター INNER JOIN (t_店舗 INNER JOIN (t_伝票 INNER JOIN t_在庫マスター ON t_伝票.伝票ID = t_在庫マスター.店舗ID) ON t_店舗.店舗ID = t_伝票.店舗ID) ON t_薬剤マスター.薬剤ID = t_在庫マスター.薬剤ID;

と、なります。

今、現在作成しているクエリは「あいまいな外部結合が含まれているため実行できません」
と、出てきてしまいます。

これで大丈夫でしょうか？

お手数おかけ致しますが、よろしくお願い致します。m(__)m