Accessで、在庫表を作りたいのですが・・・

質問

いつも大変お世話になっております。m(＿ ＿)m
また質問させてください。

Accessで、在庫表を作っています。
「入庫情報_テーブル」と「出庫情報_テーブル」があり、それぞれのテーブルは、製品名（型番）と数量の情報を持っています。

入庫情報の数量はプラスして、出庫情報の数量はマイナスして、差引きの在庫表を作りたいのですが・・・

入庫情報に入っている製品名のすべてを表示して、出庫情報の数量をマイナスする・・・というのは、「結合のプロパティ」で、入庫情報の全レコードと・・・というオプションを選べば可能です。
またその逆も可能なわけですが、両方を全部表示したい時はどうすればいいのでしょうか？

つまり、入庫情報に入力されている商品で出庫情報にのっていないものと、出庫情報に入力されている商品で入庫情報にのっていないものがあるわけです。
在庫表では、それぞれのテーブルに存在するすべての製品名を載せ、型番が合致した場合は、入庫数量－出庫数量で在庫を出したいのです。

出庫情報にあって、入庫情報にないものというのは、つまり、在庫数がマイナスになるということです。

両方を同時に満たすには、どのようにすればいいのでしょうか？

質問の説明文が不足していたら指摘してください。
よろしくお願いします。

回答No.6

noname#22222

COUNT() でなく SUM() です！

お礼

何度もお手数おかけしてすみません。
少し時間がかかるかと思いますが、ちゃんと挑戦してみます。
ありがとうございました。

回答No.5

noname#22222

s_husky です。

＜入庫履歴＞
ID　　入庫日　　製品名　数量
1　　2006/10/01　A　　　2
2　　2006/10/02　A　　　2
3　　2006/10/03　B　　　2

＜出庫履歴＞
ID　　出庫日　　製品名　数量
1　　2006/11/01　A　　　1
2　　2006/11/02　A　　　1
3　　2006/11/03　C　　　1

＜在庫表＞
製品名　数量
A　　　　2
B　　　　1
A　　　　-2
C　　　　-1

＜入庫履歴＞と＜出庫履歴＞から＜在庫表＞を生成するという手もあります。

