Ａｃｃｅｓｓでのあいまい検索について

質問

Access2010の使用者で＆初心者です。
クエリの抽出条件に別テーブルのフィールドを文字列検索させ、その文字列が含まれていた場合は、その文字列を別フィールドに抜き出すというクエリを作成したいのですが・・
・検索したいテーブル(T商品情報)
・年月日
・品目情報
・検索する文字列が入ったテーブル(T品目)
・検索文字
検索したいテーブル(T商品情報)の「品目情報」から、検索する文字列が入ったテーブル(T品目)の「検索文字」を検索し、その検索文字列が含まれていた場合は別フィールにその検索文字列を抽出。という作業を行いたく思っております。

例
---------------------------T商品情報
年月日｜品目情報
20010/8/10｜武田様より、口頭にてもも大を53個受け付けました
20010/8/10｜上坂様より、電話にてりんごを3個受け付けました
20010/8/10｜木村様より、電話にてももを3個受け付けました
---------------------------

---------------------------T品目
検索文字
もも大
りんご
もも
---------------------------
↓

---------------------------クエリ
年月日｜品目情報 | 検索文字
20010/8/10｜武田様より、口頭にてもも大を53個受け付けました｜もも大
20010/8/10｜上坂様より、電話にてりんごを3個受け付けました｜りんご
20010/8/10｜木村様より、電話にてももを3個受け付けました｜もも
---------------------------
このようなクエリ結果を望んでおります。文章の中から商品名を抜き出し、その抜き出した商品に価格をひもづけたいのです。このようなことをクエリで行えますでしょうか？

回答No.6

atom900

No.5の補足からすると、「手元に届くデータが定型的ではない」ということですか。
それはコンピュータ処理に向きませんねぇ。(^_^;)

どんなに雑であっても、一定の法則が無ければ簡単な解決方法はありません。

一番の近道は、現状のままで解決策を見つけるのではなく、
ある程度コンピュータ処理に向いた記述に変更してもらうのが一番だと思います。

#3さんが言われるようにどうしても雑な文には抜け道が発生します。

それらの注文方法が最低でもいくつかのパターンに収まるようにしてもらうべきだと思います。

例えば、

◆区切り文字を決め（空白とか、：とか、／とか品目などで使用しない文字にします）、項目の順番を決める。

１３時５０分 3個 商品：7777 するめ
（項目を空白で区切ってもらう、品目が最後と仮定し、商品コード以降の空白は品目とみなす）

高知へうど 3kg
（品目は「へ」から最後の空白まで、最後の空白以降の文字は受注数とする）

高知へうどを3kg
（品目は「へ」から「を」まで、「を」以降の文字は受注数とする）

上記のようないくつかのパターンに、ある程度整形してもらうことは難しいのでしょうか？

私の今までの経験から、どんな文字が来るかわからない、ような受注内容では、いつまで経っても解決策はありませんし、
また、新たな受注文章が来た場合、今までの方法が使えなくなったり、訂正することによってこれまでの処理がうまくいかなくなるケースも往々にしてあります。

こういった、建設的ではない方法は、時間と労力と金銭の無駄．．という考え方でいます。

解決策にはなっていませんが、もっと前向きに、
建設的な方法（少しの労力を払ってでも、すべてがうまくいく方法）にシフトされることを望みます。

回答No.5

atom900

質問の内容から察するに、
まず大事なことは、「T商品情報」の「品目情報」の中から商品名だけを取り出す。
ということではないかと思います。

取り出すことさえできれば、その後、二次加工として、
「検索文字」項目にセットしようが、
その「検索文字」を「T品目」とぶつけて、「該当なしエラー」を表示したり、単価を自動セットしたり、できますものねぇ。

で、ちょっと変わった方法にはなりますが、文字列検索を使って取り出す方法があります。

クエリのSQL文は次のようなものです。

SELECT Mid([品目情報],InStr([品目情報],"にて")+2,InStr([品目情報],"を")-InStr([品目情報],"にて")-2) AS 抽出品目
FROM T商品情報;

何をしているかといえば、

文字列「にて」と「を」の間にある文字が品目である。

という条件を指定しています。

ただ、この方法もいくつか問題があって、品目の中に「を」があればそこで切れてしまいます。

また、相手先名に「にて」という文字があれば誤作動を起こします。
この場合は、手間はかかりますが、次の２ステップで回避できます。

