ACCESSで3ファイルを結合して、表を作成するやり方を教えて下さい。

企業テーブルのIDをキーに、個人情報テーブル①と②が同じメアドの場合は横並びで、違う場合は隣は空欄にしたく、メアドがどちらもない場合は、企業名だけ表示させたいと思っております。

▼企業情報テーブル
ID　会社名
１　あああ
２　いいい
３　ううう
４　えええ

▼個人情報テーブル①
ID　①のメアド
１　[e-mail]
１　[e-mail]
２　[e-mail]

▼個人情報テーブル②
ID　②のメアド
１　[e-mail]
２　[e-mail]
２　[e-mail]
３　[e-mail]

▼アウトプットイメージ
ID　会社名　①のメアド　②のメアド
１　あああ　[e-mail]　空欄
１　あああ　[e-mail]　[e-mail]
２　いいい　[e-mail]　空欄
２　いいい　空欄　　　　[e-mail]
３　ううう　空欄　　　　[e-mail]
４　えええ　空欄　　　　空欄

実際にIDを結合してクエリを実行すると
以下のように同じメアドが繰り返され空欄にはならず
①と②のメアドが同じものが横並びになりません。。

ID　会社名　①のメアド　②のメアド
１　あああ　[e-mail]　[e-mail]
１　あああ　[e-mail] [e-mail]
２　いいい　[e-mail]　[e-mail]
２　いいい　[e-mail]　[e-mail]
３　ううう　空欄　　　　[e-mail]
４　えええ　空欄　　　　空欄

アウトプットイメージのように出すのは難しいでしょうか？

No.7 回答者： 忠犬 回答日時： 2022/08/17 21:41

ACCESSでなく、PostgreSQLやMySQLなどの標準SQL準拠のRDBMSで確認のテストに用いた表定義です。

最初にSQLを提示した時は、kojin1とkojin2で同じ列名を使っていましたが、同じ列名にしないようにしました。

CREATE TABLE kigyo
(id INT PRIMARY KEY
,namae VARCHAR(30));

CREATE TABLE kojin1
(id1 INT
,email1 VARCHAR(30));

CREATE TABLE kojin2
(id2 INT
,email2 VARCHAR(30));

上記の表定義で目的の結果を得る検索です。

-- COALESCE(id1,id2) AS idは
-- ACCESSなら、
-- IIF(id1 IS NULL,id2,id1) AS id

SELECT
kg.id,namae,email1,email2
FROM kigyo AS kg
LEFT JOIN
(
SELECT
COALESCE(id1,id2) AS id
,email1,email2
FROM(
SELECT
id1,email1,id2,email2
FROM kojin1
LEFT JOIN kojin2 ON id1=id2 AND email1=email2
UNION
SELECT
id1,email1,id2,email2
FROM kojin1
RIGHT JOIN kojin2 ON id1=id2 AND email1=email2
) AS x
) AS y
ON kg.id=y.id
ORDER BY kg.id,email1,email2
;

No.6 回答者： 忠犬 回答日時： 2022/08/17 18:02

↓下の情報を提示できますか？

この回答へのお礼

No.5の返信の追記です。
フィールドサイズについてですが、
id,id1,id2が倍精度浮遊小数点型
となっておりまして、
namae,email1,email2は255
となっておりました。

また、記載いただいたSQLを実行したのですが、
「CREATE TABLE ステートメント構文エラーです。
OK　ヘルプ」
とポップアップが出てしまいました。

ーーーーーーーーーーーーーーーーーーーーーーーーー
CREATE TABLE 企業情報
(id INT PRIMARY KEY
,namae VARCHAR(30));

CREATE TABLE 個人情報A
(id1 INT
,email1 VARCHAR(30));

CREATE TABLE 個人情報B
(id2 INT
,email2 VARCHAR(30));

