Access (一般機能)

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

 
(Windows 7 Professional : Access 2007)
レコードに0を表示したい
投稿日時: 19/06/25 19:37:57
投稿者: namonamo20

クエリで2つのテーブルを使用して製品の月ごとの売上を出そうとしています。
製品マスターテーブルと日ごとの売上テーブルを使っていて、
製品マスタの製品名と売上テーブルの製品名を結合しています。
その際、売上テーブルは必ずしも製品マスタの製品すべてを売上げているわけではないので、
クエリに製品マスタの全製品名が出てきません。(売れた物のみ)
売れなかった製品も0円としてクエリに出してほしいのですが可能でしょうか?
(製品マスターテーブルの全商品の売上を0円も含めてクエリで表示したい)
 
分かる方がいましたらお教えください。
 
よろしくお願いします。
 
以上

回答
投稿日時: 19/06/26 10:16:13
投稿者: sk

引用:
クエリで2つのテーブルを使用して製品の月ごとの売上を出そうとしています。

・任意の[売上年月](例えば「2019年6月」)に該当する
 [売上テーブル]のレコードを抽出し、
 [製品名]ごとにグループ化して
 [売上金額]の合計を集計しようとしている。
 
・[売上テーブル]の全ての(あるいは任意の条件に該当する)レコードを
 [売上年月]と[製品名]ごとにグループ化して
 [売上金額]の合計を集計しようとしている。
 
どちらの意味でおっしゃっているのでしょうか。
 
引用:
製品マスターテーブルと日ごとの売上テーブルを使っていて、
製品マスタの製品名と売上テーブルの製品名を結合しています。

とりあえず、それらのテーブルの定義内容、及び
クエリの実行結果のイメージを明記されることをお奨めします。

投稿日時: 19/06/26 15:34:15
投稿者: namonamo20

skさん
 
返信ありがとうございます。
グループ化については月ごとでグループ化し売上の合計はできております。
ただ、その状態で製品マスターテーブルと日ごとの売上テーブルから製品名を結合しても売上のない月が出てこないのでそれを0円で表示したいというのが今回の質問の意図になります。
 
わかりにくい質問で申し訳ありません。
分かるようでしたらお教えください。
 
以上
 

回答
投稿日時: 19/06/26 15:48:50
投稿者: sk

namonamo20 さんの引用:
グループ化については月ごとでグループ化し売上の合計はできております。
ただ、その状態で製品マスターテーブルと日ごとの売上テーブルから製品名を結合しても売上のない月が出てこないのでそれを0円で表示したいというのが今回の質問の意図になります。

ならば「全ての[売上年月]と[製品名]の組み合わせを返す選択クエリ」と
「[売上年月]と[製品名]ごとの[売上金額]の合計を返す選択クエリ」とを
[売上年月]と[製品名]をキーとして外部結合する選択クエリを作成なされば
よろしいのではないでしょうか。
 
引用:
とりあえず、それらのテーブルの定義内容、及び
クエリの実行結果のイメージを明記されることをお奨めします。

投稿日時: 19/06/26 19:35:11
投稿者: namonamo20

skさん
何度も回答ありがとうございます。
ご指摘の通りイメージを記載します。
 
【クエリ完成イメージ】
製品名   売上月   金額   
----------------------------------
ABC    2019/01   1,000
ABC    2019/02   2,000   
ABC    2019/03   3,500      
ABC    2019/04   4,000   
ABC    2019/05   6,000      
ABC    2019/06   7,050   
ABC    2019/07   5,678
ABC    2019/08   0   →ココを表示したい(日ごとの売上テーブルにない)
ABC    2019/09   50
ABC    2019/10   3,450
ABC    2019/11   100
ABC    2019/12   0   →ココを表示したい(日ごとの売上テーブルにない)
DEF    2019/01   1,000
・      ・     ・
----------------------------------
※売上月はクエリの集計に売上月: Format([売上日],"yyyy/mm")
※金額はクエリの集計に金額: Sum(日ごとの売上テーブル.[金額])
 
【やりたいこと】
上記「ココを表示したい」部分が今は出ないので出るようにしたい。
 
【テーブル】
■製品マスターテーブル
 
製品名 
-------
ABC
DEF
 ・
 ・
 ・
 ・
-------
※すべての取扱製品のテーブル
 
■日ごとの売上テーブル
 
製品名   売上日   金額
------------------------------
ABC  2019/1/1   500
DEF  2019/2/2   1000
ABC  2019/1/30   500
DEF  2019/12/4   2000
 ・     ・    ・
 ・     ・    ・
------------------------------
※製品ごとにいついくら売り上げたかがわかるテーブル
※上記2つのテーブルは製品名を結合している
 
不足があれば教えてください。
よろしくお願いします。
 
以上
 
 
 

回答
投稿日時: 19/06/27 07:30:21
投稿者: よろずや

>上記2つのテーブルは製品名を結合している
 
※外部結合の仕方
結合線をダブルクリックして、結合プロパティというダイアログボックスを開く。
「'製品マスタ'の全レコードと'売上テーブル'の同じ結合フィールドのレコードだけを含める。」
を選択する。
 
https://water2litter.net/gin/?p=1379

投稿日時: 19/06/27 08:56:53
投稿者: namonamo20

よろずやさん
 
返信ありがとうございます。
教えていただいた設定はすでにしてあります。
それでも0円の月が出てきません。
他に考えられる原因はありますでしょうか?

回答
投稿日時: 19/06/27 09:28:37
投稿者: hatena
投稿者のウェブサイトに移動

