Access 複数のフィールドからクエリで抽出

質問

お世話になっております。

Accessのクエリについてお聞きしたいのですが、

テーブルに

mas_id－－－mas_nm---mas_nm2
１－－－-ABC－－－---DEF
２－－－-GHI－－－---JKL

としたとき、フォームのテキストボックスに１、２列のどれかをあいまい検索したときに
結果（ID）が取り出せるようにしたいです。

例：
「ABC」と入力→「１」
「DE」と入力→「１」
「GH」と入力→「２」

結果的にテキストボックスから３つのリストボックスを経て
大分類から細かい分類へ選択していくフォームを作ろうと思います。

テキストボックス(txtMaster)からキーワード１を入力（例：a1)
↓
リストボックス１（lstSub)に「１１、１２、１３」が表示される。
どれか１つを入力する（例：１１）
↓
リストボックス２(lstDet)に「１１１、１１２、１１３」が表示される。
どれか１つを入力する（例：１１１）
↓
リストボックス３(lstsai)に「１１１１、１１１２、１１１３」が表示される。

リスト１（値集合ソースに記述）
SELECT sub.sub_nm, sub.mas_id, sub.sub_id, master.mas_nm, master.mas_nm_2 FROM master INNER JOIN sub ON master.mas_id=sub.mas_id WHERE (((master.mas_nm) Like "*" & forms!フォーム!lblMaster.Caption & "*")) Or (((master.mas_nm_2) Like "*" & Forms!フォーム!lblMaster2.Caption & "*")) ORDER BY sub.sub_nm;

リスト２
SELECT detail.det_nm, detail.mas_id, detail.sub_id, detail.det_id, master.mas_nm, master.mas_nm_2 FROM master INNER JOIN (sub INNER JOIN detail ON (sub.sub_id = detail.sub_id) AND (sub.mas_id = detail.mas_id)) ON master.mas_id = sub.mas_id WHERE (((detail.sub_id)=[Forms]![フォーム]![lstSub]) AND ((master.mas_nm) Like "*" & [Forms]![フォーム]![lblMaster].[Caption] & "*")) OR (((master.mas_nm_2) Like "*" & [Forms]![フォーム]![lblMaster2].[Caption] & "*")) ORDER BY detail.det_nm;

リスト３も、リスト１・2同様にmas_nmとmas_nm2をORで記載していますが
フォームを使用したとき正確に結果が表示されるのはリスト１のみで、リスト２・３は全てのフィールドが表示されてしまいます。
またテーブルは４つありmaster、sub、detail、saiで

master（テキストボックス）：
mas_id---mas_nm---mas_nm2
　　１－－AAAA－－－BBB
　　２－－CCCC－－－DDD

sub（リスト１）：
mas_id---sub_id---sub_nm
　　１－－－１－－－１１
　　１－－－２－－－１２
　　２－－－１－－－２１
　　２－－－２－－－２２

detail（リスト２）：
mas_id---sub_id---det_id---det_nm
　　１－－－１－－－１－－－１１１
　　１－－－１－－－２－－－１１２
　　１－－－１－－－３－－－１１３
　　１－－－２－－－１－－－１２１
　　１－－－２－－－２－－－１２２
　　１－－－３－－－１－－－１３１

sai（リスト３）：
mas_id---sub_id---det_id---sai_id---sai_nm
　　１－－－１－－－１－－－１－－－１１１１
　　１－－－１－－－１－－－２－－－１１１２
　　１－－－１－－－１－－－３－－－１１１３
　　１－－－１－－－２－－－１－－－１１２１
---以下省略---

としています。同じ名前のものは一対多のリレーションをそれぞれつけています

例：
master sub deta
mas_id---mas_id---mas_id
mas_nm sub_id----sub_id

回答No.4

piroin654 · ベストアンサー

テーブルの作り方を変更する必要があるのでは。
各テーブルのフィールドで必要ないもの、
あるいは、master以外の各テーブルには主キーとなる
フィールドがないので、たぶん分類はできない
と思いますが。

各テーブルについて確認しますと、
(1)
mastereは、
mas_id---mas_nm---mas_nm2
　　１－－AAAA－－－BBB
　　２－－CCCC－－－DDD
ですから、msa_idを主キーに設定します。
構造はこのままでいいと思います。

