エクセル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.13

noname#192382

クエリの結果をお送りします前にお示ししたのはクエリーのデザインでした。

お礼

ありがとうございます。

ここで一つ質問なのですが、ご回答No.10～13で示していただいた内容に沿って私が同じことを進めていき、
同じところまでいったら再びその先を示していただくという
流れを想定されていますでしょうか？

作業の流れの確認をさせて頂いております。

回答No.12

noname#192382

カラーがピンクで、ワンピースの商品を抽出するため、在庫検索クエリーをつくり、マスターテーブルの全項目を読み込んだ上、さらに新しいフィールドを臨時に作りました。そこには
InStr([在庫管理マスターテーブル]![商品管理番号],"onepiece")
と入れてあります。そして抽出条件のところに、からーについては「ピンク」新フィールドのところには「onepiece」と入れてあります。
このクエリーをツールバーの実行のボタンを押して実行したところ添付の結果が得られました。
とりあえずここまでご報告します。

お礼

作成しました。
式についても組んでみました。
成功しました。

私が用意してお知らせしてあったサンプルレコードのままでやりましたが、同じく1001onepieceのピンクのレコードのみ抽出されました。式の部分にはこのワンピースの合計在庫がセットされているようです！

続きをよろしくお願いいたします。
実物の在庫整理が済みましたので今日から数日以内に在庫システムを確立させて、日々売れている商品の手管理分を合流させたいと考えています。

よろしくお願いいたします！

回答No.11

noname#192382

たびたび現れてすみません。アクセスのテーブルが出来上がりました。一番左の項目はマスターテーブルのID番号です。試みにこのテーブルからピンクのワンピースを検索するクエリーを作ってみたいと思います。できましたら回答いたします。

MテーブルID 商品管理番号 サイズ カラー 在庫数
1 9292poloshirt 68cm ホワイト 5
10 1001onepiece 4～6ヶ月(68cm) アイボリー 1
11 1001onepiece 6～9ヶ月(74cm) ピンク 2
12 1001onepiece 6～9ヶ月(74cm) アイボリー 2
13 1001onepiece 1歳(=12ヶ月)(80cm) ピンク 2
14 1001onepiece 1歳(=12ヶ月)(80cm) アイボリー 1
2 9292poloshirt 68cm ピンク 4
3 9292poloshirt 74cm ホワイト 4
4 9292poloshirt 74cm ピンク 1
5 9292poloshirt 80cm ホワイト 0
6 9292poloshirt 80cm ピンク 4
7 9292poloshirt 86cm ホワイト 0
8 9292poloshirt 86cm ピンク 3
9 1001onepiece 4～6ヶ月(68cm) ピンク 1

お礼

テーブル内容について理解しました。
IDも用意するということですね。
同じレイアウトにして作ってみます。

ありがとうございます。

回答No.10

noname#192382

あなたのデータの一部を使ってマスターテーブルを一応作ってみました。エクセルです。これをアクセスに取り込んでみます。結果は次回に。

MテーブルID 商品管理番号 サイズ カラー 在庫数
1 9292poloshirt 68cm ホワイト 5
2 9292poloshirt 68cm ピンク 4
3 9292poloshirt 74cm ホワイト 4
4 9292poloshirt 74cm ピンク 1
5 9292poloshirt 80cm ホワイト 0
6 9292poloshirt 80cm ピンク 4
7 9292poloshirt 86cm ホワイト 0
8 9292poloshirt 86cm ピンク 3
9 1001onepiece 4～6ヶ月(68cm) ピンク 1
10 1001onepiece 4～6ヶ月(68cm) アイボリー 1
11 1001onepiece 6～9ヶ月(74cm) ピンク 2
12 1001onepiece 6～9ヶ月(74cm) アイボリー 2
13 1001onepiece 1歳(=12ヶ月)(80cm) ピンク 2
14 1001onepiece 1歳(=12ヶ月)(80cm) アイボリー 1

お礼

ありがとうございます。

回答No.9

noname#192382

今晩は。ここはますますの盛況でおめでとうございます。
回答いたします。エクセルではマクロのプログラムで全体をお示しできますが、アクセスでは、たとえば在庫検索クエリーの場合、「クエリー言語」(Query Stryctured Languq\ge)では
ELECT 在庫テーブル.ID, 在庫テーブル.品種, 在庫テーブル.型式, 在庫テーブル.カラー, 在庫テーブル.サイズ, 在庫テーブル.在庫数
FROM 在庫テーブル
WHERE (((在庫テーブル.品種)="blouse") AND ((在庫テーブル.カラー)="white"))
WITH OWNERACCESS OPTION;
となっていて、デザインは先日お示ししたものになります。
あなたのおっしゃるようにおおむね「アクセスのプログラムはソース記述というよりはアクセス上のボタンやプルダウンで設定して進めていくようなプログラムの作り方なのでしょうか。」そのとおりです。高度の作業、くぇりーをフォームで体裁よく動かすようにするとなると、プログラムで書くようになりますが、・・・・・・

