定義域関数の抽出と連結表示について

質問

Win XP Access2002を使用しております。
クエリ上でrinkというフィールドに同一種別groupで20個のcodeを抽出して半角スペースを入れ連結したいのですがどのような関数を使えばよろしいのでしょうか。
同一種別の中で抽出し常時最大20個(10個の場合有り)のデータが必要です。
codeは、欠番を含め30000件くらいあります。
何となく　Mid 　DMax　かなと思っているのですが、
レコードの連結は出来てもフィールドの検索方法が解かりません。
VBAもしくはSQLは基本のみでユーザー定義関数がやっと出来る程度なので
出来ることであれば簡単な関数を望んでいるのですが・・・・・・・

テーブル名　T_code
フィルード名1　ID　　(データ型・・・オートナンバー)
フィルード名2 code　(データ型・・・数値)
フィルード名3 種別group　(データ型・・・数値)

選択クエリ名 C_code
フィールド名1 code : [T_code]![code]
フィールド名2　rink : ??????

表示フィールド最終表示例
種別01で20個のデータ抽出後の連結　(" "&" "&・・・・・・・・・)
code code code code code code code code code code code code code code code

例1
種別　01　が連番の場合
00001 00002 00003 00004 00005 00006 00007 00008 00009 00010 00011 00012 00013 00014 00015 00016 00017 00018 00019 00020

欠番発生時詰めて20個の抽出
00001 00002 00003 00005 00006 00007 00009 00011 00012 00013 00014 00015 00016 00017 00018 00019 00020 00021 00022 00023

例2
種別　012　が連番の場合
00101 00102 00103 00104 00105 00106 00107 00108 00109 00110 00111 00112 00113 00114 00115 00116 00117 00118 00119 00120

欠番発生時詰めて20個の抽出
00102 00103 00104 00105 00106 00107 00108 00110 00111 00112 00113 00115 00118 00119 00121 00122 00123 00125 00128 00130

ご教授願えれば光栄です。よろしくお願いいたします。

回答No.5

30246kiku · ベストアンサー

＃４です

近いと思われるものを２パターンで、
新規mdbでサンプル取り込み後、確認してみてください。
（サンプル部分をメモ帳とかに貼り付け保存後、インポート）

> 番号をずらすことが出来ればと
これは理解できてません。

１）グループ１とグループ２でグループ化
２）指定 code を基準とした生成

共通）

元となるテーブルのサンプルを以下とします。
テーブル名：T6
フィールド名：code, sold, syuG1, syuG2
sold のみテキスト型、その他は長整数

テーブルサンプル（インポートしやすいように：見にくいです）

"code","sold","syuG1","syuG2"
1,,1,1
2,,1,1
3,"完売",1,1
4,,1,2
5,,2,2
6,,2,2
7,"完売",2,2
8,,2,3
9,,3,3
10,"完売",3,3
11,,3,3
12,"完売",3,4
13,"完売",4,4
14,,4,4
15,,4,4
16,"完売",4,5
17,,5,5
18,,5,5
19,,5,5
20,,5,6

グループ１テーブル
テーブル名：T6_G1

"syuG1","内容"
1,"AAAA"
2,"BBBB"
3,"CCCC"
4,"DDDD"
5,"EEEE"

グループ２テーブル
テーブル名：T6_G2

"syuG2","内容"
1,"aaaa"
2,"bbbb"
3,"cccc"
4,"dddd"
5,"eeee"
6,"ffff"

１）グループ１とグループ２でグループ化

標準モジュールにユーザ定義関数を記述（以下例）

Public Function GetCode20Pat1(vG1 As Variant, vG2 As Variant) As String
　　Dim rs As New ADODB.Recordset
　　Dim sSql As String
　　Dim sRet As String
　　Dim i As Integer

　　On Error Resume Next
　　sRet = ""