引用:
教えていただいた設定はすでにしてあります。
それでも0円の月が出てきません。

 
売上テーブルにその月のレコードがないという状態ですね。
 
出力したい年月全てを含むテーブルまたはクエリが必要になります。
例えば、1年分の集計を出力したいなら、
 
売上月    
--------- 
2019/01
2019/02
・・・
・・・・
2019/12    

 
あるいは、
 
月
---
1
2
・
・
12

 
というテーブルを作成しておいて、下記のクエリを作成すればいいでしょう。
 
SELECT [年を入力] & "/" & [月] AS 年月 FROM 月;
 
さらに、クエリを新規作成して、上記の年月のテーブルまたはクエリと製品マスタを追加して保存します。
これですべての製品×12か月分のデータが出力されます。
 
このクエリと売上テーブルを外部結合して集計クエリにすれば希望の結果になります。
 
このままでは合計金額が 0 にならずにNullになると思いますので、集計クエリのフィールドを下記のように設定します。
 
フィールド 合計金額: Nz(Sum([金額]).0)
集計    演算

回答
投稿日時: 19/06/27 10:42:59
投稿者: sk

引用:
【クエリ完成イメージ】
製品名   売上月   金額   
----------------------------------
ABC    2019/01   1,000
ABC    2019/02   2,000   
ABC    2019/03   3,500      
ABC    2019/04   4,000   
ABC    2019/05   6,000      
ABC    2019/06   7,050   
ABC    2019/07   5,678
ABC    2019/08   0   →ココを表示したい(日ごとの売上テーブルにない)
ABC    2019/09   50
ABC    2019/10   3,450
ABC    2019/11   100
ABC    2019/12   0   →ココを表示したい(日ごとの売上テーブルにない)
DEF    2019/01   1,000
・      ・     ・
----------------------------------

[売上月]がいつからいつまでの期間の集計結果を求めたいのか次第。
 
上記の例のように「(まだ売上が発生していない)未来の年月
( 2019/07 以降)まで含めるのであれば、hatena さんが示されているように
[年月テーブル]なり[年テーブル]なり[月テーブル]なりが必要です。
 
引用:
全ての[売上年月]と[製品名]の組み合わせを返す選択クエリ

あくまで[売上テーブル]に格納されている[売上日]の範囲のみに限定し、
かつ「どの製品の売上も発生していない年月」が存在していない
(毎月必ず 1 つ以上の製品の売上が発生している)ことが
保証されている場合は、例えば次のような選択クエリを作成することで
「全ての[売上月]と全ての[製品名]の組み合わせ」を得ることが出来るでしょう。
 
( SQL ビュー)
-------------------------------------------------------------
SELECT Format([日ごとの売上テーブル].[売上日],"yyyy/mm") AS [売上月],
       [製品マスターテーブル].[製品名]
FROM [日ごとの売上テーブル],
     [製品マスターテーブル]
GROUP BY Format([日ごとの売上テーブル].[売上日],"yyyy/mm"),
         [製品マスターテーブル].[製品名];
-------------------------------------------------------------

回答
投稿日時: 19/06/27 16:15:25
投稿者: Suzu

レコードが無いので表示されない。のは理解されていると思います。
 
製品名 売上日 金額
----------------
ABC 2019/01/01 0
ABC 2019/02/01 0
  :
ABC 2019/12/01 0
DEF 2019/01/01 0
DEF 2019/02/01 0
  :
---------------
 
があれば良い。
 
更に、「いつから」「いつまで」 に対し動的に指定できるとかなり楽ですよね。
 
「いつから」「いつまで」 の 年月 のレコードを予めテーブルに持たせれば、良いですが
期間を変えたいとき、テーブルのレコードを書き換える必要があります。
VBAを使うなら、レコードの書き換えは 楽ちんですが 手動だと面倒です。
 
「何か月分」の表示が必要なのかは、多分、変わりませんよね?
なので、その月数分のレコードを持ったテーブル「期間」を準備します。
 
テーブル【期間】
ID
---------
 0
 1
 2
 :
11
---------
12ヶ月分表示したいなら 0〜11 のレコードを持たせます
 
その上で
 
PARAMETERS [prm_DATE] DATETIME;
SELECT
  製品マスタ.製品名,
  DATEADD("m",期間.ID, [prm_DATE]) AS 売上日,
  0 AS 金額
FROM 製品マスタ, 期間;
 
上記SQL の様に
・パラメーターで期間開始日を 入力する
・パラメーターの値に対し、期間の各レコードの値を月に足し算する
を行う様にします。
 
実行時に prm_DATE のダイアログが出ますので
そこで 2019/01/01 を入力すれば、先のデータが得られます。
 
 
そして 作成したクエリと、「売上」テーブルを ユニオン を使い繋げます。
 
そうすると
製品名    売上日    金額
--------------------
ABC    2019/01/01    500
DEF    2019/02/02    1000
ABC    2019/01/30    500
DEF    2019/12/04    2000
ABC    2019/01/01    0
ABC    2019/02/01    0
    :
ABC    2019/12/01    0
DEF    2019/01/01    0
DEF    2019/02/01    0
    :
--------------------
を得られますので
 
そのクエリに対し集計を行えば良いでしょう。
 
 
※ 最終的に、レポートにて表示したいのなら、別の方法があります。

投稿日時: 19/06/28 19:35:56
投稿者: namonamo20

皆様
 
回答ありがとうございました。
何とかやれそうです。