Access VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


417 / 2272 ツリー ←次へ | 前へ→

【11779】VBAをクエリで使う方法 かな 10/9/24(金) 13:30 質問[未読]
【11780】Re:VBAをクエリで使う方法 かな 10/9/24(金) 15:53 発言[未読]
【11781】Re:VBAをクエリで使う方法 かな 10/9/24(金) 15:55 発言[未読]
【11783】Re:VBAをクエリで使う方法 YU-TANG 10/9/24(金) 17:31 質問[未読]
【11784】Re:VBAをクエリで使う方法 かな 10/9/27(月) 10:15 発言[未読]
【11787】Re:VBAをクエリで使う方法 10/9/27(月) 13:10 発言[未読]
【11789】Re:VBAをクエリで使う方法 かな 10/9/27(月) 16:04 質問[未読]
【11791】Re:VBAをクエリで使う方法 10/9/27(月) 17:16 回答[未読]
【11800】Re:VBAをクエリで使う方法 かな 10/9/30(木) 12:39 お礼[未読]
【11792】Re:VBAをクエリで使う方法 YU-TANG 10/9/27(月) 20:07 発言[未読]
【11801】Re:VBAをクエリで使う方法 かな 10/9/30(木) 12:41 お礼[未読]
【11793】Re:VBAをクエリで使う方法 247b 10/9/27(月) 21:16 発言[未読]
【11794】Re:VBAをクエリで使う方法 247b 10/9/28(火) 14:09 発言[未読]
【11802】Re:VBAをクエリで使う方法 かな 10/9/30(木) 12:48 お礼[未読]
【11805】Re:VBAをクエリで使う方法 mayu 10/10/3(日) 1:26 回答[未読]
【11818】Re:VBAをクエリで使う方法 かな 10/10/19(火) 14:07 お礼[未読]

【11779】VBAをクエリで使う方法
質問  かな  - 10/9/24(金) 13:30 -

引用なし
パスワード
   アクセス初心者です。
どうしてもわからないのでアドバイスお願いします

今、下記のようなテーブルが全部で10万件あります

品番    キー   数 
A      A    2
B      A|B   2
C      A|B|C  3
D      D    1
E      D|E   2
F      D|E|F  2
A      A    3
B      A|B   3
W      A|B|W  3

このテーブルを使用して、クエリで、数の集計をしたいと考えています
結果は、
品番    キー   数   総計
A      A    2    2
B      A|B   2    4
C      A|B|C  3    12 
D      D    1    1
E      D|E   2    2
F      D|E|F  2    4
A      A    3    3
B      A|B   3    9
W      A|B|W  3    27

こんな感じに総計欄に数字を入れていきたいのですが、うまくいきません
この総計の算出方法としては、
キーの組み合わせをヒントに順番に掛けて総計数を出しています

例えば、品番のCの総計を出そうとするならば、
Cと繋がってる品番、AとBの数を順番に掛けて、総計を出しています

(2)A*(2)B*(3)C=(12)Cの総計


ただ、問題なのが、同じ品番でも、数が違うものが多々あります

例えば、上記例でも、品番Aが出ていますが、下記の組み合わせの場合は数が違いますので

(3)A*(3)B*(3)*W=(27)Wの総計

ですから、キーをヒントに繋がっている品番の数を掛けて総計を算出したいのですが、
これを、アクセス上で表示させるにはどうしたらいいのでしょうか?

今は、こんな感じで作ってみました


クエリー上のフィールド名に
総計:QTY([キー],[数])

標準モジュールに

Public Function QTY(A As String, B As Integer)

Dim Hairetu
Dim MyItem As String

Hairetu = Split(A, "|")

If A Like "*|*" Then
  MyItem = test(UBound(test) - 1)
 
  '↑ここの記述方法で悩んでます
  ’とりあえず、|で分割して、一つ上の親となるアイテムの数を
   掛けて、総計を出そうかと考えておりますが、
   それをどう記述すればよいのか?
   どうかアドバイスお願いします

