[Access2013]リストボックスの値指定

質問

以下の様な事をしたいと思っていますが上手く行きません。
上手くいく方法はありますでしょうか？

テーブル1
id 名称　→列名
1 みかん
2 りんご
30 トマト

上記の様なテーブルがありそれをフォーム上に配置したリストボックス（複数選択可）の値集合ソースにしています。
リストボックスから入力した値は、みかんのみを選択した場合は「1」、みかんとりんごを選択した場合は「1;2」とDBに保存しています。
（この部分は想定通りの動きをしています。）

入力用フォーム以外に、別フォームのデータシートビューで一覧表示をしています。
そこで上記リストボックス値でDBに入っている「1」や「1;2」を「みかん」や「みかん;りんご」と表示したいと思っています。

データシートビューにもリストボックスを配置し、値集合ソースは入力フォームと同じテーブル1に、コントロールソースを「1;2」が入っているフィールドにしましたが、「1」のみの場合は選択状態で表示されますが、「1;2」の場合未選択になってしまいます。

やりたいことは、ここで入力をするつもりはありませんのでただ文字列として「みかん;りんご」が出てくればいいです。

ただ、一々フォームロードなどでSQL文（＋ループ処理）を書くと件数が数万件は少なくともあるのでロード時の待ちが長くなりそうです。
また、リンクテーブルマネージャで外部DBと接続しているので、テーブル定義をAccess独特のものに変更するのは難しいです。
idは自然増加に任せており桁数はまちまち、複数選択の個数も1～数十と様々です。

質問内容
・リストボックスのコントロールソースにて複数値を指定する方法はありますか？（そもそもとして、そういうことが出来ないものなのでしょうか？　また,区切りに変更、splitで配列化等はすでに行って駄目でした。）
・処理時間に影響が少ない方法で「みかん;りんご」とデータシートビューに表示する方法はありますでしょうか？
・例えばリストボックスを使用せずテキストボックス等に変更してコントロールソースでselect 名称 from テーブル1 where id in Replace("1;2",";",",")の様な事をした結果複数件出た場合は区切り文字で区切って全部出す等を可能なのでしょうか？

よろしくお願いいたします。

回答No.6

chayamati

要望されている事にお答え出来そうにありませんが 感じたことのみ記述しておきます。

