（祝）東京オリンピック！

（祝）北京オリンピック！

ACCESSで使うSQL関係

サブフォームに通番を表示するためのレコードソース

COPY

  1. SELECT
  2.    A.ID, 
  3.    , A.code
  4.    , A.name 
  5.    , A.address 
  6.    , (SELECT
  7.          Count(*) 
  8.       FROM
  9.          (SELECT
  10.              C.* 
  11.           FROM
  12.              Sheet1 AS C 
  13.           WHERE
  14.              C.CODE='030') AS B 
  15.       WHERE
  16.          A.ID >= B.ID) AS 順番
  17. FROM
  18.    (SELECT
  19.        D.* 
  20.     FROM
  21.        Sheet1 AS D 
  22.     WHERE
  23.        D.CODE='030') AS A;
  24.  
  25. ここで
  26. (SELECT
  27.     D.* 
  28.  FROM
  29.     Sheet1 AS D 
  30.  WHERE
  31.     D.CODE='030')
  32. をAAとすると
  33.  
  34. SELECT
  35.    A.ID
  36.    , A.code
  37.    , A.name
  38.    , A.address
  39.    , (SELECT
  40.          Count(*) 
  41.       FROM
  42.          AA AS B 
  43.       WHERE A.ID >= B.ID) AS 順番
  44. FROM AA AS A;
  45. となり、理解がし易くなります。

件数および数値の合計

Table1とTable2のCODEで連結してCODE毎にTable2の件数と合計を計算してTable1にセットします。

COPY

  1. UPDATE
  2.    Table1 
  3. INNER JOIN Table2 
  4. 　 ON (Table1.CODE=Table2.CODE) 
  5. SAVE
  6.    Table1.件数 = Table1.件数+1
  7.    , Table1.合計 = Table1.合計+Table2.数値;

データが重複しないように連番を付けて追加

*****Y(PostTable)にIDとCODEで照合して有無を判定
NOT EXISTS (SELECT
               Y.* 
　　　　　　FROM
               PostTable AS Y
　　　　　　WHERE
               Y.ID=X.ID AND Y.CODE=X.CODE)

*****Yに存在しないものを抽出(C)
SELECT
   X.* 
FROM
   MeiboTable AS X 
WHERE [判定]

*****CODEが040であるものを抽出(A)(B)
SELECT
   C.* 
FROM
   [抽出] AS C 
WHERE C.CODE='040'

*****抽出(A)のIDよりも小さい抽出(B)のIDのレコード数を求める
SELECT
   COUNT(*) 
FROM
   [抽出]
WHERE B.ID<=A.ID

*****PostTableのCODEの条件(この場合040)に合う順番の最大値を起算の基準値とする
     最初の1回しか実行されない
NZ(DMAX("順番","PostTable","CODE='040'"),0)

COPY

  1. INSERT INTO
  2.    PostTable ( 順番, 
  3.                ID, 
  4.                code, 
  5.                name, 
  6.                address )
  7. SELECT
  8.    (SELECT
  9.        COUNT(*) 
  10.     FROM
  11.        (SELECT
  12.            C.* 
  13.         FROM
  14.            (SELECT
  15.                X.*
  16.             FROM
  17.                MeiboTable AS X 
  18.             WHERE
  19.                NOT EXISTS (SELECT
  20.                               Y.* 
  21.                            FROM
  22.                               PostTable AS Y 
  23.                            WHERE
  24.                               Y.ID=X.ID AND Y.CODE=X.CODE)) AS C 
  25.        WHERE
  26.           C.CODE='040') AS B 
  27.     WHERE
  28.        B.ID <= A.ID )+ NZ(DMAX("順番", "PostTable", "CODE = '040'"), 0) AS 順番
  29.     , A.ID
  30.     , A.code
  31.     , A.name
  32.     , A.address 
  33. FROM
  34.    (SELECT
  35.        D.* 
  36.     FROM
  37.        (SELECT
  38.            X.* 
  39.         FROM
  40.            MeiboTable AS X 
  41.         WHERE NOT EXISTS (SELECT
  42.                              Y.* 
  43.                           FROM
  44.                              PostTable AS Y 
  45.                           WHERE
  46.                              Y.ID=X.ID AND Y.CODE=X.CODE))  AS D 
  47.     WHERE
  48.        D.CODE='040') AS A;

集計SQL


