Access SQL Switch文が長すぎてエラーになる

Question

業務でAccessを使う必要があり、既存のテーブルから設定した分類ごとに集計を行いたいと考えています。

下に記載したSQL文を実行すると、「データベース エンジンの不明なエラー」となります。SWITCH文で32項目を分類しているのですが、そのうち4行分をコメントアウトすると実行することができました。
（SWITCHは13回以上はネストしないと使えないとどこかに書いてにあったので、13回を超える時点でネストしています）

恐らくSWITCH文が長すぎるせいでエラーになってしまっていると思うのですが。。。

SQL文分割して2度流せばできることなのですが、なるべく１回のSQLで実行したいと考えています。
長くならずに実現できる方法はないでしょうか？

【テーブル】
テーブル名：t_製品データ

製品メインコード	製品サブコード	借方	貸方	部門コード	期間
50222	501	100	00000	2020年5月
50222	504	100	00000	2020年5月
50221	501	100	00000	2020年5月
50224	501	100	00000	2020年5月
50225	501	100	00000	2020年5月

【SQL文】

SELECT
SWITCH(
製品メインコード = '50222' AND 製品サブコード = '501' , 'A'
,製品メインコード = '50222' AND 製品サブコード = '504' , 'B'
,製品メインコード = '50221' , 'C'
,製品メインコード = '50224' , 'D'
,製品メインコード = '50225' , 'E'
,製品メインコード = '50226' , 'F'
,製品メインコード = '50227' , 'G'
,製品メインコード = '50229' , 'H'
,製品メインコード = '50206' , 'I'
,製品メインコード = '50231' , 'J'
,製品メインコード = '50232' , 'K'
,製品メインコード = '50233' , 'L'
,製品メインコード = '50234' , 'M'
,TRUE,
SWITCH(
製品メインコード = '50235' , 'N'
,製品メインコード = '50236' , 'O'
,製品メインコード = '50237' , 'P'
,製品メインコード = '50238' AND 製品サブコード <> '502' , 'Q'
,製品メインコード = '50238' AND 製品サブコード = '502' , 'R'
,製品メインコード = '50239' , 'S'
,製品メインコード = '50240' , 'T'
,製品メインコード = '50257' , 'U'
,製品メインコード = '50243' , 'V'
,製品メインコード = '50244' , 'W'
,製品メインコード = '50245' , 'X'
,製品メインコード = '50242' , 'Y'
,製品メインコード = '50263' , 'Z'
,TRUE,
SWITCH(
製品メインコード = '50265' , 'A1'
,製品メインコード = '50248' AND 製品サブコード = '527' , 'B1'
,製品メインコード = '50248' AND 製品サブコード = '524' , 'C1'
,製品メインコード = '50248' AND 製品サブコード = '526' , 'D1'
,製品メインコード = '50248' , 'E1'
,TRUE, 'Z1'
)
)
) AS 分類
,SUM(IIF(ISNULL(借方), 0, 借方)) - SUM(IIF(ISNULL(貸方), 0, 貸方))AS 金額
FROM
t_製品データ
WHERE
部門コード = '00000'
AND
期間 = '2020年5月'
GROUP BY
SWITCH(
製品メインコード = '50222' AND 製品サブコード = '501' , 'A'
,製品メインコード = '50222' AND 製品サブコード = '504' , 'B'
,製品メインコード = '50221' , 'C'
,製品メインコード = '50224' , 'D'
,製品メインコード = '50225' , 'E'
,製品メインコード = '50226' , 'F'
,製品メインコード = '50227' , 'G'
,製品メインコード = '50229' , 'H'
,製品メインコード = '50206' , 'I'
,製品メインコード = '50231' , 'J'
,製品メインコード = '50232' , 'K'
,製品メインコード = '50233' , 'L'
,製品メインコード = '50234' , 'M'
,TRUE,
SWITCH(
製品メインコード = '50235' , 'N'
,製品メインコード = '50236' , 'O'
,製品メインコード = '50237' , 'P'
,製品メインコード = '50238' AND 製品サブコード <> '502' , 'Q'
,製品メインコード = '50238' AND 製品サブコード = '502' , 'R'
,製品メインコード = '50239' , 'S'
,製品メインコード = '50240' , 'T'
,製品メインコード = '50257' , 'U'
,製品メインコード = '50243' , 'V'
,製品メインコード = '50244' , 'W'
,製品メインコード = '50245' , 'X'
,製品メインコード = '50242' , 'Y'
,製品メインコード = '50263' , 'Z'
,TRUE,
SWITCH(
製品メインコード = '50265' , 'A1'
,製品メインコード = '50248' AND 製品サブコード = '527' , 'B1'
,製品メインコード = '50248' AND 製品サブコード = '524' , 'C1'
,製品メインコード = '50248' AND 製品サブコード = '526' , 'D1'
,製品メインコード = '50248' , 'E1'
,TRUE, 'Z1'
)
)
);