Private Sub コマンド0_Click()
　　CnnExecute ("DELETE FROM 在庫表")
　　CnnExecute ("INSERT INTO 在庫表 " & _
　　　　　　　　　SELECT 製品名, COUNT(数量) AS 数量 FROM 入庫履歴 GROUP BY 製品名")
　　CnnExecute ("INSERT INTO 在庫表 " & _
　　　　　　　　　SELECT 製品名, COUNT(数量) * -1　AS 数量 FROM 出庫履歴 GROUP BY 製品名")
End Sub

なお、CnnExecute() は次のようです。

Public Function CnnExecute(ByVal strSQL As String) As Boolean
On Error GoTo Err_CnnExecute
　　 Dim isOK As Boolean
　　 Dim cnn　As ADODB.Connection

　　 isOK = True
　　 Set cnn = CurrentProject.Connection
　　 With cnn
　　　　 .Errors.Clear
　　　　 .BeginTrans
　　　　 .Execute strSQL
　　　　 .CommitTrans
　　 End With
Exit_CnnExecute:
On Error Resume Next
　　 cnn.Close
　　 Set cnn = Nothing
　　 CnnExecute = isOK
　　 Exit Function
Err_CnnExecute:
　　 isOK = False
　　 If cnn.Errors.Count > 0 Then
　　　　 ErrMessage cnn.Errors(0), strSQL
　　　　 cnn.RollbackTrans
　　 Else
　　　　 MsgBox "プログラムエラーが発生しました。システム管理者に報告して下さい。(CnnExecute)", _
　　　　　　　　vbExclamation, " 関数エラーメッセージ"
　　 End If
　　 Resume Exit_CnnExecute
End Function

お礼

詳細な返答ありがとうございました。
少し時間がかかるかと思いますが、挑戦してみます。
再度、質問させていただく場合もありますが、その時はよろしくお願いします。m(＿ ＿)m

回答No.4

Nii

No3でも書かれてるけど、UNIONクエリを使った方法を

select 製品名
from 出庫
UNION select 製品名
from 入庫;

で、とりあえずは、「ユニオン」って名前で今回は保存します。
そのクエリを元に、

SELECT ユニオン.製品名, 入庫.数量 AS 入庫数, 出庫.数量 AS 出庫数, nz([入庫数])-nz([出庫数]) AS 在庫
FROM (ユニオン LEFT JOIN 出庫 ON ユニオン.製品名 = 出庫.製品名) LEFT JOIN 入庫 ON ユニオン.製品名 = 入庫.製品名;

で、どうでしょう？

後、件数があるならば、テーブル作成クエリで、入庫テーブルから製品名と数量（入庫数）、そして出庫数（0にしとく）の作業テーブルを作成。
追加クエリで、入荷テーブルに無い在庫テーブルの製品名を追加、この時、入庫数は0にしとく。
更新クエリで、出庫数を更新・・・
ただ、繰り返しやってるとMDBがよく壊れるので、作業テーブルを外部のMDBにするのと、まめな修復・最適化が必要ですが・・・

お礼

返答ありがとうございました。

ユニオンクエリというのは、初挑戦ですが、ヘルプ等を参照しながら、挑戦してみたいと思います。
時間がかかるかもしれませんが、また再度、ご質問等させていただくかと思います。よろしくお願いします。

回答No.3

CHRONOS_0

ご希望のような結合のことをフル外部結合と言います
ただしこれはAccessではサポートされていませんから
入庫から出庫へ外部結合したものと出庫から入庫へ外部結合したものを
ユニオンクエリで重複なしで、縦に結合します

詳細が分からないので雰囲気だけ

select ・・・・・・
from 入庫情報_テーブル left join 出庫情報_テーブル
union
select ・・・・・・
from 入庫情報_テーブル right join 出庫情報_テーブル

お礼

回答ありがとうございます。
やはり・・・(-_-#)ですか・・・
理解しました。やってみます。

回答No.2

noname#22222

s_husky です。

入庫日=>出庫日
出庫出庫=>出庫総計

多少、ミスがあります。全体の主旨だけを汲んで頂ければ幸いです。

回答No.1

noname#22222

[製品一覧]

ID-----------　1
品名---------　A
前月期首-----　1
前月入庫-----　1
前月出庫-----　0
当月入庫-----　1
当月出庫-----　2

[入庫履歴]
入庫日-------　2006/11/11
製品_ID------　1
数量---------　1

[出庫履歴]
入庫日-------　2006/11/16
製品_ID------　1
数量---------　2

[各種設定]
現在庫締日---　2006/10/31

というテーブル設計ですと話が簡単です。
[各種設定.現在庫締日]に基づいて[製品一覧.前月期首]等を更新すればいいからです。

さて、質問内容からすると、次の設計でも良い気もします。

[製品一覧]

ID-----------　1
品名---------　A
入庫総計-----　1
出庫出庫-----　2

であれば、単純に入出庫履歴を集計するだけです。

※質問の意図に反した回答であることは重々に承知しています。
※が、少なくとも、[製品一覧]、[入庫履歴]、[出庫履歴]という３テーブルをお勧めします。
※[各種設定]は、棚卸処理コードを書くことを意味しますのでウーンです。

お礼

早速のお返事ありがとうございました。
確かに、「製品マスタ」が存在すれば、一番楽なことは分かっているのですが、現状ではそれが不可能な業務状況でして・・・（＾＾；）
すみません。
テーブル設計が悪いのかもしれませんが、あくまで「入庫情報」と「出庫情報」のテーブルは分かれ、その２テーブルを比較して、同じ型番のものを足したり引いたりする・・・ということが必要になってきます。
お手数おかけしますが、良い方法はないでしょうか？？