Access (一般機能)

Accessの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Professional : Access 2010)
複数のテーブルを照合し1つのテーブルにしたい
投稿日時: 17/11/20 11:28:56
投稿者: mendel

お世話になります。
説明が至らないと思いますが、よろしくお願いします。
 
T_請求テーブルとT_支払テーブルを照合し、1つのテーブルにしたいです。
 
上手に説明できないので、やりたいことのイメージを作りました。
 

T_処理番号	
処理番号	取引先CD
1	100
2	200

 
T_請求テーブルには1つの取引先に対して、
1つまたは複数の請求が入力されています。
 
	T_請求	
取引先CD	請求金額	処理番号
100	1000	1
100	2000	1
200	1700	2

 
T_支払テーブルは、同一取引先の複数の請求に対し一括して支払う場合がある。
また、1つの請求に対し複数の支払方法が存在することもある。
 
	T_支払		
取引先CD	支払金額	支払方法	処理番号
100	3000	現金	1
200	500	手形	2
200	1200	現金	2

 
T_照合				
処)取引先CD	処)処理番号	請求金額	支払金額	支払方法
100	1	1000	3000	現金
100	1	2000		
200	2	1700	500	手形
200	2		1200	現金

 
イメージの説明
取引先[200]の1700円の請求を[手形500円]と[現金1200円]で支払う。
取引先[100]の1000円と2000円の請求を[現金一括3000円]で支払う。
 
どのようにクエリを作れば、
T_照合のテーブルを作ることができますか?
 
試したこと
結合プロパティの方法を変更してみましたが、
歯抜けの部分を作ることができません。
 
よろしくお願いします。

回答
投稿日時: 17/11/20 13:35:44
投稿者: sk

引用:
T_請求テーブルには1つの取引先に対して、
1つまたは複数の請求が入力されています。
 
	T_請求	
取引先CD	請求金額	処理番号
100	1000	1
100	2000	1
200	1700	2

・「それぞれの請求(書)を識別するための ID 」、
 「請求が行なわれた順番」もしくは
 「請求が行なわれた日付」を示すフィールドが
 不足している。
 
引用:
T_支払テーブルは、同一取引先の複数の請求に対し一括して支払う場合がある。
また、1つの請求に対し複数の支払方法が存在することもある。
 
	T_支払		
取引先CD	支払金額	支払方法	処理番号
100	3000	現金	1
200	500	手形	2
200	1200	現金	2

・「どの請求(書)に対する支払であるかを示す ID 」
 「支払が行なわれた順番」もしくは
 「支払が行なわれた日付」を示すフィールドが
 不足している。
 
引用:
T_照合				
処)取引先CD	処)処理番号	請求金額	支払金額	支払方法
100	1	1000	3000	現金
100	1	2000		
200	2	1700	500	手形
200	2		1200	現金

 
イメージの説明
取引先[200]の1700円の請求を[手形500円]と[現金1200円]で支払う。
取引先[100]の1000円と2000円の請求を[現金一括3000円]で支払う

・1 件目のレコードのように
 「支払金額が請求金額を上回っている状態」は
 会計処理上望ましくないのではないか。
(たとえその場で支払われた現金が 3000 円であっても、
 あくまで「 1000 円の請求 A に対して 1000 円の支払があった」
 「 2000 円の請求 B に対して 2000 円の支払があった」
 という形で処理しなければならないはずであり、
 データ入力に際してもそのようにすべきではないのか)
 
引用:
試したこと
結合プロパティの方法を変更してみましたが、
歯抜けの部分を作ることができません。

「帳票の見た目」に「テーブルの中身」を
合わせようとするのは、データベースの設計上
問題があります。

投稿日時: 17/11/24 11:52:31
投稿者: mendel

質問に目を通していただきありがとうございます。
レスポンスが遅くなりもうしわけありません。
 