Else
QTY = B  ’もし、キー内に|が含まれていなかったら、総計は数量の値
End If


End Function

【11780】Re:VBAをクエリで使う方法
発言  かな  - 10/9/24(金) 15:53 -

引用なし
パスワード
   >If A Like "*|*" Then
>  MyItem = test(UBound(test) - 1)
> 
>
上記の部分の変数ミスがありました
 MyItem = Hairetu(UBound(test) - 1)
が正しいです

すみません記入ミスでした

【11781】Re:VBAをクエリで使う方法
発言  かな  - 10/9/24(金) 15:55 -

引用なし
パスワード
   たびたび・・・すみません
またまた間違いが見つかりましたので訂正させてください
 
MyItem = Hairetu(UBound(Hairetu) - 1)


>>If A Like "*|*" Then
>>  MyItem = test(UBound(test) - 1)
>> 
>>
>上記の部分の変数ミスがありました
> MyItem = Hairetu(UBound(test) - 1)
>が正しいです
>
>すみません記入ミスでした

【11783】Re:VBAをクエリで使う方法
質問  YU-TANG  - 10/9/24(金) 17:31 -

引用なし
パスワード
   方法論以前に、

>品番    キー   数 
>A      A    2
>B      A|B   2



> A      A    3
> B      A|B   3

を区別できるグループキーはどこにあるのでしょうか?

【11784】Re:VBAをクエリで使う方法
発言  かな  - 10/9/27(月) 10:15 -

引用なし
パスワード
   ▼YU-TANG さん:

説明不足ですみません
区別するものというと、
データの並び順通りなんですが、
対象アイテムのすぐ上にある品番が、同じグループとして認識するようにしたいのです。
例えば、8行目の品番をターゲットにしたとき、自分より一つ上はAとなります
8行目から上のデータを見て、一番近い場所でAがあったものの数を掛けたいのです

実際は、ツリー構造みたいにしてあります

1 2 3 4 5 品番 キー    数 ←フィールド名
-----------------------------------------
1       A   A     2
  2      B   A|B    2
   3    C   A|B|C   3
1       D   D     1
  2      E   D|E    2
   3    F   D|E|F   2
1       A   A     3
  2      B   A|B    3
   3    W   A|B|W   3


今までは、エクセルのマクロでこの処理を行っていました
そのときは、
ターゲットの品番の一つ上の行から、上へ順番に見ていき、
同じ品番があったら、その数を掛けていました
そんな感じにアクセス上でできないものか?と思い、考えているところです

説明のしかたが下手ですみません
どうかよろしくお願いします


>方法論以前に、
>
>>品番    キー   数 
>>A      A    2
>>B      A|B   2
>
>と
>
>> A      A    3
>> B      A|B   3
>
>を区別できるグループキーはどこにあるのでしょうか?

【11787】Re:VBAをクエリで使う方法
発言    - 10/9/27(月) 13:10 -

引用なし
パスワード
   雅です。

AccessはDBなのでデータの並び順は連番フィールドとかないと保障されません
けどそこら辺は問題ないですか?

SQLで何とかするよりもVBAでやったほうが早そうです。
Excelで書いていたVBAはほとんど同じように使えると思いますよ。

【11789】Re:VBAをクエリで使う方法
質問  かな  - 10/9/27(月) 16:04 -

引用なし
パスワード
   ▼雅 さん:

アドバイスありがとうございます
エクセルでのマクロは、うまくアクセスに取り込むことは、できませんでした

例えば、現在のレコード位置より前のデータを順番に見ていき、
ターゲットとなる部品が見つかれば、そのレコードの[数]をかけて、現在のレコードのフィールド[総計]に値を入力するように考えています

その為、現在のフィールド位置の取得をしたほうがいいのかな?と思い
ネットであちらこちら調べてはいるんですが、いまいちうまくいきません

デザインビューに総計:QTY([キー],[数])
と入れて、マクロを呼び出しているのですが、
そのFunction 内に DAOで、先頭レコードまで逆順読みみたいなものを作ってみようとしたのですが、使えないのでしょうか?
なんだかエラーになる為・・・無理なのか、私が意味をわかっていないのか・・・
そんな感じで四苦八苦しております

