エクセルVBAとアクセスでの在庫管理PGMの仕様

質問

おはようございます。
お世話になります。

こちらの質問からの派生で、エクセルVBAとアクセスで在庫管理をするプログラムの作り方を質問させていただいております。
http://okwave.jp/qa/q8352035.html
よろしくお願いいたします。

仕様について
【１】テーブルのフィールドとしては5つ、商品番号、カラー、サイズ、在庫数、備考

→上記のレイアウトでお願いします。ただし、最初に設定したフィールドを今後増やす場合にプログラムに修正の必要が発生するかどうか私の知識ではわかりません。
今後は色々なサインを各商品に持たせて、色々な切り口で商品を抽出したいと考えているため、フィールドを後から増やすのがやっかいな場合は最初から備考を１から１６までの16個用意したいです。それに商品番号、カラー、サイズ、在庫数を合わせた20フィールドでお願いできますでしょうか。

【２】テーブルの入力は、フォームは作らず、当面テーブルに直接入力とする

→上記の方式で問題ありません。INSERT文を作り、新しいアイテムの在庫レコードは直接テーブルに挿入します。

【３】検索用フォームとクエリーをつくる。カラーとサイズを条件にして該当するレコードを抽出

→はい、商品番号・カラー・サイズを条件にしてレコードを抽出したいです。
ここでどこまで可能かわからないのでもし無理を言っているようでしたらご指摘いただきたいですが、現在のデータ状況とともに希望を述べさせていただきます。

※１※カラーに関しては、色違いのものがある場合にはセットされていますが、色違いがない場合はセットされていません。カラーを何かセットしないとならない仕様や、カラーが入っていないレコードは抽出されない仕様ではない方がありがたいです。

※２※商品番号をあいまい検索にすることは可能でしょうか。たとえば「99999shirt」と登録されているものもあれば「9999」だけで特に服の型が入っていないものもあります。手元に「99999」の服があったときに、その在庫を確認するときに「99999」とうてば結果が確認できるとよいですが、「99999shirt」とうたないと検索されないと不都合があります。(「99999shirt」だったか、「99999blouse」だったか…と困りそうです)

※３※検索結果は条件にあてはまるものすべてを表示したいです。理由はほぼ同じ番号で登録されているものがあるからです。たとえば「999onepiece」「999_onepiece」などです。このとき、「999_onepiece」を確認したく、「999」と打ち込んで検索したときに「999onepiece」を先に見つけたので検索を終了するような仕様だと結果が得られません。

※４※抽出レコードの表示フィールドは指定できますでしょうか。「商品番号、カラー、サイズ、在庫数、備考」が表示される、が標準で、目的によりたとえば「商品番号、カラー、サイズ、在庫数、備考１、備考２、備考３、備考４」まで選べるということです。これは最低限の機能ではないので検索機能に手間が増えるようであれば贅沢は申し上げません。

4在庫数を更新するためのクエリーを作る

→おねがいいたします。

【補助】ほかにご希望の機能はございませんか

→商品が売れた時に日々使うという意味で、上記の通りの希望となります。
拡張機能として、将来的に現在開いている各ネットショップ上での商品番号もフィールドに持たせて、それをキーとして各ネットショップシステムに更新をかけられるレイアウトのcsvを吐き出せられたらさらに自動化が進んだプログラムになると思うのでそういう希望はあります。そういった拡張の余地のあるプログラムを希望いたします。

私のPC環境はWindows7・Office2010です。
どうぞよろしくお願いいたします。

回答No.3

chayamati

横入りごめんなさい

仕様について
【１】テーブルのフィールドとしては5つ、商品番号、カラー、サイズ、在庫数、備考
　　一つのテーブルではエクセルの延長でしかありません。
　　複数のテーブルを活用しましょう

