アクセス フィールド値を分割したい

質問

いつも大変お世話になっております。
初心者につき、ご教示いただけますと幸いです。

アクセスのテーブル・フィールドに
AAAA,BBBB,CCCC　という値が入ってしまうため
「,」を区切りに分割したいです。

テーブル：T_商品
フィールド：
顧客ID｜注文内容(連結で入ってしまうフィールド）
0001｜AAAA
0002｜AAAA,BBBB
0010｜AAAA,BBBB,CCCC
0101｜AAAA,BBBB,CCCC,DDDD,・・・
といったように、連結した値の数はまちまちで、
最大10個になります。

ちなみに、
これを
↓フィールド名
顧客ID｜注文商品　　　に
0001｜AAAA
0002｜AAAA
0002｜BBBB
0010｜AAAA
0010｜BBBB
0010｜CCCC
　・
　・
　・
といったように、
最終的には　1つのフィールドにまとめたいです。

ご教示のほど、よろしくお願い致します。

尚、前半の「連結値を分割」だけでも
テーブル更新（分割毎にフィールドに挿入）できれば、とても助かります！
→この場合、予備フィールドなどを作成しておくか、
　ワークテーブルなどで処理になりますでしょうか。

是非とも、よろしくお願い致します。

回答No.1

piroin654 · ベストアンサー

新しいテーブルを用意し、名前をいちおうT_Tempとします。
フィールドは、
　　　顧客ID　　　テキスト型
　　　注文商品　　テキスト型
とします。

DAOを使用するので参照設定でDAOにチェックが入っているか確認してください。

Sub test1()
　　Dim db As DAO.Database
　　Dim rs1 As DAO.Recordset
　　Dim rs2 As DAO.Recordset
　　Dim buf As Variant
　　Dim i As Long

　　Set db = CurrentDb
　　Set rs1 = db.OpenRecordset("T_商品")
　　Set rs2 = db.OpenRecordset("T_Temp", dbOpenDynaset)

　　If rs1.RecordCount > 0 Then
　　　　rs1.MoveFirst
　　　　Do Until rs1.EOF
　　　　　　buf = Split(rs1!注文内容, ",")
　　　　　　For i = 0 To UBound(buf)
　　　　　　　　rs2.AddNew
　　　　　　　　　　rs2!顧客ID = rs1!顧客ID
　　　　　　　　　　rs2!注文商品 = buf(i)
　　　　　　　　rs2.Update
　　　　　　Next i
　　　　rs1.MoveNext
　　　　Loop
　　End If
　　rs1.Close: Set rs1 = Nothing
　　rs2.Close: Set rs2 = Nothing
　　db.Close: Set db = Nothing
End Sub

わからないところがあれば補足してください。

お礼

失礼しました。T_Tempは、前もって作成しておくテーブルと記載いただいておりました。すみません。ご親切な明記、ありがとうございます。

補足

T_Tempは、前もって作成しておくテーブルなんでしょうか？
初心者につき、初歩的なことが理解できておりませんでしたら申し訳ありません。上記コードだけでは、その部分でエラーになって止まってしまうので・・・。

回答No.11

piroin654

＞そもそも「注文商品」がnullのレコードがあったようです。まずはそこを
＞整備してから動かしたら、きちんと分割できました！お騒がせして申し訳
＞ありませんでした！

はい、確認。やはりそうでしたか。一応、No10で対策をいれておきました。
メッセージボックスは不要ならばMsgBoxのところをコメントアウトして
おいてください。

お礼

何から何まで ご親切にありがとうございました！

回答No.10

piroin654

以下のように
　　　　　　If IsNull(rs1!注文内容) Then
　　　　　　　　MsgBox ("注文内容が空です.次のレコードに移動します")
　　　　　　　　Exit Do
　　　　　　End If
を追加してみてください。

　　　　Do Until rs1.EOF
　　　　　　If IsNull(rs1!注文内容) Then
　　　　　　　　MsgBox ("注文内容が空です.次のレコードに移動します")
　　　　　　　　Exit Do
　　　　　　End If
　　　　　　buf = Split(rs1!注文内容, ",")

たぶん、T_商品の「注文内容」にデータが入っていないか、あるいは
なんらかの理由でデータが取得できていないのかもしれません。
これでどうなるか確認してみてください。

＞注文商品が1つの場合の「,」が無いものが、拾えないようです。

こちらでは拾えているのでそれが問題ではないようです。

回答No.9

山田 太郎（@f_a_007）

１、標準モジュールにCutStr()をコピペ。

２、修正したユニオンクエリをクエリにコピペ。

　(1)テーブル名を質問者のに修正。
　(2)列名を質問者のに修正。
　(3)SELECT文を１０個並べる。

　　　SELECT ID, CutStr(注文内容,",",1) AS 注文 FROM テーブル2;
　　　UNION
　　　・・・・・
　　　SELECT ID, CutStr(注文内容,",",10) AS 注文 FROM テーブル2;
　　　UNION
　　　・・・・・
　(4)適当な名前で保存。

　　　※ユニオンクエリは、SQLビューを開いてコピペします。

３、目的のクエリをコピペ。

　(1)SQLビューを開く。
　(2)コピペする。
　(3)列とクエリ名を修正する。

　　クエリ８－＞２で保存したユニオンクエリの名前

以上で目的は達成できます。

なお、別テーブルへの登録は、下記のSQL文を実行することでできます。

INSERT INTO テーブル3
SELECT *
FROM ユニオンクエリ
WHERE 注文<>"";

これも、SQLビューにコピペしてクエリ名、列名を合致させるだけです。なお、[イミディエイトウインドウ]でも SQL文を実行する関数（CnnExecute())の類を用意すれば可能です。

