ACCESS 複数テーブルの各合計値を期間を指定して算出、閲覧する方法について

質問

私はACCESS初心者です。会社用で日々の在庫数量を閲覧出来る仕組みを作成しております。
標題の件で行き詰ってしまい、皆様のお力添えを頂きたく、投稿させて頂きました。

現在の設定は下記の通りです。

部品情報テーブル：
・ＩＤ（主キー）
・部品番号

仕入履歴テーブル
・ＩＤ（主キー）
・部品番号
・仕入数量
・仕入日

生産履歴テーブル
・ＩＤ（主キー）
・部品番号
・生産数量
・入庫日

販売履歴テーブル
・ＩＤ（主キー）
・部品番号
・販売数量
・販売日

この後、下のようなクエリ（フォーム）で期間を指定して、
それぞれの合計値の算出・閲覧と、在庫数量を算出したいと考えております。
在庫数量確認クエリ（フォーム）
・期間指定　開始～終了
・部品番号
・（指定期間の）仕入数量合計(1)
・（指定期間の）入庫数量合計(2)
・（指定期間の）販売数量合計(3)
・在庫数量：（[(1)]＋[(2)]）－[(3)]

例で御説明致しますと、

部品番号：Ａ
（仕入履歴テーブル）仕入日、数量：
８月　１日　１０個
８月１０日　１０個

（生産履歴テーブル）入庫日、数量：
８月　２日　２０個
８月１５日　２０個

（販売履歴テーブル）販売日、数量：
８月　５日　３０個
８月１８日　３０個

この時、期間指定「８月１日～８月１９日」で設定し、
（仕入１０＋１０）＋（入庫２０＋２０）－（販売３０＋３０）＝（在庫　０）
を成り立たせたいのです。

複数のテーブルに「日付」がある為、どのようにBETWEENを設定をすれば良いか、
また、期間を指定した時に各テーブルの数量を合計で算出するには、
どのようなクエリ設定をすれば良いのでしょうか。

何卒アドバイスを頂けます様、お願い申し上げます。

回答No.1

kurodai2 · ベストアンサー

在庫数量確認クエリ（フォーム）
・期間指定　開始～終了
・部品番号
フォーム上に、開始・終了・部品のテキストボックスを置きました。
フォーム名は、お書きになっているとおり。
Access2003で、実際にクエリをデザインしてSQLビューでCOPYしています。
蛇足的に、自動で（）が沢山付いています。

>・（指定期間の）仕入数量合計(1)

名前：クエリ1

SELECT 仕入履歴テーブル.部品番号, Sum(仕入履歴テーブル.仕入数量) AS 仕入数量の合計
FROM 仕入履歴テーブル
WHERE (((仕入履歴テーブル.仕入日) Between [Forms]![在庫数量確認クエリ]![開始] And [Forms]![在庫数量確認クエリ]![終了]) AND ((仕入履歴テーブル.部品番号)=[Forms]![在庫数量確認クエリ]![部品番号]))
GROUP BY 仕入履歴テーブル.部品番号;

結果
部品番号　　仕入数量の合計
Ａ　　　　　20

>・（指定期間の）入庫数量合計(2)

名前：クエリ2

SELECT 生産履歴テーブル.部品番号, Sum(生産履歴テーブル.生産数量) AS 生産数量の合計
FROM 生産履歴テーブル
WHERE (((生産履歴テーブル.入庫日) Between [Forms]![在庫数量確認クエリ]![開始] And [Forms]![在庫数量確認クエリ]![終了]) AND ((生産履歴テーブル.部品番号)=[Forms]![在庫数量確認クエリ]![部品番号]))
GROUP BY 生産履歴テーブル.部品番号;

結果
部品番号　　生産数量の合計
Ａ　　　　　　40

>・（指定期間の）販売数量合計(3)

名前：クエリ3