(2)
subは、
mas_id---sub_id---sub_nm
　　１－－－１－－－１１
　　１－－－２－－－１２
　　２－－－１－－－２１
　　２－－－２－－－２２
となっていますが、本来はこうではなく、
mas_id---sub_id---sub_nm
　　１－－－１－－－１１
　　１－－－２－－－１２
　　２－－－３－－－２１
　　２－－－４－－－２２
のように、sub_idを一意に設定しなければ
sub_idを設定する意味がありません。
つまり、sub_idをsubの主キーに設定します。

(3)
detailは、
mas_id---sub_id---det_id---det_nm
　　１－－－１－－－１－－－１１１
　　１－－－１－－－２－－－１１２
　　１－－－１－－－３－－－１１３
　　１－－－２－－－１－－－１２１
　　１－－－２－－－２－－－１２２
　　１－－－３－－－１－－－１３１
のように、なっていますが、
mas_idはsubでデータを持っていますから
detailで持つ必要はなく、subと同様に、
sub_id---det_id---det_nm
　　１－－－１－－－１１１
　　１－－－２－－－１１２
　　１－－－３－－－１１３
　　２－－－４－－－１２１
　　２－－－５－－－１２２
　　３－－－６－－－１３１
とすれば、subとdetailの間のデータの
関連性は保たれ、かつdetailの各レコードの
一意性が保たれます。ここでも
det_idを主キーに設定します。

(4)
同様に、saiについても、
det_id---sai_id---sai_nm
　　１－－－１－－－１１１１
　　１－－－２－－－１１１２
　　１－－－３－－－１１１３
　　２－－－４－－－１１２１
のようにします。sai_idは重複のない数字に
設定します。

まずは、このことについて確認してみてください。
No2で示している、T大分類、T中分類、
T小分類の各テーブルのフィールドの
設定をよく見ればわかると思いますが。

一応、説明しますと、
T大分類　：
大分類ID(主キー)
大分類名

T中分類　：
中分類ID(主キー)
中分類名
大分類ID

T小分類　：
小分類ID(主キー)
小分類名
中分類ID

のようになっています。

なお、いったんリレーションは解除
しておいてください。
あるいは、あえて言うならばリレーションは
必要はありません。

お礼

返事が遅くなり申し訳ございません。
ありがとうございます。
無事解決することができました。
値集合ソースのクエリの条件が間違っていたようです。

回答No.3

piroin654

No2です。続きですが、リストの値を参照する場合、
リスト2を例にすると、

>[Forms]![フォーム]![lstSub]
リスト1のsub_idの列を参照することになります。
したがって、
[Forms]![フォーム]![lstSub].Column(1)
のようになるのでは、と思います。ただし、
質問のSQL文が正しいとすればですが。

なお、リストの幅をたとえば、
0cm,0cm,2cm
にして、一番目と二番目の列を非表示にしても、
一番目の列を参照する場合は、
Column(0)として参照します。三番目だと
Column(2)となります。

回答No.2

piroin654

いくつかのサイトで同じ質問をされていますが、なかなか
具体的な回答がつかないようですが。

中身が分かりづらいので以下のようにして回答します。
たとえば、3つのテーブルとそれぞれのフィールドが
以下のようだとします。

T大分類　：
大分類ID
大分類名

T中分類　：
中分類ID
中分類名
大分類ID

T小分類　：
小分類ID
小分類名
中分類ID

テキストボックスを
tx検索
とします。

フォームに三つのリストを設定します。
それぞれ、リスト1、リスト2、リスト3とします。

テーブルのフィールドをすべて表示するとして、
それぞれのリストの列数をそれぞれ2、3、3、リスト幅を2cm,2cm
　2cm,2cm,2cm　　2cm,2cm,2cm
とします(幅の数値は適当です).

値集合ソースは空のままにしておきます。

テキストボックス、各リストのイベントに以下を
設定します。