引用:
・「それぞれの請求(書)を識別するための ID 」、
 「請求が行なわれた順番」もしくは
 「請求が行なわれた日付」を示すフィールドが
 不足している。

 
情報が不足しておりもうしわけありません。
 
[T_請求]には以下のフィールドがあります。
[明細番号]フィールド:オートナンバー型
[取引先CD]、[取引先名]、[取引名]、[仕訳CD]、[仕訳名]、[適用]、
[請求金額]、[税額]、[金額計]、[処理日]
 
引用:
・「どの請求(書)に対する支払であるかを示す ID 」
 「支払が行なわれた順番」もしくは
 「支払が行なわれた日付」を示すフィールドが
 不足している。

 
[T_支払]には以下のフィールドがあります。
[支払番号]フィールド:オートナンバー型
[請求番号]、[支払日]、[取引先CD]、[発生年月]、[金額]、
[適用]、[処理番号]
 
引用:
・1 件目のレコードのように
 「支払金額が請求金額を上回っている状態」は
 会計処理上望ましくないのではないか。
(たとえその場で支払われた現金が 3000 円であっても、
 あくまで「 1000 円の請求 A に対して 1000 円の支払があった」
 「 2000 円の請求 B に対して 2000 円の支払があった」
 という形で処理しなければならないはずであり、
 データ入力に際してもそのようにすべきではないのか)

 
私も指摘いただいた通りだと考えますが、
現行の業務の進め方に合わるとの要望があります。
 
現行の業務の進め方が
原則、取引先毎の総請求金額計に対してどのような支払方法で、
いくら支払ったかを月度一括で記録している。
まれにどの支払方法・支払金額がどの請求明細に対する支払なのかを
記録する必要がある。
*まれなケースに対応するために[T_支払]には[請求番号]フィールドを設け、
必要時に請求番号を入力できるようにしてあります。
 
現行の業務に準じるため
 
[T_処理番号]には以下のフィールドがあります。
[処理番号]フィールド:数値型
[処理番号]、[取引先CD]、[支払日]、[処理時刻]
 
支払処理時に[取引先CD]・[支払日]を条件に合致レコードの
有無を判定し[処理番号]を発番し、この番号を
[T_請求明細]と[T_支払明細]の[処理番号]フィールドに埋め込みます。
結局、[処理番号]フィールドは支払アクションを起こした際に
関連して欲しい請求明細と支払明細に同じ処理番号を振り、ひもづけをしています。
*[処理時刻]フィールドは現時点で使っていない。
 
これで[処理番号]フィールドをキーにして[T_請求明細]と[T_支払明細]
の関係が特定できるのではと考えました。
 
[T_照合]の段階では、請求に関しては明細単位で表示し、
支払に関しては支払方法毎の金額を表示して欲しいとの要望があります。
 
[処理番号]フィールドがあれば歯抜けのテーブル・クエリが作れると考えました。
しかし実現できず質問した次第です。

回答
投稿日時: 17/11/24 19:19:47
投稿者: mayu.

引用:
どのようにクエリを作れば、
T_照合のテーブルを作ることができますか?

テーブルに「 見た目 」という価値観を持ち込むことは
慎むべきことであるという認識はお持ちなのでしたら
VBAを用いず、SQLの記述だけで ご希望の表示は可能ではある
ということに加え
実行効率が悪すぎて現実的な処理ではない
という事実もご覧に入れましょう。
 
なお、SQLの実行速度・読みにくさを少しでも改善しょうとするのでしたら
T_請求・T_支払 の両テーブルに
[ 取引先CD + 処理番号 ]のグループ連番を格納するフィールドを設け、
VBAを用いてテーブルへ値を書き込むようにすればよろしいでしょう。
 
■ データ例
< T_請求 >
明細番号  取引先CD  請求金額  処理番号
----------------------------------------
    1        100      1,000      1
    2        100      2,000      1
    3        200      1,700      2

