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

Question

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

ご教授願えれば光栄です。よろしくお願いいたします。

30246kiku · Accepted Answer

＃４です近いと思われるものを２パターンで、新規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 では得られませんでした） ※※　＃１の回答をベースにするなら、１）でしょうか ※※　サンプルテーブル内のデータを書き換えて、いろいろ確認してみてください ※※　これで完とは思っていないので

30246kiku · Answer

＃３です

> テーブル名　[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は "完売" っていう文字列？

近いですか

30246kiku · Answer

＃２です

> 2.sold　欠番を飛ばしてつめて抽出(表示codeから外したいです)
> 3.種別group_2　最初のcode番号を変えたいです(300件くらいの同一データの分割)
> 4.rinkフィールドでcodeを1つずつずらして表示したいです)

各テーブルの関連付けがわかりません。
欠番がどのように構成されているのか、
種別group_2　最初のcode番号　の最初とは、
codeを1つずつずらして　何を基準にどうずらすとか、などなど

イメージできてません。

前回までは２０件でしたが３～５件くらいにして、
クエリを完結させる各テーブルのサンプルを提示ください。

クエリでの結果は最低２パターンは欲しいです。

30246kiku · Answer

＃１です

> 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を追加して複合検索、またさらに検索条件を複数追加する時はどのようにすればよろしいでしょうか

すみません。テーブル構造等イメージできてません。
そもそも、＃１　の回答であっていたのか、わからなくなっています。

素の構造と素のサンプルデータを提示いただければ、回答できるかもしれません。

30246kiku · Answer

以下、未検証ですが動けば、

ユーザ定義関数を作成し、クエリでグループ化した際の 種別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 の例なので、参照設定は忘れずに

※　関数名は適宜変更してください

※　意味合い違ったらごめんなさい

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

＃４です

＃３です

この回答への補足

＃２です

この回答への補足

＃１です

この回答への補足

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

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング