INSERT INTO と UNION ALL の Accessでの使い方について

Question

前提・実現したいこと

ExcelだったデータをAccessに移植した古いデータベースがあります。
ベースがExcelだったため、ピボットテールブルのような扱いをしており、
以下のような構造になっています。
ID　案件名　4月度売上　5月度売上　6月度売上
1 　AAA　　 100　　　　120　　　　130
2 　BBB　　 140　　　　150　　　　160
3　 CCC 　　 170　　　　180　　　　190
…

これをデータベースとして扱いやすいテーブル構造に変換するため、
1つの1年分のレコードを12分割し、以下のようなテーブルを作ろうと思っています。
ID 案件名 月度　売上
1 AAA　4月 100
2 AAA　5月 120
3 AAA　6月 130
…
13　BBB　4月　140
14　BBB　5月 150
…

しばらくは古いデータから定期的に新しいテーブルに
データを更新・インポートしたく、AccessのクエリをSQLで構築中なのですが
1カ月分のクエリは作れても、12カ月全部を一度に実行するクエリがうまく動作できません。
INSERT INTOとUNION ALLの組み合わせで、Access用の書き方が間違っているのかと思いますが、あまり例文も無く、躓いてしまいました。エラーが出ないようにするためには
どのようにSQLのクエリを記載すればよろしいでしょうか。

■1カ月分の追加クエリ（動作する）

INSERT INTO Newテーブル ( OldID, 案件名, 年度, 月度, 売上 )
SELECT
 [Oldテーブル].ID,
 [Oldテーブル].案件名,
 [Oldテーブル].年度,
 '4月',
 [Oldテーブル].[4月度売上]
FROM
 Oldテーブル LEFT JOIN Newテーブル ON [Oldテーブル].ID = Newテーブル.OldID
 WHERE ([Oldテーブル].年度 >= 2021 AND NOT EXISTS (SELECT ＊ FROM Newテーブル WHERE
 Newテーブル.OldID = Oldテーブル.ID));

■2カ月分の追加クエリ（動作しない）※本当は12カ月分やりたい

INSERT INTO Newテーブル ( OldID, 案件名, 年度, 月度, 売上 )
SELECT
 [Oldテーブル].ID,
 [Oldテーブル].案件名,
 [Oldテーブル].年度,
 '4月',
 [Oldテーブル].[4月度売上]
FROM
 Oldテーブル LEFT JOIN Newテーブル ON [Oldテーブル].ID = Newテーブル.OldID
 WHERE
 ([Oldテーブル].年度 >= 2021 AND NOT EXISTS (SELECT ＊ FROM Newテーブル WHERE Newテーブル.OldID = Oldテーブル.ID))

UNION ALL

SELECT
 [Oldテーブル].ID,
 [Oldテーブル].案件名,
 [Oldテーブル].年度,
 '5月',
 [Oldテーブル].[5月度売上]
FROM
 Oldテーブル LEFT JOIN Newテーブル ON [Oldテーブル].ID = Newテーブル.OldID
 WHERE ([Oldテーブル].年度 >= 2021 AND NOT EXISTS (SELECT ＊ FROM Newテーブル WHERE
 Newテーブル.OldID = Oldテーブル.ID));

のように記述しましたが、エラーでクエリを保存できません。

発生している問題・エラーメッセージ

保存しようとすると以下のようなエラーが出ます。

クエリ式 '([Oldテーブル].年度 >= 2021 AND NOT EXISTS (SELECT ＊ FROM Newテーブル WHERE Newテーブル.OldID = Oldテーブル.ID))

UNION ALL

SELECT
 [Oldテーブル].ID,
 [Oldテーブル].案件名,
 [Oldテーブル].年度,
 '5月',
 [Oldテーブル].[5月度売上]
FROM
 Oldテーブル LEFT JOIN Newテーブル ON [Oldテーブル].ID = Newテーブル.OldID
 WHERE ([Oldテーブル].年度 >= 2021 AND NOT EXISTS (SELECT ＊ FROM Newテーブル WHERE
 Newテーブル.OldID = Oldテーブル.ID))'の 構文エラー：演算子がありません

試したこと

本来は、UpdateとRIGHT JOINで更新・追加を同時にしたいと思っていたのですが、
新しいデータベース側のSQL命令でRIGHT JOINが用意されてなく、
LEFT JOINしか対応しないために、更新しかできず追記がどうしても出来なかったため、
今は更新と追記を別々のクエリで作ってみています。

補足情報（FW/ツールのバージョンなど）

古いデータベースはAccessのデータベースで、新しいデータベースは
kintoneのデータベースをCData ODBC Driverでリンクテーブルとして読み込んでいます。

Comments

[sazi]

> 新しいデータベース側のSQL命令でRIGHT JOINが用意されてなく
記述が誤っているだけでしょう。

> 更新しかできず追記がどうしても出来なかったため
更新のSQLも追記して下さい。

[t.mihiro]

記述が間違っている可能性は否定できません・・・
ただ、LEFT JOIN では動作するのにRIGHT JOINではエラーになるので、ドライバのリファレンスを見た所、LEFT JOINの記述はありましたが、RIGHT JOINの記述がなく、現在公式サポートに問い合わせ中です。

Answer 1

他の方の回答にもあるようにユニオンクエリをサブクエリにする必要があると思います。
また、字数制限も気になりますので、ユニオンクエリを使わない方法を提案しておきます。

まず、下記のようなテーブルを作成します。

月度マスター

月度ID	月度
1	4月
2	5月
3	6月

下記のSQLでユニオンクエリと同じ結果になります。SQLはかなりシンプルになります。（テーブルは一つ増えますが）

SELECT
    Oldテーブル.ID AS OLdID,
    Oldテーブル.案件名,
    Oldテーブル.年度,
    月度マスタ.月度,
    Choose([月度ID], [4月度売上], [5月度売上], [6月度売上]) AS 売上
FROM
    Oldテーブル,
    月度マスタ
WHERE
    Oldテーブル.年度=2021;

6月度までのSQLですが、同様にして3月度まで増やせばいいでしょう。

---

本来は、UpdateとRIGHT JOINで更新・追加を同時にしたいと思っていたのですが、

新しいデータベース側のSQL命令でRIGHT JOINが用意されてなく、
LEFT JOINしか対応しないために、更新しかできず追記がどうしても出来なかったため、
今は更新と追記を別々のクエリで作ってみています。

上記のSQLをQ1と名前をつけて保存して、下記のようなSQLで更新と追加が同時にできます。

UPDATE
    Q1 LEFT JOIN Newテーブル
    ON Q1.OLdID = Newテーブル.OldID
SET
    Newテーブル.OldID = [Q1].[OLdID],
    Newテーブル.案件名 = [Q1].[案件名],
    Newテーブル.年度 = [Q1].[年度],
    Newテーブル.月度 = [Q1].[月度],
    Newテーブル.売上 = [Q1].[売上];

クエリが２つになるのを避けたいなら、Q1をサブクエリにすれば一つのクエリにできます。

Comments

[t.mihiro]

かなりシンプルになりますね。勉強になります。
こちらも試してみたいと思います。

[t.mihiro]

おかげさまでChooseを利用してSQLを大幅にシンプルにする事ができ、やりたいことが実現できそうです。
ありがとうございました。

Answer 2

UNION ALLの手前でINSERT文が完結されたとAccessが判断して
「演算子がありません」と出ているような・・・
UNION ALLするところ更にサブクエリにすれば実行出来ると思います。

INSERT INTO Newテーブル ( OldID, 案件名, 年度, 月度, 売上 )
SELECT ID, 案件名, 年度, 月度, 売上
FROM (SELECT
      [Oldテーブル].ID
    , [Oldテーブル].案件名
    , [Oldテーブル].年度
    , '4月' AS 月度
    , [Oldテーブル].[4月度売上] AS 売上
  FROM    [Oldテーブル] 
  LEFT JOIN [Newテーブル] ON [Oldテーブル].ID = [Newテーブル].OldID 
  WHERE( 
      [Oldテーブル].年度 >= 2021 
      AND NOT EXISTS ( 
        SELECT *
        FROM  [Newテーブル] 
        WHERE [Newテーブル].OldID = [Oldテーブル].ID
      )
    ) 
UNION ALL 
SELECT
      [Oldテーブル].ID
    , [Oldテーブル].案件名
    , [Oldテーブル].年度
    , '5月'
    , [Oldテーブル].[5月度売上] 
  FROM [Oldテーブル] 
  LEFT JOIN [Newテーブル] ON [Oldテーブル].ID = [Newテーブル].OldID 
  WHERE( 
      [Oldテーブル].年度 >= 2021 
      AND NOT EXISTS ( 
        SELECT * 
        FROM [Newテーブル] 
        WHERE [Newテーブル].OldID = [Oldテーブル].ID
      )
    )
)  AS A;

一発のSQLで12ヶ月分やろうとすると、AccessのSQL文字数の制限に引っかかるかも知れません。
その場合はクエリを作ってクエリの入れ子状態にする必要があります。
（クエリの中のSQL文字数は文字数制限にはカウントされない仕様のようです）

Comments

[t.mihiro]

なるほど、入れ子にして AS テーブル名でまとめるのですね。
やってみたいと思います。

Answer 3

皆様回答ありがとうございます。
とりあえずサブクエリで解決できました。

ただ、長いSQLなので、Chooseと別テーブルも時間のある時に検証したいと思います。
ありがとうございました！

動作したSQL抜粋は下記となります。