table_1                        SQL実行
名前       日付       金額     名前　合計　　日付1   金額1　　日付2   金額2　　日付3   金額3
 A       2021/5/1      100      A     600  2021/5/1   100   2021/7/5   400   2021/8/10  100
 B       2021/6/1      200      B     500  2021/6/1   200   2021/7/15  300
 C       2021/6/10     300      C     300  2021/6/10  300
 A       2021/7/5      400
 B       2021/7/15     300 
 A       2021/8/10     100

COPY

  1. SELECT 
  2.    名前
  3.    , SUM(金額) AS 金額合計
  4.    , MAX(IIF(ランク = 0, 日付, NULL)) AS 日付1
  5.    , MAX(IIF(ランク = 0, 金額, NULL)) AS 金額1
  6.    , MAX(IIF(ランク = 1, 日付, NULL)) AS 日付2
  7.    , MAX(IIF(ランク = 1, 金額, NULL)) AS 金額2
  8.    , MAX(IIF(ランク = 2, 日付, NULL)) AS 日付3
  9.    , MAX(IIF(ランク = 2, 金額, NULL)) AS 金額3
  10. FROM
  11.    (SELECT 
  12.        *
  13.        , (SELECT
  14.              COUNT(*)
  15.           FROM
  16.              table_1 AS B
  17.           WHERE
  18.              A.名前 = B.名前 AND B.日付 < A.日付) AS ランク
  19.     FROM table_1 AS A) AS T
  20. GROUP
  21.    BY 名前;

複数のレコードをグループ化した時の期間の満了日

期間の満了日が空白ということは、現在期間の進行中であるからグループ化するときも空白にしたい。集計関数Maxは、Nullを無視するのでそのままでは意図する集計はできない。そこで、レコードをカウントするCount(＊)とNullを無視したカウントCount(フィールド名)を使い、NullがないのならばCount(＊)=Count(フィールド名)、あるのであればCount(＊)<>Count(フィールド名)となる性質を利用する。日付2とするのは日付では循環エラーがでるためである。

IIF関数のところは、SWITCH関数を使うのであれば、
SWITCH (COUNT(*) = COUNT(フィールド名), MAX(フィールド名), TRUE, NULL)
とします。

COPY

  1. SELECT
  2.    [テーブル1].A
  3.    , IIF(COUNT(*) = COUNT(日付), MAX([日付]), NULL) AS 日付2
  4. FROM
  5.    テーブル1
  6. GROUP BY
  7.    [テーブル1].A;

SWITCH関数の入れ子

SWITCH関数の分岐は13までで14以上になるとエラーになります。14以上になるのであれば入れ子をして対応します。

13番目、最終の True, 次の入れ子（最終値）の部分は、ここに至る xpr-○ で真にならなければ
必ず実行されます。
特に、予期せぬ結果にならないように True, 最終値 を必ず書くようにします。

COPY

  1. SWITH ( xpr-1, value-1, 
  2.         xpr-2, value-2, 
  3.         ・・
  4.         xpr-12, value-12,  
  5.         True, SWITCH ( xpr-13, value-13,
  6.                        xpr-14, value-14,
  7.                        ・・
  8.                        xpr-24, value-24,                       
  9.                        True, SWITCH ( xpr-25, value-25,
  10.                                       xpr-26, value-26,
  11.                                       ・・
  12.                                       True, 最終値
  13.                                     )
  14.                      )
  15.       )

汎用的に縦から横へ変換（クロス表）

フィールド名を汎用的に作成する必要があります

グループ毎に連番を振り、それを利用します。

     元テーブル                          順番の振付
------------------               ------------------------
ID  事件ID    業者   順番を振る  ID  事件ID    業者  順番
 1       1    AAAA       →　     1       1    AAAA　　 1
 2       1    BBBB       →　　 　2       1    BBBB　　 2
 3       1    CCCC       →　　 　3       1    CCCC　　 3
 4       1    DDDD       →　　 　4       1    DDDD　　 4
 5       2    EEEE       →　     5       2    EEEE　　 1
 6       2    FFFF       →　     6       2    FFFF　　 2
 7       2    GGGG       →　     7       2    GGGG　　 3
 8       2    HHHH       →　     8       2    HHHH　　 4
 9       2    IIII       →　     9       2    IIII　　 5
10       2    JJJJ       →　    10       2    JJJJ　　 6

