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.1 回答者： 初心者ですが何か。 回答日時： 2022/08/16 05:32

経験皆無です。

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

この回答へのお礼

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

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

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.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.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.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.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.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.8 回答者： 忠犬 回答日時： 2022/08/18 15:21

表定義の列名(フィールド名)と、SELECT文が不整合になっているようです。

ACCESSでは、クエリ、ユニオンクエリなど別に作ることができるようですが、現状のSELECT文(クエリ)は、No.3のお礼で提示されたものから変更はないですか？

No.3のお礼のSELECT文は、表の列名(フィールド名、項目名)は、次のようになっている前提のものです。

企業情報→id,namae
個人情報A→id,email
個人情報B→id,email

No.4の回答で、各表の列名を一意にして同じ名前の列名をなくせば、SELECT文で「列名 AS 別名」や「表名.列名」といった列名に別名を付けたり、表名で修飾する必要がなくなることを説明し、SELECT文のパーツ例を示しました。

No.4のお礼で、テーブルの項目名を
id,namae,email1,email2
にしてなかった。
テーブルを修整して実行したら、「式で型が一致しません」になった。

No.5のお礼で最新の列名とデータ型について
企業情報→id,name
個人情報A→id,email1
個人情報B→id,email1

No.6のお礼では、No.5のお礼の追記として
id,id1,id2が倍精度〜
namae,email1,email2は255

お礼によって列名(項目名)がバラバラ、不一致になっています。
項目の定義やクエリの選択項目など、別々の方法で確認しているからなのでしょうか？

いずれにしても、一度、リセットする必要があると思います。
表定義、SELECT文(ACCESSのクエリやユニオンクエリなど複数あるならそれらも)を整理しましょう。

(1)企業情報、個人情報A、個人情報Bの最新というかこの項目名でやりたいというのを提示してください。
(2)クエリなど作成したものは、一旦、削除してください。
(3)(1)を反映したSELECT文を再度、提示します。
　その場合、SELECT文をパーツに分けて、段階的に確認してもらう方法にするかも知れません。
どこでエラーになっているか、その方が調べやすいですからね。

この回答へのお礼

ご丁寧に整理までしていただきありがとうございます。
そして大変申し訳ございません。。
CREATEと書かれた文を試した際に、SELECT文は変わらないと思い込み
SELECT文は以前のままにしておりました。。

以下にテーブル名と項目を記載致します。

テーブル名：企業情報
id　数値型　倍精度浮遊小数点
namae　短いテキスト　255

テーブル名：個人情報A
id1　数値型　倍精度浮遊小数点
email1　短いテキスト　255

テーブル名：個人情報B
id2　数値型　倍精度浮遊小数点
email2　短いテキスト　255


何度もお手数おかけして申し訳ございませんが
どうぞよろしくお願いいたします。

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

No.9 回答者： 忠犬 回答日時： 2022/08/19 18:36

お待たせしました。

列名(項目名)を確認しての再提示です。
(1)から順番に動くか試してみてください。
動かない場合は、その次には進まないでください。

(1)個人情報Aにはあり、個人情報Bにはないものを含めて結合

SELECT
id1,email1,id2,email2
FROM 個人情報A
LEFT JOIN 個人情報B
ON id1=id2 AND email1=email2
;

(2)個人情報Bにはあり、個人情報Aにはないものを含めて結合

SELECT
id1,email1,id2,email2
FROM 個人情報A
RIGHT JOIN 個人情報B
ON id1=id2 AND email1=email2
;

(3)(1)と(2)の結果を縦につなげる。両方にあるものは重複排除(一つだけ得る)

SELECT
id1,email1,id2,email2
FROM 個人情報A
LEFT JOIN 個人情報B
ON id1=id2 AND email1=email2
UNION
SELECT
id1,email1,id2,email2
FROM 個人情報A
RIGHT JOIN 個人情報B
ON id1=id2 AND email1=email2
;

(4)企業情報と(3)を結合。企業情報だけにあるものを含む。
-- COALESCE(id1,id2) AS id
-- ACCESSなら、
-- IIF(id1 IS NULL,id2,id1) AS 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
id1,email1,id2,email2
FROM 個人情報A
LEFT JOIN 個人情報B
ON id1=id2 AND email1=email2
UNION
SELECT
id1,email1,id2,email2
FROM 個人情報A
RIGHT JOIN 個人情報B
ON id1=id2 AND email1=email2
) AS x
) AS y
ON kg.id=y.id
ORDER BY kg.id,email1,email2
;

この回答へのお礼

ご丁寧に対応くださり、本当にありがとうございます。

(1)個人情報Aにはあり、個人情報Bにはないものを含めて結合
→この段階で
「JOIN式はサポートされていません
　OK　　ヘルプ」
とポップアップがでたのですが、ご確認いただけますでしょうか。
申し訳ありません。。

お礼日時：2022/08/19 18:46

No.10 回答者： 忠犬 回答日時： 2022/08/19 19:01

ACCESSの構文に特殊な制約があるみたいですね。

ON句での条件式を、()で囲んでやってみてもらえますか？

ON (id1=id2 AND email1=email2)

この回答へのお礼

早々にご返信ありがとうございます。
申し訳ありません。。また
「JOIN式はサポートされていません
　OK　　ヘルプ」
と出てしまいました。

ーーーーーーーーーー
SELECT
id1,email1,id2,email2
FROM 個人情報A
LEFT JOIN 個人情報B
ON (id1=id2 AND email1=email2)
;

お礼日時：2022/08/19 22:38