Accessクエリでのグループ化

質問

Accessクエリでのグループ化

以下の2つのテーブルが有ります。

■社員テーブル
ID　名前
1　Aさん
2　Bさん
3　Cさん
4　Dさん
5　Eさん

■受付テーブル
ID　受付内容　受付日　　　社員ID
1　　xxxxx 2010/3/1　　　1
2 xxxxx 2010/3/20　 　3
3 xxxxx 2010/4/10　 　2
4 xxxxx　　 2010/4/11 　5
5 xxxxx 2010/4/15 　1
6 xxxxx 2010/4/30 　2
7 xxxxx 2010/5/3 　4

そこで社員毎の受付数をカウントしたいと思っています。
※受付日が2010/4/1以上5/1未満

名前　受付回数
Aさん　1
Bさん　2
Cさん　0
Dさん　0
Eさん　1

となるイメージです。

しかし、実際にクエリを作ると結果が
名前　受付回数
Aさん　1
Bさん　2
Eさん　1
と、なってしまいます。。

名前：グループ化
受付テーブルのID：カウント
受付日：>=2010/04/01 and <2010/05/01
※社員テーブルの『ID』と、受付テーブルの『社員ID』を
　紐つけており、結合プロパティでは『社員テーブルの
　全レコードと受付テーブルの同じ結合フィールドのレコード
　だけを含める』としております。

あらかじめ、受付テーブルで受付期間の条件でクエリ
を作成しておき、それと社員テーブルを紐つけた場合は
イメージ通りの結果になるのですが、1つのクエリで
上記イメージでの結果を表示したいと思ってます。
可能でしょうか？

ご教授願います。

回答No.1

30246kiku · ベストアンサー

サブクエリを使うのでしょうか。

以下でどうなりますか。

SELECT 社員.名前, IIF(IsNull(T1.社員ID),0,T1.カウント) AS 受付回数
FROM 社員 LEFT JOIN
(SELECT 社員ID, Count(*) AS カウント FROM 受付
WHERE 受付日 >= #2010/4/1# AND 受付日 < #2010/5/1#
GROUP BY 社員ID) AS T1 ON 社員.ID = T1.社員ID;

社員テーブルを、受付日の範囲でグループ化した社員IDの結果T1と LEFT JOIN で結合します。
この時、社員テーブルにあって T1 に無い社員ID部分は Null となるので、
Null なら 0 へ、そうでなければカウントを受付回数とします。

（Nullのところは見た目、空欄になります）
IIF(IsNull(T1.社員ID),0,T1.カウント) AS 受付回数
↓
T1.カウント AS 受付回数
で確認できると思います。

お礼

ご回答の確認をするのが遅くなってしまい申し訳ございません。

30216kikuさまの回答にありましたサブクエリで、イメージ通りの表示が
できるようになりました。

SELECT 社員テーブル.名前, IIf(IsNull(T1.社員ID),0,T1.カウント) AS 受付回数
FROM 社員テーブル LEFT JOIN [SELECT 社員ID, Count(*) AS カウント FROM 受付テーブル
WHERE 受付日>=#4/1/2010# And 受付日<#5/1/2010# GROUP BY 社員ID]. AS T1
ON 社員テーブル.ID = T1.社員ID;

大変勉強になりました。
ありがとうございました。

回答No.5

30246kiku

＃１です

現在＃４までの投稿通知メールが届いていますが、＃１のだけ届いてません。
システム上の何かでしょうか。検閲に回っているとか？
質問者さんへの通知は、どうなっているのでしょう？

＃１のは 受付日 >= #2010/4/1# AND 受付日 < #2010/5/1# の決め打ちでしたが
流動的に指定できるようにしてみました。

クエリを開くと [年月？]　を聞いてくるので、
「2010/4」とか「H22/4」とか「平成22年4月」とか入力します。

SELECT 社員.名前, IIF(IsNull(T1.社員ID),0,T1.カウント) AS 受付回数
FROM 社員 LEFT JOIN
(SELECT 社員ID, Count(*) AS カウント FROM 受付
WHERE 受付日 >= CDate([年月？]) AND 受付日 < DateAdd("m",1,[年月？])
GROUP BY 社員ID) AS T1 ON 社員.ID = T1.社員ID;

CDate や DateAdd 部分で、日にちを指定しないと１日に解釈してもらえます。
後は、[年月？] 記述部分をフォーム参照に変更するとか・・・

回答No.4

piroin654

失礼しました。質問を読み間違えていました。
以下です。

TRANSFORM Nz(Count([受付テーブル].[社員ＩＤ]),0) AS 社員ＩＤのカウント
SELECT 社員テーブル.名前, Count(受付テーブル.社員ＩＤ) AS [合計 社員ＩＤ]
FROM 社員テーブル LEFT JOIN 受付テーブル ON 社員テーブル.ＩＤ = 受付テーブル.社員ＩＤ
GROUP BY 社員テーブル.名前
PIVOT Format([受付テーブル]![受付日],"oooo") In ("1月","2月","3月","4月","5月","6月","7

月","8月","9月","10月","11月","12月");

社員全員を表示することを忘れていました。
Nzのくくりをはずせばデータのない部分は
Nullで表示されます。

回答No.3

layy

もう１つクエリを作成し、

■社員テーブル
ID　名前
1　Aさん
2　Bさん
3　Cさん
4　Dさん
5　Eさん

■既存クエリ
名前　受付回数
Aさん　1
Bさん　2
Eさん　1

を紐付けすれば

■社員テーブル
ID　名前　（クエリの）受付回数
1　Aさん　　１
2　Bさん　　２
3　Cさん　　０かＮＵＬＬ
4　Dさん　　０かＮＵＬＬ
5　Eさん　　１

ができると思います。
これではダメでしょうか。

お礼

ご回答ありがとうございました。
サブクエリを使用することにより、イメージ通りの
表示をすることができました。

回答No.2

piroin654

必要なければスルーしてください。

別の観点から。一ヶ月単位での集計ならば、
以下のようなものもあります。

TRANSFORM Count(受付テーブル.社員ＩＤ) AS 社員ＩＤのカウント
SELECT 社員テーブル.名前, Count(受付テーブル.社員ＩＤ) AS [合計 社員ＩＤ]
FROM 社員テーブル INNER JOIN 受付テーブル ON 社員テーブル.ＩＤ = 受付テーブル.社員ＩＤ
GROUP BY 社員テーブル.名前
PIVOT Format([受付テーブル]![受付日],"oooo") In ("1月","2月","3月","4月","5月","6月","7月","8月","9月","10月","11月","12月");

最後のIn以下を除けばデータのある月だけ表示されます。
（30246kikuさんオヒサ）

お礼

ご回答ありがとうございました。
サブクエリを使用することにより、イメージ通りの
表示をすることができました。