INSERT INTO Newテーブル ( 案件名, FctID, エリア, 年度, 所課, クライアント名, 商材, 担当, 月度, 売上予測, 月 )
SELECT base.案件名, base.FctID, base.[エリア], base.年度, base.所課, base.[クライアント名], base.商材, base.担当, base.月度, base.売上予測, base.月
FROM
 (SELECT 案件名, ID as FctID, エリア, 年度, 支社 as 所課, クライアント名, 商材, 担当, '4月' as 月度, [4月Fct・売上] as 売上予測, 4 as 月 FROM Oldテーブル
UNION ALL
 SELECT 案件名, ID as FctID, エリア, 年度, 支社 as 所課, クライアント名, 商材, 担当, '5月' as 月度, [5月Fct・売上] as 売上予測, 5 as 月 FROM Oldテーブル
UNION ALL
 SELECT 案件名, ID as FctID, エリア, 年度, 支社 as 所課, クライアント名, 商材, 担当, '6月' as 月度, [6月Fct・売上] as 売上予測, 6 as 月 FROM Oldテーブル
)  AS base
～
省略
～
WHERE NOT EXISTS (SELECT *  FROM Newテーブル WHERE  (Newテーブル.FctID = base.FctID AND Newテーブル.月度 = base.月度) );

[追記]

ご提示頂きましたChooseを使用した方法もRIGHT JOINと共に動作したので、
追加・更新が1つのクエリ対応できました。やはりRIGHT JOINに関しては記述に問題があったようです。
都度全部のデータを更新すると重たいので、今後は更新日時からデータを絞り込んで
実行しようかと思っております。
おかげさまでかなりシンプルなSQLになり大変助かりました。
皆様、ありがとうございました。

UPDATE Newテーブル AS T1
 RIGHT JOIN (
SELECT [Oldテーブル].案件名,
 [Oldテーブル].ID AS FctID,
 [Oldテーブル].[エリア],
 [Oldテーブル].年度,
 [Oldテーブル].支社 AS 所課,
 [Oldテーブル].[クライアント名],
 [Oldテーブル].商材,
 [Oldテーブル].担当,
 月度マスタ.月度,
 Choose(月度マスタ.[月度ID], [3月Fct・売上], [4月Fct・売上], [5月Fct・売上], [6月Fct・売上], [7月Fct・売上], [8月Fct・売上], [9月Fct・売上], [10月Fct・売上], [11月Fct・売上], [12月Fct・売上], [1月Fct・売上], [2月Fct・売上] ) AS 売上予測,
 Choose(月度マスタ.[月度ID], 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2 ) AS 月,
 [Oldテーブル].[データ更新日時] AS 更新日時
 FROM Oldテーブル, 月度マスタ ORDER BY [Oldテーブル].ID)  AS base
 ON (T1.月度 = base.月度) AND (T1.FctID = base.FctID)
 SET
 T1.FctID = base.FctID,
 T1.年度 = base.年度,
 T1.月度 = base.月度,
 T1.月 = base.月,
 T1.案件名 = base.案件名,
 T1.クライアント名 = base.[クライアント名],
 T1.エリア = base.[エリア],
 T1.商材 = base.商材,
 T1.担当 = base.担当,
 T1.所課 = base.所課,
 T1.売上予測 = base.売上予測,
 T1.更新日時 = base.更新日時
;

Comments

[sazi]

各unionで該当月の売上の有無は判定しなくて良いのですか？
※例えば、今年度中で到来していない月の分とか。
データが無くても追加するなら、新しいテーブルに処理を変更する際には必ず１２か月分のデータを入れておくなどの考慮が必要になると思います。

[t.mihiro]

今は古いDBシステムからの移行準備で定期的にデータを更新させるので、一定時間ごとに更新できればと思っております。ありがとうございます。

[sazi]

Newテーブルを更新するんだからright joinは意味がありませんね。
実行時間を考えると inner joinの方が良いと思います。
※サブクエリー内のORDER BYも不要ですね。

Answer 4

IIf()を駆使したらカラムの参照先の読み替えもシンプルにできそう。

IIf(～="4月",[Oldテーブル].[4月度売上],IIf(～="5月",[Oldテーブル].[5月度売上],IIf(～="6月",[Oldテーブル].[6月度売上],・・・・・・)))

Comments

[t.mihiro]

古いテーブルは1レコードにを12カ月分のデータが入っているので、
IIFでは出来ない気がするのですが・・・

新しいテーブルから古いテーブルへの逆方向なら
月をIIFで分岐させれば、データを入れ込むカラムを指定できると思います。

1個のレコードを12個のレコードに分解させる必要があるので、
12カ月分 UNION ALL して読み込ませる必要があるのではないかと思うのですが、
それがうまくいかずに困っております。そもそも１回のクエリで12個に分ける方法があれば良いのですが・・・