Private Sub tx検索_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT * FROM T大分類 WHERE T大分類.大分類名 LIKE ""*" & Me![tx検索] & "*"""
Me!リスト1.RowSource = strSQL
'他のリストの初期化
Me!リスト2.RowSource = ""
Me!リスト3.RowSource = ""
End Sub

Private Sub リスト1_Click()
Dim strSQL As String
strSQL = "SELECT * FROM T中分類 WHERE T中分類.大分類ID = " & Me!リスト1.Column(0) & ""
Me!リスト2.RowSource = strSQL
End Sub

Private Sub リスト2_Click()
Dim strSQL As String
strSQL = "SELECT * FROM T小分類 WHERE T小分類.中分類ID = " & Me!リスト2.Column(0) & ""
Me!リスト3.RowSource = strSQL
End Sub

このように設定すると各リストには段階的に分類された
データが表示されます。
ここで、たとえば、Me!リスト1.Column(0)　はリスト1最初の列に
表示されるデータ、すなわちT大分類の大分類IDを示しています。
このように、リストのデータから値を参照する場合はColumnプロパティ
を使います。リストの二番目の列はColumn(1)のようにして参照します。
必要に応じて、Column()の括弧の中の数値を変更して参照する
列を変更します。上記の例の場合は参照するデータがすべて最初の
列にあるので、すべてColumn(0)となっています。

回答を進める上での質問の場合の疑問点は、

>テキストボックス(txtMaster)からキーワード１を入力（例：a1)
>↓
>リストボックス１（lstSub)に「１１、１２、１３」が表示される。
>どれか１つを入力する（例：１１）

の部分の

>どれか１つを入力する（例：１１）

で、これはリストから選択するというよりも、再び
テキストボックスに入力するという意味ですか。

補足

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

＞＞回答を進める上での質問の場合の疑問点

どれか１つを入力するというのは
リストボックスに表示された結果をクリックするということです。

テキストボックスに「AB」または「DE」とうったとき、
リストボックス１に、「１１、１２、１３」と表示させて
そのうちどれか１つをクリックして（例：１１）
リストボックス２に「１１１、１１２、１１３」としたいです。

今の段階では、テキストボックス→リストボックス１までは
例：
「AB」または「DE」とテキストボックスに入力
↓
リストボックス１に「１１、１２、１３」と表示

されるのですが、リストボックス２から３までは
すべてのフィールドが表示されてしまいます。

あいまいな記述ですみませんでした。
また、VBAは
Option Compare Database

Private Sub txtMaster_LostFocus()
lblMaster.Caption = txtMaster.Text
lstSub.Requery
lstDet.Requery
lstsai.Requery
End Sub

Private Sub lstSub_AfterUpdate()
lstDet.Requery
lstsai.Requery
End Sub

Private Sub lstDet_AfterUpdate()
lstsai.Requery
End Sub

Private Sub lstsai_AfterUpdate()
txtICD.Value = lstsai
txtICD.SetFocus
End Sub

としています、長々としてわかりづらいですが
よろしくお願いします。

回答No.1

layy

コンボボックスで
住所を選ぶときの、
都道府県＞市区＞町村と選んでいく概念と同じ感じかと思います。
サンプルはないか探してみる。

都道府県が選ばれてこそ、市区の絞り込み、さらに
それまでが選ばれたら町村・・・。

リストボックス１が更新されたときのアクション、
リストボックス２が更新されたときのアクション、
そういうのはどうなっているのでしょうか？。
再クエリとかしていますか？。

一度に求めたい表示にはならない、
順番に表示を変えていかなくてはならない仕組みかと思います。

お礼

返事が遅くなり、申し訳ございません。
回答ありがとうございます、無事解決しました。

補足

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

＞＞リストボックス１が更新されたときのアクション、
　　リストボックス２が更新されたときのアクション、
　　そういうのはどうなっているのでしょうか？。
　　再クエリとかしていますか？。

VBAのことでしょうか？
piroin654さんの補足返信にVBAを載せましたので、よろしければそちらを見ていただけるとありがたいです。

テキストボックスやリストボックスがそれぞれ何か変更があったときは
それぞれのリストボックスの値集合ソースにいれているクエリを再クエリしています。

テキストボックスに変更があったとき
リストボックス1～３のクエリを再クエリ

リストボックス１に変更があったとき
リストボックス２～３のクエリを再クエリ

リストボックス２に変更があったとき
リストボックス３のクエリを再クエリ

という風にしています。