お礼

手順のご説明までいただき、ありがとうございます。
ユニオンクエリはあまり理解できていなかったのですが、今回、とても参考になりました。ありがとうございます。

回答No.8

piroin654

＞No.1の部分の「buf = Split(rs1!BO, ",")」でnullの使い方が
＞不正です。というエラーが出てしまいます。

ひょっとしたらT_商品にカンマ切りした箇所で、空の文字列が
はいっているのかもしれません。
一応、T_Tempのデザインビューで「注文商品」の「空文字列の許可」
を「はい」にしてみてください。もしエラーがでなければ、切り分けられた
データをテーブルで確認してみてください。

お礼

どうやら、全く動かないのではなく、注文商品が2つの場合は動いているようで、注文商品が1つの場合の「,」が無いものが、拾えないようです。これがエラーになる要因でしょうか？お手数おかけしますが、よろしくお願い致します。

補足

注文商品」の「空文字列の許可」を「はい」になっていますが、やはりnullの使い方が不正です。というエラーが出てしまいます。すみません。

回答No.7

piroin654

＞T_Tempは、前もって作成しておくテーブルなんでしょうか？

そうです。質問での
＞ワークテーブルなどで処理になりますでしょうか。
になります。
フィールドは、

　　　顧客ID　　　テキスト型
　　　注文商品　　テキスト型

とします。
切り分けたデータをこのテーブルに書き込み、
T_商品と差し替えてこのT_TempをT_商品
とします。この差し替えはNo5、No6に記載
している通りです。確認してみてください。

お礼

ありがとうございます！！！！！！！！

補足

No.8のお礼コメントの訂正です。
そもそも「注文商品」がnullのレコードがあったようです。まずはそこを整備してから動かしたら、きちんと分割できました！お騒がせして申し訳ありませんでした！
また、親切なご対応、本当に感謝しております。ありがとうございました！！

回答No.6

piroin654

No5の続きです。

本当はいきなり、

　　DoCmd.DeleteObject acTable, "T_商品"
　　DoCmd.Rename "T_商品", acTable, "T_Temp"

のように、元テーブルの「T_商品」を削除し、
「T_Temp」を「T_商品」に名前を変更しても構わないのですが。
そのあたりは、現状に応じてアレンジしてください。

わからないところがあれば補足してください。

回答No.5

piroin654