　　sSql = "SELECT code FROM T6 WHERE (Nz(sold,'') <> '完売')"
　　If (IsNull(vG1)) Then
　　　　sSql = sSql & " AND (syuG1 Is Null) "
　　Else
　　　　sSql = sSql & " AND (syuG1 = " & vG1 & ")"
　　End If
　　If (IsNull(vG2)) Then
　　　　sSql = sSql & " AND (syuG2 Is Null) "
　　Else
　　　　sSql = sSql & " AND (syuG2 = " & vG2 & ")"
　　End If
　　sSql = sSql & " ORDER BY code;"

　　i = 0
　　rs.Source = sSql
　　rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
　　While ((Not rs.EOF) And (i < 20))
　　　　sRet = sRet & " " & rs("code")
　　　　rs.MoveNext
　　　　i = i + 1
　　Wend
　　rs.Close
　　If (Len(sRet) > 0) Then sRet = Mid(sRet, 2)
　　GetCode20Pat1 = sRet
End Function

クエリのSQLビューに以下記述（貼り付け＆保存：どちらでも）

SELECT Min(T6.code) AS code
, T6.syuG1, First(T6_G1.内容) AS 内容1
, T6.syuG2, First(T6_G2.内容) AS 内容2
, GetCode20Pat1(T6.[syuG1],T6.[syuG2]) AS rink
FROM (T6 LEFT JOIN T6_G1 ON T6.syuG1 = T6_G1.[syuG1])
LEFT JOIN T6_G2 ON T6.syuG2 = T6_G2.[syuG2]
WHERE (Nz([T6].[sold],"")<>"完売")
GROUP BY T6.syuG1, T6.syuG2
ORDER BY Min(T6.code);

とか

SELECT Q1.code
, Q1.syuG1, T6_G1.内容 AS 内容1
, Q1.syuG2, T6_G2.内容 AS 内容2
, Q1.rink FROM
((SELECT Min(T6.code) AS code
, T6.syuG1, T6.syuG2, GetCode20Pat1(T6.[syuG1],T6.[syuG2]) AS rink
FROM T6 WHERE (Nz([T6].[sold],"")<>"完売")
GROUP BY T6.syuG1, T6.syuG2) AS Q1
LEFT JOIN T6_G1 ON Q1.syuG1 = T6_G1.syuG1)
LEFT JOIN T6_G2 ON Q1.syuG2 = T6_G2.syuG2
ORDER BY Q1.code;

このクエリを実行すると以下の表示に

code...syuG1.....内容1....syuG2.....内容2.....rink
1..........1..........AAAA..........1..........aaaa..........1 2
4..........1..........AAAA..........2..........bbbb..........4
5..........2..........BBBB..........2..........bbbb..........5 6
8..........2..........BBBB..........3..........cccc..........8
9..........3..........CCCC..........3..........cccc..........9 11
14.........4..........DDDD..........4..........dddd..........14 15
17.........5..........EEEE..........5..........eeee..........17 18 19
20.........5..........EEEE..........6..........ffff..........20

左の code は、グループ１とグループ２でグループ化した際の最小 code になります。
（完売は除く）

このグループ１とグループ２でグループ化すると、以下の様な表示にはなりません
> 1 　01 クリスマス　　01 リース 　1 2 3 4 5 6 7 8 ～
> 15 　01 クリスマス　 01 リース 　　　　　　　15(最初の番号) ～
> 10 　01 クリスマス 　 02 ファイバーツリー　 10 11 15 18 ～
> 30 　01 クリスマス 　 02 ファイバーツリー 30(最初の番号)～

↓上記での1,2行目が1つに、3,4行目が１つになります

> 1 　01 クリスマス　　01 リース 　1 2 3 4 5 6 7 8 ～
> 10 　01 クリスマス 　 02 ファイバーツリー　 10 11 15 18 ～

２）指定 code を基準とした生成

左の code に表示するものを別テーブルで指定する例

rink は、指定された code のグループ１とグループ２が同じものを完売除きで、指定された code 以上で生成

code を指定するテーブルサンプル
テーブル名：T6_1

"code"
1
5
10
15
20

標準モジュールに以下ユーザ定義関数を記述

Public Function GetCode20Pat2(iCode As Long) As String
　　Dim rs As New ADODB.Recordset
　　Dim sSql As String
　　Dim sRet As String
　　Dim i As Integer