この考えでは、やはり、むずかしいでしょうか?
連番フィールドはとらないとやっぱりまずいでしょうか?


>雅です。
>
>AccessはDBなのでデータの並び順は連番フィールドとかないと保障されません
>けどそこら辺は問題ないですか?
>
>SQLで何とかするよりもVBAでやったほうが早そうです。
>Excelで書いていたVBAはほとんど同じように使えると思いますよ。

【11791】Re:VBAをクエリで使う方法
回答    - 10/9/27(月) 17:16 -

引用なし
パスワード
   雅です。

連番は必要だと思います。
連番なくてもよい場合のパターンは思いつかないのでほかの方の回答を
お待ちください。

遅くてもよければ

Public Function QTY(連番 As Long, キー As String, 数量 As Integer)

  Dim R_REC As Recordset

  QTY = 数量

  Set R_REC = CurrentDb.OpenRecordset("SELECT 数 FROM [テーブル名] WHERE 連番<" & 連番 & " And 品番 In (" & Replace(キー, "|", ",") & ") ORDER BY 連番")

  Do Until R_REC.EOF
    QTY = QTY * R_REC![数]

    R_REC.MoveNext
  Loop

  R_REC.Close

End Function

こんな感じかな?
(ダイレクトに打ったので構文エラーとかあるかも)

これをクエリから呼べばとりあえず出ると思います。

# ほかにもっといい方法があると思いますけど。。。
# とりあえず思いつかなかった。。。

【11792】Re:VBAをクエリで使う方法
発言  YU-TANG  - 10/9/27(月) 20:07 -

引用なし
パスワード
   こんばんは、YU-TANG です。

▼かな さん:
> 例えば、現在のレコード位置より前のデータを順番に見ていき、

並べ替えの基準が無い限り、必ず破綻するロジックです。
下記を熟読されることをお奨めします。

テーブルデータの並び順 - Fly Me To The Access-Heaven
www.naboki.net/access/achell/achell-03.html

[ACC2003] MDB のレコードの並び順について
support.microsoft.com/kb/834927/ja

▼かな さん:
> 連番フィールドはとらないとやっぱりまずいでしょうか?

主キーが無いのは致命傷ですが、それはそれとして、グループキーが
あった方が RDB の処理としては何かと便利です。というより、無いと
RDB を使っている意味がほとんどありません。

それでは。

【11793】Re:VBAをクエリで使う方法
発言  247b  - 10/9/27(月) 21:16 -

引用なし
パスワード
   ▼かな さん:

横入りの上に回答でなくて大変恐縮ですが、一点気になったので。
データの構成を勝手に解釈した限りでは、以下のようにまとめられるように思われます。
ただし、この場合もユニークキー(上位キー)は必要になります

○元のデータ
1 2 3 4 5 品番 キー    数 ←フィールド名
-----------------------------------------
1       A   A     2
  2      B   A|B    2
   3    C   A|B|C   3
1       D   D     1
  2      E   D|E    2
   3    F   D|E|F   2
1       A   A     3
  2      B   A|B    3
   3    W   A|B|W   3

○修正後のトランザクションデータ(例)
Gキー 枝番 品番 キー    数 ←フィールド名
-----------------------------------------
100   1   A   A     2
101   2   B   A|B    2
102   3   C   A|B|C   3
103   1   D   D     1
104   2   E   D|E    2
105   3   F   D|E|F   2
106   1   A   A     3
107   2   B   A|B    3
108   3   W   A|B|W   3

○Gキーのマスタテーブル
Gキー 名称等
-----------------------------------------
100   何か名称等区別のつく文言
101   〜
102   
103   
104   
105   
106   
107   
108   

トランザクションテーブルとマスターテーブルを結合し(しなくとも良いかもしれませんが)、枝番で条件抽出すれば欲しいデータが取れると思います。

