Accessで条件設定用のテーブルを作成する方法

質問

Accessの条件設定用のテーブルを作成してクエリで結合する方法について質問があります

テーブル名「名簿」(データ数99）
ID 名前 役職
1 名前1 部長
2 名前2 次長
3 名前3 NULL
4 名前4 NULL
5 名前5 NULL
・・・
99 名前99 NULL
※NULLは空欄で役職無し

上記名簿に対して
部長と次長は目標値0
特定の人物は目標値2
それ以外は目標値4
と設定したいのです。

下記の「目標値」のような
条件設定用のテーブルを作成し、
クエリで結合することは可能でしょうか？

テーブル名「目標値」(データ数5）
名前 役職 目標値
* 部長 0
* 次長 0
名前3 * 2
名前5 * 2
* * 4
※「*」はどの値でも関係なくヒットして欲しい

クエリの結果としてほしいのは下記のような結果です

クエリ(データ数99)
ID 名前 役職 目標値
1 名前1 部長 0
2 名前2 次長 0
3 名前3 NULL 2
4 名前4 NULL 4
5 名前5 NULL 2
・・・
99 名前99 NULL 4

テーブル「名簿」の内容は変更できないものとします。
同じ結果が得られるのであればテーブル「目標値」は変更可能です。

回答No.10

chayamati

＞ただ、NULLが含まれてると抽出できないので
　NULLが含まれていても抽出できるようにするために
　Nz(T名簿.名前,"") Like Nz(T目標値.名前,"")
-----------------------------------------------------------
NZは「Is Null」です。
従ってSQLビューは
「SELECT 名簿.ID, 名簿.名前, 名簿.役職, 名簿.目標値
　　FROM 名簿
　　WHERE (((名簿.名前) Like "*" & [forms]![メニュー]![名前検索] & "*")
　　　 AND ((名簿.役職) Like "*" & [forms]![メニュー]![役職検索] & "*")
　　　 AND ((名簿.目標値) Is Null)) OR (((名簿.役職) Is Null));

補足

今回の質問では2つのテーブルを結合する方法を質問しています。
それがchayamatiさんは
なぜ１つのテーブルの抽出方法を答えているのでしょうか？
私はテーブルを抽出したいのではなく、結合したいのです。
クエリの結合で列を追加したいと難度も伝えているにもかかわらず、chayamatiさんは最初から名簿に
目標値という項目を追加し、聞いてもいない抽出条件についてばかり述べていますよね？

何がしたいのか理解できません。

回答No.9

chayamati

＞なにやらフォームで入力した名前で抽出をしていますが、
　これでクエリの結果が99件になるのでしょうか？
　質問の本文で書いている通り99件結果が得られるようにしたい
　質問の本文にあるとおり名簿テーブルの項目は「ID」と「名前」と「役職」のみです。
　質問の最後に『テーブル「名簿」の内容は変更できないものとします。』と書いてある通り、
　回答No.4の補足や回答No.5の補足でも伝えた通り、名簿テーブルは変更できません。
　どうやら、chayamatiさんはどうあっても、
　名簿テーブルに目標値を直接設定すべきだという考えをお持ちのようですが
　そもそもAというテーブルとBというテーブルをクエリで結合したいという質問に対し、
　最初からAのテーブルに項目を追加して自分で入力したらいいという回答が返ってきたら、
　本末転倒もいい所ではないですか？
　それとも、chayamatiさんは結合するクエリに対する質問は
　すべて大本のテーブルで項目を増やして、自分で入力すればいいと答えるのですか？
　クエリの存在意義を理解しているのか疑問です。
　たしかにT目標値のテーブルが20件のレコードが必要とかであれば、確かに
　20件も目標値を入力するなら大本のテーブルの99件を修正した方がややこしくなくていい
　となりますが、T目標値のレコードが5件で済むなら、大本のテーブルの99件を入力するより
　T目標値で5件入力して、あとはクエリで解決させようと考えるのはそんなにおかしいですか？
　おやおやそこはクエリで設定すべきか
-----------------------------------------------------------
・最初の質問には名簿テーブルだけでここには目標値の項目がありません
　そこで、役職というテーブル新規に追加して「目標値」を入れたのですが
　クエリの期待値が役職が「*」のとき目標値2,4があり、
　「目標値」は名前で決めるのではないかと
　「目標値」を名簿に追加しました

　テーブル名「目標値」(データ数5）
　名前 役職 目標値
　* 部長 0
　* 次長 0
　名前3 * 2
　名前5 * 2
　* * 4
　※「*」はどの値でも関係なくヒットして欲しい
　WHERE (((名簿.名前) Like "*" & [名前検索] & "*") AND ((名簿.役職) Like "*" & [役職検索] & "*"))
　[名前検査]または[役職検索]の入力窓に入力せずにOKクリックで全てが表示対象になります
　 Like "*" & [名前検索] & "*"で名前検索が空白だとワイルドカードは「**」となります
　尚、99件とはどのようなレコードでしょうか
　SQLにTOP関数があるようですが、SQLは自分はマスタしていません
　下記のURLがご参考になりますか
　TOP (Transact-SQL)
　https://59log.com/?func=detail&id=148#
------------------------------------------------

補足

>>[名前検査]または[役職検索]の入力窓に入力せずにOKクリックで全てが表示対象になります

それって名簿ってテーブルをただ開いてるだけですよね？
目標値は結局クエリではなく名簿テーブルに追加してますよね？

なんの意味があるんですか？

>>尚、99件とはどのようなレコードでしょうか
結果自体はchayamatiさんが提示しているものであっています。
私が問題にしてるのは
「テーブル「名簿」の内容は変更できないものとします。」という質問本文の前提を無視していることです。
何度もいいますが、名簿テーブルの定義は変更しないでください。
この質問は結果を出力することが目的というより
目標値を入力・管理する工数を減らすことが目的です。
結果があってればいいわけじゃありません。

書いてて気づきましたが、chayamatiさんは前提は違ってもいいから出力結果があってればいいと思ったということでしょうか？
ちゃんと目的を質問本文に書いておけばよかったですね。すみません。
(ただ、何度も目的を補足で書いてるのに無視されるのでこまっているのですが）

回答No.8

chayamati

回答No.7の続きです
ワイルドカード「*」の位置には何があっても検索対象から外れるため
[[]内の文字列を検索対象とします
[名前検索]、[役職検索]に何も記述せずにOKとするとすべてのレコードが表示されます
また[名前検索]、[役職検索]をフォーム(例えばメニュー）に配置したテキストボックス、コンボボックスを
　Forms![メニュー]![名前検索]
　Forms![メニュー]![役職検索]
と置き換えることが出来ます。

回答No.7のsqlビューは
SELECT 名簿.ID, 名簿.名前, 名簿.役職, 名簿.目標値
FROM 名簿
WHERE (((名簿.名前) Like "*" & [名前検索] & "*") AND ((名簿.役職) Like "*" & [役職検索] & "*"));

補足

なにやらフォームで入力した名前で抽出をしていますが、
これでクエリの結果が99件になるのでしょうか？
質問の本文で書いている通り99件結果が得られるようにしたいと考えています

>>回答No.7のsqlビューは
SELECT 名簿.ID, 名簿.名前, 名簿.役職, 名簿.目標値
FROM 名簿
WHERE (((名簿.名前) Like "*" & [名前検索] & "*") AND ((名簿.役職) Like "*" & [役職検索] & "*"));

なぜ名簿テーブルに目標値という項目があるのか分かりません。

質問の本文にあるとおり名簿テーブルの項目は「ID」と「名前」と「役職」のみです。
質問の最後に『テーブル「名簿」の内容は変更できないものとします。』と書いてある通り、
回答No.4の補足や回答No.5の補足でも伝えた通り、名簿テーブルは変更できません。

どうやら、chayamatiさんはどうあっても、
名簿テーブルに目標値を直接設定すべきだという考えをお持ちのようですが

そもそもAというテーブルとBというテーブルをクエリで結合したいという質問に対し、
最初からAのテーブルに項目を追加して自分で入力したらいいという回答が返ってきたら、
本末転倒もいい所ではないですか？
それとも、chayamatiさんは結合するクエリに対する質問は
すべて大本のテーブルで項目を増やして、自分で入力すればいいと答えるのですか？

クエリの存在意義を理解しているのか疑問です。

たしかにT目標値のテーブルが20件のレコードが必要とかであれば、確かに
20件も目標値を入力するなら大本のテーブルの99件を修正した方がややこしくなくていい
となりますが、T目標値のレコードが5件で済むなら、大本のテーブルの99件を入力するより
T目標値で5件入力して、あとはクエリで解決させようと考えるのはそんなにおかしいですか？

おやおやそこはクエリで設定すべきか

回答No.7

chayamati

クエリ1
SELECT T名簿.ID, T名簿.名前, T名簿.役職, max(T目標値.ID) AS 目標値ID
FROM T名簿 LEFT JOIN T目標値 ON (Nz(T名簿.名前,"") Like Nz(T目標値.名前,"")) AND (Nz(T名簿.役職,"") Like Nz(T目標値.役職,""))
GROUP BY T名簿.ID, T名簿.名前, T名簿.役職;

クエリ2
SELECT クエリ1.ID, クエリ1.名前, クエリ1.役職, T目標値.目標値
FROM クエリ1 LEFT JOIN T目標値 ON クエリ1.目標値ID = T目標値.ID;
--------------------------------------------------------------------------------
・「JOIN」は同じテーブルはだめです」
・「like」 は　ワイルドカード「*」とともに使います。
　添付の抽出条件行を見てください。
　この行以下は抽出条件を記述します
　同一行は＆条件、行が異なればOR条件です。　

・また「GROUP BY」はSUM(目標値)のように「SUM」とともに使います

補足

No5の補足で私が書いたクエリの添削をしてくれたのでしょうか？

・「JOIN」は同じテーブルはだめです
そうですね。
ちなみにクエリ1は
「T名簿」と「T目標値」を結合し、
クエリ2は「クエリ1」と「T目標値」を結合しているので
同じテーブルではありません。

・「like」 は　ワイルドカード「*」とともに使います。
そうですね。
なので
T名簿.名前 Like T目標値.名前
と指定して「T目標値.名前」のレコードに「*」が入っていれば
T名簿.名前にどんな値が入っていても基本的に抽出できるようにしました。
ただ、NULLが含まれてると抽出できないので
NULLが含まれていても抽出できるようにするために

Nz(T名簿.名前,"") Like Nz(T目標値.名前,"")

としています。

・また「GROUP BY」はSUM(目標値)のように「SUM」とともに使います
そうですね。
ちなみにSUMだけでなくMAXという関数も使用できます。
なのでクエリ1ではMAXを使用していますね。

回答No.6

chayamati

＞さらにクエリの結果を含むとさらにとんでもない行数になってしまいとてもではないですが、正しい結果が得られそうにありませんでした。
！テーブル間にリレーションシップがないときは
　結果は2つのレコード件数の掛け算になります
　名簿の件数が99、もう一つのテーブルの件数が10件とすると
　99×10＝9900件ととんでもない件数になります。
！クエリのレコードソースは回答No.4の添付のように
「名簿テーブル」ともう一つありませんか
名簿テーブルの
　IDはオートナンバー型で主キー
　名前はユニーク設定になりませんか

お礼

すみません。補足を読み返していて、間違っている部分や分かりづらいところがあったので、
訂正いたします。

×「同一のT目標値のIDを集計クエリで１つにまとめ」
○「同一のT名簿のIDを集計クエリで１つにまとめ、T目標値のIDは最大のものを表示し」

×「部長、次長、と特定の人物以外の目標値4の設定は必ずT目標値.IDが一番小さい値にする必要がある」
○「下記のように名前と役職を「*」で指定する場合は必ずT目標値.IDが一番小さい値にする必要がある」
名前 役職 目標値
* * 4

補足

回答No.1についてですが、回答No.3で回答者本人が述べている通り、内容が間違っていたようです。
(途中まではほとんどあっていたので参考にはなりました)
本当は画像がついていたのですが、削除されています。

>>名簿テーブルの
>>IDはオートナンバー型で主キー
>>名前はユニーク設定になりませんか
その程度の変更は可能です。
というよりすでになっていると思います。

とりあえず、自分でもいろいろ試してみて次のようにクエリを組んでみました

まず「T目標値」テーブルを次のように設定し、クエリ1とクエリ2のクエリを2つ作成しました。
(クエリ1はデザインビューで表現できないのでSQLビューでコピーアンドペーストすると試すことが可能です)

ID 名前 役職 目標値
1 * * 4
2 * 部長 0
3 * 次長 0
4 名前3 * 2
5 名前5 * 2

クエリ1
SELECT T名簿.ID, T名簿.名前, T名簿.役職, max(T目標値.ID) AS 目標値ID
FROM T名簿 LEFT JOIN T目標値 ON (Nz(T名簿.名前,"") Like Nz(T目標値.名前,"")) AND (Nz(T名簿.役職,"") Like Nz(T目標値.役職,""))
GROUP BY T名簿.ID, T名簿.名前, T名簿.役職;

クエリ2
SELECT クエリ1.ID, クエリ1.名前, クエリ1.役職, T目標値.目標値
FROM クエリ1 LEFT JOIN T目標値 ON クエリ1.目標値ID = T目標値.ID;

これはNo2の補足で述べた方法の問題点
ID 名前 役職 目標値
1 * * 4
と
2 * 部長 0
というレコードが重複した条件として判定されてしまい
クエリの結果が99件を超えてしまうという問題を
同一のT目標値のIDを集計クエリで１つにまとめ、

そのクエリ結果にもう一度T目標値テーブルを結合しなおしたものとなります。

これで一応できているのですが、
T目標値.IDが大きい方が優先されるため、
部長、次長、と特定の人物以外の目標値4の設定は必ず
T目標値.IDが一番小さい値にする必要があるという問題があります。
一応下記のようにT目標値.IDの1番目に設定し
ID 名前 役職 目標値
1 * * 4

その下のレコードに部長、次長、特定の人物の条件を追加することで実装はできました。

できればクエリを1つにまとめ、
T目標値のIDの順番に依存しない形で作りたかったのですが、難しそうですかね。

回答No.5

chayamati

「おやおや」の気持ち分かっていただきましたか
最初目標値は役職毎に固定のものと思ってました。
なので、昇級すると目標値も変更するものと思っていましたが
質問の終わりの方に、役職がNullの時目標値が0,2,4があり、任意のような気がします。
目標値は役職で決まるのでなく
職種（事務、製造、営業）学歴、年齢等他の要素のようですね

余計なことを考えずに、
名簿テーブルに目標値の項目を追加するだけではだめですか

補足

>>目標値は役職で決まるのでなく職種（事務、製造、営業）学歴、年齢等他の要素のようですね

正確には、部長、次長は目標値0固定で、
それ以外はほとんどが目標値4なのですが
例外で目標値2の人が数名います。

なので、部長、次長を目標値0、
特定の人物を目標値2
と設定し、それ以外を目標値4と設定したいのです。

>>名簿テーブルに目標値の項目を追加するだけではだめですか
名簿テーブルは私の管理ではないので、勝手に設定することはできないですし、設定すると何十もの目標値を設定する必要があり、変動するたび、名簿テーブルを管理している人に修正してもらう必要が出てきます。

そのため、最初の質問にある通り
テーブル「名簿」の内容は変更できないものとします。

回答No.4

chayamati

添付のように役職テーブルを追加して目標値を役職テーブルに入れます
名簿テーブルのデザインビューで役職テーブルをルックアップします。
おやおや目標値は名簿テーブルに定義すべきか

補足

この方法だと部長や次長に対して目標値0を設定することはできますが、名前3、名前5に対して目標値2を設定することができていません。

>>おやおや目標値は名簿テーブルに定義すべきか
名簿テーブルは部内で共有で仕様しているアクセスファイルからリンクテーブルで持ってきていて、勝手に設定することができません。
仮にできたとして、何十の数値を設定してそれを逐次修正したり管理するのは面倒です。
その為、部長、次長は目標値0、特定の人物（名前3、名前５)は目標値2、それ以外は4という最低限の設定でテーブルで設定して、クエリで表示したいです。

回答No.3

kkkkkm

すみませんNo1は間違ってましたので、無視してください。

回答No.2

kkkkkm

No1の追加です。
書き忘れてましたが、テーブル「目標値」で「*」などワイルドカードを使うと、クエリの条件に入れるときなどに="*"とせずに単純に"*"としたらLike "*"になってしまったりしますし、後々他の個所でうっかり勘違いしそうな気もします。

補足

おっしゃる通りクエリの抽出部分に「*」を使用すると自動で「Like "*"」になりますが、例えば
抽出条件に
Where 句にNz([T名簿].[名前],"") like Nz([T目標値].[名前],"")
と指定したり
デザインビューで表現はできないですが
LEFT JOINのON句に
Nz([T名簿].[名前],"") like Nz([T目標値].[名前],"")
と指定してうまくテーブルの「*」をLike演算子でうまくできないかと思って行っています。
※テーブル名に分かりやすく先頭にTをつけました

例えば下記の例です(デザインビューで表現できないのでSQLビューでコピーアンドペーストすると試すことが可能です)

SELECT [T名簿].[ID], [T名簿].[名前], [T名簿].[役職], [T目標値].[目標値] AS 目標値
FROM [T名簿] LEFT JOIN T目標値 ON (Nz([T名簿].[名前],"") like Nz([T目標値].[名前],"")) AND (Nz([T名簿].[役職],"") like Nz([T目標値].[役職],""));

この例では「T目標値」の
* 部長 0
と
* * 4
が重複した条件と判定されてしまうため、
レコードが99件を超えてしまい失敗しました。

回答No.1

kkkkkm

名前3 * 2
名前5 * 2
の役職の所はNULL(もしくは*以外)にしておいてください。

添付画像のクエリ(2個)を作成してクエリ1で結果を試してみてください。

この投稿のマルチメディアは削除されているためご覧いただけません。

補足

今まで返信できなくてすみません。
Accessに触れる機会がなくて確認ができませんでした。
実際に試してみましたが、２つテーブルと1つのクエリの直積(交差結合)から条件で抽出しているようですが、2つのテーブルの直積の時点で99×5=495行ほどのレコードが存在し、さらにクエリの結果を含むとさらにとんでもない行数になってしまいとてもではないですが、正しい結果が得られそうにありませんでした。