　　On Error Resume Next
　　sRet = ""

　　sSql = "SELECT Q1.code FROM T6 AS Q1 INNER JOIN "
　　sSql = sSql & "(SELECT * FROM T6 WHERE code = " & iCode & ") AS Q2 "
　　sSql = sSql & "ON (Q1.syuG1 = Q2.syuG1 AND Q1.syuG2 = Q2.syuG2 AND Q1.code >= Q2.code)"
　　sSql = sSql & " WHERE (Nz(Q1.sold,'') <> '完売')"
　　sSql = sSql & " ORDER BY Q1.code;"

　　i = 0
　　rs.Source = sSql
　　rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
　　While ((Not rs.EOF) And (i < 20))
　　　　sRet = sRet & " " & rs("code")
　　　　rs.MoveNext
　　　　i = i + 1
　　Wend
　　rs.Close
　　If (Len(sRet) > 0) Then sRet = Mid(sRet, 2)
　　GetCode20Pat2 = sRet
End Function

クエリのSQLビューに以下記述（貼り付け＆保存）

SELECT T6_1.code
, T6.syuG1, T6_G1.内容 AS 内容1
, T6.syuG2, T6_G2.内容 AS 内容2, GetCode20Pat2(T6_1.code) AS rink
FROM
((T6_1 INNER JOIN T6 ON T6_1.code = T6.code)
LEFT JOIN T6_G1 ON T6.syuG1 = T6_G1.[syuG1])
LEFT JOIN T6_G2 ON T6.syuG2 = T6_G2.[syuG2]
ORDER BY T6_1.code;

このクエリを実行すると以下の表示に

code...syuG1....内容1....syuG2.....内容2....rink
1..........1..........AAAA..........1..........aaaa..........1 2
5..........2..........BBBB..........2..........bbbb..........5 6
10.........3..........CCCC..........3..........cccc..........11
15.........4..........DDDD..........4..........dddd..........15
20.........5..........EEEE..........6..........ffff..........20

code = 10 のところは、code = 10 自体が完売なので、rink は 11 から
また、rink 生成は指定された code 以上と条件付けているので code = 9 は除外

※　各クエリでSQLビュー記述後は、デザインビューで設定を確認してください。
　（デザインを見ればどのような指定になっているかわかりやすいと思います）

※　rink 作成時の20件抽出は、ループのところでカウントしています。
　（サンプルは20件以下なので、TOP 20 では得られませんでした）

※※　＃１の回答をベースにするなら、１）でしょうか

※※　サンプルテーブル内のデータを書き換えて、いろいろ確認してみてください
※※　これで完とは思っていないので

お礼

お礼のお言葉大変遅くなりました。
ご回答頂いた件につきましてそのとおりインポートして出来ました。
実際の私のテープル構成に問題が有り只今悪戦苦闘しております。
お時間が多少必要なのでお礼のみ先にさせて頂くご無礼をお許しください。

いつも迅速な初心者相手にとて親切な細かいご回答本当に感謝しております。
今後ともよろしくお願いいたします。

回答No.4

30246kiku

＃３です

> テーブル名　[T_code]
> フィルード名1　ID　　(データ型・・・オートナンバー)
> フィルード名2 code　(データ型・・・数値　長整数型)
> フィルード名3 sold　(データ型・・・テキスト型)
> フィルード名4 種別group_1　(データ型・・・数値　長整数型)
> フィルード名5 種別group_2　(データ型・・・数値　長整数型)

いきなり１テーブルにされた形で説明いただいても、・・・
これから１テーブルのもので考えられますか。

> クエリ名　[C_code]
> フィルード名1 [T_ccode]![code]
> フィルード名2 rink: GetCode20([T_code]![種別group])

> code　　　group_1 　　　　group_2 　　　　　　　rink
> 1 　01 クリスマス　　01 リース 　1 2 3 4 5 6 7 8 ～

ここで、T_ccode　は新しいテーブルでしょうか。