< T_支払 >
支払番号  取引先CD  支払金額  処理番号  支払方法    支払日
------------------------------------------------------------
    1        100      3,000      1        現金    2017/10/11
    2        200        500      2        手形    2017/10/24
    3        200      1,200      2        現金    2017/10/30

■ SQL
SELECT t1.明細番号 As seq
     , t1.取引先CD
     , t1.処理番号
     , t1.請求金額
     , t2.支払金額
     , t2.支払方法
FROM
(
    SELECT q1.明細番号
         , q1.取引先CD
         , q1.請求金額
         , q1.処理番号
         , Count(1) As 連番
    FROM
    (
                   T_請求 q1
        INNER JOIN T_請求 q2
                ON q1.取引先CD = q2.取引先CD AND
                   q1.処理番号 = q2.処理番号
    )
    INNER JOIN
    (
        SELECT 取引先CD, 処理番号, Count(1) As c
        FROM T_請求
        GROUP BY 取引先CD, 処理番号
    ) q3
    ON q1.取引先CD = q3.取引先CD AND
       q1.処理番号 = q3.処理番号
    WHERE q1.明細番号 >= q2.明細番号
      AND (
                EXISTS
                (
                    SELECT 1 FROM T_支払 q4
                     WHERE q4.取引先CD = q3.取引先CD 
                       AND q4.処理番号 = q3.処理番号
                    HAVING Count(1) <= q3.c
                )
            OR
                NOT EXISTS
                (
                    SELECT 1 FROM T_支払 q4
                     WHERE q4.取引先CD = q3.取引先CD 
                       AND q4.処理番号 = q3.処理番号
                )
          )
    GROUP BY q1.明細番号
           , q1.取引先CD
           , q1.請求金額
           , q1.処理番号
    
) t1
LEFT JOIN 
(
    SELECT q1.支払番号
         , q1.取引先CD
         , q1.支払日
         , q1.支払金額
         , q1.支払方法
         , q1.処理番号
         , Count(1) As 連番
     FROM T_支払 q1
        , T_支払 q2
    WHERE q1.取引先CD = q2.取引先CD
      AND q1.処理番号 = q2.処理番号
      AND q1.支払番号 >= q2.支払番号
    GROUP BY q1.支払番号
           , q1.取引先CD
           , q1.支払日
           , q1.支払金額
           , q1.支払方法
           , q1.処理番号
) t2
ON t1.取引先CD = t2.取引先CD AND
   t1.処理番号 = t2.処理番号 AND
   t1.連番     = t2.連番

UNION ALL

SELECT t1.支払番号
     , t1.取引先CD
     , t1.処理番号
     , t2.請求金額
     , t1.支払金額
     , t1.支払方法
FROM
(
    SELECT q1.支払番号
         , q1.取引先CD
         , q1.支払日
         , q1.支払金額
         , q1.支払方法
         , q1.処理番号
         , Count(1) As 連番
    FROM
    (
                   T_支払 q1
        INNER JOIN T_支払 q2
                ON q1.取引先CD = q2.取引先CD AND
                   q1.処理番号 = q2.処理番号
    )
    INNER JOIN
    (
        SELECT 取引先CD, 処理番号, Count(1) As c
        FROM T_支払 
        GROUP BY 取引先CD, 処理番号
    ) q3
    ON q1.取引先CD = q3.取引先CD AND
       q1.処理番号 = q3.処理番号
    WHERE q1.支払番号 >= q2.支払番号
      AND EXISTS
          (
              SELECT 1 FROM T_請求 q4
               WHERE q4.取引先CD = q3.取引先CD 
                 AND q4.処理番号 = q3.処理番号
              HAVING Count(1) < q3.c
          )
    GROUP BY q1.支払番号
           , q1.取引先CD
           , q1.支払日
           , q1.支払金額
           , q1.支払方法
           , q1.処理番号
) t1
LEFT JOIN 
(
    SELECT q1.明細番号
         , q1.取引先CD
         , q1.請求金額
         , q1.処理番号
         , Count(1) As 連番
     FROM T_請求 q1
        , T_請求 q2
    WHERE q1.取引先CD  = q2.取引先CD
      AND q1.処理番号  = q2.処理番号
      AND q1.明細番号 >= q2.明細番号
    GROUP BY q1.明細番号
           , q1.取引先CD
           , q1.請求金額
           , q1.処理番号
) t2
ON t1.取引先CD = t2.取引先CD AND
   t1.処理番号 = t2.処理番号 AND
   t1.連番     = t2.連番
