AccessVBA値の入替えNullの使い方が不正

質問

以前こちらで教えていただいのですが、
http://okwave.jp/qa/q8086569.html
以下のクエリを実行すると、「Nullの使い方が不正です」とのエラーが出てしまいます。

------------------------------------
テーブル名「T9」
フィールド：顧客番号（テキスト）/ 売上金額（通貨） / 売上月（テキスト
テーブル名「T9A」
フィールド：顧客番号（テキスト）/ 集約先番号（テキスト）

●標準モジュール
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

●クエリ
SELECT Bango(顧客番号) AS 集約番号, Sum(売上金額) AS 売上金額計, 売上月
FROM T9
GROUP BY Bango(顧客番号), 売上月;
------------------------------------

当初は、テーブル「T9A」には集約先番号を持つ顧客番号のみにするはずでしたが、
便宜上、集約先番号の有無にかかわらず、すべての顧客番号が入ったテーブルを使います。

そのため、「T9A」の集約先番号にはNullがあり、
Nullに対しては、同行の顧客番号を返すように対応したいです。

If IsNull(集約先番号) Then・・・とやるのかな？と浮かびましたが、うまくいかず、困っております。

何卒ご指南くださいますよう、お願いいたいます。

回答No.4

nda23 · ベストアンサー

Q1が少しおかしかったですね。
正しくは以下のようになります。
Q1：
SELECT A.顧客番号,←★これが無かった。
Nz(E.集約先番号,Nz(D.集約先番号,
Nz(C.集約先番号,Nz(B.集約先番号,
A.集約先番号)))) AS 実集約先
FROM (((T9A AS A LEFT JOIN T9A AS B
ON A.集約先番号=B.顧客番号) LEFT JOIN
T9A AS C ON B.集約先番号=C.顧客番号)
LEFT JOIN T9A AS D
ON C.集約先番号=D.顧客番号) LEFT JOIN
T9A AS E ON D.集約先番号=E.顧客番号

それでQ2はこちら
SELECT Nz(B.実集約先,A.顧客番号) AS 集約番号,
売上月,売上金額 FROM T9 AS A LEFT JOIN
Q1 AS B ON A.顧客番号=B.顧客番号←★これが正しい。

Q3はそのままです。

前のクエリだと集約先がない顧客だと上手くいきません。

お礼

早速のご回答、ありがとうございました。
試してみたところ、期待したとおりの結果が得られました。

今回はいずれも大変勉強になる回答をいただけたのですが、
最も処理時間の速かった回答者様の投稿をベストアンサーとして選ばせて いただいきました。

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

回答No.3

nda23

最大5レベルのネストであれば、以下のようにします。
クエリ:Q1
SELECT Nz(E.集約先番号,Nz(D.集約先番号,
Nz(C.集約先番号,Nz(B.集約先番号,
A.集約先番号)))) AS 実集約先
FROM (((T9A AS A LEFT JOIN T9A AS B
ON A.集約先番号=B.顧客番号) LEFT JOIN
T9A AS C ON B.集約先番号=C.顧客番号)
LEFT JOIN T9A AS D
ON C.集約先番号=D.顧客番号) LEFT JOIN
T9A AS E ON D.集約先番号=E.顧客番号

目的の一つ前のクエリ:Q2
SELECT Nz(B.実集約先,A.顧客番号) AS 集約番号,
売上月,売上金額 FROM T9 AS A LEFT JOIN
Q1 AS B ON A.顧客番号=B.実集約先

目的のクエリ:Q3
SELECT A.集約番号,Sum(A.売上金額) AS 売上金額計,
A.売上月 FROM Q2 GROUP BY A.集約番号,A.売上月

前にも述べましたが、SQLで実現する方法を考えるべきで、
プログラムでやると、700倍以上効率が悪く、特にODBCの
リンクテーブルを使うと、コーヒー一杯飲んでくる時間が
あくほど性能が劣化します。

上記設計で、拙いのはネストが5レベルもあることで、
別のテーブルを置くなりして、1レベルに収まるよう
工夫すべきです。

お礼

どうもありがとうございます。

テーブルや効率・・・、
ご指摘の通り工夫が足りませんよね、なんとか改善していきたいです。

動作確認がまだですので、また結果を報告したいと思います。

補足

いただいたものをそのままコピペして試してみました。
Sqlだとサクサク動くんですね！
しかし、結果が、
Q1クエリの実集約先が、nullのままになってしまいました。
Q3でも集計は顧客番号の集計結果と同じでした。

顧客番号、集約先が文字列だからしょうか？
何度もお手数をおかけしますが、よかったら教えてください。

回答No.2

nda23

集約先のネストは何段階まででしょう？
仮に集約先のネストが1レベルしかないと
するならば、以下のクエリで集約先を特定
します。
クエリ：Q1
SELECT A.顧客番号
,Nz(B.集約先,A.集約先) AS 実集約先
FROM T9A AS A
LEFT JOIN T9A AS B
ON A.集約先=B.顧客番号

Nz関数は以下の働きがあります。
Nz(A,B) Aが非NULLならA,AがNULLならB

このクエリの集約先で集計します。
クエリ：Q2
SELECT Nz(B.集約番号,A.顧客番号) AS 集約番号
,SUM(A.売上金額) AS 売上金額計,A.売上月
FROM T9 AS A LEFT JOIN Q1 AS B
ON A.顧客番号=B.実集約先
GROUP BY Nz(B.集約番号,A.顧客番号),A.売上月

サブクエリを使うと、Q1は要らないのですが、
SQLに不慣れのようなので、分けて作る方が
安全でしょう。
こういうものはクエリだけで処理すべきで、
それができない時はテーブルの設計が
間違っています。

お礼

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

情報が漏れており、失礼いたしました。
集約先は最大５段階まで入れ子になっています。

不勉強で申し訳ないのですが、
１段階以上の入れ子構造の場合でも
クエリで処理できるものなのでしょうか？

回答No.1

30246kiku

案１）　rs("集約先番号")　が Null でなければ、再度 Bango を呼び出す

> If (Not rs.EOF) Then sR = Bango(rs("集約先番号"))
↓
If (Not rs.EOF) Then
　　If (Not IsNull(rs("集約先番号"))) Then sR = Bango(rs("集約先番号"))
End If

案２）　Recordset を得る時の条件に　集約先番号 Is Not Null　を追加してみる

> rs.Source = "SELECT * FROM T9A WHERE 顧客番号='" & sSrc & "';"
↓
rs.Source = "SELECT * FROM T9A WHERE 集約先番号 Is Not Null AND 顧客番号='" & sSrc & "';"

案３）　全面的に書き方を変えてみる

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

※ 各処理性能はわかりません
※ 未検証ですので不都合あれば修正してください

※※ テーブル「T9A」での循環には注意してください

ブログに記事として載せてましたので、興味あれば探してみてください。
・自分の管理するURLは記述できない規則です
・また、辿りつくまでのキーワード提示もできません

お礼

お礼が遅れまして失礼いたしました。

早速のご回答をいただきありがとうございます。
成長せず、何度もお手数をかけてしまい申し訳ないです。
回答者様のブログもあわせて確認しました。

テーブル「T9A」の集約先番号が、Nullが大多数を占めるので、
いただいた案１、案２を、まず試してみたいと思います。

Accessソフトが自宅PCに無いため、
動作確認については明後日以降報告いたします。

補足

案１～３まで試して、期待した結果が得られることを確認できました！
どうもありがとうございました。

回答者様のブログを参考に、
もっと勉強しようと思います。