縦横変換（クロス表）
---------------------------------------------------------------------
事件ID   業者1 業者2 業者3 業者4 業者5 業者6 業者7 業者8 業者9 業者10
1         AAAA  BBBB  CCCC  DDDD
2         EEEE  FFFF  GGGG  HHHH  IIII  JJJJ
グループ(Partition)毎に1から連番を振るSQL（IDに重複がないこと）
SELECT
    A.事件ID
   , A.業者
   , (SELECT COUNT(*) FROM
         業者 AS B 
       WHERE B.事件ID = A.事件ID AND B.ID <= A.ID) AS 順番
FROM 業者 AS A
GROUP BY A.事件ID, A.業者, A.ID

COPY

  1. 連番が振れれば汎用的に縦横変換（クロス表）が作成できます。
  2. SELECT
  3.    Z.事件ID
  4.    , MAX(IIF(Z.順番 = 1, Z.業者, NULL)) AS 業者1
  5.    , MAX(IIF(Z.順番 = 2, Z.業者, NULL)) AS 業者2
  6.    , MAX(IIF(Z.順番 = 3, Z.業者, NULL)) AS 業者3
  7.    , MAX(IIF(Z.順番 = 4, Z.業者, NULL)) AS 業者4
  8.    , MAX(IIF(Z.順番 = 5, Z.業者, NULL)) AS 業者5
  9.    , MAX(IIF(Z.順番 = 6, Z.業者, NULL)) AS 業者6
  10.    , MAX(IIF(Z.順番 = 7, Z.業者, NULL)) AS 業者7
  11.    , MAX(IIF(Z.順番 = 8, Z.業者, NULL)) AS 業者8
  12.    , MAX(IIF(Z.順番 = 9, Z.業者, NULL)) AS 業者9
  13.    , MAX(IIF(Z.順番 = 10, Z.業者, NULL)) AS 業者10
  14. FROM
  15.   (SELECT
  16.       A.ID
  17.       , A.事件ID
  18.       , A.業者
  19.       , (SELECT
  20.             COUNT(*)
  21.          FROM
  22.             業者 AS B 
  23.          WHERE
  24.             B.事件ID = A.事件ID AND B.ID <= A.ID) AS 順番
  25.     FROM
  26.        業者 AS A
  27.     GROUP BY
  28.        A.事件ID, A.業者, A.ID) AS Z
  29. GROUP BY 
  30.   Z.事件ID;

グループをまとめて全体の連番を振る

     元テーブル                          順番の振付
------------------               ------------------------
ID  事件ID    業者   順番を振る  ID  事件ID    業者  順番
 1       1    AAAA       →　     1       1    AAAA　　 1
 2       2    BBBB       →　　 　2       2    BBBB　　 5
 3       1    CCCC       →　　 　3       1    CCCC　　 2
 4       1    DDDD       →　　 　4       1    DDDD　　 3
 5       2    EEEE       →　     5       2    EEEE　　 6
 6       2    FFFF       →　     6       2    FFFF　　 7
 7       2    GGGG       →　     7       2    GGGG　　 8
 8       1    HHHH       →　     8       1    HHHH　　 4
 9       2    IIII       →　     9       2    IIII　　 9
10       2    JJJJ       →　    10       2    JJJJ　　10

COPY

  1. SELECT
  2.    A.ID
  3.    , A.事件ID
  4.    , A.業者
  5.    , (SELECT COUNT(*) FROM
  6.          業者 AS B 
  7.        WHERE B.事件ID & FORMAT(B.ID,'0000') <= A.事件ID & FORMAT(A.ID,'0000')) AS 順番
  8. FROM 業者 AS A
  9. GROUP BY A.事件ID, A.業者, A.ID;

横持ちの表を縦持ちに変換

テーブル名：Personnel
Class       Member1     Member2    Member3
クラスA        ああ        いい       うう
クラスB        かか        きき
クラスC        ささ
クラスD

を
Class        Member
クラスA        ああ
クラスA        いい
クラスA        うう
クラスB        かか
クラスB        かか
クラスC        ささ
クラスD
に変換します。

Memberのマスター表を考えます。
前半のSQLを実行すると次のように出力されます（後半のSQLでこれをサブSQLにします。）
Member
ああ
かか
ささ
いい
きき
うう

テーブルPersonnelとサブSQLで外部結合をします。
外部結合する訳は、MemberがいないクラスDも表示するためです。