この例を解釈しようとすると、
group_1 , group_2　を表示するクエリ記述部分がありませんが、
code = 1 の group_1 , group_2 と同じ code （完売でない）を 1 スタートで rinkとして２０件？
code = 10 なら 10 スタートで？
で、code = 1 はどこから持ってくる？
soldは "完売" っていう文字列？

近いですか

お礼

何度もご多忙中にも関わらずご回答いただき誠にありがとうございます。
昨日、VBAの本を読んで勉強して下りましたらADOの説明がありSQLで抽出をする内容がありました。定義域関数など関係が無いのですね、混乱させてしまい大変申し訳ございませんでした。

補足

早速のご回答ありがとうございます。
むちゃくちゃ近いです。

>>いきなり１テーブルにされた形で説明いただいても、・・・
これから１テーブルのもので考えられますか。

大変申し訳ございません。各テーブルの関連の説明が私には難しいので
新しくテーブルを作成し1つにまとめさせていただきました。
同一テーブルに5つのフィールドが有るとして考えていただきたいのですが
よろしいでしょうか。

>>ここで、T_ccode　は新しいテーブルでしょうか。

大変申し訳ございません。フィールド名を入れ忘れました。
フィルード名1 code :[T_ccode]![code]

>>code = 1 の group_1 , group_2 と同じ code （完売でない）を 1 スタートで rinkとして２０件？
code = 10 なら 10 スタートで？

言葉が足りずご迷惑をおかけしております。
そのとおりでございます。

　1 　01 クリスマス　　01 リース 　1 2 3 4 5 6 7 8 ～
15 　01 クリスマス　 01 リース 　　15 16 17 18 19 20 ～

>code = 1 はどこから持ってくる？

#1でのご回答のままで構いません。最初は、group_1のみの抽出条件のため
私のお伝えの仕方が悪く同一データが表示されてしまいました。そこでgroup_2を追加して
さらは番号をずらすことが出来ればと思って下ります。

>soldは "完売" っていう文字列？

そのとおりでございます。

今後ともよろしくお願いいたします。

回答No.3

30246kiku

＃２です

> 2.sold　欠番を飛ばしてつめて抽出(表示codeから外したいです)
> 3.種別group_2　最初のcode番号を変えたいです(300件くらいの同一データの分割)
> 4.rinkフィールドでcodeを1つずつずらして表示したいです)

各テーブルの関連付けがわかりません。
欠番がどのように構成されているのか、
種別group_2　最初のcode番号　の最初とは、
codeを1つずつずらして　何を基準にどうずらすとか、などなど

イメージできてません。

前回までは２０件でしたが３～５件くらいにして、
クエリを完結させる各テーブルのサンプルを提示ください。

クエリでの結果は最低２パターンは欲しいです。

お礼

ご回答ありがとうございます。
今回も説明不足で大変お手数をおかけ致します。
趣旨の説明が出来ておらずご迷惑をおかけしております。
よろしくお願いいたします。

補足

#2についての最度説明をさせて頂きます。

> 2.sold　欠番を飛ばしてつめて抽出(表示codeから外したいです)
> 3.種別group_2　最初のcode番号を変えたいです(300件くらいの同一データの分割)
> 4.rinkフィールドでcodeを1つずつずらして表示したいです)

商品の在庫管理と同時にショッピングカートに使用して下ります。
商品が完売(sold)になった場合は、詰め次の商品(code)を抽出したい。

同一種別商品(group_1)が多いので違う種別商品(group_2)でと細分し抽出したい。
お勧め商品として(rinkに最大20個抽出)を表示したい。

テーブル名　[T_code]
フィルード名1　ID　　(データ型・・・オートナンバー)
フィルード名2 code　(データ型・・・数値　長整数型)
フィルード名3 sold　(データ型・・・テキスト型)
フィルード名4 種別group_1　(データ型・・・数値　長整数型)
フィルード名5 種別group_2　(データ型・・・数値　長整数型)

クエリ名　[C_code]
フィルード名1 [T_ccode]![code]
フィルード名2 rink: GetCode20([T_code]![種別group])