【実行結果】

分類	金額
A	100
B	-100
C	100
D	100
E	100

Answer 1

分類用のマスタを作るか、ユーザー定義関数を利用するかですね。
マスターはhatena19さんが回答されているので、ユーザー定義関数の場合で回答します。

以下の様なユーザー定義関数を作成します。

Public Function 分類コード(製品メインコード, 製品サブコード)
Dim w分類コード
  Select Case 製品メインコード
  Case "50222"
      If 製品サブコード = "501" Then
        w分類コード = "A"
      ElseIf 製品サブコード = "504" Then
        w分類コード = "B"
      End If
  Case "50221": w分類コード = "C"
  Case "50224": w分類コード = "D"
' 以下分岐が続く
'    ・
'    ・
'    ・
  Else: w分類コード = "Z1"
  End Select
  分類コード = w分類コード
End Function

上記を利用したクエリーは以下の様になります。

SELECT  分類コード(製品メインコード, 製品サブコード) AS 分類
      , SUM(IIF(ISNULL(借方), 0, 借方)) - SUM(IIF(ISNULL(貸方), 0, 貸方)) AS 金額
FROM  t_製品データ
WHERE 部門コード = '00000'
  AND 期間 = '2020年5月'
GROUP BY 分類コード(製品メインコード, 製品サブコード)

Comments

[tsunacan292]

今回は、アクセスに新規のテーブルを配置できたのでマスタを作るほうで解決しました。
今後、テーブルをいじることのできないファイルの場合はこちらの方式を参考にさせていただきます。
ご回答ありがとうございました。

[sazi]

分類されていないものをZ1の分類にするというのはマスターだけでは解決しないはずなので、注意して下さい。
マスターで行うにしてもleft　joinしての判断が必要になります。

Answer 2

製品コードと分類の対応テーブルを作成してそれを利用すればいいでしょう。

メンテナンスもその方かやりやすいです。

テーブル名: t_分類

製品メインコード	製品サブコード	以外	分類
50222	501	No	A
50222	504	No	B
50221	0	No	C
50224	0	No	D
・・・・	・	・	・
50238	502	Yes	Q
50238	502	No	R

仕様

• 製品サブコードが 0 の場合は、すべて抽出（サブコードを条件にしない）
• 「以外」フィールドはYes/No型、Yesの場合のそのサブコード以外を抽出

SELECT t_製品データ.*, t_分類.分類
FROM t_製品データ, t_分類
WHERE
t_製品データ.製品メインコード=t_分類.製品メインコード
And
IIf(t_分類.製品サブコード=0, True,
    IIf(t_分類.以外=True,
        t_製品データ.製品サブコード<>t_分類.製品サブコード,
        t_製品データ.製品サブコード=t_分類.製品サブコード)
   )
;

集計クエリ

SELECT t_分類.分類, Sum(Nz(借方)-Nz(貸方)) AS 金額
FROM t_製品データ, t_分類
WHERE
t_製品データ.製品メインコード=t_分類.製品メインコード
And
IIf(t_分類.製品サブコード=0, True,
    IIf(t_分類.以外=True,
        t_製品データ.製品サブコード<>t_分類.製品サブコード,
        t_製品データ.製品サブコード=t_分類.製品サブコード)
   )
GROUP BY t_分類.分類;

追記

Z1 の考慮がされてませんでしたので、それを考慮したSQLを追記しておきます。

SELECT Nz(t_分類.分類,"Z1") AS 分類, Sum(Nz(借方)-Nz(貸方)) AS 金額
FROM
 t_製品データ LEFT JOIN t_分類
ON t_製品データ.製品メインコード=t_分類.製品メインコード
And
IIf(t_分類.製品サブコード=0, True,
    IIf(t_分類.以外=True,
        t_製品データ.製品サブコード<>t_分類.製品サブコード,
        t_製品データ.製品サブコード=t_分類.製品サブコード)
   )
WHERE
 部門コード = '00000'
 AND
 期間 = #2020/5/1#
GROUP BY Nz(t_分類.分類,"Z1");

Comments

[tsunacan292]

分類用のテーブルを作る。。。という方法が頭からすっかり抜けていました。
今回はアクセスにテーブルを作成可能でしたので、こちらの方法でうまくいきました！
ご回答ありがとうございました。

[sazi]

Z1の考慮が漏れていないか確認下さいね