何か参考になれば良いのですが。。。
尚、Gキーは例なので、本当に必要な数字の桁は業務にあわせて大きくする必要があると思います。

【11794】Re:VBAをクエリで使う方法
発言  247b  - 10/9/28(火) 14:09 -

引用なし
パスワード
   すみません、間違ってたのに気づきました。正確には以下です。

○修正後のトランザクションデータ(例)
Gキー 枝番 品番 キー    数 ←フィールド名
-----------------------------------------
100   1   A   A     2
100   2   B   A|B    2
100   3   C   A|B|C   3
101   1   D   D     1
101   2   E   D|E    2
101   3   F   D|E|F   2
102   1   A   A     3
102   2   B   A|B    3
102   3   W   A|B|W   3

○Gキーのマスタテーブル
Gキー 名称等
-----------------------------------------
100   何か名称等区別のつく文言
101   〜
102

【11800】Re:VBAをクエリで使う方法
お礼  かな  - 10/9/30(木) 12:39 -

引用なし
パスワード
   ▼雅 さん:

ありがとうございます
連番をつける必要があるんですね
もう一度、テーブルを見直してみて、
やってみます

また、下記のコードの件でお聞きするかもしれませんが、
そのときはよろしくおねがいします

>雅です。
>
>連番は必要だと思います。
>連番なくてもよい場合のパターンは思いつかないのでほかの方の回答を
>お待ちください。
>
>遅くてもよければ
>
>Public Function QTY(連番 As Long, キー As String, 数量 As Integer)
>
>  Dim R_REC As Recordset
>
>  QTY = 数量
>
>  Set R_REC = CurrentDb.OpenRecordset("SELECT 数 FROM [テーブル名] WHERE 連番<" & 連番 & " And 品番 In (" & Replace(キー, "|", ",") & ") ORDER BY 連番")
>
>  Do Until R_REC.EOF
>    QTY = QTY * R_REC![数]
>
>    R_REC.MoveNext
>  Loop
>
>  R_REC.Close
>
>End Function
>
>こんな感じかな?
>(ダイレクトに打ったので構文エラーとかあるかも)
>
>これをクエリから呼べばとりあえず出ると思います。
>
># ほかにもっといい方法があると思いますけど。。。
># とりあえず思いつかなかった。。。

【11801】Re:VBAをクエリで使う方法
お礼  かな  - 10/9/30(木) 12:41 -

引用なし
パスワード
   ▼YU-TANG さん:

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

どうも、アクセスはいまいち理解していないようです
YU-TANGさんが教えてくれたサイトを観ました
なかなか、読解するには時間がかかりそうですが、
もうすこし勉強してみます


>こんばんは、YU-TANG です。
>
>▼かな さん:
>> 例えば、現在のレコード位置より前のデータを順番に見ていき、
>
>並べ替えの基準が無い限り、必ず破綻するロジックです。
>下記を熟読されることをお奨めします。
>
>テーブルデータの並び順 - Fly Me To The Access-Heaven
>www.naboki.net/access/achell/achell-03.html
>
>[ACC2003] MDB のレコードの並び順について
>support.microsoft.com/kb/834927/ja
>
>▼かな さん:
>> 連番フィールドはとらないとやっぱりまずいでしょうか?
>
>主キーが無いのは致命傷ですが、それはそれとして、グループキーが
>あった方が RDB の処理としては何かと便利です。というより、無いと
>RDB を使っている意味がほとんどありません。
>
>それでは。

【11802】Re:VBAをクエリで使う方法
お礼  かな  - 10/9/30(木) 12:48 -

引用なし
パスワード
   ▼247b さん:

ありがとうございます
もう一度、テーブル等を整理してみます
Grキーを設定できるようにやってみます
また、なんか、分からないことがありましたら、
アドバイスをいただけるとありがたいです


