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

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

ACCESSで使うSQL関係

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

COPY

SELECT
   A.ID, 
   , A.code
   , A.name 
   , A.address 
   , (SELECT
         Count(*) 
      FROM
         (SELECT
             C.* 
          FROM
             Sheet1 AS C 
          WHERE
             C.CODE='030') AS B 
      WHERE
         A.ID >= B.ID) AS 順番
FROM
   (SELECT
       D.* 
    FROM
       Sheet1 AS D 
    WHERE
       D.CODE='030') AS A;

ここで
(SELECT
    D.* 
 FROM
    Sheet1 AS D 
 WHERE
    D.CODE='030')
をAAとすると

SELECT
   A.ID
   , A.code
   , A.name
   , A.address
   , (SELECT
         Count(*) 
      FROM
         AA AS B 
      WHERE A.ID >= B.ID) AS 順番
FROM AA AS A;
となり、理解がし易くなります。

件数および数値の合計

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

COPY

UPDATE
   Table1 
INNER JOIN Table2 
　 ON (Table1.CODE=Table2.CODE) 
SAVE
   Table1.件数 = Table1.件数+1
   , 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

INSERT INTO
   PostTable ( 順番, 
               ID, 
               code, 
               name, 
               address )
SELECT
   (SELECT
       COUNT(*) 
    FROM
       (SELECT
           C.* 
        FROM
           (SELECT
               X.*
            FROM
               MeiboTable AS X 
            WHERE
               NOT EXISTS (SELECT
                              Y.* 
                           FROM
                              PostTable AS Y 
                           WHERE
                              Y.ID=X.ID AND Y.CODE=X.CODE)) AS C 
       WHERE
          C.CODE='040') AS B 
    WHERE
       B.ID <= A.ID )+ NZ(DMAX("順番", "PostTable", "CODE = '040'"), 0) AS 順番
    , A.ID
    , A.code
    , A.name
    , A.address 