１ステップ目、「にて」ではなく「様より、口頭にて」を使用する
２ステップ目、「にて」ではなく「様より、電話にて」を使用する

ひとつのヒントとして参考にしていただければと思います。

補足

ヒントありがとうございます。SQL文とても参考になりました。
残念ながらこちらの手元に届くデータは本当にきたなく・・・ある程度きまった定型文で入っているものもありますが、例えば
「１３時５０分3個　商品：7777するめ」
「高知へうど3kg」
など本当に自由に入っており（笑）
日にち・商品名・個数が情報として入っていればよいという指示で始まったらしく、現時点での変更はできない模様です。

こちらとしてもマスタにある商品名さえ抜き出せればなんとか作業が進むのですが、商品名が「もも」と「もも大」で複数行抽出されてしまい、・・・膨大なデータの中から、不要な行を手で削除していくなどとてもできません。なんとかなりませんか？助けてください。

回答No.4

30246kiku

＃３です

＃３では、おかしくなる場合がありますので
例えば、

武田様より、口頭にてすもも大を53個受け付けました
上坂様より、電話にてりんごを3個受け付けました
木村様より、電話にてももを3個受け付けました

１行目の「もも大」が「すもも大」
で、品目に「すもも」があったとすると

年月日｜品目情報 | 検索文字
20010/8/10｜武田様より、口頭にてすもも大を53個受け付けました｜もも大
20010/8/10｜上坂様より、電話にてりんごを3個受け付けました｜りんご
20010/8/10｜武田様より、口頭にてすもも大を53個受け付けました｜すもも
20010/8/10｜木村様より、電話にてももを3個受け付けました｜もも

の様な表示になります。＃３の回答では、
長い方の検索文字で抽出されていたら、その検索文字に含まれるものは除外・・・
（「もも大」で抽出されていたら「もも」は除外）
としていましたが、今回の例のように
・検索文字の文字数は同じ
場合は正しく動きません。

やはり、品目は = で抽出指定できるように、加工した方が良いかと

"今日は猛打賞、むねりんごきげん"

とかあったら、りんご　に引っ掛かると思います。
上記例では、解釈パターンが合わないので、事前の加工時にはじかれる？
（はじく処理を入れていればですが）

回答No.3

30246kiku

＃２です

せっかくなので SQL での抽出を試みる事に
＃１に示していた以下を、クエリ名「Q_商品」として作っておきます。

SELECT T商品情報.年月日, T商品情報.品目情報, T品目.検索文字
FROM T商品情報, T品目
WHERE (((T商品情報.品目情報) Like "*" & [T品目].[検索文字] & "*"));

このクエリを元に、以下クエリを SQL ビューで記述します。
（クエリのデザインでは記述できないかと）

SELECT Q1.* FROM
Q_商品 AS Q1 LEFT JOIN Q_商品 AS Q2
ON Q1.年月日 = Q2.年月日
AND Q1.品目情報 = Q2.品目情報
AND Len(Q1.検索文字) < Len(Q2.検索文字)
AND Q2.検索文字 Like "*" & Q1.検索文字 & "*"
WHERE Q2.年月日 Is Null;

※ 処理性能はわかりません。
　また、以降に控える処理にどう使っていかれるのか、わかりません。

回答No.2

30246kiku

＃１です

> 到着したデータが、残念ながら下記のようなかたちでしか届きません。

加工しない状態での抽出については、他の方の回答を待ってください。

以降に控えているもの・事をやりやすいように加工する、という選択肢はないのでしょうか。
なければ、以下スルーしてください。

テーブル「TS1」として以下を作っておいたとします。

・受注NO（オートナンバ）
・年月日（日付）
・顧客（テキスト）　（武田、上坂、木村、・・・）
・方法（テキスト）　（口頭、電話、・・・）
・品目（テキスト）　（もも大、りんご、もも、・・・）
・数量（数値）　　　（53、3、3、・・・）

以下を標準モジュールに記述し、MkTable を実行すると内容が出来上がります

Private Function CutMoji(sSrc As String _
　　　　　　　　　　, sLeft As String, sRight As String) As String
　　Dim iPosS As Long, iPosE As Long

