質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
Access

Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

3回答

6815閲覧

ユニオンクエリでまとめると特定のデータが二重で表示される

rabbit_

総合スコア1

Access

Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

0クリップ

投稿2020/05/27 08:12

編集2020/05/28 05:15

前提・実現したいこと

集計した5個のクエリをユニオンクエリでまとめ、まとめたクエリをさらに集計し、合計売上をまとめたい。

発生している問題

①Excelからコピーしてきた売上データ5個分のテーブル
例)2000年売上 20001年売上・・・

②①をひとつずつ集計し、クリエを作成(5個分)
例)2000年売上クリエ(テーブルの2000年売上を集計したもの)
フィールド名:JANコード・商品名・販売価格をグループ化、販売数・合計売上を合計に設定

③②のクリエ5個分をユニオンクリエでまとめる
※まとめた結果のクリエで問題が発生

4個分のクリエのデータは二重表示なしなのに、1個分のクリエデータが二重表示されてしまう

最終的には、③でまとめたユニオンクリエをさらに集計したデータを出したいです。

データ例

フィールド名:JANコード 商品名 販売数 販売価格(税抜) 合計売上(税抜)
1234    ○○   5    1000       5000


データ例①での状態
(2000年売上)
フィールド名:JANコード 商品名 販売数 販売価格(税抜) 
1234    ○○   5    1000      
1234    ○○   5    1000
5678    ●●   4    1000

(2001年売上)
フィールド名:JANコード 商品名 販売数 販売価格(税抜) 
5678    ●●   5    1000      
5678    ●●   3    1000       

データ例②での状態(2000年売上テーブルを元に集計)
フィールド名:JANコード 商品名 販売数 販売価格(税抜) 合計売上(税抜)
1234    ○○   5    1000       5000
1234    ○○   5    1000       5000
5678    ●●   4    1000       5000

データ例③での状態 ※こうなっていてほしい例
フィールド名:JANコード 商品名 販売数 販売価格(税抜) 合計売上(税抜)
1234    ○○   5    1000       5000
1234    ○○   5    1000       5000
5678    ●●   4    1000       4000
5678    ●●   5    1000       5000
5678    ●●   3    1000       3000

データ例③での状態 ※実際の状態
フィールド名:JANコード 商品名 販売数 販売価格(税抜) 合計売上(税抜)
1234    ○○   5    1000       5000
1234    ○○   5    1000       5000
5678    ●●   4    1000       5000
5678    ●●   5    1000       5000
5678    ●●   5    1000       5000
5678    ●●   3    1000       3000
5678    ●●   3    1000       3000
この状態でユニオンクエリでのデータが作成されてしまうので、ここからさらに集計をした際に
JANコード5678の商品が販売合計数12になっている状態が正しいはずが、
「※実際の状態」でユニオンクエリでのデータが作成されてしまうせいで
ユニオンクエリでまとめたデータをさらに集計すると販売合計数20となってしまい、
本来は
フィールド名:JANコード 商品名 販売数 販売価格(税抜) 合計売上(税抜)
1234    ○○   10    1000       10000
5678    ●●   12    1000       12000
となった最終データがほしいのに
フィールド名:JANコード 商品名 販売数 販売価格(税抜) 合計売上(税抜)
1234    ○○   10    1000       10000
5678    ●●   20    1000       20000
となってしまいます。

解決方法をご存知の方いらっしゃいましたらご助力いただけますと幸いです。


SQL追加

SELECT [2000年].JANコード, [2000年].商品名, Sum([2000年].販売数) AS 販売数の合計, [2000年].販売価格, Sum([2000年].合計売上) AS 合計売上の合計
FROM 2000年
GROUP BY [2000年].JANコード, [2015年].商品名, [2000年].販売価格;

UNION ALL

SELECT [2001年].JANコード, [2001年].商品名, Sum([2001年].販売数) AS 販売数の合計, [2001年].販売価格, Sum([2001年].合計売上) AS 合計売上の合計
FROM 2001年
GROUP BY [2001年].JANコード, [2001年].商品名, [2001年].販売価格;




UNION ALLで5個分すべて入力し、5個目の最後にはUNION ALL入れておりません

この度はご回答くださった皆様ありがとうございました。
おかげ様で原因が解決しました。
今回は原因解決の直接的なご助言をいただいた方をベストアンサーとさせていただきました。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

sousuke

2020/05/27 08:33