SELECT kg.id, namae, email1, email2
FROM 企業情報 AS kg LEFT JOIN (SELECT IIF(id1 IS NULL,id2,id1) AS id, email1, email2 FROM (SELECT k1.id AS id1,k1.email AS email1 ,k2.id AS id2,k2.email AS email2 FROM 個人情報A AS k1 LEFT JOIN 個人情報B AS k2 ON k1.id=k2.id AND k1.email=k2.email UNION SELECT k1.id AS id1,k1.email AS email1 ,k2.id AS id2,k2.email AS email2 FROM 個人情報A AS k1 RIGHT JOIN 個人情報B AS k2 ON k1.id=k2.id AND k1.email=k2.email ) AS x) AS y ON kg.id=y.id
ORDER BY kg.id, email1, email2;

本当に何度も申し訳ないですが、もし原因などお分かりなるようでしたら
ご教示いただけますと大変助かります。

どうぞよろしくお願いいたします。

お礼日時：2022/08/18 09:52

No.5 回答者： 忠犬 回答日時： 2022/08/17 18:00

(1)最新の表ごとの列名とデータ型、データ長。

→テストは質問で提示されたデータでやってるのですか？
(2)最新のSELECT文
→「式で型が一致しません」と返されたもの。エラー箇所(エラー位置、列名など)などを示す情報は表示されてないのでしょうか？

この回答へのお礼

早々にご回答いただいていたのにご返信がおそくなり大変申し訳ありません。

(1)最新の表ごとの列名とデータ型、データ長。
企業情報
id 数値型
name 短いテキスト

個人情報A
id 数値型
email1 短いテキスト

個人情報B
id 数値型
email1 短いテキスト

データ長というのが分からなかったのですが
フィールドサイズというのがあり、それはいずれも
倍精度浮遊小数点
となっております。

テストは質問させていただいたデータでやっております。

エラーに関しては、
ポップアップで

式で型が一致しません
OK ヘルプ

とでてきて、ヘルプをクリックすると
マイクソフトのサポートページに飛び
そこに
JOIN式で型が一致しません。エラー3615

と出てきます。

NO7でご回答いただいたものも試させていただきます。
本当にありがとうございます。

お礼日時：2022/08/18 09:22

No.4 回答者： 忠犬 回答日時： 2022/08/17 16:42

いくつかのクエリの組み合わせで構成されているので、各クエリをパーツとして動作確認してからパーツを組み立ていくといいと思います。

その中ではACCESSでは一つのSQLとしては記述できず、パーツのクエリの結果を作業用の表を作って保存したりする必要があるかも知れません。

サンプルのSQLでは、表の列構成をidとemailにしました。
個人情報Aと個人情報Bという表があるのですね。
それぞれの列構成は、私のサンプルと同じ(あるいは合わせて)idとemailという列構成になっているのでしょうか？

二つの表に同じ列名があるので、「AS 別名」がたくさんあったり、「k1.id」などのように列名を表名で修飾する箇所が多くなっています。
例えば列名を一意に識別できるようにすれば、別名を付けたり、表名での修飾箇所を大幅に減らせます。

個人情報A(id1,email1)
個人情報B(id2,email2)

例えばパーツ1は、次のようになります。
SELECT
id1,email1,id2,email2
FROM 個人情報A
LEFT JOIN 個人情報B ON id1=id2 AND email1=email2

(1)パーツ1
SELECT
k1.id AS id1,k1.email AS email1
,k2.id AS id2,k2.email AS email2
FROM 個人情報A AS k1
LEFT JOIN 個人情報B AS k2 ON k1.id=k2.id AND k1.email=k2.email

(2)パーツ2
SELECT
k1.id AS id1,k1.email AS email1
,k2.id AS id2,k2.email AS email2
FROM 個人情報A AS k1
RIGHT JOIN 個人情報B AS k2 ON k1.id=k2.id AND k1.email=k2.email

(3)パーツ3
パーツ1
UNION
パーツ2

(4)パーツ4　というように続く

この回答へのお礼

とてもご丁寧な説明、本当にありがとうございます。
ご指摘いただいた通り、テーブルの項目名が
id,namae,email1,email2
になってませんでした。。

テーブルを修正して再度実行したところ
「式で型が一致しません」
とでるのですが、原因お分かりなりますでしょうか？

何度も申し訳ございませんが、ご教示いただけますと
大変ありがたいです。

お礼日時：2022/08/17 17:49

