Access フォームのコンボボックスでの検索

質問

Access2010で質問です。

フォーム上のコンボボックスで
レコードを検索・抽出したいと考えています。

以下のような事をやりたい場合、
フォームやVBAをどのように
作ればよろしいのでしょうか？

お忙しいところたいへん恐れ入りますが、
ご教授いただけますと助かります。

＝＝＝＝＝＝テーブルの説明＝＝＝＝＝

以下の3つのテーブルがあります。

◆メインテーブル
|No|職業　|住所|年齢|名前|
--------------------------
|01|会社員|東京|　31|品川|
|02|自営業|埼玉|　30|川口|
|03|会社員|東京|　30|渋谷|
|04|自営業|埼玉|　20|戸田|
|05|自営業|埼玉|　20|熊谷|
|06|自営業|東京|　30|太田|

◆職業テーブル
|No|職名　|
-----------
|01|自営業|
|02|会社員|
|03|無職　|

◆住所テーブル
|No|地名|
---------
|01|東京|
|02|埼玉|
|03|千葉|

そして、各テーブルは
以下のようにリレーションされています。
・職業テーブルはメインテーブルの「職業」へリレーション
・住所テーブルはメインテーブルの「住所」へリレーション

＝＝＝＝＝検索方法の説明＝＝＝＝＝

検索はフォーム上で行いたいです。
フォームに以下のようなコンボボックスを２つ作成します。

　コンボボックス１・・・職業が選べます
　コンボボックス２・・・住所が選べます

それぞれのコンボボックスで条件を選んだ後、
「実行」ボタンを押すことで検索結果を表示させたいです。

◆両方のコンボボックスが空欄だった場合
「検索条件を選んでください」との
メッセージボックスを表示させたいです。

◆複数のレコードが抽出された場合にやりたい事
各レコードの「年齢」を比較して
値が一番小さいレコードだけが
抽出されるようにしたいです。
「年齢」が同じ値だった場合（競合した場合）は
「No」の値が小さいレコードを抽出したいです。

◆検索結果の表示方法
抽出されたレコードの「名前」だけを
フォームに表示したいです。

＝＝＝＝＝検索結果の例（やりたい事）＝＝＝＝＝

◆事例１
コンボボックス１＝「会社員」
コンボボックス２＝「東京」
検索結果＝「渋谷」

01と03が検索にヒットしますが
「年齢」の値が小さい方（03）だけを
抽出されるようにしたいです。

◆事例２
コンボボックス＝「自営業」
コンボボックス＝「埼玉」
検索結果＝「戸田」

まず02と04と05が検索にヒットしますが
02の「年齢」の値が大きいので除外します。
次に04と05の「年齢」の比較ですが、
同じ値になっています。
したがって「No」の値が小さい04が
抽出されるようにしたいです。

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

以上です。
できるだけ詳細に書いたつもりですが、
不足な情報がありましたらご指摘ください。
なにとぞよろしくお願いいたします。

回答No.2

layy · ベストアンサー

＞◆複数のレコードが抽出された場合にやりたい事
＞◆検索結果の表示方法
は
SELECT TOP 1 メイン.名前
FROM メイン
WHERE (((メイン.職業)="会社員") AND ((メイン.住所)="東京"))
ORDER BY メイン.年齢, メイン.ＩＤ;
みたいにします。
クエリデザインで左列から
名前・・・表示＝ＯＮ
年齢・・・表示＝ＯＦＦ、並べ替えに昇順
ＩＤ・・・表示＝ＯＦＦ、並べ替えに昇順
職業・・・表示＝ＯＦＦ、抽出条件をlike文で「フォームのコンボボックスの値」に設定
住所・・・表示＝ＯＦＦ、抽出条件をlike文で「フォームのコンボボックスの値」に設定
先頭１件のみ表示 「Top 1」