No1です。
肝心なことを忘れていました。
「T_商品」と「T_Temp」のそれぞれの名前を変更して
テーブルを差し替えます。つまり、
「T_商品」を「T_商品_お祓い箱」
「T_Temp」を「T_商品」
にします。
そこで、No1のコードの終わりのところで、

　　rs1.Close: Set rs1 = Nothing
　　rs2.Close: Set rs2 = Nothing
　　db.Close: Set db = Nothing

　　On Error Resume Next
　　DoCmd.Rename "T_商品_お祓い箱", acTable, "T_商品"
　　DoCmd.Rename "T_商品", acTable, "T_Temp"
　　'DoCmd.DeleteObject acTable, "T_商品_お祓い箱"
　　On Error Resume 0
End Sub

のように、

　　DoCmd.Rename "T_商品_お祓い箱", acTable, "T_商品"
　　DoCmd.Rename "T_商品", acTable, "T_Temp"

を追加しておきます。
また、一応上記ではコメントアウトしていますが、
　　'DoCmd.DeleteObject acTable, "T_商品_お祓い箱"
で、不要になるであろう元の「T_商品」テーブルを削除します。
必要ならばコメントアウトを解除してください。

以上のことは、「T_商品」から「T_Temp」へデータが
移動できたかを確認してからコードに実装してもよろしいかと。

わからないところがあれば補足してください。

補足

すみません！No.1の部分の「buf = Split(rs1!BO, ",")」でnullの使い方が不正です。というエラーが出てしまいます。ご教示いただけますか。よろしくお願いします。

回答No.4

山田 太郎（@f_a_007）

【補足の補足】

Where節は最終クエリには不要でした。
ユニオンクエリを基にINSERTするのであれば必要。

《Insert SQL 1》

INSERT INTO テーブル3
SELECT *
FROM ユニオンクエリ
WHERE 注文<>"";

《Insert SQL 2》

INSERT INTO テーブル3
SELECT *
FROM 最終クエリ;

回答No.3

山田 太郎（@f_a_007）

【補足】最終クエリをテーブルにINSERTするには？

追加クエリと呼ばれるSQL 文を書きます。

INSERT INTO テーブル3
SELECT *
FROM クエリ
WHERE 注文<>"";

なお、添付図は、CnnExecute()、DBSelect()はAccessのそれではありません。回答の説明のために書いた関数です。

PS、ユニオンは、もちろん10個並べます。

回答No.2

山田 太郎（@f_a_007）

Q、フィールド値を分割するには？
A、SQL文で分割できますよ。

標準モジュールに次の関数を登録しておけば、よりシンプルなSQLを書けます。

Public Function CutStr(ByVal Text As String, _
　　　　　　　　　　　　ByVal Separator As String, _
　　　　　　　　　　　　ByVal N As Integer) As String
　　Dim strDatas() As String

　　strDatas = Split("" & Separator & Text, Separator, , 0)
　　CutStr = strDatas(N * Abs(N <= UBound(strDatas)))
End Function

【ユニオンクエリ】

SELECT ID, CutStr(注文内容,",",1) AS 注文 FROM テーブル2;
UNION
SELECT ID, CutStr(注文内容,",",2) AS 注文 FROM テーブル2;
UNION
SELECT ID, CutStr(注文内容,",",3) AS 注文 FROM テーブル2;

【目的のクエリ】

SELECT クエリ8.ID, クエリ8.注文
FROM クエリ8
WHERE (((クエリ8.注文)<>""))
ORDEY BY クエリ8.ID;

添付図のユニオンクエリの実行結果には、注文が空文も含まれています。それを除外するSQL文を書けば目的を達成することが出来ます。なお、ORDER BY 節は必要です。その並びは絶対保証ではありませんので・・・

補足

初心者につき、ユニオンクエリが理解できていないので、モジュールを追加するまでは理解できたんですが、そのあとから何がなんだか・・・すみません。ユニオンクエリをご教示いただいたコードで作成＋SQLクエリをご教示いただいたコードで作成するんでしょうか？それらの名称はどうすれば連携しますか？？お手数おかけします！！