Access テーブルでyes/no型をjoinしたときnullにしたい

Question

Access2007において、データなしを外部結合したときデータがnullになるようにしたいです。

文書マスタ

文書番号	タイトル
1	取扱説明書
2	仕様書
3	領収書

文書廃止テーブル

文書番号	廃止済
1	true
2	false

(3番は状況不明であり未登録)

このとき、文書マスタに文書廃止テーブルをleft joinしたとき以下のようになってほしいです。
が、廃止済をyes/noで定義するとfalseが入ってしまい、実情と違うデータになってしまいます。

文書番号	タイトル	廃止済
1	取扱説明書	true
2	仕様書	false
3	領収書	null

Accessのフォームにおけるチェックボックスではトリプルステートがつかえるようなのですが、
ここにける「廃止済」のような情報で（tableやクエリ上において）nullを許容するyes/no型というものを実現する方法はあるのでしょうか。

---

文書マスタが下のようなものでした。
文書Rev管理テーブル

文書番号	タイトル	rev
1	取扱説明書	1
1	取扱説明書	2
2	仕様書	1
3	領収書	1

文書マスタ　（マスタと名前を付けているのにクエリでした、すみません。）

SELECT B.文書番号, B.タイトル
FROM (SELECT 文書番号, max(rev) AS mRev FROM 文書Rev管理 GROUP BY 文書番号)  AS A LEFT JOIN 文書Rev管理 AS B ON (A.mRev=B.Rev) AND (A.文書番号=B.文書番号);

問題のクエリ

SELECT a.*, b.廃止済
FROM 文書マスタ AS a LEFT JOIN 文書廃止テーブル AS b ON a.文書番号=b.文書番号;

Answer 1

普通に LEFT JOIN でデータ無しはNullになります。
Falseになっているというのはどのように確認しましたか。

また、実際のSQLも提示してください。

当方で実験したSQL

SELECT 文書マスタ.文書番号, 文書マスタ.[タイトル], 文書廃止テーブル.廃止済
FROM 文書マスタ LEFT JOIN 文書廃止テーブル ON 文書マスタ.文書番号 = 文書廃止テーブル.文書番号;

クエリ表示結果

チェックボックスが ■ はNull

追記

編集、追記されたテーフル情報、SQLをみて
クエリと結合するとFalseになることを確認しました。
下記のようなSQLで対処することになりそうです。

SELECT a.*, IIf(b.文書番号 Is Null,Null,b.廃止済) AS 廃止済
FROM 文書マスタ AS a LEFT JOIN 文書廃止テーブル AS b ON a.文書番号=b.文書番号;

ただ、そもそものテーブル設計が間違っていると思います。

文書番号 と REV は一対多の関係です。
また、文書番号 と 廃止済 は一対一の関係です。

一対多の関係のデータはテーブルを分割すべきです。
一対一の関係のデータはひとつのテーブルにまとめるべきです。
これはデータベース設計のセオリーです。

上記を考慮すると下記のような設計になります。

文書マスター

文書番号	タイトル	廃止済み
1	取扱説明書	True
2	仕様書	False
3	領収書	Null

文書Rev管理

文書番号	rev
1	1
1	2
2	1
3	1

このような設計にすれば、「文書マスター」クエリのような複雑なSQLは不必要になりますし、データとして格段に扱いやすくなるはずです。

---

あと、廃止済み フィールドで、存続, 廃止済み, 状況不明 という３つの状況を記録したいなら、Yes/No型でなく、数値型にして、
0：存続
1：廃止済み
2：状況不明
というようにするのが意味か明確になると思います。
Nullだと、状況不明なのか入力忘れなのか曖昧になります。
入力方法としてはコンボボックスとかオプショングループにすると分かりやすくなります。

Comments

[tokita.]

回答ありがとうございます。
おっしゃる通り、文書マスタが正しくマスタテーブルなら問題は起きないようです。
実は文書マスタと謳っていたものが別のテーブルを集計したクエリでした。
質問が不適当で申し訳ありません。

[tokita.]

追記ありがとうございます。
文書番号でnullを確認すれば確かに動きました。
テーブル設計に関しては、最近変な苦労をたくさんしているので、見直しを検討します。

ところで、質問表題とは別の質問になるのですが、
このDBでは、Revごとに様々な情報があり、それを全部保管しつつ、
実際の操作では最新Revの情報のみ見るということがしばしば求められます。

この場合、文書マスターと最新Revを関連付ける方法としては、
方法1. 文章番号と最新のRevを1:1で関連付けるテーブルを作成して、Revデータが増えるたびに関連付け用テーブルを同時にupdateする
方法2. 文書マスターに最新Revを記録するフィールドを設けて、Revデータが増えるたびに文書マスターテーブルを同時にupdateする
方法3. 上のような情報は保管せずに、結合時、上質問で文書Rev管理テーブルに対してやっていたようなSQLを使って最新を取得する
方法4. Revテーブルに最新Revを識別するフィールドを設けて、Revデータが増えるたびに前のRevデータの識別をoffにする
というような方法があると考えました。

方法1,2では登録時に2つのテーブルに処理が必要になる
方法3では呼び出し時に複雑なSQLが残る
方法4では同じテーブルであるものの登録時にinsertとupdateを要する(手間としては方法1,2とあまり変わらない)
と一長一短で、悩んでおります。どういった方法が好ましく思われますか？

[hatena19]

文書に関するデータで、
一つの文書（文書番号）に対して、
一つしかないデータ、一つあればよいデータは文書マスタに。
一つの文書（文書番号）に対して、変更の履歴(Revデータ)を残しておく必要があるデータは文書Rev管理に。
と考えればいいでしょう。

文書番号に対して、最新のRevデータを取り出すには、サブクエリやDMax関数を抽出条件にするとか、SQLを工夫すれば可能です。

ただ、複雑になったり、重い処理になる可能性もあるので、文書マスタ の方に最新Rev番号フィールドを持たせるのもありだと思います。
これなら、文書番号同士、最新RevとRevを結合するクエリで簡単に目的のデータを取得できます。
方法2 になりますね。
「Revデータが増えるたびに文書マスターテーブルを同時にupdateする」の部分は、
Revデータを入力する場合は、メインフォームのソースを文書マスタ、サブフォームのソースを Revデータにしておいて、サブフォームの新規レコード追加時に、VBAでメインフォームの最新Rev番号を更新するようにすればいいでしょう。

[tokita.]

よくわかりました。
その方法でやってみようと思います
ありがとうございます。