再帰処理にはまる(その6)
テーブル名「T9」
フィールド:顧客番号(テキスト)/ 売上金額(通貨) / 売上月(テキスト)
テーブル名「T9A」
フィールド:顧客番号(テキスト)/ 集約先番号(テキスト)
上記2つのテーブルから以下の結果を導きたい
フィールド:顧客番号(テキスト)/ 売上金額(通貨) / 売上月(テキスト)
an | 顧客番号 | 売上金額 | 売上月 |
---|---|---|---|
1 | 001 | ¥100 | 1月 |
2 | 002 | ¥50 | 1月 |
3 | 003 | ¥200 | 1月 |
4 | 004 | ¥150 | 1月 |
テーブル名「T9A」
フィールド:顧客番号(テキスト)/ 集約先番号(テキスト)
an | 顧客番号 | 集約先番号 |
---|---|---|
1 | 001 | 002 |
2 | 002 | 003 |
上記2つのテーブルから以下の結果を導きたい
集約番号 | 売上金額計 | 売上月 |
---|---|---|
003 | ¥350 | 1月 |
004 | ¥150 | 1月 |
標準モジュールに以下を記述しておきます。
クエリの SQL ビューで以下を記述します。
Public Function Bango(sSrc As String) As String
Dim rs As New ADODB.Recordset
Dim sR As String
sR = sSrc
rs.Source = "SELECT * FROM T9A WHERE 顧客番号='" & sSrc & "';"
rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
If (Not rs.EOF) Then sR = Bango(rs("集約先番号"))
rs.Close
Bango = sR
End Function
Dim rs As New ADODB.Recordset
Dim sR As String
sR = sSrc
rs.Source = "SELECT * FROM T9A WHERE 顧客番号='" & sSrc & "';"
rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
If (Not rs.EOF) Then sR = Bango(rs("集約先番号"))
rs.Close
Bango = sR
End Function
クエリの SQL ビューで以下を記述します。
SELECT Bango(顧客番号) AS 集約番号, Sum(売上金額) AS 売上金額計, 売上月
FROM T9
GROUP BY Bango(顧客番号), 売上月;
FROM T9
GROUP BY Bango(顧客番号), 売上月;
というのを回答したわけですが・・・・
や~~、回答内容で動いて良かったですね・・・
というのは、もし、テーブル「T9A」が
an | 顧客番号 | 集約先番号 |
---|---|---|
1 | 001 | 002 |
2 | 002 | 003 |
3 | 003 | 001 |
のように、循環していたら・・・・無限ループになっちゃいますね・・・
ある程度動いたら、スタック領域がない・・・とかのエラーになりますが・・・
強制終了して・・・・最悪、mdb/accdb が壊れ・・・・ってな状況になったりするのでしょうか・・・・
循環する様なデータを許可している・・・・これが根本的な問題になるのですが・・・・
何を想定して回答すれば良いんですかね・・・・
基本的には、提示されたサンプルで、そこそこ動けば OK だと思っています。
この話は、これ位にして・・・
前述した VBA 記述と、以下の記述ではどちらが良いのでしょう。
Public Function Bango(sSrc As String) As String
Dim rs As New ADODB.Recordset
Dim sR As String
sR = sSrc
rs.Source = "SELECT 集約先番号 FROM T9A WHERE 顧客番号='" & sSrc & "';"
rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
If (Not rs.EOF) Then sR = Bango(rs(0))
rs.Close
Bango = sR
End Function
Dim rs As New ADODB.Recordset
Dim sR As String
sR = sSrc
rs.Source = "SELECT 集約先番号 FROM T9A WHERE 顧客番号='" & sSrc & "';"
rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
If (Not rs.EOF) Then sR = Bango(rs(0))
rs.Close
Bango = sR
End Function
得られる結果は同じなんですけどね・・・・
前者は、SELECT でフィールド全部を得ておいて、後参照では必要なフィールドを指定
後者は、SELECT で必要なフィールドだけを求めておいて、後参照では何個目・・・・
雰囲気的には、後者の方がリソースを節約できるのかなぁ~~・・・・わかりません。
関数の再帰呼出し・・・ではなくて、ループ処理でも記述できますね。
Public Function Bango(sSrc As String) As String
Dim v As Variant
Dim sR As String
v = sSrc
While (Not IsNull(v))
sR = v
v = DLookup("集約先番号", "T9A", "顧客番号='" & v & "'")
Wend
Bango = sR
End Function
Dim v As Variant
Dim sR As String
v = sSrc
While (Not IsNull(v))
sR = v
v = DLookup("集約先番号", "T9A", "顧客番号='" & v & "'")
Wend
Bango = sR
End Function
Until って書き方もあると思いますが、私は While を使います。
途中抜ける事がなければ While ~ Wend で・・・・
抜ける事があれば、Do While ~ Loop で・・・・
××の間、処理する・・・・・っていうのが考えやすいので・・・(ある意味 For と同じですね)
いろいろなサイトを見ると、D 系の関数は遅いとか・・・云々言われてますね。
実際にどうなのか・・・私にはわかりません。
ただ、汎用のものを専用にすると速いかな・・・・と思いますけど・・・・
循環があった時には、この書き方ではスタック領域が・・・云々のエラーは起きないと思うので、
無限ループに陥ると思います。
循環チェックの例は、過去記事「クエリで再起処理?その2」で記述していたかと・・・・
参考になるかどうか・・・・ダメもとで見てみる・・・・それはありかと
【追記】6/2
テーブル「T9A」のデータの持ち方が変わったようで
an | 顧客番号 | 集約先番号 |
---|---|---|
1 | 001 | 002 |
2 | 002 | 003 |
3 | 003 | |
4 | 004 |
の様に、顧客番号は全て登録・・・で、集約先番号は、Null だったり、従来通りだったり
また、顧客番号 / 集約先番号 でネストするのは最大でも5段とか
最悪、循環があっても動くように再帰・・・ではなく、クエリ(SQL)で記述してみた。
SELECT Switch(
Not IsNull(Q5.顧客番号), Q5.顧客番号,
Not IsNull(Q4.顧客番号), Q4.顧客番号,
Not IsNull(Q3.顧客番号), Q3.顧客番号,
Not IsNull(Q2.顧客番号), Q2.顧客番号,
True, Q1.顧客番号) AS 集約番号, Sum(Q0.売上金額) AS 売上金額計, Q0.売上月
FROM (((
(T9 AS Q0 INNER JOIN T9A AS Q1 ON Q0.顧客番号=Q1.顧客番号)
LEFT JOIN T9A AS Q2 ON Q1.集約先番号=Q2.顧客番号)
LEFT JOIN T9A AS Q3 ON Q2.集約先番号=Q3.顧客番号)
LEFT JOIN T9A AS Q4 ON Q3.集約先番号=Q4.顧客番号)
LEFT JOIN T9A AS Q5 ON Q4.集約先番号=Q5.顧客番号
GROUP BY Switch(
Not IsNull(Q5.顧客番号), Q5.顧客番号,
Not IsNull(Q4.顧客番号), Q4.顧客番号,
Not IsNull(Q3.顧客番号), Q3.顧客番号,
Not IsNull(Q2.顧客番号), Q2.顧客番号,
True, Q1.顧客番号), Q0.売上月;
Not IsNull(Q5.顧客番号), Q5.顧客番号,
Not IsNull(Q4.顧客番号), Q4.顧客番号,
Not IsNull(Q3.顧客番号), Q3.顧客番号,
Not IsNull(Q2.顧客番号), Q2.顧客番号,
True, Q1.顧客番号) AS 集約番号, Sum(Q0.売上金額) AS 売上金額計, Q0.売上月
FROM (((
(T9 AS Q0 INNER JOIN T9A AS Q1 ON Q0.顧客番号=Q1.顧客番号)
LEFT JOIN T9A AS Q2 ON Q1.集約先番号=Q2.顧客番号)
LEFT JOIN T9A AS Q3 ON Q2.集約先番号=Q3.顧客番号)
LEFT JOIN T9A AS Q4 ON Q3.集約先番号=Q4.顧客番号)
LEFT JOIN T9A AS Q5 ON Q4.集約先番号=Q5.顧客番号
GROUP BY Switch(
Not IsNull(Q5.顧客番号), Q5.顧客番号,
Not IsNull(Q4.顧客番号), Q4.顧客番号,
Not IsNull(Q3.顧客番号), Q3.顧客番号,
Not IsNull(Q2.顧客番号), Q2.顧客番号,
True, Q1.顧客番号), Q0.売上月;
テーブル「T9」「T9A」を内部結合しておいてから、
段数分、テーブル「T9A」を外部結合・・・・・
Switch を使って、どの顧客番号を集約番号として求めるか・・・・
これと、売上月でグループ化して・・・・売上金額を Sum すれば・・・
表示結果は同じになりましたが・・・・処理性能はわかりません
実際には、こう考えた方が良いよ・・・・等、教えてください。
- 関連記事
-
- Excel VBA をやってみた その14 (2015/01/01)
- 再帰処理にはまる(その6) (2013/05/21)
- イベント処理記述の変形 (2013/09/19)
- Excel VBA をやってみた その13 (2014/11/28)
- CSV ファイルの取り込み (2014/03/27)
2013/05/21
Category: 解説か
TB: -- /
CM: 0
« Nz の罠
再帰処理にはまる(その5) »
この記事に対するコメント
| h o m e |