accessでクロス検索のような挙動をしたい

Question

実現したいこと

accessにて次のような2つのテーブルがあります。

テーブルA（階級と勤務地の直積）
階級,勤務地
平,東京
平,福岡
平,大阪
係長,東京
係長,福岡

テーブルB（階級と勤務地の組み合わせから給与を示すテーブル）
階級,東京,福岡,大阪
平,200000,100000,150000
係長,300000,200000,250000

この2つのテーブルから以下のような表示をするクエリを作成したいです。
階級,勤務地,金額
平,東京,200000
平,福岡,100000
平,大阪,150000
係長,東京,300000
係長,福岡,200000

発生している問題・分からないこと

IIFの入れ子構造で作成できそうだということは確認できたのですが、
実際のデータは階級と勤務地にあたるものの数がとても多いため、
それ以外の方法で作成したいと考えています。

該当のソースコード

特になし

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

上記の詳細・結果

IIFの入れ子構造で作成できはすること

補足

Access 2007 - 2016　です。

Comments

[logres_Fan]

> この2つのテーブルから以下のような表示をするクエリを作成したいです。
　この場合、クエリ作成じゃなくて、１つのテーブルにまとめる作業を検討するんじゃないかな？クロス表を土台にクエリなどを組み上げる事はやりません。

Answer 1

直接の回答ではありません。
テーブルBと作りたいというクエリは同じものなので、クエリでどうこうではなく、テーブルの作り直しが必要なように思います。
私なら以下のようなテーブルを作成して、給与に関しては手入力します。
金額が、階級と勤務地の２つに依存しているからです。

テーブル

[階級]テーブル

階級ID	階級名
1	平
2	係長

[勤務地]テーブル

勤務地ID	勤務地名
1	東京
2	福岡
3	大阪

[給与]テーブル

給与ID	階級	勤務地	金額
1	1	1	200000
2	1	2	100000
3	1	3	150000
4	2	1	300000
5	2	2	200000
6	2	3	250000

クエリ

SELECT 階級ID, 階級名, 勤務地ID, 勤務地名, 金額 FROM 階級 RIGHT JOIN (勤務地 RIGHT JOIN 給与 ON 勤務地.勤務地ID = 給与.勤務地) ON 階級.階級ID = 給与.階級

どうしても元のテーブルでクエリを作成するのであれば、以下のようになるでしょうか。

SELECT 階級, 勤務地, IIf(勤務地 = "東京", DLookup("東京", "テーブルB", "階級='" & 階級 & "'"), IIf(勤務地 = "福岡", DLookup("福岡", "テーブルB", "階級='" & 階級 & "'"), DLookup("大阪", "テーブルB", "階級='" & 階級 & "'"))) AS 金額 FROM テーブルA

金額が階級か勤務地のどちらかと結合可能か、階級と勤務地から導き出せる係数のようなものが無い以上、現状のテーブル構成では難しいと思います。私が無知なだけかもしれませんが。
というよりも、テーブルBと作成したいクエリは同じものなので、DBでどうこうする必要はありません。
おとなしく手入力するか、入力フォームを作成するくらいでしょうか。

追記

他の方の回答でズバリできていますね笑
失礼いたしました。
ついでに入力フォームはこんな感じになります。

Comments

[tomikawa]

フォームの例なども提示いただきありがとうございます。
参考にさせていただきます。

Answer 2

テーブルBを正規化すれば良いので、ユニオンクエリーを用います。

select 階級, '東京' as 勤務地, 東京 as 金額 from テーブルB
union all select 階級, '福岡' as 勤務地, 福岡 as 金額 from テーブルB
union all select 階級, '大阪' as 勤務地, 大阪 as 金額 from テーブルB

上記をテーブルAと階級と勤務地で結合すれば意図する結果の表示になります。

Comments

[tomikawa]

お教えいただいた方法で望む動作を実現できました。
こちらの内容が最もシンプルに実現できそうなため、こちらをベストアンサーにさせていただきます。
ありがとうございました。

Answer 3

現状の正規化されていないテーブルから、クエリで正規形に変換して、それから正規形のテーブルを作成したいということでしょうか。
データベースで現状のテーブルのまま運用をするのは効率が悪すぎるので。

クエリでできないことはないですが、どうせテーブルにするのなら、
VBA(DAOかADO)でテーブルAとテーブルBをレコードセットとして読み込んで、正規形のテーブルに書き込んでいくという処理を作成した方がシンプルかと思います。

---

よく考えたら、DLookup関数を使えばクエリでも簡単にできますね。

SELECT
 テーブルA.階級, テーブルA.勤務地,
 DLookUp([勤務地],"テーブルB","階級='" & [階級] & "'") AS 金額
FROM テーブルA;

このクエリからテーブル作成クエリでテーブル化すればいいでしょう。

Comments

[tomikawa]

「クエリで求める形式を出したい」という要望に答えていただきありがとうございます。
参考にさせていただきます。