「クエリ」のsqlを提示できませんか?
rabbit_

2020/05/27 08:51

sousuke様 閲覧ありがとうございます。 SELECT [2000年].JANコード, [2015年].商品名, Sum([2015年].販売数) AS 販売数の合計, [2000年].販売価格, Sum([2000年].合計売上) AS 合計売上の合計 FROM 2000年 GROUP BY [2000年].JANコード, [2000年].商品名, [2000年].販売価格; 2000年の部分はテーブルごとに2000年・2001年・2002年・2004年・2005年の5個分あり、 SQLビューでの表記はこの部分だけ異なり、それ以外はすべて一緒です。 宜しくお願いいたします。
YT0014

2020/05/27 09:53

1から3の全クエリをSQL表示にして、ここへの返答ではなく、質問の編集にてご提示いただけないと、どこがまちがっているのかの指摘ができかねます。
YT0014

2020/05/27 10:00

データ例3での希望状態で、最初の同一内容の2行が問題ない理由が、現在の情報ではわかりません。
sousuke

2020/05/27 10:41

質問を編集してクエリ文を載せてください。おそらく上で提示しているのは1のクエリでしょうけど、ユニオンクエリのSQLも提示してください。
rabbit_

2020/05/28 01:04

YT0014様 閲覧ありがとうございます。 上部の「③②のクリエ5個分をユニオンクリエでまとめる」の作業をした結果のデータ表記が「データ例③での状態 ※こうなってほしい例」に繋がります。 例えば、JANコード1234 商品名○○がテーブル名:2000年売上の中のデータだったとして、JANコード5678 商品名●●がテーブル名:2001年売上の中のデータとします。(「データ例①での状態」に記載通り) 上部②の作業時にはデータに問題はなく、ユニオンクリエでまとめるとデータの結果が2001年売上データの中の一部が二重表記されてしまいます。
rabbit_

2020/05/28 01:06

sousuke様 ご返信ありがとうございます。 編集でユニオンクエリのSQL記載しました。 集計したクエリのSQLをコピペして、改行下にUNION ALLを入力を繰り返したものがユニオンクリエのSQLになっています。
YT0014

2020/05/28 02:28

例示の2(2000年)の結果が、そもそも、間違っています。正しくは、以下。 1234 ○○ 10 1000 10000 5678 ●●  4 1000  5000 もし、これを誤解されていたとしたら、最終結果も誤解されていませんか?
guest

回答3

0

こちらで把握した限りでは、「二重に出力される」のが正しい結果だと思います。
1行だけなのは、年単位の販売数がたまたま一致したのが1回だけだったからでは?

まずは、年毎(2)の出力結果を、実際に確認してみてください。
そして、年毎の結果から、正しい総合計を(二重になっている商品だけでも)計算してみてください。

例の場合
データ例①での状態
(2000年売上)
JANコード 商品名 販売数 販売価格(税抜) 
1234    ○○   5   1000
1234    ○○   5   1000
5678    ●●   4   1000

(2001年売上)
JANコード 商品名 販売数 販売価格(税抜) 
5678    ●●   5   1000
5678    ●●   3   1000

データ例②での状態(2000年売上テーブルを元に集計)
JANコード 商品名 販売数 販売価格(税抜) 合計売上(税抜)
1234    ○○   10   1000      10000
5678    ●●   4   1000       5000

2001年
JANコード 商品名 販売数 販売価格(税抜) 合計売上(税抜)
5678    ●●   8   1000       8000

データ例③での状態 クエリの結果
JANコード 商品名 販売数 販売価格(税抜) 合計売上(税抜)
1234    ○○   10   1000      10000 <<2000年
5678    ●●   4   1000       5000 <<2000年
5678    ●●   8   1000       8000 <<2001年

なお、個人的には、hatena19さんの回答のようなデータ構造にするのをお勧めします。
そうしておけば、2010年から7年分とかの集計が必要になっても、条件欄の変更のみで対応可能になります。

投稿2020/05/28 02:32

編集2020/05/28 02:46
YT0014

総合スコア1708

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

YT0014

2020/05/28 02:50

なお、二重計上か否かが不明瞭なのは、集計時に年データを付記していないのが原因です。別年であれば、二重計上ではないのが明確になります。
rabbit_

2020/05/28 05:12

ありがとうございます。 原因の解決ならびに作成方法の改善についてご教示いただきました方法にて作成してみようと思います。 ご丁寧な回答ありがとうございました。
guest

