Access (VBA)

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

 
(Windows 7全般 : Access 2010)
同一値が複数行存在するテーブルから横配列の連番取得SQLについて
投稿日時: 19/06/21 12:02:01
投稿者: wh

Access2010でクエリを使って出荷明細にある取引先CDで連番を振り、横
配列型の表を作ろうとしているのですが、ググっていろんなHPを参考に
しながらDCountでの連番を採番する方法がのっていたので、同じような
方法で試したところ、同一の取引先CDが出荷明細に複数レコード存在す
る場合、サブクエリの部分でDcountを使った場合、連番ではなくレコー
ド数が累積され連番の値として返ってきます。
 
これを同一取引先CDが複数レコード存在しても、サブクエリの部分で必
ず1件づつ連番になるようにするにはどうしたら良いのでしょうか。
 
実際は、レポートに略称と数量を横に表示するために、Report_Openイ
ベントでサブクエリ部分を抜き出したものを読み込んで項目名1=略称A、
項目名2=略称Bとヘッダーにセットするため、必ず1から順に採番した
いのです。
 
レポートデザイン時のレコードソースのクエリ
SELECT
  出荷明細.商品CD
, 出荷明細.規格
, MAX(IIF(連番=1,出荷数,0)) AS 出荷数01
, MAX(IIF(連番=2,出荷数,0)) AS 出荷数02
, MAX(IIF(連番=3,出荷数,0)) AS 出荷数03
, 出荷数01 + 出荷数02 + 出荷数03 AS 出荷数計
FROM 出荷明細 INNER JOIN
(SELECT 出荷明細.取引先CD, 取引先マスタ.略称, DCount("出荷明細.取引先CD","出荷明細","出荷明細.取引先CD<=" & [出荷明細.取引先CD]) AS 連番
FROM 出荷明細 INNER JOIN 取引先マスタ ON 出荷明細.取引先CD = 取引先マスタ.取引先CD
GROUP BY 出荷明細.取引先CD, 取引先マスタ.略称
ORDER BY 出荷明細.取引先CD
) AS 出荷先 ON 出荷明細.取引先CD = 出荷先.取引先CD
GROUP BY 出荷明細.商品CD, 出荷明細.規格
ORDER BY 出荷明細.商品CD, 出荷明細.規格
;
 
Report_Openイベント時のレコードセットに読み込むクエリ
SELECT 出荷明細.取引先CD, 取引先マスタ.略称, DCount("出荷明細.取引先CD","出荷明細","出荷明細.取引先CD<=" & [出荷明細.取引先CD]) AS 連番
FROM 出荷明細 INNER JOIN 取引先マスタ ON 出荷明細.取引先CD = 取引先マスタ.取引先CD
GROUP BY 出荷明細.取引先CD, 取引先マスタ.略称
ORDER BY 出荷明細.取引先CD
 
出荷明細
--------------------------------------------
取引先CD| 取引先名 | 商品CD | 規格 | 出荷数
    1012|A商社 | 1001 | S| 1000
    1012|A商社 | 1002 | M| 500
    1021|B商社 | 1001 | S| 500
    1021|B商社 | 1003 | M| 1000
    1021|B商社 | 1004 | L| 500
    1030|C商社 | 1001 | M| 2000
--------------------------------------------
 
サブクエリの結果
-----------------------------------
取引先コード| 取引先名 | 略称 |連番
        1012|A商社 | A | 2 <--- 連番を1にしたい
        1021|B商社 | B | 5 <--- 連番を2にしたい
        1030|C商社 | C | 6 <--- 連番を3にしたい
-----------------------------------
 
作表したいレポート
 
商品CD | 規格 | A | B | C | 出荷合計
-------+------+-------+-------+-------+---------
  1001 | M | 0 | 0 | 2000 | 2000
  1001 | S | 1000 | 500 | 0 | 1500
  1002 | M | 500 | 0 | 0 | 500
  1003 | M | 0 | 1000 | 0 | 1000
  1004 | L | 0 | 500 | 0 | 500
-------+------+-------+-------+-------+--------
  合計 | | 1500 | 2000 | 2000 | 5500

回答
投稿日時: 19/06/21 13:57:37
投稿者: Suzu

クロス集計クエリを作り、必要に応じて
クロス集計クエリ結果をワークテーブルに追加クエリにて追加し
そのテーブルをレポートのレコードソースでは対応できませんか?
 
TRANSFORM SUM(出荷数) AS 出荷数の合計
SELECT 商品CD, 規格
FROM [出荷明細$A1:E20] AS T1
GROUP BY T1.商品CD, T1.規格
ORDER BY T1.商品CD, T1.規格
PIVOT T1.取引先名

回答
投稿日時: 19/06/21 14:22:30
投稿者: mayu.

引用:
Report_Openイベント時のレコードセットに読み込むクエリ
 
SELECT 出荷明細.取引先CD, 取引先マスタ.略称
, DCount("出荷明細.取引先CD","出荷明細","出荷明細.取引先CD<=" & [出荷明細.取引先CD]) AS 連番
FROM 出荷明細 INNER JOIN 取引先マスタ ON 出荷明細.取引先CD = 取引先マスタ.取引先CD
GROUP BY 出荷明細.取引先CD, 取引先マスタ.略称
ORDER BY 出荷明細.取引先CD

以下のSQLに書き換えると、ご希望の結果になるでしょう。
 
SELECT A.取引先CD
     , A.略称
     , Count(1) As 連番
FROM 取引先マスタ                               A
   , ( SELECT DISTINCT 取引先CD FROM 出荷明細 ) B
   , ( SELECT DISTINCT 取引先CD FROM 出荷明細 ) C
WHERE A.取引先CD  = B.取引先CD
  AND B.取引先CD >= C.取引先CD
GROUP BY A.取引先CD
       , A.略称

投稿日時: 19/06/21 15:33:27
投稿者: wh

みなさん、ありがとうございます。
 
mayuさんへ。
実際には項目数がもっとあって、取りあえず以下のようにしてみました。
SQL文の意味もわからずに単純に追加してみましたが、WHERE句の最後の
引数のみ不等号でつなぐ事でうまくいきそうです。
 
SELECT
  A.取引先CD
, A.略称
, Count(1) AS 連番
FROM 取引先マスタ AS A
, (SELECT DISTINCT 取引先CD FROM 出荷明細) AS B
, (SELECT DISTINCT 取引先CD FROM 出荷明細) AS C
, (SELECT DISTINCT 取引先CD FROM 出荷明細) AS D
, (SELECT DISTINCT 取引先CD FROM 出荷明細) AS E
, (SELECT DISTINCT 取引先CD FROM 出荷明細) AS F
, (SELECT DISTINCT 取引先CD FROM 出荷明細) AS G
, (SELECT DISTINCT 取引先CD FROM 出荷明細) AS H
, (SELECT DISTINCT 取引先CD FROM 出荷明細) AS I
, (SELECT DISTINCT 取引先CD FROM 出荷明細) AS J
WHERE
A.取引先CD = B.取引先CD
 AND B.取引先CD = C.取引先CD
 AND C.取引先CD = D.取引先CD
 AND D.取引先CD = E.取引先CD
 AND E.取引先CD = F.取引先CD
 AND F.取引先CD = G.取引先CD
 AND G.取引先CD = H.取引先CD
 AND H.取引先CD = I.取引先CD
 AND I.取引先CD >= J.取引先CD
GROUP BY A.取引先CD, A.略称
;