1.「;」は「*-+/<>>=等」の演算子と同じく命令文の記号として使われるもので、テキストの値の一部とした場合不都合が生じそうで感心しません
＿因みに「;」はSQL文の文末を意味します。
2.>複数選択の個数も1～数十
　この組み合わせはどのようにして決まるのですか(外部DB,orテーブル1より全ての組合せを自動生成）
3.>外部DBと接続しているので、テーブル定義をAccess独特のものに変更するのは難しいです。
　情報のフィードバック（外部DBへの送信）はあるのですかあったとしてもテキストファイル、エクセルファイルへの変換は可能です
　Access独特のものではありません。リレーショナルデーターベース（ RDB ）のコンパクト版とご理解ください

添付図のようにテーブル1に加えて組合せテーブルと結合テーブル定義してリレーションを設定すると
添付図下段のような組合せの数の問題も難なくも解消します
フォーム、レポートを作成することでより取り扱いやすく改良することも可能です

回答No.5

chayamati

>「1;1;2」などという形で入ることはありません。
一つのテキスト内の重複でなく主キーのようにテーブル内全体での重複はいかがでしょう
添付のようにテーブルのデザインビューでフィールドのインデクスで定義します
この定義が出来れば重複レコード無いことになります。

補足

補足が遅くなってすみません１、２というのは主キー項目です。

回答No.4

chayamati

実際やってみましたが、「1;2」となっている部分以外は値が切り替わりました。
エクセルではA1;D5のようにセル範囲の区切りに使いますね
Accessでもテキストの使用禁止記号ではないでしょうか
「1;2」を「1_2」に替えると不都合ありますか

回答No.3

chayamati

これはテーブルのデザインビューのルックアップで定義します。
添付は
食事履歴明細（ID、食品名ID、…）に食品名（ID、食品名、食品名のフリガナ、…）
を食品名IDに食品名.IDを関連付けしてルックアップしています
註.関連付けする食品名.ID重複なしです。
　「1;2」のフィールドは重複なしに定義していますか

1.値集合ソース：SELECT 食品名.ID, 食品名.食品名 FROM 食品名 ORDER BY 食品名.食品名のフリガナ;
2.連結列： 食品名.IDを指します
3.列数： 食品名.IDと 食品名.食品名の2つを指します。
4.列幅： 食品名.IDは非表示です。

お礼

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

>「1;2」のフィールドは重複なしに定義していますか
「1;1;2」などという形で入ることはありません。

実際やってみましたが、「1;2」となっている部分以外は値が切り替わりました。
ただ、「1;2」と入っているフィールドについては上手くいっていません。
複数の値の許可だろうと思ったのですが、「はい」に変えようとすると、「この操作はこのタイプのオブジェクトには実行できません」とエラーが出ます。
「1;2」と入れられるように当該フィールドはテキストです。
表示コントロールは、リストボックス、コンボボックス共に試してみましたが駄目でした。
ルックアップウィザードを使用してもだめでした。
クエリならと思ったのですが、そもそも、複数値の項目がありません。

よろしくお願いいたします。

補足

ローカルテーブルで行ったところ問題なくできたので、リンクテーブルだと上手くいかないのかもしれません。

回答No.2

hatena1989

> 正規化する場合、みかん、りんご両方と結びついている場合、
> リスト表示は２行になってしまいませんでしょうか？
> ２行で出すことだけはどうしても避けたいので現状の様な形になっています。

なぜ、避けたいのでしょうか。
人間が見た場合の見やすさからでしょうか。

データベースにしているとういことは、
大量をデータの集計、検索、抽出、演算などのデータ操作を高速にしたいということだと思います。
その場合、正規化されたテーブル設計でないと、データベースの機能を活かせないので、
複雑で重い処理になってしまいます。データベースにしている意味がありません。

現状のままでいくならExcelの方がよほどシンプルで高速な処理になるでしょう。

データとしては、正規化した形で持ち、
表示するときに見やすいように１行に変換するようにする、
というのが順当な設計だと思います。

補足

最低数万件と書いたのですが
実は数十万件はありそうで且つ、リンゴ；みかんとしている部分の個数が各10個ほどある上に同様の結びつきが1テーブルの中に4か所ほどあるのでので子テーブルにして一覧で表示する場合数百万件以上になってしまうからというのが理由です。
Join文で相当時間がかかるので正規化をしても逆に処理速度が落ちるだろうと判断しています。

ＤＢの基本理念として正規化したほうがいいという事は理解できているのですが、今回のような場合、
>表示するときに見やすいように１行に変換するようにする、
というのも結局ループ処理にするしかない（入っているりんご；みかん等の個数が不定なので）と思うのですが処理時間はそんなに違うものなのでしょうか？

回答No.1

hatena1989

> また、リンクテーブルマネージャで外部DBと接続しているので、テーブル定義をAccess独特のものに変更するのは難しいです。

外部DBというのはAccess以外のDBということでしょうか。

> 上記の様なテーブルがありそれをフォーム上に配置したリストボックス（複数選択可）の値集合ソースにしています。
> リストボックスから入力した値は、みかんのみを選択した場合は「1」、みかんとりんごを選択した場合は「1;2」とDBに保存しています。

リストボックスは非連結で、VBAで「1;2」という形に変換して保存しているということでしょうか。

もし、そうなら、データベースの設計として思いっきり間違っています。工夫すれば希望の出力は出せますが、どうしても重い処理になります。また、他にも集計や検索などあらゆるデータ操作をするにも、面倒で重い処理になります。

テーブルの「正規化」をすることを強く推奨します。（具体的には、一対多関係のテーブルに分割する。）

外部DBの設計がどうしても変更不可ならVBAで「1;2」を「みかん;りんご」に変換するユーザー定義関数を作成して、クエリのフィールドに埋め込むことになりますね。ただ、レコード件数分呼び出されることになるので重い処理になります。

補足

回答ありがとうございます。
やはりユーザ定義関数をコントロールに仕込む方法しかない感じなんですね。

保存はデータシートビュー以外のフォームで非連結でVBAで連結しています。

正規化する場合、みかん、りんご両方と結びついている場合、
リスト表示は２行になってしまいませんでしょうか？
２行で出すことだけはどうしても避けたいので現状の様な形になっています。
結局１行で表示しようとするとループになってしまうかと思いますが何かAccessには打開策があるのでしょうか？