元になるテーブルをまずつくったらいかがでしょうか。サンプルで結構ですから、10件ていどでいいですから作ってください。クエリーの作り方の説明が具体的にできますので。
ところでテーブルですが、エクセルで作られた既成の在庫テーブルがあるようでしたら、それは容易にアクセスに取り込めます。手入力の必要はありません。

お礼

こんばんは。

ご回答ありがとうございます。

なるほど、おおむねソース記述ではないんですね。
理解できました。

既成の在庫テーブルがあるので必要なカラムのみにしてアクセスに取り込みました。

しかし、一度質問スレッドを立ち上げると途中で追加で画像を掲載ができません。

自分で自分に回答する形なら画像を掲載できると考えましたが、自分の質問には回答できないようです。

ならばと仕方なく新しい質問スレッドを立ち上げてそこを画像を貼り付けるためだけに利用することにしました。
http://okwave.jp/qa/q8364621.html
しかし私の画像も不鮮明になってしまいました。

いずれにせよ、かなりOKWAVEの機能がこの部分はイケてないです。。。
とりあえず明朝OKWAVEに問い合わせをしてみようと思います。

あとは、たぶんうまく読めないと思いますが、アクセスに取り込んだエクセルの内容をテキストエディタに貼り付けたものを以下にコピペしてみます。

ID 商品管理番号（商品URL） 項目選択肢別在庫用横軸選択肢 項目選択肢別在庫用縦軸選択肢 項目選択肢別在庫用在庫数
1-11-111111111 68cm 4
1-11-111111111 74cm 4
1-11-111111111 80cm 2
1-11-111111111 86cm 3
9292poloshirt 68cm ホワイト 5
9292poloshirt 68cm ピンク 4
9292poloshirt 74cm ホワイト 4
9292poloshirt 74cm ピンク 1
9292poloshirt 80cm ホワイト 0
9292poloshirt 80cm ピンク 4
9292poloshirt 86cm ホワイト 0
9292poloshirt 86cm ピンク 3
1001onepiece 4～6ヶ月(68cm) ピンク 1
1001onepiece 4～6ヶ月(68cm) アイボリー 1
1001onepiece 6～9ヶ月(74cm) ピンク 2
1001onepiece 6～9ヶ月(74cm) アイボリー 2
1001onepiece 1歳(=12ヶ月)(80cm) ピンク 2
1001onepiece 1歳(=12ヶ月)(80cm) アイボリー 1
99-99-999999999 92 1
99-99-999999999 98 1
99-99-999999999 104 3
99-99-999999999 110 2
99-99-999999999 116 1
99-99-999999999 128 2

実際にこんな感じになっています。
カラーがセットされていないものは色違いがないものです。
サイズに関してはおわかりになるかと思いますが、同じcmを示すものでも表記が色々と違っています。cm付きのもの、cmなしのもの、cmに年齢までついているもの、などです。こちらは、アクセス内での管理のためにはデータ整備ということで、同一の表記に統一するのがよいのは理解しています。

しかし、実際にネットショップで販売中のサイズ表記になりますので、アクセス内で統一してしまうと、アクセスからネットショップに更新するcsvを作るときに、更新レコード特定キーの一つであるサイズの表記が変わるために、更新該当のレコードがないということになってしまうという不都合があります。

補足

申し訳ございません。

スペースでもあればまだ読めるところ、
そういったものもなく貼り付けてしまいました。

カンマで区切りました。
4種類の商品について、それぞれ例として１レコードずつカンマ付きのものを用意しました。

1-11-111111111,86cm,3
9292poloshirt,68cm,ホワイト,5
1001onepiece,4～6ヶ月(68cm),ピンク,1
99-99-999999999,92,1

こういう感じです。

よろしくお願いいたします。

回答No.8

chayamati

基準日の初期値設定を忘れてました。

添付のように、基準値のテキストボックスの
プロパティーの規定値に DATE()とします

EXCELのTODAY()関数のことです

お礼

No.7同様に、軌道に乗ってから確認させていただきます。
ご親切にありがとうございます。

回答No.7

chayamati