ちなみに、
職業と住所をコード化すると以下のようになります。
|No|職業|住所|年齢|名前|職業&住所
--------------------------
|01|　02|　01|　31|品川|0201
|02|　01|　02|　30|川口|0102
|03|　01|　01|　30|渋谷|0101
|04|　02|　02|　20|戸田|0202
|05|　02|　02|　20|熊谷|0202
|06|　02|　01|　30|太田|0201
です。
ここでいう「職業&住所」が
フォームでいう２つのコンボボックスで生成される検索キーと一致すればよい
検索キー=forms!フォーム名!職業コンボの選択値 & forms!フォーム名!職業コンボの選択値
検索は１項目でできることになります。
＞◆両方のコンボボックスが空欄だった場合
実行ボタン押したとき、
合成した検索キーが４バイトでないときが、選択していないとき、に相当します
参考になれば・・・。

フォームでの（コンボボックスなど）入力値をクエリの検索条件に使う、
この機能について検索し習得してみてください。

お礼

ありがとうございます！
おかげさまで、やりたい事ができるようになりました。

自分ではややこしいVBAのコードを考えていたのですが、
ご提案にいただいた作り方ならシンプルで簡単に実装できますね。
たいへん参考になりました。

回答No.1

MRT1452

色々やり方はあろうかと思います。
自分が思いつく事としては。

(1)プログラム上でSQL(クエリ)を作って発行。
(2)複数のクエリとワークテーブルを使う。
(3)一旦テーブルデータを全件読み出して、ファイルや変数上で処理する。

この2通りですかね。

(1)は、本来のプログラムの形での方法ですね。
OracleとかDB2とかで同様の事をするなら、基本的にこの方法しか無いです。

(2)は、Accessでしか使えません。
検索条件等が複雑になればなるほどゴチャゴチャしてきますし、非効率的になってきます。

(3)はもはやDBの利便性を捨てて強行策に出る場合ですね。
普通、この方法を取ることは無いでしょう。

(1)であれば、そうですね・・・。

Select
　Min(No) AS No,
　職業,
　住所,
　Min(年齢) AS 年齢,
　年齢
From メインテーブル
Where
　メインテーブル.職業 = (職業の選択コンボボックスの値)
and
　メインテーブル.住所 = (住所の選択コンボボックスの値)
Group by No,職業,住所,年齢,年齢

(※試してないのでSQL文が間違っている可能性大です。)

というようなSQLを作成するプログラムを作成し、発行。検索結果をデータソースとして表示。
この際、コンボボックスの値の有無によってif判定をいれて、Where句(検索条件句)の内容が可変的に変わるように工夫。

http://eprostation.jpn.org/code/access/access0001.html
上のURLのサンプルのsSQLという変数にSQL文をセットして実行するという感じ
データ取得のところのF1とかF2とかはフィールド名(ここでは職業とか住所とか)ですね、多分。

(2)は、段階的にクエリを発行し、ワークテーブルにセットしてういく方法。

(1) メインテーブルに対し、職業で絞込みを行いワークテーブル１にその結果を書き込む。
職業が選択されていない場合は、全件ワークテーブル１に書き込む。

(2) ワークテーブル１に対し、住所で絞込みを行いワークテーブル２にその結果を書き込む。
住所が選択されていない場合は、全件ワークテーブル２に書き込む。

(3 )ワークテーブル２をNo順、年齢順に並び替え、先頭の1件だけ抜き出して表示。

(※これに関しても全く検証等はしていないので、誤っている可能性大。)

(3)は全データを自分が使いやすい形、配列とかテキストファイルとかに書き出して、アナログ的に行う。
Accessを使う意味が無いので、お勧めしない。

フォームは、

ヘッダ部に検索条件の入力部、
メイン部に結果を表示するフィールド

で良いかと。
メイン部分のレコードの扱いは、単票にするか一覧(複数件表示)にするかでレイアウトとか変わってくると思います。

お礼

ありがとうございます！
いろいろな方法をご教授いただきまして
勉強になりました。
特に（1）が参考になりました。