SELECT 販売履歴テーブル.部品番号, Sum(販売履歴テーブル.販売数量) AS 販売数量の合計
FROM 販売履歴テーブル
WHERE (((販売履歴テーブル.販売日) Between [Forms]![在庫数量確認クエリ]![開始] And [Forms]![在庫数量確認クエリ]![終了]) AND ((販売履歴テーブル.部品番号)=[Forms]![在庫数量確認クエリ]![部品番号]))
GROUP BY 販売履歴テーブル.部品番号;

結果
部品番号　　　販売数量の合計
Ａ　　　　　　　60

>・在庫数量：（[(1)]＋[(2)]）－[(3)]

名前：クエリ4

SELECT クエリ1.部品番号, クエリ1!仕入数量の合計+クエリ2!生産数量の合計-クエリ3!販売数量の合計 AS 在庫数量
FROM (クエリ1 INNER JOIN クエリ2 ON クエリ1.部品番号 = クエリ2.部品番号) INNER JOIN クエリ3 ON クエリ1.部品番号 = クエリ3.部品番号;

結果
部品番号　　　在庫数量
Ａ　　　　　　0

参考になれば、幸いです。

補足

kurodai2様　丁寧なアドバイスを頂き、誠に有難うございます。
本方法にて、希望の数値を取得する事が出来ました。

御教示頂いてばかりで大変恐縮なのですが、
本件に関連して一点問題が発生してしまい、その点に関してもアドバイスを頂きたくお願い申し上げます。

現在弊社には、仕入時と販売時の部品番号が異なる商品が存在します。
仕入時の部品番号Ａ
販売時の部品番号Ａ、Ｂ、Ｃ

御教示頂いた方法ですと、販売時の合計数量はＡのみ計算されますが、
Ａ、Ｂ、Ｃの合計を販売合計数量として、仕入部品番号Ａで表示させたいのです。

＊投稿させて頂いた際の[部品番号]は、販売時の部品番号を指しています。
　仕入時と販売時の部品番号が同じ商品は、問題なく希望数値を取得する事が出来ます。

先日、本サイトでkurodai2様にアドバイスを頂き、この現象を解決する為に部品情報テーブルに[仕入時部品番号]を追加しております。

部品情報テーブル
・ＩＤ（主キー）
・部品番号
・仕入時部品番号

クエリ３に[部品情報テーブル]を追加し、[販売履歴テーブル]の[部品番号]と上記[仕入時部品番号]を結合しました。
その後、クエリ３の[部品番号]を[仕入時部品番号]に変更して算出してみましたが、数量が実際と異なり、上手く行きませんでした。

本件を投稿させて頂いた際、ここまでの事に気がつかず
お手数をおかけしてしまいますが、何卒お力添えを頂けます様、お願い申し上げます。

回答No.6

noname#140971

<部品情報>
ID___部品番号　<--- コピペミス
1____A-101

回答No.5

noname#140971

多少、回答の毛色が変わっていますので参考程度に読み流して下さい。

<部品情報>
ID___品番号
1____A-101

<仕入履歴>
ＩＤ___部品情報_ID___仕入数量___仕入日
1__________________1____________10___2007/08/01
2__________________1____________10___2007/08/10

<生産履歴>
ＩＤ___部品情報_ID___生産数量___入庫日
1___________________1____________20___2007/08/02
2___________________1____________20___2007/08/15

<販売履歴>
ＩＤ___部品情報_ID___販売数量___販売日
1___________________1____________30___2007/08/05
2___________________1____________30___2007/08/18

8/1～8/19 で 在庫=仕入+入庫-販売 を求めたいとのこと。
そこで、イミディエイトウインドウで、先ずは、それぞれの数量を求めてみます。

[イミディエイト]
? DBSUM("仕入数量","仕入履歴", "部品情報_ID=1 AND 仕入日 BETWEEN #2007/08/01# AND #2007/08/19#")
20
? DBSUM("生産数量","生産履歴", "部品情報_ID=1 AND 入庫日 BETWEEN #2007/08/01# AND #2007/08/19#")
40
? DBSUM("販売数量","販売履歴", "部品情報_ID=1 AND 販売日 BETWEEN #2007/08/01# AND #2007/08/19#")
60