・カラーテーブル
　ID(主キー オートナンバー）
　色名(テキスト型 重複なし)

・サイズテーブル
　ID(主キー オートナンバー）
　サイズ(テキスト型 重複なし)

・商品テーブル
　ID(主キー オートナンバー）
　商品名(テキスト型 重複なし)
　カラーID(長整数型)
　サイズID(長整数型)
　商品番号(整数型 重複なし）
　商品名カナ(テキスト型 重複あり)
　　※余分なようですが参照時に活用できます。後でフィールドを
　追加することも出来ますが、振り仮名機能があり、
　商品名を入力したときの読みがここへ入ります。
　先でフィールドを追加することも出来ますが、
　それまで登録したレコードに振り仮名を入力するのは嫌なものです。

・在庫テーブル
　ID(主キー オートナンバー）
　商品ID(長整数型)
　初期在庫数(長整数型)
　入庫合計数(長整数型)
　出庫合計数(長整数型)

・入庫テーブル
　ID(主キー オートナンバー）
　入庫日(日付・時刻型)
　商品ID
　入庫数

・入庫テーブル
　ID(主キー オートナンバー）
　出庫日(日付・時刻型)
　商品ID
　出庫数

以上6個のテーブルから始めましょう
　この内カラーテーブル、寸法テーブルは無駄なようですが、
　都度入力すると同じものが違う文字列になりかねません
　表現方法に決まりが出来トラブルを回避できます。

フィールドの追加は自由です。事前に用意する必要はありません。

【２】テーブルの入力は、フォームは作らず、当面テーブルに直接入力とする
　　　何方が入力しますか？エクセルではありません、入力しなくてよいフィールドはフォーム上には配置する必要はありません、
　　オートナンバー型のフィールドもフォームから外せます。
　　フォームの作成は
　　【テーブルの選択】⇒【作成リボン】⇒【フォーム】で1画面1レコード
　　【テーブルの選択】⇒【作成リボン】⇒【その他のフォーム】⇒【複数アイテム】1画面複数レコード
　　と3～4回のクリックで出来上がりです。
　　あとは入力の必要ないフィールドを除去します

【３】検索用フォームとクエリーをつくる。カラーとサイズを条件にして該当するレコードを抽出
　→はい、商品番号・カラー・サイズを条件にしてレコードを抽出したいです。
　　メニューフォームを作りここに検索項目をコンボボックスで配置します
　　【２】で作ったフォームに少し手を加えるだけで検索用フォームの完成です

※１※カラーに関しては、色違いのものがある場合にはセットされていますが、色違いがない場合はセットされていません。カラーを何かセットしないとならない仕様や、カラーが入っていないレコードは抽出されない仕様ではない方がありがたいです。
　　……同じ商品番号でカラー、サイズの異なるものがあるのでしたら在庫管理が半端になりますが

※２※商品番号をあいまい検索にすることは可能でしょうか。たとえば「99999shirt」と登録されているものもあれば「9999」だけで特に服の型が入っていないものもあります。手元に「99999」の服があったときに、その在庫を確認するときに「99999」とうてば結果が確認できるとよいですが、「99999shirt」とうたないと検索されないと不都合があります。(「99999shirt」だったか、「99999blouse」だったか…と困りそうです)
　　……あいまい検索も可能ですが、商品番号を手入力する積りですか、これでは在庫管理は出来ません
　　商品番号入力は登録してある商品テーブルをコンボボックスで処理します

※３※検索結果は条件にあてはまるものすべてを表示したいです。理由はほぼ同じ番号で登録されているものがあるからです。たとえば「999onepiece」「999_onepiece」などです。このとき、「999_onepiece」を確認したく、「999」と打ち込んで検索したときに「999onepiece」を先に見つけたので検索を終了するような仕様だと結果が得られません。
　　……エクセルのVLOOKUP関数と異なり、条件に当てはまるレコード全てが得られます

※４※抽出レコードの表示フィールドは指定できますでしょうか。「商品番号、カラー、サイズ、在庫数、備考」が表示される、が標準で、目的によりたとえば「商品番号、カラー、サイズ、在庫数、備考１、備考２、備考３、備考４」まで選べるということです。これは最低限の機能ではないので検索機能に手間が増えるようであれば贅沢は申し上げません。
　　……表示項目は自由に選択できます(【2】で作成したフォームがそれです

4在庫数を更新するためのクエリーを作る
　　……クエリーではありませんが在庫テーブルを出庫テーブルの出庫数の合計と、入庫テーブルの入庫数の合計でUPDATEします。

【補助】ほかにご希望の機能はございませんか

→商品が売れた時に日々使うという意味で、上記の通りの希望となります。

拡張機能として、将来的に現在開いている各ネットショップ上での商品番号もフィールドに持たせて、それをキーとして各ネットショップシステムに更新をかけられるレイアウトのcsvを吐き出せられたらさらに自動化が進んだプログラムになると思うのでそういう希望はあります。そういった拡張の余地のあるプログラムを希望いたします。

　　……つぎは[http://www.geocities.jp/cbc_vbnet/top/nyumon.html]よりの抜粋です

　　　次の例は、[T_名簿]テーブルをcsv形式で保存しています。
　　　DoCmd.TransferText acExportDelim, , "T_名簿", "D:\Backup\Honkasei.csv", True

念押しです。
曖昧入力の出来ない仕組みが絶対に必要です。
入力の一部をEXCELと考えられているように思われてなりません。
商品コードの入力はVLOOKUP関数を使い、曖昧入力の危険を回避して下さい。

お礼

ありがとうございます。
（もう一つ上のアンサーと重複しているようです。）

回答No.2

chayamati

横入りごめんなさい

仕様について
【１】テーブルのフィールドとしては5つ、商品番号、カラー、サイズ、在庫数、備考
　　一つのテーブルではエクセルの延長でしかありません。
　　複数のテーブルを活用しましょう

・カラーテーブル
　ID(主キー オートナンバー）
　色名(テキスト型 重複なし)

・サイズテーブル
　ID(主キー オートナンバー）
　サイズ(テキスト型 重複なし)

・商品テーブル
　ID(主キー オートナンバー）
　商品名(テキスト型 重複なし)
　カラーID(長整数型)
　サイズID(長整数型)
　商品番号(整数型 重複なし）
　商品名カナ(テキスト型 重複あり)
　　※余分なようですが参照時に活用できます。後でフィールドを
　追加することも出来ますが、振り仮名機能があり、
　商品名を入力したときの読みがここへ入ります。
　先でフィールドを追加することも出来ますが、
　それまで登録したレコードに振り仮名を入力するのは嫌なものです。

・在庫テーブル
　ID(主キー オートナンバー）
　商品ID(長整数型)
　初期在庫数(長整数型)
　入庫合計数(長整数型)
　出庫合計数(長整数型)

・入庫テーブル
　ID(主キー オートナンバー）
　入庫日(日付・時刻型)
　商品ID
　入庫数

・入庫テーブル
　ID(主キー オートナンバー）
　出庫日(日付・時刻型)
　商品ID
　出庫数

以上6個のテーブルから始めましょう
　この内カラーテーブル、寸法テーブルは無駄なようですが、
　都度入力すると同じものが違う文字列になりかねません
　表現方法に決まりが出来トラブルを回避できます。

フィールドの追加は自由です。事前に用意する必要はありません。

【２】テーブルの入力は、フォームは作らず、当面テーブルに直接入力とする
　　　何方が入力しますか？エクセルではありません、入力しなくてよいフィールドはフォーム上には配置する必要はありません、
　　オートナンバー型のフィールドもフォームから外せます。
　　フォームの作成は
　　【テーブルの選択】⇒【作成リボン】⇒【フォーム】で1画面1レコード
　　【テーブルの選択】⇒【作成リボン】⇒【その他のフォーム】⇒【複数アイテム】1画面複数レコード
　　と3～4回のクリックで出来上がりです。
　　あとは入力の必要ないフィールドを除去します

【３】検索用フォームとクエリーをつくる。カラーとサイズを条件にして該当するレコードを抽出
　→はい、商品番号・カラー・サイズを条件にしてレコードを抽出したいです。
　　メニューフォームを作りここに検索項目をコンボボックスで配置します
　　【２】で作ったフォームに少し手を加えるだけで検索用フォームの完成です

※１※カラーに関しては、色違いのものがある場合にはセットされていますが、色違いがない場合はセットされていません。カラーを何かセットしないとならない仕様や、カラーが入っていないレコードは抽出されない仕様ではない方がありがたいです。
　　……同じ商品番号でカラー、サイズの異なるものがあるのでしたら在庫管理が半端になりますが

※２※商品番号をあいまい検索にすることは可能でしょうか。たとえば「99999shirt」と登録されているものもあれば「9999」だけで特に服の型が入っていないものもあります。手元に「99999」の服があったときに、その在庫を確認するときに「99999」とうてば結果が確認できるとよいですが、「99999shirt」とうたないと検索されないと不都合があります。(「99999shirt」だったか、「99999blouse」だったか…と困りそうです)
　　……あいまい検索も可能ですが、商品番号を手入力する積りですか、これでは在庫管理は出来ません
　　商品番号入力は登録してある商品テーブルをコンボボックスで処理します

※３※検索結果は条件にあてはまるものすべてを表示したいです。理由はほぼ同じ番号で登録されているものがあるからです。たとえば「999onepiece」「999_onepiece」などです。このとき、「999_onepiece」を確認したく、「999」と打ち込んで検索したときに「999onepiece」を先に見つけたので検索を終了するような仕様だと結果が得られません。
　　……エクセルのVLOOKUP関数と異なり、条件に当てはまるレコード全てが得られます

※４※抽出レコードの表示フィールドは指定できますでしょうか。「商品番号、カラー、サイズ、在庫数、備考」が表示される、が標準で、目的によりたとえば「商品番号、カラー、サイズ、在庫数、備考１、備考２、備考３、備考４」まで選べるということです。これは最低限の機能ではないので検索機能に手間が増えるようであれば贅沢は申し上げません。
　　……表示項目は自由に選択できます(【2】で作成したフォームがそれです

4在庫数を更新するためのクエリーを作る
　　……クエリーではありませんが在庫テーブルを出庫テーブルの出庫数の合計と、入庫テーブルの入庫数の合計でUPDATEします。

【補助】ほかにご希望の機能はございませんか

→商品が売れた時に日々使うという意味で、上記の通りの希望となります。

拡張機能として、将来的に現在開いている各ネットショップ上での商品番号もフィールドに持たせて、それをキーとして各ネットショップシステムに更新をかけられるレイアウトのcsvを吐き出せられたらさらに自動化が進んだプログラムになると思うのでそういう希望はあります。そういった拡張の余地のあるプログラムを希望いたします。

　　……つぎは[http://www.geocities.jp/cbc_vbnet/top/nyumon.html]よりの抜粋です

　　　次の例は、[T_名簿]テーブルをcsv形式で保存しています。
　　　DoCmd.TransferText acExportDelim, , "T_名簿", "D:\Backup\Honkasei.csv", True

念押しです。
曖昧入力の出来ない仕組みが絶対に必要です。
入力の一部をEXCELと考えられているように思われてなりません。
商品コードの入力はVLOOKUP関数を使い、曖昧入力の危険を回避して下さい。

お礼

おはようございます。

アドバイスありがとうございます。

カラーテーブル・サイズテーブルについてはコードテーブルを持つことで、マスターテーブル側では数値を持つだけでよい、紐付けをしてしまえばコードテーブル側にある諸カラムの内容を表示できるということですね。

入力については新しいアイテムの新規セットのイメージですので、私だけがやり、その内容はcsvデータの読み込みまたはINSERTクエリを作っての一括セットを考えていました。
しかし入力フォームというのも初めて存在を知り（ほとんどアクセスの諸機能にふれていないので）、アクセス上でメニューなどから調べてみるきっかけができました、ありがとうございます。
みてみます。

同じ商品番号で、サイズ・カラーが異なるもの→存在しますが、実物が違うのに同じ商品番号で管理しているものはありません。

商品番号の手入力についてですが、登録(INSERT)を手入力でするつもりはありません。初期セットは上記のように一括登録、その後は検索文言を手入力して、検索結果に出てきたレコードに対して在庫数値を書き換えて更新(UPDATE)なので、手入力した商品番号がテーブルにそのまま登録されるタイミングはないという理解です。念押ししていただいているので、お手数ですが上記の理解についてアドバイスいただけますでしょうか。この、検索ワードを手入力する、ということが危険なのでしょうか。そうであればその危険性が現在はわかっておりません。

在庫に関しては「在庫テーブル」というマスターの直接更新ではなく、入庫と出庫の都度のアクションをすべてレコードで持たせる(日付も管理）、初期値はあらかじめ在庫テーブルセット、そしてそれに入庫数と出庫数を足せば結果が得られるということですね。
ん？違いますか。初期値(現在数えている在庫数)は入庫テーブルに一括セットですかね。そうしないと在庫テーブルが、入庫テーブルと出庫テーブルの差引の値だけを持つ性質にできないですね。

ネットショップ向けcsv吐き出しの参考サイトありがとうございます。

見てみます。

いろいろと細かにありがとうございました。

回答No.1

noname#192382

アクセスのプログラムを作りました。内容は在庫管理テーブル、在庫検索クエリー、在庫更新クエリーです。あなたに送るにはどうしたらよいか管理者に問い合わせ中です。
ここで書けるだけは書いておきます。
データ入力は在庫管理テーブルを開いて直接入力する

在庫管理テーブル
商品番号を二つに分割して品種と型式とする。検索のとき品種だけの指定もできるし、品種と型式の利用法の指定もできます。

ID 品種 型式 カラー サイズ 在庫数 備考1 備考2 備考3 備考4
1 shirt 9999 blue 42 10
2 shirt 9988 yellow 42 12
3 blouse 5555 pink 100 25

在庫検索クエリー
1.在庫検索クエリーをデザインで開く。
2.抽出条件のところに品種、型式、カラー、サイズなど必要に応じて書き込みます。
3.ツールバーの実行ボタン ( ! )を押す。
試しに品種のところにblouseと指定しましたら、下のような検索結果が現れます。
デザインはコピーはりつけができませんのでここにお示しできません。あしからず。

ID 品種 型式 カラー サイズ 在庫数
3 blouse 5555 pink 100 25

在庫更新クエリー
在庫数の更新はテーブルを開いて直接入れなおすことでできます。この方がやりやすいと思いますが、一応更新クエリーを作りました。
更新するデータの指定は在庫管理テーブルのID番号でやるのがよいと思います。
1. 在庫更新クエリーをデザインで開く。
2.更新するレコードのID番号を抽出条件の欄に記入する。
3.在庫数のフィールドのレコード更新の欄に新在庫数を書き入れる。
4.ツールバーの実行ボタン ( ! )を押す。
5.確認のためのメッセージが現れますので「はい」と答えます。
これで在庫数は更新されます。
参考URLのサイトについて問い合わせ中です。

お礼

ありがとうございます！

ブランドによっては品種形式がないものもあるので、それらに今から品種形式を振っていくのはちょっと厳しいのですが、そういう場合はID側にすべて含めてしまってもよろしいですよね？

とりあえず最初はすべて品種形式はナシで登録すると思います。

プログラムの授受方法についての問い合わせまでしていただきありがとうございます。
結果を待たせていただきます。

ありがとうございます。