code　　　group_1 　　　　group_2 　　　　　　　rink
1 　01 クリスマス　　01 リース 　1 2 3 4 5 6 7 8 ～
15 　01 クリスマス　 01 リース 　　　　　　　15(最初の番号) ～
10 　01 クリスマス 　 02 ファイバーツリー　 10 11 15 18 ～
30 　01 クリスマス 　 02 ファイバーツリー 30(最初の番号)～
50　 01 クリスマス　 03 ブランチツリー　　 50 51 55 57 ～

(code 52 53 54 56 は完売)

101　 03 かばん　　　　01 セカンドバック　 101 102 104 105 ～
115　 03 かばん　　　 01 セカンドバック　 115(最初の番号)～
151　 03 かばん　　　　02 パイロットケース 151 155 157 159 ～
200　 03 かばん　　　　02 パイロットケース 200(最初の番号)～
211　 03 かばん　　　　03 ブリーフケース 211 215 240 250 ～

(code 103 152 153 154 156 158は完売)

>種別group_2　最初のcode番号　の最初とは、
表示レコードのcodeです。
15 　01 クリスマス　 01 リース 　　　　　　　15(最初の番号) ～
30 　01 クリスマス 　 02 ファイバーツリー 30(最初の番号)～
115　 03 かばん　　　 01 セカンドバック　 115(最初の番号)～
200　 03 かばん　　　　02 パイロットケース 200(最初の番号)～

>何を基準にどうずらすとか
各レコードのcodeを基準に+1(欠番含めずに)ずつずらしたい。

このようなご説明でイメージできますでしょうか。

大変お手数をおかけしております。よろしくお願いいたします。

回答No.2

30246kiku

＃１です

> 1.種別groupが空白の場合、#エラーが表示されます。

＃１　で提示した
Public Function GetCode20(iNum As Long) As String
は、NULL を受け取れません。なので、クエリから呼び出す際にエラーになったと思います。

NULL を受け取れるようにするのなら Variant である必要があります。
Public Function GetCode20(iNum As Variant) As String

NULL の扱いがあることから SQL 部分をそれなりに処理追加します。

If (IsNull(iNum)) Then
　　rs.Source = "SELECT TOP 20 code FROM T_code WHERE 種別group Is Null ORDER BY code;"
Else
　　rs.Source = "SELECT TOP 20 code FROM T_code WHERE 種別group = " & iNum & " ORDER BY code;"
End If

> 3.TOP 20を削除するとすべて表示されます。ループ部分で２０件カウントという表示方法

変数の宣言部分で Dim i As Integer を追記しているとします。
例えば、ループのところで以下のように記述します。

　　i = 0
　　While ((Not rs.EOF) And (i<20))
　　　　sRet = sRet & " " & rs("code")
　　　　rs.MoveNext
　　　　i = i + 1
　　Wend

とか

　　i = 0
　　Do While (Not rs.EOF)
　　　　sRet = sRet & " " & rs("code")
　　　　rs.MoveNext
　　　　i = i + 1
　　　　If( i >= 20 ) Then Exit Do
　　Loop

> 2.種別groupの数が100件以上有る場合、同一データが抽出されてしまうのですが、
> 種別group_2を追加して複合検索、またさらに検索条件を複数追加する時はどのようにすればよろしいでしょうか

すみません。テーブル構造等イメージできてません。
そもそも、＃１　の回答であっていたのか、わからなくなっています。

素の構造と素のサンプルデータを提示いただければ、回答できるかもしれません。

お礼

迅速なご回答本当にありがとうございます。

＃１　＃３　両方ともクリアできました。
徐々に勉強しながら進めて下ります。

問題の＃２ですが、ご教授していただきましたことで実際に入力していくと
どんどんエスカーレートしてしまい本題がご指摘どおりずれていってしまいました。
大変申し訳ございません。

補足

テーブル構造をお伝えさせていただきます。おかげさまで理想にかなり近づきました。
今度は、複合検索後のcode抽出をし始まりの番号を１つずつずらしていきたいのですが・・・
DMaxなどで動作をさせるのでしょうか。私の言葉の説明不足でここで初めて定義域関数ということにつながると思うのですが・・・

抽出項目
1.種別group_1　同一グループで最大20個のcodeを抽出
ここまでOKです。

