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

質問

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

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

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

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

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

という感じです。

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

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

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

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

回答No.13

noname#140971 · ベストアンサー

＞(入荷数+出荷数) AS 現在庫

ちょっと、DBSum関数に落とし穴があったみたいです。
テストで見落としていました。
まあ、還暦目前のじっちゃまのことです。
許されたし。

Public Function DBSum(ByVal strField As String, _
ByVal strTable As String, _
Optional strWhere As String = "") As Variant
・・・・
End Function

このように戻り値の型を Variant にしています。
ですから、

SELECT DBSum("数量","入出庫明細") AS AAA, DBSum("数量","入出庫明細") AS BBB, AAA+BBB;

AAA=80
BBB=80
AAA+BBB=8080

とクエリではなります。

これは、DBSum関数は、対象列や戻り値の型を知って使えということです。

SELECT CDbl(DBSum("数量","入出庫明細")) AS AAA, CDbl(DBSum("数量","入出庫明細")) AS BBB・・・

AAA+BBB=160

CINT・・・・整数
CLng・・・・長整数
CDbl・・・・倍精度

このように、列[数量]の型に変換してみて下さい。
そうすれば、この不具合は是正されます。
まあ、戻り値を Variant から変更すれば済むことですが・・・。
さて、どっちが良いのかはプロではないので明確な意見を持ち合わせていません。

お礼

ありがとうございました！！遂に出来ました！！
でも、今回できたのは、Husky2007さんに作っていただいたあの標準モジュールあってのものですよね。
やっぱりかなり詳しくないとできないですね（￣～￣;）

本当に長々と根気強く教えていただいてありがとうございました。

お茶ぐらいごちそうしたいもんです。

回答No.2

noname#140971

商品マスター:
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, _・・・・
というのは標準モジュールに登録しました。（でいいんでしょうか？）

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

回答No.1

CHRONOS_0

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

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