Access (一般機能)

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

 
(Windows 7 Professional : Access 2007)
合計が正しく出ない
投稿日時: 19/07/01 18:41:09
投稿者: namonamo20

以下のようなSQL文がSQLビューに記載されているのですが合計が明らかに違う数字で出てきます。
どこがおかしいのか教えてください。
 
SELECT [TEST].SYUKKA_HINBAN_CD, Format([TEST].[URIAGE_DATE],"yyyy/mm") AS 売上月, Sum([MIE_T_SYUKKA].[TANKA]*[MIE_T_SYUKKA].[SURYO]) AS 合計金額
FROM (SELECT * FROM 出荷 UNION ALL SELECT * FROM 売上なし月) AS [TEST], MIE_T_SYUKKA
GROUP BY [TEST].SYUKKA_HINBAN_CD, Format([TEST].[URIAGE_DATE],"yyyy/mm")
HAVING ((([TEST].SYUKKA_HINBAN_CD)="abcdef") AND ((Format([TEST].[URIAGE_DATE],"yyyy/mm")) Between #12/1/2018# And #12/31/2019#))
ORDER BY Format([TEST].[URIAGE_DATE],"yyyy/mm");
 
abcdefの2019年の月ごとの売上合計を出しています。
売上のない月は0で出るようにしています。
なぜか実行結果が出るまでに時間がかかります。
 
分かる方がいましたら教えてください。
よろしくお願いします。
 
以上

回答
投稿日時: 19/07/02 01:37:46
投稿者: Suzu

引用:
合計が明らかに違う数字で出てきます。

((Format([TEST].[URIAGE_DATE],"yyyy/mm")) Between #12/1/2018# And #12/31/2019#)
 
左辺はどんな形の出力になりますかね?
対して
右辺は?日付/時刻型
 
 
引用:
なぜか実行結果が出るまでに時間がかかります。

 
FROM (SELECT * FROM 出荷 UNION ALL SELECT * FROM 売上なし月) AS [TEST], MIE_T_SYUKKA
 
レコード件数として
の部分で、
  テーブル「出荷」件数 + テーブル「売上なし月」 の 件数になります。
 
更に
の部分のレコード を
 
FROM句での JOIN の 連結
WHERE句での 連結
 が無い状態で 指定しています。
 
この場合、内部的には
  赤 件数 x 青 件数 のレコードが生成され、それらのレコード に対し SUMを行います。
 
なので、
 
レコードの生成、レコードの集計 と時間が掛かっているのでは?

投稿日時: 19/07/02 15:47:56
投稿者: namonamo20

Suzuさん
 
返信ありがとうございます。
 
左辺、右辺とはどこを指しているのでしょうか?
 
レコードの生成、レコードの集計 と時間が掛かっているのでは?とのことですが確かにレコードの件数は多いです。
結果を短縮させるSQLの書き方や方法はありますでしょうか?
 
引き続きご教授願います。
 
以上
 
 

回答
投稿日時: 19/07/02 17:01:15
投稿者: Suzu

Format([TEST].[URIAGE_DATE],"yyyy/mm")  : この式の結果返る値のデータ型と値はどんな物でしょう。
 
Between #12/1/2018# And #12/31/2019#   : これは、左辺のデータのうち、
                                              2018年12月1日〜12月31日まで 日付/時刻型
 
 

引用:
結果を短縮させるSQLの書き方や方法はありますでしょうか?

 
テーブル構成全くわかりませんので気づいた部分だけ。
 
1. 初めの UNINクエリのレコード件数が少なくなる様にする。
   各テーブル「出荷 」「売上なし月」 のレコードを少なくする。
    (抽出条件を指定したサブクエリを使わないで
     ワークーブルを使えるならそっちの方が良いでしょう。)
     ワークテーブルに抽出後のレコードのみを入れて、
     そのワークテーブルと、「売上なし月」の組み合わせとする
 
    ※ FROM句が、 【FROM テーブルA, テーブルB】 の場合
       生成される レコード数は
 
      「出荷」のレコード数 + 「売上なし月」 のレコード件数
                         ×
       「MIE_T_SYUKKA」のレコード件数
           になります。
 
 
2. SELECT句 における * は使用せず、必要最小限のフィールドを指定します。
 
3. HAVING は 使用せず WHEREを使う。
 
4. 結合や、条件を指定するフィールドには、インデックスを指定する。
 
 
SQLや、インデックスを色々やっても、レコード件数が多くなれば重くなります。
 
処理対象数が、レコード数 の掛算 になりますから。。。
 
ならない様に
 
テーブルそのままのレコード数を扱わない様に
 ・WHERE句にて、抽出対象を指定した、サブクエリを対象とする。
 ・ワークテーブルを用意し抽出対象をワークテーブルに放り込んでしまう。
 
の様にする事が多いです。

投稿日時: 19/07/03 09:40:11
投稿者: namonamo20

Suzuさん
 
コメントありがとうございます。
 
Format([TEST].[URIAGE_DATE],"yyyy/mm")は元テーブルが日付/時刻型です。
Between #12/1/2018# And #12/31/2019#はクエリのデザインビューで抽出条件で指定したものです。
 
SQLの書き方についてもアドバイスありがとうございます。
私には少し高度な話です。
取り急ぎは正しいデータが出るようになったらチャレンジしてみます。
 
よろしくお願いします。

回答
投稿日時: 19/07/03 10:29:48
投稿者: Suzu

引用:

Format([TEST].[URIAGE_DATE],"yyyy/mm")は元テーブルが日付/時刻型です。
Between #12/1/2018# And #12/31/2019#はクエリのデザインビューで抽出条件で指定したものです。

 
いや。。そういう話ではなくてですね。。
 
 
(Format([TEST].[URIAGE_DATE],"yyyy/mm")) Between #12/1/2018# And #12/31/2019#
 
    文字列型の出力データ Between 日付/時刻型 And 日付時刻型
 
となっており、左辺 と 右辺 のデータ型が一致していません。
つまり、正確な比較ができないので、正しい抽出ができてない。って事です。
 
やるなら、
(Format([TEST].[URIAGE_DATE],"yyyy/mm")) = "2018/12"
 
または、
[TEST].[URIAGE_DATE] Between #12/1/2018# And #12/31/2019#
 
としないと比較ができません。
 
 
なので、SQLのみでの速度Upを図るなら
 
 
案.1 (ユニオンクエリの前の段階でレコード抽出)
引用:
SELECT
    [TEST].SYUKKA_HINBAN_CD,
    Format([TEST].[URIAGE_DATE],"yyyy/mm") AS 売上月,
    Sum([MIE_T_SYUKKA].[TANKA]*[MIE_T_SYUKKA].[SURYO]) AS 合計金額
FROM
    (
        SELECT * FROM 出荷
        WHERE
            出荷.SYUKKA_HINBAN_CD="abcdef"
            AND
            出荷.URIAGE_DATE Between #12/1/2018# And #12/31/2019#
    UNION ALL
        SELECT * FROM 売上なし月
        WHERE
            売上なし月.SYUKKA_HINBAN_CD="abcdef"
            AND
            売上なし月.URIAGE_DATE Between #12/1/2018# And #12/31/2019#
    ) AS [TEST],
    MIE_T_SYUKKA
GROUP BY
    [TEST].SYUKKA_HINBAN_CD,
    Format([TEST].[URIAGE_DATE],"yyyy/mm")
ORDER BY Format([TEST].[URIAGE_DATE],"yyyy/mm");

 
 
案.2 (ユニオンクエリの結果に対しレコード抽出)
引用:
SELECT
    [TEST].SYUKKA_HINBAN_CD,
    Format([TEST].[URIAGE_DATE],"yyyy/mm") AS 売上月,
    Sum([MIE_T_SYUKKA].[TANKA]*[MIE_T_SYUKKA].[SURYO]) AS 合計金額
FROM
    (
        SELECT * FROM 出荷
        UNION ALL
        SELECT * FROM 売上なし月
    ) AS [TEST],
    MIE_T_SYUKKA
WHERE [TEST].SYUKKA_HINBAN_CD="abcdef" AND [TEST].URIAGE_DATE Between #12/1/2018# And #12/31/2019#
GROUP BY
    [TEST].SYUKKA_HINBAN_CD,
    Format([TEST].[URIAGE_DATE],"yyyy/mm")
ORDER BY Format([TEST].[URIAGE_DATE],"yyyy/mm");

 
※SQLは試していません。(案1のサブクエリの括弧 エラー扱いになるかも。。)
 
インデックス次第ですが、案1、案2 でも差がでるんじゃないかな。
出荷 の レコード件数が少なければ、案2、
           多ければ 案1の方が速そう。

投稿日時: 19/07/03 13:43:32
投稿者: namonamo20

Suzuさん
 
ご丁寧にありがとうございます。
教えていただいたものを一通り試してみました。
 
(Format([TEST].[URIAGE_DATE],"yyyy/mm")) = "2018/12"
  または、
[TEST].[URIAGE_DATE] Between #12/1/2018# And #12/31/2019#
はどう変えてもエラーで結果が出ません。
 
案1,2では結果は出たのですが合計がやはり明らかに違う金額になっています。
なぜとんでもない桁の数字で出てくるのでしょうか?
また、売上のない月にも数字が入っています。
 
結果
SYUKKA_HINBAN_CD 売上月    合計金額
079661-0430    2019/01    1595613786826.04
079661-0430    2019/02    1595613786826.04
079661-0430    2019/03    1595613786826.04
079661-0430    2019/04    1675394476167.52
079661-0430    2019/05    1675394476167.52
079661-0430    2019/06    1675394476167.52
079661-0430    2019/07    319122757365.338
079661-0430    2019/08    79780689341.317
079661-0430    2019/09    79780689341.317
079661-0430    2019/10    79780689341.317
079661-0430    2019/11    79780689341.317
079661-0430    2019/12    79780689341.317
 
合計金額を調べるクエリ(MIE_T_SYUKKAテーブルで別途クエリ)
MIE_T_SYUKKA.SYUKKA_HINBAN_CD    TANKA    売上月    合計数
079661-0430            60.89    2019/02    16920
079661-0430            60.89    2019/03    12600
079661-0430            60.89    2019/04    15480
079661-0430            60.89    2019/05    14400
079661-0430            60.89    2019/06    14040
079661-0430            60.89    2019/07    2160
※単価x合計数が合計金額になる
 
 
何度もすいませんが教えてください。
 
以上

回答
投稿日時: 19/07/03 15:16:59
投稿者: Suzu

引用:
(Format([TEST].[URIAGE_DATE],"yyyy/mm")) = "2018/12"
  または、
[TEST].[URIAGE_DATE] Between #12/1/2018# And #12/31/2019#
はどう変えてもエラーで結果が出ません。

 
どんなエラーになるのですか?
その時のSQLは?
 
 
合計値が違うのは、結果だけ提示されても、元デレコードが判らないと判断できない事が多いです。
でも、多分、今回のものは、クロス結合のせいなのかな。。
 
【「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典】
https://wa3.i-3-i.info/word15316.html
 
TEST
--------------------------------
SYUKKA_HINBAN_CD    URIAGE_DATE
--------------------------------
AAA    2019/3/1
AAA    2019/3/2
BBB    2019/4/1
BBB    2019/5/1
 
 
MIE_T_SYUKKA
--------------------------------
TANKA    SURYO
--------------------------------
100    20
200    100
 
 
だとして
 
 
SELECT
    SYUKKA_HINBAN_CD,
    URIAGE_DATE,
    TANKA,
    SURYO
FROM
    TEST, MIE_T_SYUKKA
 
の結果 は クロス結合(積)になりますので
 
 
SYUKKA_HINBAN_CD    URIAGE_DATE    TANKA    SURYO
-----------------------------------------
AAA    2019/3/1    100    20
AAA    2019/3/2    100    20
BBB    2019/4/1    100    20
BBB    2019/5/1    100    20
AAA    2019/3/1    200    100
AAA    2019/3/2    200    100
BBB    2019/4/1    200    100
BBB    2019/5/1    200    100
 
になり、
その結果に対し、SUMを行っているのではないですか?
 
SELECT 
	[TEST].SYUKKA_HINBAN_CD, 
	[TEST].[URIAGE_DATE], 
	Format([TEST].[URIAGE_DATE],"yyyy/mm") AS 売上月, 
	[MIE_T_SYUKKA].[TANKA], 
	[MIE_T_SYUKKA].[SURYO] 
FROM 
	(
		SELECT * FROM 出荷 
		UNION ALL 
		SELECT * FROM 売上なし月 
	) AS [TEST], 
	MIE_T_SYUKKA 
WHERE 
	([TEST].SYUKKA_HINBAN_CD)="abcdef") 
	AND 
	([TEST].[URIAGE_DATE] Between #12/1/2018# And #12/31/2019#) 
ORDER BY [TEST].SYUKKA_HINBAN_CD, Format([TEST].[URIAGE_DATE],"yyyy/mm");

 
だと、にてデータを確認してみてください。
その段階で、集計して良いレコード群になっているか確認しましょう。
 
 
これ以上は各テーブルの構成が判らないと答えることができません。

回答
投稿日時: 19/07/03 16:43:16
投稿者: sk

namonamo20 さんの引用:
SELECT * FROM 出荷
UNION ALL
SELECT * FROM 売上なし月

まず、このユニオンクエリの実行結果が
本当に意図した通りに返されているか否かを
確認されるのが先。
 
namonamo20 さんの引用:
[TEST].SYUKKA_HINBAN_CD

namonamo20 さんの引用:
Format([TEST].[URIAGE_DATE],"yyyy/mm")

もし「全ての[売上月]と全ての[SYUKKA_HINBAN_CD]の組み合わせ」を
得るためにユニオンクエリをメインクエリ側の FROM 句に記述されたのであれば、
[出荷]と[売上なし月]の全てのレコードの全てのフィールドを
UNION ではなく UNION ALL によって併合している点、
そして Format 関数をサブクエリ(ユニオンクエリ)側ではなく
メインクエリ側から呼び出している点が、既に方法として
間違っていると考えられます。
 
また、[出荷]と[売上なし月]に関して詳しい説明がなされていませんが、
これらの 2 つのオブジェクトはテーブルなのでしょうか。
それとも、何らかのテーブルを元に作成されたクエリなのでしょうか。
 
もし後者なのであれば、[出荷]と[売上なし月]の作り方が正しいのか否か、
そしてそもそもユニオンクエリを用いることが適切であるか否かについても
精査しなければならないと思います。
 
Suzu さんの引用:
MIE_T_SYUKKA
--------------------------------
TANKA SURYO
--------------------------------
100 20
200 100

そのテーブルには恐らく[URIAGE_DATE]と[SYUKKA_HINBAN_CD]も
定義されているはず。
(要するに、その辺りのグループ化と外部結合のやり方に問題がある)

投稿日時: 19/07/04 16:41:45
投稿者: namonamo20

Suzuさん
 
いただいたコードで試したところメモリエラーで結果が出力できませんでした。
 
skさん
コメントありがとうございます。
UNIONクエリ結果は意図したものが出ているようです。
 
また、やりたいことは[出荷]クエリにない売上月を[売上なし月]クエリで補って1つのSYUKKA_HINBAN_CDに対して2019年分のすべての月を抽出することです。
今のやり方が間違っているとのことですがどのようにすべきなのでしょうか?
 
[出荷]と[売上なし月]はテーブルから作成したクエリになります。
■出荷クエリ
SELECT MIE_T_SYUKKA.SYUKKA_HINBAN_CD, MIE_T_SYUKKA.URIAGE_DATE, MIE_T_SYUKKA.TANKA, MIE_T_SYUKKA.SURYO
FROM MIE_T_SYUKKA;
■売上なし月
PARAMETERS [prm_DATE] DateTime;
SELECT MIE_TM_SYUKKA_HINBAN.SYUKKA_HINBAN_CD, DateAdd("m",日付.ID,[prm_DATE]) AS 売上日, 0 AS TANKA, 0 AS SURYO
FROM MIE_TM_SYUKKA_HINBAN, 日付;
 
アドバイスいただけますでしょうか?
よろしくお願いします。
 
以上
 
 
  

回答
投稿日時: 19/07/04 17:40:02
投稿者: sk

引用:
■売上なし月
PARAMETERS [prm_DATE] DateTime;
SELECT MIE_TM_SYUKKA_HINBAN.SYUKKA_HINBAN_CD, DateAdd("m",日付.ID,[prm_DATE]) AS 売上日, 0 AS TANKA, 0 AS SURYO
FROM MIE_TM_SYUKKA_HINBAN, 日付;

テーブル[日付]はどのような構造のテーブルで、
どのようなレコードが格納されているのでしょうか。
 
例えば、数値型のフィールド[ID]のみが定義されていて、
それぞれのレコードの[ID]に 0 から 11 までの
いずれかの整数が格納されている、ということでしょうか。

回答
投稿日時: 19/07/04 17:59:12
投稿者: Suzu

引用:
メモリエラーで結果が出力できませんでした。

何件のレコード対象なんでしょうね。。
 
【レコードに0を表示したい】
https://www.moug.net/faq/viewtopic.php?t=78378
 
テーブル「日付」は
 
引用:
テーブル【期間】
ID
---------
 0
 1
 2
 :
11
---------
12ヶ月分表示したいなら 0〜11 のレコードを持たせます
  
その上で
  
PARAMETERS [prm_DATE] DATETIME;
SELECT
  製品マスタ.製品名,
  DATEADD("m",期間.ID, [prm_DATE]) AS 売上日,
  0 AS 金額
FROM 製品マスタ, 期間;

 
の事「期間」の事でしょうか。
 
 
さて、データがおかしいのは、
 
引用:
FROM
    (
        SELECT * FROM 出荷
        UNION ALL
        SELECT * FROM 売上なし月
    ) AS [TEST],
    MIE_T_SYUKKA

 
出荷 には、MIE_T_SYUKKAの全レコードが含まれているにも関わらず
さらにその出荷 に対し、MIE_T_SYUKKA のクロス結合をしているからです。
 
(MIE_T_SYUKKA 件数+α )X MIE_T_SYUKKA件数 の処理をしていますから処理時間も掛かります。
 
 
 
売上なし月 を下記に変更
 
PARAMETERS [prm_DATE] DateTime;
SELECT Q1.SYUKKA_HINBAN_CD, DateAdd("m",日付.ID,[prm_DATE]) AS 売上日, 0 AS TANKA, 0 AS SURYO 
FROM (SELECT DISTINCT MIE_TM_SYUKKA_HINBAN.SYUKKA_HINBAN_CD FROM MIE_TM_SYUKKA_HINBAN) AS Q1, 日付;

 
 
その上で
 
SELECT 
	[TEST].SYUKKA_HINBAN_CD, 
	Format([TEST].[URIAGE_DATE],"yyyy/mm") AS 売上月, 
	Sum([TEST].[TANKA]*[TEST].[SURYO]) AS 合計金額 
FROM 
	(
		SELECT MIE_T_SYUKKA.SYUKKA_HINBAN_CD, MIE_T_SYUKKA.URIAGE_DATE, MIE_T_SYUKKA.TANKA, MIE_T_SYUKKA.SURYO FROM MIE_T_SYUKKA 
		UNION ALL 
		SELECT * FROM 売上なし月
	) AS [TEST], 
WHERE 
	(
		(
			([TEST].SYUKKA_HINBAN_CD)="abcdef"
		) 
		AND 
		(
			[TEST].[URIAGE_DATE] Between #1/1/2019# And #12/31/2019#
		)
	) 
GROUP BY 
	[TEST].SYUKKA_HINBAN_CD, 
	Format([TEST].[URIAGE_DATE],"yyyy/mm") 
ORDER BY 
	Format([TEST].[URIAGE_DATE],"yyyy/mm");

 
だとどうでしょう。

投稿日時: 19/07/04 18:29:06
投稿者: namonamo20

Suzuさん
skさん
 
早速のコメントありがとうございます。
補足です。
 
[日付]テーブル
ID    tsuki
1    0
2    1
3    2
4    3
5    4
6    5
7    6
8    7
9    8
10    9
11    10
12    11
 
以上の構造です。

回答
投稿日時: 19/07/04 19:11:50
投稿者: sk

引用:
やりたいことは[出荷]クエリにない売上月を[売上なし月]クエリで補って
1つのSYUKKA_HINBAN_CDに対して2019年分のすべての月を抽出することです。

引用:
[日付]テーブル
ID tsuki
1 0
2 1
3 2
4 3
5 4
6 5
7 6
8 7
9 8
10 9
11 10
12 11

1. 以下の選択クエリ[Q_年間全製品]を作成する。
 
( SQL ビュー)
-------------------------------------------------------
PARAMETERS [prm_YEAR] Long;
SELECT Format([prm_YEAR],"0000") & Format([日付].[ID],"\/00") AS [売上月],
       [MIE_TM_SYUKKA_HINBAN].[SYUKKA_HINBAN_CD]
FROM [MIE_TM_SYUKKA_HINBAN],
     [日付];
-------------------------------------------------------
([prm_YEAR]は任意の西暦年(この場合は 2019 )を入力するための
長整数型のパラメータ)
 
2. 以下の選択クエリ[Q_月別売上金額合計]を作成する。
 
( SQL ビュー)
-------------------------------------------------------
SELECT Format([MIE_T_SYUKKA].[URIAGE_DATE],"yyyy/mm") AS [売上月],
       [MIE_T_SYUKKA].[SYUKKA_HINBAN_CD],
       Sum([MIE_T_SYUKKA].[TANKA] * [MIE_T_SYUKKA].[SURYO]) AS [合計金額]
FROM [MIE_T_SYUKKA]
GROUP BY Format([MIE_T_SYUKKA].[URIAGE_DATE],"yyyy/mm"),
         [MIE_T_SYUKKA].[SYUKKA_HINBAN_CD];
-------------------------------------------------------
 
3. 上記の 2 つのクエリを元に以下の選択クエリ
   [Q_年間全製品月別売上金額合計]を作成する。
 
( SQL ビュー)
-------------------------------------------------------
SELECT [Q_年間全製品].[SYUKKA_HINBAN_CD] AS [出荷品番コード],
       [Q_年間全製品].[売上月],
       CCur(Nz([Q_月別売上金額合計].[合計金額],0)) AS [合計金額]
FROM [Q_年間全製品]
LEFT JOIN [Q_月別売上金額合計]
 ON ([Q_年間全製品].[売上月] = [Q_月別売上金額合計].[売上月])
AND ([Q_年間全製品].[SYUKKA_HINBAN_CD] = [Q_月別売上金額合計].[SYUKKA_HINBAN_CD])
WHERE [Q_年間全製品].[SYUKKA_HINBAN_CD] = "abcdef"
ORDER BY [Q_年間全製品].[SYUKKA_HINBAN_CD],
         [Q_年間全製品].[売上月];
-------------------------------------------------------
 
基本的には以上のような形でよいのではないかと。
 
なお、今のところの懸念すべき点としては、
 
引用:
■出荷クエリ
SELECT MIE_T_SYUKKA.SYUKKA_HINBAN_CD, MIE_T_SYUKKA.URIAGE_DATE, MIE_T_SYUKKA.TANKA, MIE_T_SYUKKA.SURYO
FROM MIE_T_SYUKKA;
■売上なし月
PARAMETERS [prm_DATE] DateTime;
SELECT MIE_TM_SYUKKA_HINBAN.SYUKKA_HINBAN_CD, DateAdd("m",日付.ID,[prm_DATE]) AS 売上日, 0 AS TANKA, 0 AS SURYO
FROM MIE_TM_SYUKKA_HINBAN, 日付;

[MIE_T_SYUKKA]が「日ごとの売上履歴テーブル」、
[MIE_TM_SYUKKA_HINBAN]が「製品マスター」に当たる
テーブルであるとして、前者にあって後者にない
[SYUKKA_HINBAN_CD](マスターにない未知の製品)が
存在しないかどうかです。

投稿日時: 19/07/09 18:57:38
投稿者: namonamo20

Suzuさん
skさん
 
書き込みありがとうございます。
Suzuさんに教えていただいたものはFROM句の構文エラーが出てしまいました。
skさんうまく動作しました。
 
皆様ありがとうございました。
 
以上