0

ユニオンクエリで元のデータ件数の合計からデータ件数が減る場合はあっても、増えることはありません。
もし、増えているなら、ユニオンクエリにする前の5つのクエリですでに増えているはずです。
もとの5つのクエリを確認してみてください。
わからなければ、もとの5つのクエリのSQLを提示してください。


質問の内容とは少しずれますが、

①Excelからコピーしてきた売上データ5個分のテーブル

例)2000年売上 20001年売上・・・

この出発点から、データベースの設計としては間違っています。そのため、複雑なこと(ユニオンクエリとか)になってます。複雑になりますし、処理速度が遅くなる原因にもなります。

5つのテーブルを作るのではなく一つのテーブルに追加するようにします。そのとき、「年度」フィールドを追加しておきます。

データ例① なら、下記のような一つのテーブルにします。

年度 JANコード 商品名 販売数 販売価格(税抜) 
--------------------------
2000 1234    ○○   5    1000
2000 1234    ○○   5    1000
2000 5678    ●●   4    1000
2001 5678    ●●   5    1000
2001 5678    ●●   3    1000

こうしておけば、ユニオンクエリを使う必要はなく、単純な集計クエリで集計できます。

投稿2020/05/28 01:47

hatena19

総合スコア33620

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

rabbit_

2020/05/28 02:05

hatena19様 ご回答ありがとうございます。 なるほどですね、ありがとうございます。 今後も継続して今回作成したものを元にテーブルを追加していき、更新をしていこうと思っていたので、二重表記される原因がわからないことには今後も使うためにこのままでいいのか…という疑問もありますので、もし解決しない場合はhatena19様のご意見参考に作り直しをしようと思います。
hatena19

2020/05/28 03:10

「今回作成したものを元にテーブルを追加していき、」だと、追加するたびにSQLを書き直す必要がでてきます。テーブルを追加するのではなく、一つのテーブルにデータを追加するという設計にすべきです(強く断言)。 件数が増える(二重表記)に関しては、ユニオンクエリにする前のクエリのデータを確認してください。ユニオンクエリの問題ではないはずです。
rabbit_

2020/05/28 05:11

ありがとうございます。 ひとつのテーブルにデータを追加していった方が簡単なんですね。 また、今回の原因について解決しましたので、ご教示いただいた方法でデータの作成をしてみようと思います。 ご丁寧な回答ありがとうございました。
guest

0

ベストアンサー

4個分のクリエのデータは二重表示なしなのに、1個分のクリエデータが二重表示されてしまう

先ず、ユニオンクエリーですが、unionでしょうかunion allでしょうか?

unionの場合は、項目の一部が違うだけでも(例えば商品名の後ろに空白の有り無しとか)別なものとして表示されます。

union allの場合は、そういったデータが別なクエリーに存在しているという事です。

2重表示されるものが分かっているなら、それぞれのクエリーでのデータ詳細を確認して下さい。

追記

元にしているデータのレイアウトが変わらないなら、union allするのはデータをマージするのを先に行うと簡潔です。

SQL

1SELECT JANコード, 商品名, 販売価格 2 , Sum(販売数) AS 販売数の合計, Sum(合計売上) AS 合計売上の合計 3from ( 4 select * from [2000] 5 union all select * from [2001] 6 union all select * from [2002] 7 union all select * from [2003] 8 union all select * from [2004] 9) as marge 10GROUP BY JANコード, 商品名, 販売価格

unionの部分で、元の年度を識別するようには以下の様にします。

SQL

1 select 2000 as 年度, * from [2000] 2 union all select 2001, * from [2001] 3 union all select 2002, * from [2002] 4 union all select 2003, * from [2003] 5 union all select 2004, * from [2004]

追記

本来 フィールド名:JANコード 商品名 販売数 販売価格(税抜) 合計売上(税抜) 1234    ○○   10    1000       10000 5678    ●●   12    1000       12000 となった最終データがほしいのに フィールド名:JANコード 商品名 販売数 販売価格(税抜) 合計売上(税抜) 1234    ○○   10    1000       10000 5678    ●●   20    1000       20000 となる

上記で言う所のJANコード「5678」のデータを見て下さい。
上記の結果になるなら、「数量」に値があり「売上」が0のデータがあるはずです。

投稿2020/05/28 00:48

編集2020/05/28 02:31
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

rabbit_

2020/05/28 01:11