FROM
   (SELECT
       D.* 
    FROM
       (SELECT
           X.* 
        FROM
           MeiboTable AS X 
        WHERE NOT EXISTS (SELECT
                             Y.* 
                          FROM
                             PostTable AS Y 
                          WHERE
                             Y.ID=X.ID AND Y.CODE=X.CODE))  AS D 
    WHERE
       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

SELECT 
   名前
   , SUM(金額) AS 金額合計
   , MAX(IIF(ランク = 0, 日付, NULL)) AS 日付1
   , MAX(IIF(ランク = 0, 金額, NULL)) AS 金額1
   , MAX(IIF(ランク = 1, 日付, NULL)) AS 日付2
   , MAX(IIF(ランク = 1, 金額, NULL)) AS 金額2
   , MAX(IIF(ランク = 2, 日付, NULL)) AS 日付3
   , MAX(IIF(ランク = 2, 金額, NULL)) AS 金額3
FROM
   (SELECT 
       *
       , (SELECT
             COUNT(*)
          FROM
             table_1 AS B
          WHERE
             A.名前 = B.名前 AND B.日付 < A.日付) AS ランク
    FROM table_1 AS A) AS T
GROUP
   BY 名前;

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

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

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

COPY

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

SWITCH関数の入れ子

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

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

COPY

SWITH ( xpr-1, value-1, 
        xpr-2, value-2, 
        ・・
        xpr-12, value-12,  
        True, SWITCH ( xpr-13, value-13,
                       xpr-14, value-14,
                       ・・
                       xpr-24, value-24,                       
                       True, SWITCH ( xpr-25, value-25,
                                      xpr-26, value-26,
                                      ・・
                                      True, 最終値
                                    )
                     )
      )

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

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

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

     元テーブル                          順番の振付
------------------               ------------------------
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

連番が振れれば汎用的に縦横変換（クロス表）が作成できます。
SELECT
   Z.事件ID
   , MAX(IIF(Z.順番 = 1, Z.業者, NULL)) AS 業者1
   , MAX(IIF(Z.順番 = 2, Z.業者, NULL)) AS 業者2
   , MAX(IIF(Z.順番 = 3, Z.業者, NULL)) AS 業者3
   , MAX(IIF(Z.順番 = 4, Z.業者, NULL)) AS 業者4
   , MAX(IIF(Z.順番 = 5, Z.業者, NULL)) AS 業者5
   , MAX(IIF(Z.順番 = 6, Z.業者, NULL)) AS 業者6
   , MAX(IIF(Z.順番 = 7, Z.業者, NULL)) AS 業者7
   , MAX(IIF(Z.順番 = 8, Z.業者, NULL)) AS 業者8
   , MAX(IIF(Z.順番 = 9, Z.業者, NULL)) AS 業者9
   , MAX(IIF(Z.順番 = 10, Z.業者, NULL)) AS 業者10
FROM
  (SELECT
      A.ID
      , 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) AS Z
GROUP BY 
  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

SELECT
   A.ID
   , A.事件ID
   , A.業者
   , (SELECT COUNT(*) FROM
         業者 AS B 
       WHERE B.事件ID & FORMAT(B.ID,'0000') <= A.事件ID & FORMAT(A.ID,'0000')) AS 順番
FROM 業者 AS A
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

'Memberのマスター表を考えます。
SELECT A.Member
FROM
    (SELECT B.Member1 AS Member FROM Personnel AS B
     UNION
     SELECT C.Member2 AS Member FROM Personnel AS C
     UNION
     SELECT D.Member3 AS Member FROM Personnel AS D
    )  AS A
WHERE Not A.Member Is Null;

'外部結合をして
SELECT A.Class, B.Member
FROM Personnel AS A 
LEFT JOIN
    (SELECT XX.Member
     FROM
        (SELECT X.Member1 AS Member FROM Personnel AS X
         UNION
         SELECT Y.Member2 AS Member FROM Personnel AS Y
         UNION
         SELECT Z.Member3 AS Member FROM Personnel AS Z
        )  AS XX
     WHERE Not XX.Member Is Null
    )  AS B 
ON B.Member = A.Member1 OR BMember = A.Member2 OR B.Member = A.Member3;
'下記のようにしたいところですが、ACCESSではエラーになります。
'ON B.Member IN (A.Member1, A.Member2, A.Member3)

行番号の付加

COPY

SELECT
    X.*
    , (SELECT
           COUNT(*) 
       FROM 
           数列１ AS Y 
       WHERE 
           Y.取引日 <= X.取引日) AS ROW
FROM 
    数列１ AS X

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

COPY

SELECT
    A.*
    , (SELECT
           B.価格 
       FROM
           (SELECT
                X.*
                , (SELECT
                       COUNT(*) 
                   FROM 
                       数列１ AS Y 
                   WHERE 
                       Y.取引日 <= X.取引日) AS ROW
           FROM 
               数列１ AS X) AS B 
           WHERE
               B.ROW = A.ROW - 1) AS 前日
FROM
    (SELECT 
         X.*
         , (SELECT 
                COUNT(*)
            FROM
                数列１ AS Y 
            WHERE 
                Y.取引日 <= X.取引日) AS ROW
     FROM 数列１ AS X) AS A;

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

COPY

SELECT 
    AA.取引日
    , AA.価格
    , SWITCH(AA.価格 > AA.前日, 'UP'
             , AA.価格 < AA.前日, 'DOWN'
             , TRUE, 'STAY') AS DIFF
    , AA.ROW
FROM (SELECT
    A.*
    , (SELECT
           B.価格 
       FROM
           (SELECT
                X.*
                , (SELECT
                       COUNT(*) 
                   FROM 
                       数列１ AS Y 
                   WHERE 
                       Y.取引日 <= X.取引日) AS ROW
           FROM 
               数列１ AS X) AS B 
           WHERE
               B.ROW = A.ROW - 1) AS 前日
FROM
    (SELECT 
         X.*
         , (SELECT 
                COUNT(*)
            FROM
                数列１ AS Y 
            WHERE 
                Y.取引日 <= X.取引日) AS ROW
     FROM 数列１ AS X) AS A)  AS AA
WHERE
     SWITCH(AA.価格 > AA.前日, 'UP'
            , AA.価格 < AA.前日, 'DOWN'
            , TRUE, 'STAY') = 'UP';

最終処理　GAPをグループ化

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

COPY

SELECT
    MIN(CC.取引日) AS 期間１
    , '～' AS ～
    ,MAX(CC.取引日) AS 期間２
FROM
    (SELECT
         AAA.取引日
         , COUNT(BBB.ROW) - MIN(AAA.ROW) AS GAP
     FROM
         qqqq AS AAA
     INNER JOIN
         qqqq AS BBB
     ON BBB.ROW <= AAA.ROW
     GROUP BY AAA.取引日) AS CC
GROUP BY CC.GAP;