COPY

  1. 'Memberのマスター表を考えます。
  2. SELECT A.Member
  3. FROM
  4.     (SELECT B.Member1 AS Member FROM Personnel AS B
  5.      UNION
  6.      SELECT C.Member2 AS Member FROM Personnel AS C
  7.      UNION
  8.      SELECT D.Member3 AS Member FROM Personnel AS D
  9.     )  AS A
  10. WHERE Not A.Member Is Null;
  11.  
  12. '外部結合をして
  13. SELECT A.Class, B.Member
  14. FROM Personnel AS A 
  15. LEFT JOIN
  16.     (SELECT XX.Member
  17.      FROM
  18.         (SELECT X.Member1 AS Member FROM Personnel AS X
  19.          UNION
  20.          SELECT Y.Member2 AS Member FROM Personnel AS Y
  21.          UNION
  22.          SELECT Z.Member3 AS Member FROM Personnel AS Z
  23.         )  AS XX
  24.      WHERE Not XX.Member Is Null
  25.     )  AS B 
  26. ON B.Member = A.Member1 OR BMember = A.Member2 OR B.Member = A.Member3;
  27. '下記のようにしたいところですが、ACCESSではエラーになります。
  28. 'ON B.Member IN (A.Member1, A.Member2, A.Member3)

行番号の付加

COPY

  1. SELECT
  2.     X.*
  3.     , (SELECT
  4.            COUNT(*) 
  5.        FROM 
  6.            数列１ AS Y 
  7.        WHERE 
  8.            Y.取引日 <= X.取引日) AS ROW
  9. FROM 
  10.     数列１ AS X

前日（ひとつ前の行）の値を並べる

COPY

  1. SELECT
  2.     A.*
  3.     , (SELECT
  4.            B.価格 
  5.        FROM
  6.            (SELECT
  7.                 X.*
  8.                 , (SELECT
  9.                        COUNT(*) 
  10.                    FROM 
  11.                        数列１ AS Y 
  12.                    WHERE 
  13.                        Y.取引日 <= X.取引日) AS ROW
  14.            FROM 
  15.                数列１ AS X) AS B 
  16.            WHERE
  17.                B.ROW = A.ROW - 1) AS 前日
  18. FROM
  19.     (SELECT 
  20.          X.*
  21.          , (SELECT 
  22.                 COUNT(*)
  23.             FROM
  24.                 数列１ AS Y 
  25.             WHERE 
  26.                 Y.取引日 <= X.取引日) AS ROW
  27.      FROM 数列１ AS X) AS A;

ひとつ前の値を並べることができたら、比較してUP/DOWN/STAYを判定

COPY

  1. SELECT 
  2.     AA.取引日
  3.     , AA.価格
  4.     , SWITCH(AA.価格 > AA.前日, 'UP'
  5.              , AA.価格 < AA.前日, 'DOWN'
  6.              , TRUE, 'STAY') AS DIFF
  7.     , AA.ROW
  8. FROM (SELECT
  9.     A.*
  10.     , (SELECT
  11.            B.価格 
  12.        FROM
  13.            (SELECT
  14.                 X.*
  15.                 , (SELECT
  16.                        COUNT(*) 
  17.                    FROM 
  18.                        数列１ AS Y 
  19.                    WHERE 
  20.                        Y.取引日 <= X.取引日) AS ROW
  21.            FROM 
  22.                数列１ AS X) AS B 
  23.            WHERE
  24.                B.ROW = A.ROW - 1) AS 前日
  25. FROM
  26.     (SELECT 
  27.          X.*
  28.          , (SELECT 
  29.                 COUNT(*)
  30.             FROM
  31.                 数列１ AS Y 
  32.             WHERE 
  33.                 Y.取引日 <= X.取引日) AS ROW
  34.      FROM 数列１ AS X) AS A)  AS AA
  35. WHERE
  36.      SWITCH(AA.価格 > AA.前日, 'UP'
  37.             , AA.価格 < AA.前日, 'DOWN'
  38.             , TRUE, 'STAY') = 'UP';

最終処理　GAPをグループ化

qqqqに前項のサブSQLを突っ込みたいところですが、突っ込んで実行させたところACCESSが落ちました。
仕方がないのでクエリqqqqを作成して実行させています。

COPY

  1. SELECT
  2.     MIN(CC.取引日) AS 期間１
  3.     , '～' AS ～
  4.     ,MAX(CC.取引日) AS 期間２
  5. FROM
  6.     (SELECT
  7.          AAA.取引日
  8.          , COUNT(BBB.ROW) - MIN(AAA.ROW) AS GAP
  9.      FROM
  10.          qqqq AS AAA
  11.      INNER JOIN
  12.          qqqq AS BBB
  13.      ON BBB.ROW <= AAA.ROW
  14.      GROUP BY AAA.取引日) AS CC
  15. GROUP BY CC.GAP;