つまり、DBSUM関数一つで事足りるということです。

ANo2 の１が一番簡単だということです。

<クエリ>
SELECT DBSUM("仕入数量","仕入履歴","部品情報_ID=1 AND 仕入日 BETWEEN #2007/08/01# AND #2007/08/19#")+DBSUM("生産数量","生産履歴","部品情報_ID=1 AND 入庫日 BETWEEN #2007/08/01# AND #2007/08/19#")-DBSUM("販売数量","販売履歴","部品情報_ID=1 AND 販売日 BETWEEN #2007/08/01# AND #2007/08/19#");

もちろん、クエリにこのように書くことも可能です。
複雑なクエリに挑戦する意義も否定はしません。
が、私は、ほとんど、この手のクエリは作成したことがないです。
簡単に済ませられるのならば、それが一番という考えの持ち主です。

※各テーブルのリンクは、<<部品情報.ID>> に変更しています。これは、今の内に検討されておいたがいいです。

なお、Access の DSum関数は、驚くほどに実行速度に問題があります。

Public Function DBSum(ByVal strField As String, _
　　　　　　　　　　　ByVal strTable As String, _
　　　　　　　　　　　Optional strWhere As String = "") As Variant
On Error GoTo Err_DBSum
　　Dim N
　　Dim strQuerySQL As String
　　Dim rst　　　　 As ADODB.Recordset

　　Set rst = New ADODB.Recordset
　　strQuerySQL = "SELECT SUM(" & strField & ") FROM " & strTable
　　If Len(strWhere) > 0 Then
　　　　strQuerySQL = strQuerySQL & " WHERE " & strWhere
　　End If
　　With rst
　　　　 .Open strQuerySQL, _
　　　　　　　 CurrentProject.Connection, _
　　　　　　　 adOpenStatic, _
　　　　　　　 adLockReadOnly
　　　　 If Not .BOF Then
　　　　　　 .MoveFirst
　　　　　　 N = Nz(.Fields(0), 0)
　　　　 End If
　　End With
Exit_DBSum:
On Error Resume Next
　　 rst.Close
　　 Set rst = Nothing
　　 DBSum = N
　　 Exit Function
Err_DBSum:
　　 MsgBox "SELECT 文の実行時にエラーが発生しました。(DBSum)" & Chr$(13) & Chr$(13) & _
　　　　　　"・Err.Description=" & Err.Description & Chr$(13) & _
　　　　　　"・SQL Text=" & strQuerySQL, _
　　　　　　vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_DBSum
End Function

お礼

Husky2007様　丁寧なアドバイスを頂き、誠に有難うございました。
投稿させて頂いた内容に関しましては、別の方にアドバイスを頂いた方法にて希望数値を取得する事が出来ました。

お恥ずかしい話、今回御教示頂いた「イミディエイト」もはじめて聞いたほど、勉強不足であります。（すぐに調べました）
それでも複数の外国人に受注・仕入管理をさせている為、入力・閲覧が分かり易いＡＣＣＥＳＳのフォームに魅力を感じ、作成に踏み切った次第です。

今後、関数についても学習をして行きたいと考えております。
誠に有難うございました。

回答No.4

kurodai2

クエリ3

SELECT 部品情報テーブル.仕入時部品番号 AS 部品番号, Sum(販売履歴テーブル.販売数量) AS 販売数量の合計
FROM 販売履歴テーブル LEFT JOIN 部品情報テーブル ON 販売履歴テーブル.部品番号=部品情報テーブル.部品番号
WHERE (((販売履歴テーブル.販売日) Between Forms!在庫数量確認クエリ!開始 And Forms!在庫数量確認クエリ!終了) And ((部品情報テーブル.仕入時部品番号)=Forms!在庫数量確認クエリ!部品番号))
GROUP BY 部品情報テーブル.仕入時部品番号;