　　If (Len(sSrc) = 0) Then Exit Function
　　If (Len(sLeft) = 0) Then
　　　　iPosS = 1
　　Else
　　　　iPosS = InStr(sSrc, sLeft)
　　　　If (iPosS = 0) Then
　　　　　　iPosS = 1
　　　　Else
　　　　　　iPosS = iPosS + Len(sLeft)
　　　　End If
　　End If
　　If (Len(sRight) = 0) Then
　　　　iPosE = Len(sSrc) + 1
　　Else
　　　　iPosE = InStr(iPosS, sSrc, sRight)
　　　　If (iPosE = 0) Then iPosE = Len(sSrc) + 1
　　End If
　　CutMoji = Mid(sSrc, iPosS, iPosE - iPosS)
End Function

Public Sub MkTable()
　　Dim rsTo As New ADODB.Recordset
　　Dim rsFrom As New ADODB.Recordset
　　Dim sSrc As String

　　rsFrom.Open "T商品情報", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
　　rsTo.Open "TS1", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
　　While (Not rsFrom.EOF)
　　　　rsTo.AddNew
　　　　rsTo("年月日") = rsFrom("年月日")
　　　　sSrc = rsFrom("品目情報")
　　　　rsTo("顧客") = CutMoji(sSrc, "", "様より")
　　　　rsTo("方法") = CutMoji(sSrc, "、", "にて")
　　　　rsTo("品目") = CutMoji(sSrc, "にて", "を")
　　　　rsTo("数量") = CutMoji(sSrc, "を", "個")
　　　　rsTo.Update
　　　　rsFrom.MoveNext
　　Wend
　　rsTo.Close
　　rsFrom.Close
End Sub

※ 作る先のテーブル「TS1」をクリアしてから実行する・・・　とか
　元の「品目情報」が Null なら・・・　とか
　「年月日」の 20010/8/10 を 2010/8/10 に変更する・・・　とか
　rsTo.Update　でエラーになった品目情報を表示する様にする・・・　とか
　元テーブル「T商品情報」で期間絞り込み後のものを対象にする・・・　とか

上記は、DB化が目的ではありません。が、
データを受け取ったタイミングで MkTable を実行するだけで、
以降のやりたいもの・事がしやすいような気がします。

※　いろいろと不都合部分は修正してください。

回答No.1

30246kiku

クエリのデザインで、添付図上段のように設定し表示してみます。

クエリをSQLビューで見た内容

SELECT T商品情報.年月日, T商品情報.品目情報, T品目.検索文字
FROM T商品情報, T品目
WHERE (((T商品情報.品目情報) Like "*" & [T品目].[検索文字] & "*"));

表示されるのは以下

年月日｜品目情報 | 検索文字
20010/8/10｜武田様より、口頭にてもも大を53個受け付けました｜もも大
20010/8/10｜上坂様より、電話にてりんごを3個受け付けました｜りんご
20010/8/10｜武田様より、口頭にてもも大を53個受け付けました｜もも
20010/8/10｜木村様より、電話にてももを3個受け付けました｜もも

※ 「もも大」は、「もも大」はもちろんですが「もも」も含んでいるため

武田様より、口頭にてもも大を53個受け付けました
上坂様より、電話にてりんごを3個受け付けました
木村様より、電話にてももを3個受け付けました

これらの内容を眺めてみます。すると

「武田」様より、「口頭」にて「もも大」を「53」個受け付けました
「上坂」様より、「電話」にて「りんご」を「3」個受け付けました
「木村」様より、「電話」にて「もも」を「3」個受け付けました

「　」で囲んだ部分を項目として作ってみます。
テーブル「T商品情報」を変更してみます

・受注NO（オートナンバ）
・年月日（日付）
・顧客（テキスト）　（武田、上坂、木村、・・・）
・方法（テキスト）　（口頭、電話、・・・）
・品目（テキスト）　（もも大、りんご、もも、・・・）
・数量（数値）　　　（53、3、3、・・・）

テーブル「T品目」

・品目（テキスト）　（もも大、りんご、もも、・・・）
・単価（通貨）　　　（200、150、100、・・・）

データを入れてみると以下の様な感じに

受注NO　｜　年月日　｜　顧客　｜　方法　｜　品目　｜　数量
1　｜　2010/08/10　｜　武田　｜　口頭　｜　もも大　｜　53
2　｜　2010/08/10　｜　上坂　｜　電話　｜　りんご　｜　3
3　｜　2010/08/10　｜　木村　｜　電話　｜　もも　｜　3

品目　｜　単価
もも大　｜　200
りんご　｜　150
もも　｜　100