>すみません、間違ってたのに気づきました。正確には以下です。
>
>○修正後のトランザクションデータ(例)
>Gキー 枝番 品番 キー    数 ←フィールド名
>-----------------------------------------
>100   1   A   A     2
>100   2   B   A|B    2
>100   3   C   A|B|C   3
>101   1   D   D     1
>101   2   E   D|E    2
>101   3   F   D|E|F   2
>102   1   A   A     3
>102   2   B   A|B    3
>102   3   W   A|B|W   3
>
>○Gキーのマスタテーブル
>Gキー 名称等
>-----------------------------------------
>100   何か名称等区別のつく文言
>101   〜
>102

【11805】Re:VBAをクエリで使う方法
回答  mayu  - 10/10/3(日) 1:26 -

引用なし
パスワード
   こんばんは。

亀レスですけど
SQLで総計計算をおこなう方法をご紹介しますね。

ただ、データが10万件ということなので
複雑なクエリになると遅くて実用に耐えない可能性が高いため、
簡単なUPDATE文の発行で済むように 設計で工夫します。

まずは更新対象のテーブルを

CREATE TABLE tbl (
   grp_num INT
  , sub_num INT
  , 品番  VARCHAR( 10 )
  , 数   INT
  , concat VARCHAR( 255 )
  , total  INT
  , CONSTRAINT pk_tbl PRIMARY KEY ( grp_num, sub_num )
) ;

↑のようなフィールド構成にします。


■データ例

grp_num sub_num 品番 数 concat total
-------------------------------------------
   1   1   A  2  Null  Null
   1   2   B  2  Null  Null
   1   3   C  3  Null  Null
   2   1   D  1  Null  Null
   2   2   E  2  Null  Null
   2   3   F  2  Null  Null
   3   1   A  3  Null  Null
   3   2   B  3  Null  Null
   3   3   W  3  Null  Null

ポイントとしては、3つ。

★1つ目
 同じ品番が繰り返し出てくるようなので
 グループキーを作ること。
 ( 上記例では grp_num が該当 )

★2つ目
 同グループの中で抜けの無い連番を振ること。
 いわゆる枝番ですね。
 ( 上記例では sub_num が該当 )

★3つ目
 品番を | で結合した キー項目
 及び
 数を掛け合わせる 総計
 のフィールドは初期値を Null にしておきます。
 ( 上記例では concat と total が該当 )

# YU-TANGさん、雅さん、247bさんと
# 発言がカブッてる点はご容赦下さいね。(^_^A;;


■SQL
-------------------------------------------------------------------------------

UPDATE tbl T1
LEFT JOIN tbl T2
    ON T1.grp_num = T2.grp_num And
      T1.sub_num = T2.sub_num + 1
SET T1.total = IIf( T2.grp_num Is Null, T1.数, T2.total * T1.数 )
 , T1.concat = IIf( T2.grp_num Is Null, T1.品番, T2.concat & '|' & T1.品番 ) ;

-------------------------------------------------------------------------------


■結果

grp_num sub_num 品番 数 concat total
-------------------------------------------
   1   1   A  2  A    2
   1   2   B  2  A|B   4
   1   3   C  3  A|B|C  12
   2   1   D  1  D    1
   2   2   E  2  D|E   2
   2   3   F  2  D|E|F  4
   3   1   A  3  A    3
   3   2   B  3  A|B   9
   3   3   W  3  A|B|W  27

以下、おまけ。

外部結合 は 内部結合 に比べて
パフォーマンスがめちゃんこ悪いので
先のSQLで 更新速度 が遅すぎる場合は
UPDATE文を2回に分けて発行します。

▼1回目
------------------------------

UPDATE tbl
SET concat = 品番, total = 数
WHERE sub_num = 1 ;

------------------------------
▼2回目
--------------------------------------------

UPDATE tbl T1
INNER JOIN tbl T2
    ON T1.grp_num = T2.grp_num And
      T1.sub_num = T2.sub_num + 1
SET T1.total = T2.total * T1.数
 , T1.concat = T2.concat & '|' & T1.品番 ;

--------------------------------------------

  では ☆

【11818】Re:VBAをクエリで使う方法
お礼  かな  - 10/10/19(火) 14:07 -