システムのイメージ出来たように推察します。
次の段階ですね
このイメージを実現するには色々な工夫が必要です。
その一つが日付の管理です。
任意の日付に対する月初日、月末日、期初日、期末日
の計算です。この任意の日付を基準日とし決算開始月
も導入して上記4つの計算方を記述します。
決算開始日とは決算日の翌日の月とします。
決算日とは家計簿は12月末日、多くの企業では3月末日ですので
決算開始月は1月、４月となります。

VBをご存知とか 次の処理はご理解いただけるかと
プロフィールと呼ぶテーブルを用意します
　いきなりプロフィールが出ましたが、
　システムの基本的な事を記録して置くもの(期初月もその一つ）
　と１レコードのみのテーブルとご理解下さい。

・プロフィール
期初月（整数型）

このプロフィールを元データとして、期初月のテキストボックス
のほか基準日、月初日、月末日、期初日、期末日の
テキストボックス配置したフォームを作成します。

Private Sub Form_Load()
基準日変更RTN
End Sub

Private Sub 基準日変更RTN()
If Month(基準日) < 期初月 Then
期初日 = DateValue(Year(基準日) - 1 & "/" & 期初月 & "/" & 1)
Else
期初日 = DateValue(Year(基準日) & "/" & 期初月 & "/" & 1)
End If
期末日 = 期初日 + 366: 期末日 = 期末日 - Day(期末日)
月初日 = 基準日 - Day(基準日) + 1
月末日 = 月初日 + 31: 月末日 = 月末日 - Day(月末日)
End Sub

日付はAccessもExcelもシリアル値の値で処理されています。
エクセルでご確認ください。

それではご検討を |(*^_^*)b♪

お礼

こんばんは、chayamati様

optimumsoup様にもご連絡させていただきましたが、
エクセル→アクセスに接続、という方式ではありますが
複数の検索キーを条件にレコードを抽出
→更新したいレコードの値を書き換えて更新
→処理完了
までが、テスト用ではありますが成功しました。

明朝実際のレイアウトに沿って作り直してみます。
うまくいけば、在庫テーブルから商品番号などで
対象レコードを読み込み、在庫データを書き換えて
更新する。という処理が可能になります。

しかしこれは在庫テーブルを直接読込＆更新する、
ということの実現までになります。

アドバイスいただきました、在庫テーブルは初期値の
セットのみで、その後は履歴の管理の上でも
データセットは入庫テーブルと出庫テーブルのいずれかに
のみ行い、最新在庫はその両テーブルと初期値セット済の
在庫テーブルの３テーブルの足し引きで確認、
という仕様はその次の段階になります。
※決算などを意識した日付の管理はそのさらに先になると
いう認識で、なるべく混乱したくないのでまずは省きます。

在庫系の３テーブルの扱いについて不明な点が起きると思いますので、No.8の補足欄を利用してご連絡させていただきたいと思います。現在私からの連絡手段は上記の欄しかありませんので、このお礼をご覧いただきましたら、何らかのご意見なりを回答欄を使っていただけると、連絡するための欄が増えて安心できます。

よろしくお願いいたします。

補足

ありがとうございます。

optimumsoupさまからPGMを受け取ってまずは軌道に乗りたいと思います。

その後、在庫テーブルの直接更新ではなく、入庫テーブルと出庫テーブルの用意による更新にデータベースと検索・更新クエリを切り替えるという手順にしたいと思います。

とりあえず今週中には在庫を数え終わるのでそろそろPGM側に進めそうです。

回答No.6

noname#192382

サポート担当の人からメールがあり、下記のようにサイトの決まりとして個人が特定できるようなサイトは使えないので、画像添付の形でやってくださいということでした。

「お問い合わせの件については、大変申し訳ございませんが、当サイトでは、
ご自身の管理運営するホームページへアップロードを行ったプログラムについ
てのURLを開示することや、アップロードサイトなどへアップロードしたファ
イルを紹介していただくことは、禁止行為としております。」

画像が小さくてすみません。どうすれば大きくできるかどなたか教えていただけないでしょうか。
在庫検索クエリーのデザインはクエリーに取り上げるフィールドはテーブルの中の備考関係のもの以外すべてで、抽出条件として品種のところに"blouse"カラーのところに"white"と書いてあります。
もどかしいですが、一つ一つ進めるしかないようです。

お礼

おはようございます。

ありがとうございます。

プログラムということでしたので、画像ではなくソースを貼り付けていただくことは難しいでしょうか？
投稿文字数に収まるような文字数ではないですかね・・・。

メイン処理開始

検索処理

更新処理

メイン処理終了

のようにメインが一つあってその中に細分化した処理が記述されているようならその細分化部分を個別に貼り付けて頂ければ理解できますが、画像で渡していただこうとしているということは、アクセスのプログラムはソース記述というよりはアクセス上のボタンやプルダウンで設定して進めていくようなプログラムの作り方なのでしょうか。

すみません、エクセルVBAをOKWAVEで教えていただいたものを丸ごとコピーして作った回数が2回ある程度で、アクセスに関してはVBAがメニューバーのどこにあるかもまだ確認していないほどなじみがないので、ちょっと状況がつかめておりません。

回答No.5

noname#192382

遅くなりましたが、在庫検索クエリーのデザインをjpg形式に作りましたので添付してお送りします。抽出条件として品種にblouseカラーにwhiteを指定しています。

お礼

ありがとうございます。

補足

すみません、画像が小さくてうまく読めません。

ダウンロードして拡大したら文字がぼやけてしまい、やはり読めませんでした。

在庫管理プログラムの受け渡しについて、OKWAVE側からお返事は来ましたでしょうか。

色々とお手数おかけしております。
よろしくお願いいたします。

回答No.4

chayamati

今日は。あなたの読解力と迅速な反応に関心しています。
こちらもやりがいを感じています。

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

Ｑ．カラーテーブル・サイズテーブルについてはコードテーブルを持つことで、
　　マスターテーブル側では数値を持つだけでよい、紐付けをしてしまえばコードテーブル側に
　　ある諸カラムの内容を表示できるということですね。
Ａ．その通りです。この紐付けのことをリレーションシップと呼び処理の都度の紐付けも
　　可能ですがシステムツールから設定すると、サブテーブルにレコードが残っていると、
　　メインテーブルのレコードは削除できません。

Ｑ．同じ商品番号で、サイズ・カラーが異なるもの→存在しますが、
　　そうであればその危険性が現在はわかっておりません。
Ａ．商品番号レベルで重複があると言うことは商品を入出庫したとき在庫の増減の処理は
　　商品番号、カラー、サイズの３つを項目をキーが必要になります。
　　そのためＰＧＭが冗長になり、キーも３項目入力が必要になります
　　次の方法で重複がないことを確認してください
　　　「実物が違うのに同じ商品番号で管理しているものはありません。」のテーブルの
　　　デザインビューで、商品番号、サイズ、カラーの３つのフィールドを隣同士に移動して、
　　　３つのフィールドの組み合わせを主キーにしてして通ったら重複がないことになります。

Ｑ．在庫に関しては「在庫テーブル」というマスターの直接更新ではなく、
　　入庫と出庫の都度のアクションをすべてレコードで持たせる　(日付も管理）、
　　初期値はあらかじめ在庫テーブルセット、
　　そしてそれに入庫数と出庫数を足せば結果が得られるということですね。
　　ん？違いますか。初期値(現在数えている在庫数)は入庫テーブルに一括セットですかね。
　　入庫テーブル　と出庫テーブルの差引の値だけを持つ性質にできないですね。
Ａ．ある基準点(通常月初、期初）の在庫数を初期値とします。
　　ある時点の理論在庫数は次の式で計算できます。
　　理論在庫数＝初期在庫数＋期間内の入庫合計－出庫合計
　　この計算は在庫テーブルの
　　　入出庫フィールドを初期化するＳＱＬ
　　　入庫合計を更新するＳＱＬ文
　　　出庫合計を更新するＳＱＬ文
　　　の３つのＳＱＬ文で実現できます。
　　　処理時間も一呼吸後に結果が出ます。(もっともレコード数は５～６千件程度ですが）
　　入出庫データにエラーが見つかっても、そのエラーデータを修正すると理論在庫も
　　修正されます。

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

追伸

Ｑ．「99999shirt」だったか、「99999blouse」だったか…と困りそうです)
Ａ．商品種別テーブルを新規作成する手もあります。
・商品種別テーブル
　ID(オートナンバー型 主キー)
　商品種別名(テキスト型 重複なし)

　こうして、商品テーブルの商品名を商品種別ＩＤに置き換える

お礼

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

商品番号・カラー・サイズ、の3項目レベルでの重複がないかどうかの確認、します。
登録用の入力を曖昧にすることにより同じものを示したいのに違う表記になっていることにより重複して登録してしまえばデータ管理上危険どころか管理ができるわけがないことについては、大丈夫です、理解しています。

在庫のテーブル構成については
１．在庫テーブル(初期値格納)
２．入庫テーブル
３．出庫テーブル
→最新在庫状況を示す理論在庫はテーブルを持たずクエリで１＋２－３をして取得するということですね。概念理解しました。

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