2.sold　欠番を飛ばしてつめて抽出(表示codeから外したいです)

3.種別group_2　最初のcode番号を変えたいです(300件くらいの同一データの分割)

4.rinkフィールドでcodeを1つずつずらして表示したいです)

テーブル名1　[T_cart_category]
フィルード名1 code　(データ型・・・数値　長整数型)
フィルード名2 sold　(データ型・・・テキスト型)

テーブル名2　[T_code]
フィルード名1　ID　　(データ型・・・オートナンバー)
フィルード名2 code　(データ型・・・数値　長整数型)
フィルード名3 種別group_1　(データ型・・・数値　長整数型)

テーブル名3　[T_cart_item]
フィルード名1 code　(データ型・・・数値　長整数型)
フィルード名2 種別group_2　(データ型・・・数値　長整数型)

クエリ名　[C_code]
フィルード名1 [T_category]![code]
フィルード名2 rink: GetCode20([T_code]![種別group])

大変お手数をおかけいたします、再度ご教授よろしくお願いいたします。

回答No.1

30246kiku

以下、未検証ですが動けば、

ユーザ定義関数を作成し、クエリでグループ化した際の 種別group の code 順で 20件とり、１つの文字列として返すものにします。
（種別group が長整数だったとした場合）

ユーザ定義関数例：標準モジュールに記述）

Public Function GetCode20(iNum As Long) As String
　　Dim rs As New ADODB.Recordset
　　Dim sRet As String

　　On Error Resume Next
　　sRet = ""
　　rs.Source = "SELECT TOP 20 code FROM T_code WHERE 種別group = " & iNum & " ORDER BY code;"
　　rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
　　While (Not rs.EOF)
　　　　sRet = sRet & " " & rs("code")
'　　　　sRet = sRet & " " & Format(rs("code"),"00000") ' 必要なら数値の５桁化
　　　　rs.MoveNext
　　Wend
　　rs.Close
　　If (Len(sRet) > 0) Then sRet = Mid(sRet, 2)
　　GetCode20 = sRet
End Function

※駄目だったら、TOP 20 を削除し、ループ部分で２０件カウントすれば良いと思います

クエリ記述例）

SELECT 種別group, GetCode20(種別group) AS code20 FROM T_code GROUP BY 種別group;

※ ADO の例なので、参照設定は忘れずに

※　関数名は適宜変更してください

※　意味合い違ったらごめんなさい

お礼

このたびは、大変お世話になりました。
その後、テープル構造の正規化と今回の処理についてもう少し吟味しなければならないという結論に至りました。
今回は、多大なお時間を費やして頂きまして本当にありがとうございました。

補足

速のご回答ありがとうございました。
とても感謝しております。VBAという言語はすごいですね。
ありがとうございました。
もう少し深くご教授してい頂きたいのでよろしいでしょうか。

1.種別groupが空白の場合、#エラーが表示されます。
多分初歩的な事だと思うのですが
文字列で計算がされているという事はわかります。

テーブル名　T_code
フィルード名1　ID　　(データ型・・・オートナンバー)
フィルード名2 code　(データ型・・・数値　長整数型)
フィルード名3 種別group　(データ型・・・数値　長整数型)

2.種別groupの数が100件以上有る場合、同一データが抽出されてしまうのですが、
種別group_2を追加して複合検索、またさらに検索条件を複数追加する時はどのようにすればよろしいでしょうか。

現状
code　group_1 group_2 rink
00199　 8　 1 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218
00228　 8　 2 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218
00231　 8　 1 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218
00260　 8　 3 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218

今後(重複codeはなし)
code　group_1 group_2 rink
00199　 8　 1 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218
00228　 8　 2 228 229 230 295 296 297 300 311
00231　 8　 1 231 232 233 234 235 236 237 238 239 240 241 370 371 375 385 397 451 452 455 456
00260　 8　 3 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 286 287 298 299

3.TOP 20を削除するとすべて表示されます。ループ部分で２０件カウントという表示方法も教えていただけますでしょうか。

大変お手数をおかけいたしますがよろしくお願いいたします。