＊部品情報テーブルを外部結合させています。
条件の部品番号は、部品情報の仕入時部品番号に変えています。
先の回答のクエリ4の記述に変更が必要の無いように
部品情報テーブル.仕入時部品番号 AS 部品番号
括った、仕入時部品番号には、部品番号と別名をつけています。

販売履歴の部品番号を　A,B,Cの3データに分け、部品情報に
A,B,Cを登録し、すべて仕入れ時部品番号：Aで確認しました。

＊販売履歴の部品番号と結びつけるのは、あくまでも部品番号ですが
集計するのは、仕入時部品番号

お礼

kurodai2様　丁寧なアドバイスと設定の御確認までして頂き、誠に有難うございました。
御教示頂いた設定にて、希望の数値を取得する事が出来ました。

私のレベルではウィザードを使いながらの作業がメインとなり、
実務で使用出来るＡＣＣＥＳＳ作成に限界を感じておりました。
海外での業務の為、インターネットでの学習・情報収集を行う中で
こちらのサイトで熟練者の方にアドバイスを頂けて、大変光栄に感じております。

誠に有難うございました。

回答No.3

noname#79209

#2です。
> クエリ３に[部品情報テーブル]を追加し、
> [販売履歴テーブル]の[部品番号]と上記[仕入時部品番号]を結合しました。
これはダメでしょう。
販売履歴で纏めて集計したいのは、[仕入時部品番号]ですが。
結合するフィールドは、
販売履歴テーブルの部品番号と部品情報テーブルの部品番号(つまり販売時の部品番号)
でないと．．．

SELECT 部品情報テーブル.仕入時部品番号, Sum(販売履歴テーブル.販売数量) AS 販売数量の合計
FROM 販売履歴テーブル LEFT JOIN 部品情報テーブル ON 販売履歴テーブル.部品番号 = 部品情報テーブル.部品番号
WHERE (((販売履歴テーブル.販売日) Between [Forms]![在庫数量確認クエリ]![開始] And [Forms]![在庫数量確認クエリ]![終了] And [販売履歴テーブル].[部品番号]=[Forms]![在庫数量確認クエリ]![部品番号]))
GROUP BY 部品情報テーブル.仕入時部品番号;

お礼

vizzar様　早速のアドバイスを頂き、誠に有難うございました。
御教示を頂いた内容にて、希望数値を取得する事が出来ました。

今回投稿させて頂いた内容に関しましては（も）、
熟練者の方に作成を頂いて、私はただただ転写させて頂く事を
大変恐縮に感じております。。。

誠に有難うございました。

回答No.2

noname#79209

方法はいくつかありますが、
1.Dsum関数を使って、仕入、生産、販売の各テーブルの期間内数量を部品別に算出し、この数値をもとに仕入+生産-販売を計算する。
2.仕入、生産、販売の各テーブルの部品番号、数量、日付でユニオン・クエリを作りこれを部品番号でグループ化して集計する。
　ただし、販売の数量はユニオン時に-1を掛けて負の数値にしておく。
3.部品情報テーブルに対して各テーブルをLEFTJOINした集計クエリを作成。
　その際、各テーブルの日付フィールドの抽出条件に、
　between "#" & Forms![在庫数量確認]![開始] & "#" And "#" & Forms![在庫数量確認]![開始] & "#"
　と設定します。

ご参考まで...

お礼

vizzar様　アドバイスを頂き、誠に有難うございます。
投稿させて頂いた件に関しましては、別の方のアドバイス方法により、
希望の数値を取得する事が出来ました。

現在、関数・クエリ集計について学習をしておりますが、私にとって非常に難易度が高く、実践に至っておりません。
こちらのようなアドバイスを頂けるサイトの過去ログをひたすら検索する毎日でございます。
日々精進致します。
有難うございました。