ACCESSで在庫管理 備考欄の組み込み方？

ＡＣＣＥＳＳで在庫管理を始めました。
なんとか在庫管理だけはできるようになりましたが、
備考欄を設けたら、備考を入れたものは数が別でカウントされるように
なってしまいました。

今の在庫管理の状態は
＜テーブル＞
・商品ベース（商品ID、商品名が入っている）
・入出庫明細

＜フォーム＞
・入荷票（入出庫明細テーブルに入力される）
・出荷票（入出庫明細テーブルに入力される）

＜クエリ＞
・在庫表
・要発注表（在庫が１以下のものだけ表示する設定）

＜レポート＞
・在庫表（クエリの在庫表のレポート）
・要発注表（クエリの要発注表のレポート）

という感じです。

フォームに商品ＩＤを入れると、自動で商品名が表示されるようにしており（DLOOKUP）、
その下に入庫や出庫数を入力する欄、最後に備考入力欄を設けています。

たとえば、返品によって在庫が１つ増えたときに「返品」などというふうに
備考を入れたいのですが、そうすると在庫表や要発注表で
備考情報なしの物は今までの入出庫明細の合計で1行に在庫数が
ずばっと出ますが、備考を入れた物はその下に同じ商品ＩＤで
数行に出ます。
（結局最後はそれを手で計算する）

本当は、1行におさめて、備考欄を大きめにとって、そこに備考は
どんどん追加されるような感じにしたいのですが、可能でしょうか？

質問の仕方も下手ですみません。
補足要求してください。宜しくお願い致します。

No.3 回答者： noname#140971 回答日時： 2007/10/12 17:22

訂正：

現在庫の計算式の＋－に誤りが・・・。

No.2 回答者： noname#140971 回答日時： 2007/10/12 17:19

商品マスター:

ID___品名___期首在庫
1________Ａ_____________3
2________Ｂ_____________3

入荷明細:
ID___日付___________商品マスター_ID___数量___備考
1____2007/10/01_____________________1_______9___通常
2____2007/10/01_____________________2______10___返品
3____2007/10/02_____________________1_______1___返品

出荷明細:
ID___日付___________商品マスター_ID___数量___備考
1____2007/10/02____________________1_______5
2____2007/10/02____________________2_______5
3____2007/10/03____________________1_______1
4____2007/10/03____________________2_______1

現在庫クエリ:
品名____期首在庫___入荷数___出庫数____現在庫
Ａ________3___________________10___________6_________19
Ｂ________3___________________10___________6_________19

とまではＯＫだったが・・・。
これに、次のように備考欄を加える術が判らないということでしょう。

現在庫クエリ_II:
品名____期首在庫___入荷数___出庫数____現在庫___備考欄
Ａ________3___________________10___________6_________19___通常,返品
Ｂ________3___________________10___________6_________19___返品

普通は、Left join とか union とかを駆使すればと考えますが、そうではなく単純な SELECT 文で実現。

SELECT
品名,
期首在庫,
DBSum("数量","入荷明細","商品マスター_ID=" & [ID]) AS 入荷数,
DBSum("数量","出荷明細","商品マスター_ID=" & [ID]) AS 出庫数,
(期首在庫+入荷数+出庫数) AS 現在庫,
DBSelect("SELECT 備考 FROM 入荷明細 WHERE 商品マスター_ID=" & [ID],",") AS 備考欄
FROM 商品マスター;

実際に作成してテストした＜現在庫クエリ_II＞ですが、誰が見ても判る簡単なものです。
標準モジュールに次の関数を登録すれば、質問者の目的も実現できます。

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

Public Function DBSelect(ByVal strQuerySQL As String, _
　　　　　　　　　　　　 Optional ByVal strSeparator As String = ";") As String
On Error GoTo Err_DBSelect
　　Dim I　　 As Integer
　　Dim J　　 As Integer
　　Dim R　　 As Integer
　　Dim C　　 As Integer
　　Dim M　　 As Integer
　　Dim N　　 As Integer
　　Dim rst　 As ADODB.Recordset
　　Dim fld　 As ADODB.Field
　　Dim Datas As String
　　
　　Set rst = New ADODB.Recordset
　　' =================
　　'　Begin With: rst
　　' -----------------
　　With rst
　　　　 .Open strQuerySQL, _
　　　　　　　 CurrentProject.Connection, _
　　　　　　　 adOpenStatic, _
　　　　　　　 adLockReadOnly
　　　　 If Not .BOF Then
　　　　　　M = .RecordCount - 1
　　　　　　N = .Fields.Count - 1
　　　　　　.MoveFirst
　　　　　　For R = 0 To M
　　　　　　　　For C = 0 To N
　　　　　　　　　　Datas = Datas & .Fields(C) & strSeparator
　　　　　　　　Next C
　　　　　　　　.MoveNext
　　　　　　Next R
　　　　 End If
　　End With
　　' ---------------
　　'　End With: rst
　　' ===============
Exit_DBSelect:
　　DBSelect = Left(Datas, Len(Datas) + (Len(Datas) > 0))
　　Exit Function
Err_DBSelect:
　　MsgBox "SELECT 文の実行時にエラーが発生しました。(DBSelect)" & Chr$(13) & Chr$(13) & _
　　　　　　"・Err.Description=" & Err.Description & Chr$(13) & _
　　　　　　"・SQL Text=" & strQuerySQL, _
　　　　　　vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_DBSelect
End Function

この回答へのお礼

ありがとうございます。
ＳＱＬなどは全然わからない初心者で、教えて頂いた内容を
自分のテーブルやフィールド名と合うように変えて、
保存したつもりなんですが、関数エラーメッセージが出てしまいました。
ちなみに、私の作ったものには「期首在庫」はなく、入荷か出荷で
個数を入力すると初めて商品がクエリで出る、というものです。

教えていただいた式と、自分が使っているテーブル、フィールド名が違うので
訂正が必要ですよね？

たとえばIDというのは商品IDと置きなおしていいのでしょうか？
また、
DBSelect("SELECT 備考 FROM 入出庫明細 WHERE 商品ベース_商品ID=" & [商品ID],",") AS 備考欄
FROM 商品ベース;
のところが全然わかりません。

Public Function DBSum(ByVal strField As String, _・・・・
というのは標準モジュールに登録しました。（でいいんでしょうか？）

もしもお時間ありましたら教えてください。すみません。

お礼日時：2007/10/15 13:38

No.1 回答者： CHRONOS_0 回答日時： 2007/10/12 16:20

集計クエリからは備考欄を外さないとだめです

もう一点
>フォームに商品ＩＤを入れると、自動で商品名が表示されるようにしており（DLOOKUP）、
DLookupはこのような時に使うものではないですよ
クエリを使用しましょう