No.3 回答者： 忠犬 回答日時： 2022/08/17 14:48

COALESCE(式1,式2,・・・,式n)は、標準SQLにもなっていて、リスト中の最初のNULLでない値を返すもので、ACCESSでは未実装は知っていました。

COALESCE(id1,id2) AS id
は、ACCESSでは
IIF(id1 IS NULL,id2,id1) AS id
と書き換えられます。

この回答へのお礼

早々にご回答いただきありがとうございます。

記載いただいたものに書き換えたところ

パラメーターの入力
k1.id
入力エリア

と出てくるのですが、どうしたらいいでしょうか。。
何度も申し訳ございません。。

以下のように記載しております。

SELECT
kg.id,namae,email1,email2
FROM 企業情報 AS kg
LEFT JOIN
(
SELECT
IIF(id1 IS NULL,id2,id1) AS id,email1,email2
FROM(
SELECT
k1.id AS id1,k1.email AS email1
,k2.id AS id2,k2.email AS email2
FROM 個人情報A AS k1
LEFT JOIN 個人情報B AS k2 ON k1.id=k2.id AND k1.email=k2.email
UNION
SELECT
k1.id AS id1,k1.email AS email1
,k2.id AS id2,k2.email AS email2
FROM 個人情報A AS k1
RIGHT JOIN 個人情報B AS k2 ON k1.id=k2.id AND k1.email=k2.email
) AS x
) AS y
ON kg.id=y.id
ORDER BY kg.id,email1,email2

何度も申し訳ありませんが、、
ご回答いただけますと大変ありがたいです。
どうぞよろしくお願いいたします。

お礼日時：2022/08/17 15:40

No.2 回答者： 忠犬 回答日時： 2022/08/16 22:39

アウトプットイメージで[e-mail]が抜けてますね。

1.FULL JOINを使うと簡単だが、ACCESSでは実装されてないみたい。
(1)個人情報1と2を、(id,メアド)の二つの列を結合条件にFULL OUTER JOINする。
(2)会社情報と(1)をLEFT JOINをID列を結合条件にLEFT JOINする。この時、(1)には個人情報1と2の両方にid列があり、片方はNULLになっている行がある。IDがNULLでない方で結合する。

2.FULL JOINを使わない実装方法
LEFT JOINとRIGHT JOINを行い、UNIONする。UNION ALLにはしない。

3.FULL JOINを使わない実装方法
ただしACCESSでは未実装の標準SQLの関数などを使っている。
→代替の関数があったはず
他にも未実装の部分があるかもしれない。

SELECT
kg.id,namae,email1,email2
FROM kigyo AS kg
LEFT JOIN
(
SELECT
COALESCE(id1,id2) AS id,email1,email2
FROM(
SELECT
k1.id AS id1,k1.email AS email1
,k2.id AS id2,k2.email AS email2
FROM kojin1 AS k1
LEFT JOIN kojin2 AS k2 ON k1.id=k2.id AND k1.email=k2.email
UNION
SELECT
k1.id AS id1,k1.email AS email1
,k2.id AS id2,k2.email AS email2
FROM kojin1 AS k1
RIGHT JOIN kojin2 AS k2 ON k1.id=k2.id AND k1.email=k2.email
) AS x
) AS y
ON kg.id=y.id
ORDER BY kg.id,email1,email2
;

この回答へのお礼

ご丁寧に説明してくださりありがとうございます。
ご指摘いただいた通り「[e-mail]」が抜けておりました。。
すいません。。

初心者のため、丁寧にご説明いただき大変助かります。

記載いただいたコードで試したところ、「式に未定義関数’COALESCE’があります」と出てきてしまうのですが、もし可能でしたらこの部分の解決方法を教えていただけないでしょうか。

どうぞよろしくお願いいたします。

お礼日時：2022/08/17 09:57

No.1 回答者： 初心者ですが何か。 回答日時： 2022/08/16 05:32

経験皆無です。

https://www.helpforest.com/access/ob_query/ac050 …

この回答へのお礼

ご回答ありがとうございます。
参考にさせていただきます。

お礼日時：2022/08/16 11:23