確認用としてテーブル名は変えてました。
「T商品情報」→「TS1」、「T品目」→「TS2」

添付図下段の設定をして表示を見てみると

クエリをSQLビューで見た内容

SELECT TS1.年月日, TS1.顧客, TS1.方法, TS1.品目, TS1.数量, TS2.単価, [数量]*[単価] AS 合計
FROM TS1 LEFT JOIN TS2 ON TS1.品目 = TS2.品目;

表示されるのは

年月日　｜　顧客　｜　方法　｜　品目　｜　数量　｜　単価　｜　合計
2010/08/10　｜　武田　｜　口頭　｜　もも大　｜　53　｜　200　｜　10,600
2010/08/10　｜　上坂　｜　電話　｜　りんご　｜　3　｜　150　｜　450
2010/08/10　｜　木村　｜　電話　｜　もも　｜　3　｜　100　｜　300

という事も出来ます。
上記テーブルの項目は、説明上のものです。実際には、
・顧客の情報・・・連絡先やらナンチャラを持ちたいな
・品目名は１テーブルにまとめておきたいな・・・「もも大」とか「りんご」とか

雰囲気、以下の様な感じに発展していきます。

顧客用テーブル
・顧客ID（長整数：主キー）
・氏名
・連絡先
・会員登録日
・支払方法
・・・・

品目用テーブル
・品目ID（長整数：主キー）
・品目名
・単価
・・・・

方法用テーブル
・方法ID（長整数：主キー）
・方法
・・・・

受注用テーブル
・受注NO（オートナンバ：主キー）
・年月日
・顧客ID（長整数）　顧客用テーブルの顧客ID
・方法ID（長整数）　方法用テーブルの方法ID
・品目ID（長整数）　品目用テーブルの品目ID
・数量
・受注者
・・・・

受注用テーブルの「顧客ID」「方法ID」「品目ID」部分は、
テーブルのデザインで、
各テーブルをルックアップする設定にしておけば、見た目変わらないと思います。
上記では「方法」部分もテーブルに分けてみましたが・・・・
そこまでいらないと思ったら、ルックアップで値を選択するだけでも・・・

品目部分もそうですが、品目内の「単価」・・・
これ、その時の単価を入れておけばいいや・・・・とか
単価を変更した履歴を持ちたいな・・・・とか
いろいろ考えていると、
受注用テーブルにも、その時の単価を格納するようにしましょうか・・・とか
いやいや、
品目テーブルには単価を持たせずに、単価の履歴用のテーブルを新設しましょう・・・とか
どんどん発展していくと思います。

参考程度にしてください（参考になれば・・・ですが）

補足

詳しいご説明、本当にありがとうございます。
私の説明不足もあり申し訳ありませんが、もう一度質問をさせてください。

到着したデータが、残念ながら下記のようなかたちでしか届きません。

例
---------------------------T商品情報
年月日｜品目情報
20010/8/10｜武田様より、口頭にてもも大を53個受け付けました
20010/8/10｜上坂様より、電話にてりんごを3個受け付けました
20010/8/10｜木村様より、電話にてももを3個受け付けました

そして、DB化（構築・運用）する必要もなく、ただ単にある期間での商品ごとの売り上げを把握したく思っております。DB化がすぐに行えるようなデータがそろっていれば、今回の問い合わせをせずに済んだのですが・・・申し訳ありません。

自分が行えずに悩んでいるところが、30246kikuさんに教えていただきました

＞年月日｜品目情報 | 検索文字
＞20010/8/10｜武田様より、口頭にてもも大を53個受け付けました｜もも大
＞20010/8/10｜上坂様より、電話にてりんごを3個受け付けました｜りんご
＞20010/8/10｜武田様より、口頭にてもも大を53個受け付けました｜もも
＞20010/8/10｜木村様より、電話にてももを3個受け付けました｜もも

＞※ 「もも大」は、「もも大」はもちろんですが「もも」も含んでいるため
↑まさにこの結果で悩んでおります。
「もも大」は「もも大」として1行のみでクエリ結果を出したいのです。
他にも検索文字として部分的に品目名がかぶり、複数行クエリ結果が出てしまいます。

---------------------------T品目
検索文字
1111569
1111
11115
---------------------------
などもあるのです。「1111」は「1111」として1行のみでクエリ結果を出したいです。
何かよい方法はありませんでしょうか？