ORDER BY 2, 3, 1 ;

■ 結果
 seq   取引先CD	 処理番号    請求金額   支払金額   支払方法
--------------------------------------------------------------
  1      100         1         1,000      3,000      現金
  2      100         1         2,000                     
  2      200         2         1,700        500      手形
  3      200         2                    1,200      現金

回答
投稿日時: 17/11/25 00:19:49
投稿者: mayu.

引用:
[処理番号]フィールドがあれば歯抜けのテーブル・クエリが作れると考えました。
しかし実現できず質問した次第です。

11/24 19:19:47 掲載の長文SQLを、多少でも解読できれば
ご理解いただけたかもしれませんけど
ご希望の結果を得るための T_請求・T_支払 に必要な共通のフィールドは
[ 取引先CD + 処理番号 ]     では足りておらず
[ 取引先CD + 処理番号 + 連番 ]  になります。
 
また、[ 取引先CD + 処理番号 ] で [ グループ連番 ] を振った後は
 
 ・ 取引先CD
 ・ 処理番号
 ・ グループ連番
 
3つのフィールドをキーとして
Access-SQL ではサポートされていない「 完全外部結合 」をどうすれば模倣できるか
ということを考える必要があります。
 
hatenaさんが公開されている
■グループ毎連番を自動入力する関数
http://hatenachips.blog34.fc2.com/blog-entry-428.html
の SetSequenceNumber関数 を利用すれば
 
< T_請求 >
明細番号  取引先CD  請求金額  処理番号   gseq
-----------------------------------------------
    1        100      1,000      1         1
    2        100      2,000      1         2
    3        200      1,700      2         1

< T_支払 >
支払番号  取引先CD  支払金額  処理番号  支払方法    支払日     gseq
---------------------------------------------------------------------
    1        100      3,000      1        現金    2017/10/11     1
    2        200        500      2        手形    2017/10/24     1
    3        200      1,200      2        現金    2017/10/30     2

両テーブルに[ 取引先CD + 処理番号 ]のグループ連番を付与することが出来ますから
連番さえ振ってしまえば、11/24 19:19:47 に投稿した複雑な SQL は
以下のようにシンプルになります。
 
SELECT x.gseq
     , x.取引先CD
     , x.処理番号
     , y.請求金額
     , z.支払金額
     , z.支払方法
FROM 
(
    (
        SELECT 取引先CD, 処理番号, gseq
        FROM T_請求
        UNION
        SELECT 取引先CD, 処理番号, gseq
        FROM T_支払
    ) x
    LEFT JOIN T_請求 y
           ON x.取引先CD = y.取引先CD AND
              x.処理番号 = y.処理番号 AND
              x.gseq     = y.gseq
)
LEFT JOIN T_支払 z
       ON x.取引先CD = z.取引先CD AND
          x.処理番号 = z.処理番号 AND
          x.gseq     = z.gseq
ORDER BY 2, 3, 1 ;

投稿日時: 17/11/27 10:53:36
投稿者: mendel

sk様、mayu.様
 
解決策、ありがとうございます
 
解決済みとします。
 
教えていただいたクエリ、グループ連番の考え方、関数で処理する方法
これらをしっかり学んで「帳票のような見た目」が本当に必要なのかを
上司に説明できるようになろうと思います。
 
以上。