sazi様 ご回答ありがとうございます。 UNION ALLを使用しています。 それぞれのクエリも確認しましたが、二重で表示される売上データは1つしか存在しません。 今回の例でいうと、2000年・2002年~2005年には JANコード5678・商品名●●は同じ数量での売上データが存在せず、2001年の売上テーブルにのみデータがあります。
sazi

2020/05/28 01:26 編集

質問に追記したユニオンクエリーの内容ですけど、実際に動作させているものにして下さい。 ;があったりとか、販売価格でもgroup byしているとか、?な部分がありますので。
rabbit_

2020/05/28 01:37

動作させているものを[]のテーブル名を例としてあげているものに変更しているのみで、それ以外に変更はなく、実際にSQLビューに表示されているままとなっております。 販売価格は名前の通り商品1個あたりの販売価格を表示しており、販売数によって変更があるものではありませんのでJANコードや商品名と同じくグループ化をさせています。 集計したいのは販売数と合計売上のみです。([販売数]*[販売価格]で合計売上を計算)
sazi

2020/05/28 01:41

> 実際にSQLビューに表示されているまま from句にないテーブル名( [2015年].商品名)とか、unionの前に;があったりしますので、そもそもエラーになると思いますけど
sazi

2020/05/28 01:45

因みに販売価格が年で変更になっていると、別なデータとして表示されますけど、販売価格には変動はないのでしょうか?
rabbit_

2020/05/28 01:56

失礼いたしました。SQL修正しました。 [2015年].商品名は実際のデータですので実際には問題ないです。 (ここでは2000年に修正しました) ;がついてる点についてですが、SQLビューに表示されているものをコピーして[]のみ修正、それ以外の半角スペース含め;などもいじっておりませんのでデータのままです。 ちなみに、二重表示されてしまうデータがあるテーブル以外はユニオンクリエで実行した際に問題なく表示されているので、なぜエラーになるとおっしゃっているのかが分からず… 同じ商品でも販売価格が違う商品があり、そういったものは別で集計されてほしいので、一部変動はありますが、別データとしての表示で問題ありません。 今回は販売価格も同じでテーブル・テーブルを集計したクリエの状態までは該当のデータは1つしかないのに、ユニオンクリエを実行すると二重表示されてしまう件についての解決策を探しています。
sazi

2020/05/28 02:02

;については失礼しました。 ;はセンテンスの終わりを示すもので、本来そこで一文としてのSQLは終わりです。 unionの場合は確かにエラーになりませんね。
sazi

2020/05/28 02:07 編集

後半に示したSQL(unionのみの部分)を作成して保存して下さい。 その上でそのクエリーに対して重複となっているJANコードを抽出するとどうなるか確認下さい。 先ず、異なる年度のデータがいないかどうかを確認して下さい。
rabbit_

2020/05/28 02:08

ありがとうございます。 こちらこそせっかくご助言いただいているのにお話すべてを理解できておらず恐縮です。 ご提案いただいた内容で一度作成してみて、またご報告させていただきます。 引き続き、何卒宜しくお願い申し上げます。
sazi

2020/05/28 02:20 編集

回答に追記しました。
rabbit_

2020/05/28 02:32

追記ありがとうございます。 「unionの部分で、元の年度を識別するようには以下の様にします。」の下にあるSQLで作成しましたところ、2003年(仮)のデータに問題の二重表記になっているデータが存在していました。 ややこしい言い方できちんと伝わるか不安ですが… 2001年のテーブルを集計したデータが2003年のテーブル(集計前の元データ)に入っておりました。 そもそもこうなった原因は不明ですが、Accessを初めて使用しましたので、自覚なく保存をしていたのかもしれません。 テーブルのデータを確認した際にJANコードをフィルタで確認したのですが、数値フィルタに該当のJANコードがなかったため、この現象を見落としておりました。 テーブルをいちから作り直し、またご報告させていただきます。
sazi

2020/05/28 03:01 編集

今回のようなunionは正規化を疑似的に行うものです。 テーブルを正規化すればunionは不要になりますし、データチェックも容易になりますので、お勧めします。 正規化したテーブルに変更する場合、年度を含めたunionクエリーを元にテーブル作成クエリーで行うと効率的です。
rabbit_

2020/05/28 05:09

データを作り直しましたところ、無事に二重表示されていたデータが消えて、正常なデータの作成ができました。 この度はご丁寧かつ適切なアドバイスありがとうございます。 大変勉強になりました。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問