引用なし
パスワード
   ▼mayu さん:

解答ありがとうございました
まだまだアクセスが勉強不足の為、
解読中です

また、どうしてもわからないところがあったら質問してもいいでしょうか?

これを参考に、勉強してみます
ありがとうございました


>こんばんは。
>
>亀レスですけど
>SQLで総計計算をおこなう方法をご紹介しますね。
>
>ただ、データが10万件ということなので
>複雑なクエリになると遅くて実用に耐えない可能性が高いため、
>簡単なUPDATE文の発行で済むように 設計で工夫します。
>
>まずは更新対象のテーブルを
>
>CREATE TABLE tbl (
>   grp_num INT
>  , sub_num INT
>  , 品番  VARCHAR( 10 )
>  , 数   INT
>  , concat VARCHAR( 255 )
>  , total  INT
>  , CONSTRAINT pk_tbl PRIMARY KEY ( grp_num, sub_num )
>) ;
>
>↑のようなフィールド構成にします。
>
>
>■データ例
>
> grp_num sub_num 品番 数 concat total
>-------------------------------------------
>   1   1   A  2  Null  Null
>   1   2   B  2  Null  Null
>   1   3   C  3  Null  Null
>   2   1   D  1  Null  Null
>   2   2   E  2  Null  Null
>   2   3   F  2  Null  Null
>   3   1   A  3  Null  Null
>   3   2   B  3  Null  Null
>   3   3   W  3  Null  Null
>
>ポイントとしては、3つ。
>
>★1つ目
> 同じ品番が繰り返し出てくるようなので
> グループキーを作ること。
> ( 上記例では grp_num が該当 )
>
>★2つ目
> 同グループの中で抜けの無い連番を振ること。
> いわゆる枝番ですね。
> ( 上記例では sub_num が該当 )
>
>★3つ目
> 品番を | で結合した キー項目
> 及び
> 数を掛け合わせる 総計
> のフィールドは初期値を Null にしておきます。
> ( 上記例では concat と total が該当 )
>
># YU-TANGさん、雅さん、247bさんと
># 発言がカブッてる点はご容赦下さいね。(^_^A;;
>
>
>■SQL
>-------------------------------------------------------------------------------
>
>UPDATE tbl T1
>LEFT JOIN tbl T2
>    ON T1.grp_num = T2.grp_num And
>      T1.sub_num = T2.sub_num + 1
>SET T1.total = IIf( T2.grp_num Is Null, T1.数, T2.total * T1.数 )
> , T1.concat = IIf( T2.grp_num Is Null, T1.品番, T2.concat & '|' & T1.品番 ) ;
>
>-------------------------------------------------------------------------------
>
>
>■結果
>
> grp_num sub_num 品番 数 concat total
>-------------------------------------------
>   1   1   A  2  A    2
>   1   2   B  2  A|B   4
>   1   3   C  3  A|B|C  12
>   2   1   D  1  D    1
>   2   2   E  2  D|E   2
>   2   3   F  2  D|E|F  4
>   3   1   A  3  A    3
>   3   2   B  3  A|B   9
>   3   3   W  3  A|B|W  27
>
>以下、おまけ。
>
>外部結合 は 内部結合 に比べて
>パフォーマンスがめちゃんこ悪いので
>先のSQLで 更新速度 が遅すぎる場合は
>UPDATE文を2回に分けて発行します。
>
>▼1回目
>------------------------------
>
>UPDATE tbl
>SET concat = 品番, total = 数
>WHERE sub_num = 1 ;
>
>------------------------------
>▼2回目
>--------------------------------------------
>
>UPDATE tbl T1
>INNER JOIN tbl T2
>    ON T1.grp_num = T2.grp_num And
>      T1.sub_num = T2.sub_num + 1
>SET T1.total = T2.total * T1.数
> , T1.concat = T2.concat & '|' & T1.品番 ;
>
>--------------------------------------------
>
>  では ☆

417 / 2272 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
1078193
(SS)C-BOARD v3.8 is Free