Accessの集計、0件のデータを表示させるには？

質問

Accessで選択クエリの集計を作りたいのですが…。
例えば、誰が何の質問を受けたか、というような、以下のようなテーブルがあるとします。
【A：データテーブル】
Aさん 数学
Aさん 理科
Bさん 社会
Aさん 理科
Bさん 理科
【B：教科マスター】
１ 数学
２ 社会
３ 理科
AとBはリレーションでむすんで？あります。

で、Aテーブルを元に集計を使って以下のようにしたいのです。
Aさん 理科 2件
Aさん 数学 1件
Aさん 社会 0件

Aさんだけを抽出するのは、「抽出条件」で絞ればいいと思います。
教科のそれぞれの件数を出すには、「集計」で教科ごとにグループ化して教科をカウントすればいいと思うのですが、どうしても0（ゼロ）件のデータが出てきません。（上記の例で言うと、「Aさん 社会 0件」が出ない）
0を表示させるにはどこに何を書いたらいいのでしょうか？

回答No.7

fly_moon · ベストアンサー

どーも#4です。
DCount("教科ID","データテーブル","教科ID=" & [教科ID] & " And ID=" & [ID])
の意味はクエリの[式ビルダ]の組み込み関数
DCount (<expr>, <domain>, <criteria>)
の
<expr>はカウントするフィールド
<domain>はカウントするテーブル
<criteria>はカウントする条件になります。

<criteria>に"教科ID=1 And ID=2"とすると、<domain>で指定したテーブルの「教科IDの値が1」でかつ「IDが2」のデータのカウントを条件に指定していることになります。

"教科ID=" & [教科ID] & " And ID=" & [ID]
と書くと、「教科IDがクエリの[教科ID]の値」かつ、「IDがクエリの[ID]の値」という条件です。

ちなみに教科ID、IDの両方がもし文字列なら、
"教科ID='" & [教科ID] & "' And ID='" & [ID] & "'"
と書きます。

違うことですが、
>グループまたは集合のないHAVING句です」と出ます。

何をされたのかわかりませんが、私が提示したSQLではグループ化など行っていないのでこういうエラーが出るはずがないのですが・・・(私がテストで抽出したときはWHEREでやりましたし、うまくいきましたよ)

お礼

Dcountの使い方、初心者の私でもすっごくよくわかりました！
しかしわかったところで、そもそも今やっているやり方が本番で応用できないということも判明してしまいました…。
回答してくださった皆様には申し訳ないです…
ただ、今作っているDBにおいて、この「０を表示させる」というのがどのクエリでもネックになっていたので、直せばもう少しよくなりそうな気がします。
とりあえず０表示はDcountでできそうです。
ちなみにエラーの件はパラメータ指定したら出ました。
本当にありがとうございました。

回答No.8

DexMachina

No.2です。

いまさら、な感はありますけれど・・・(汗)
どうしても選択クエリでなければだめですか?：

TRANSFORM Count(データテーブル.教科ID) AS 受け数
SELECT データテーブル.人
FROM データテーブル INNER JOIN 教科マスター ON データテーブル.教科ID = 教科マスター.教科ID
GROUP BY データテーブル.人
PIVOT 教科マスター.教科名;

この種の集計ではオーソドックスなクロス集計クエリ、です。
「空白」＝「0」となり、「0」表示の目的が「何の質問に答えていないか」の明示だとすれば、用は足りると思うのですけれど。

なお、これは全くの余談ですが、データベースの正規化を考えるのであれば、「データテーブル」は「人マスター」と「データテーブル」に分けた方がよいかと思います。
人マスター：
　人
　人ID
データテーブル：
　人ID
　教科ID

お礼

このやり方が一番簡単かなーと思いましたが、AさんもBさんも社会を担当しなかったとき、社会はまったく表示されないですね。
実は、他のクエリでクロス集計を使っていますがクロス集計でもゼロが表示されないのも、ネックになっていたりします…。

しかしわかったところで、そもそも今やっているやり方が本番で応用できないということも判明してしまいました…。
回答してくださった皆様には申し訳ないです…（そもそも私の設計ミス？という感じです。ごめんなさい。）
ただ、今作っているDBにおいて、この「０を表示させる」というのがどのクエリでもネックになっていたので、直せばもう少しよくなりそうな気がします。
本当にありがとうございました。
またわからないことがあったら、別途質問させてください。

回答No.6

1050 円（@1050YEN）

さっきのSQL文の抜粋です。

SELECT DISTINCT 人
FROM データテーブル
≪絞りたいときは、ここに「WHERE 人 = 'A'」≫　←　不要なら削除
) AS データ
,教科マスター AS 教化

この部分が括弧でくくられて
「AS 一時データ」として、SQL文で仮想テーブルを作成しているわけです。

私のやりかたは、メジャーなデータベース全般なやり方です。
#4さんのやり方は、Access固有のDCount関数を利用したやり方です。

せっかくAccessを利用しているなら、#4さんのやり型の方がすっきりしていると思います。
別のDBで、どうような処理があってつまずいたら、私のを参考にしてくださるとありがたい^^;

お礼

なるほど、わかりました。
わかったところで、そもそも今やっているやり方が本番で応用できないということも判明してしまいました…。
回答してくださった皆様には申し訳ないです…
ただ、今作っているDBにおいて、この「０を表示させる」というのがどのクエリでもネックになっていたので、直せばもう少しよくなりそうな気がします。
とりあえず０表示はDcountでできそうなので、もし都合が悪かったらこちらの方法を試してみたいと思います。
本当にありがとうございました。

回答No.5

1050 円（@1050YEN）

すいません。
教化マスターの構造が変でした。

訂正です。

※教科マスター

[構造]
1.教科ID[数値型]　←　KEY設定
2.教科名[テキスト型]

[データ]
教科ID 教科名
1 数学
2 社会
3 理科

これが完成したら、再度新規クエリのSQLビューを開いて、先ほどのSQLを張ってみてください。

お礼

再度ありがとうございます。
＃３で書いていただいたSQL文に「一時データ」と書かれたところがありますが、これはどこから来ているのでしょうか？？

あとこの場ですいません、＃４で教えていただいた
DCount("教科ID","データテーブル","教科ID=" & [教科ID] & " And ID=" & [ID])
の意味がわからず応用できません。
どなたか"教科ID","データテーブル","教科ID="[教科ID]ID[ID]がそれぞれどのテーブル？から持ってきたものか教えていただけないでしょうか？

回答No.4

fly_moon

こんな風ではいかがでしょう？

--------------------------------------------

※教科マスター

[構造]
1.教科ID[数値型]
2.教科名[テキスト型]

[データ]
教科ID 　教科名
　1 　　　数学
　2 　　　社会
　3 　　　理科

※名前マスター

[構造]
1.ID[数値型]
2.氏名[テキスト型]

[データ]
ID 　　　氏名　　
　1 　　　 A
　2 　　　 B

※データテーブル

[構造]
1.ID[数値型]
2.教科ID[数値型]

[データ]
ID 教科ID
1 　　1
1 　　3
2 　　2
1 　　3
2 　　3

※クエリSQL文
SELECT 名前マスター.氏名, 教科マスター.教科名, DCount("教科ID","データ","教科ID=" & [教科ID] & " And ID=" & [ID]) AS 合計
FROM 教科マスター, 名前マスター
ORDER BY 名前マスター.氏名;

--------------------------------------------

結合なしで名前マスターと教科マスターでクエリを作り、DCount関数でデータテーブルのデータ数をカウントするというものです。。。

お礼

おぉっ！出た！！！！
（ただし、SQLの部分「データ」ではなく「データテーブル」）
ただ、人（実際のデータでは人ではなく、人に当たるデータが300件近くある）での抽出をするとエラーになりました。「グループまたは集合のないHAVING句です」と出ます。
でも、このデータを使ってEXCELで加工しなければならないので、もしかしたらそのままでもいけるかもしれません。ちょっと時間がかかってしまうかもしれませんが、今から実際のデータで試してみて、ご報告します。
ありがとうございました。

回答No.3

1050 円（@1050YEN）

※データテーブル

[構造]
1.人[テキスト型]
2.教化ID[数値型]

[データ]
人 教化ID
A 1
A 3
B 2
A 3
B 3

※教科マスター

[構造]
1.人[教化ID]　←　KEY設定
2.教化ID[教化名]

[データ]
教化ID 教化名
1 数学
2 社会
3 理科

として、クエリを作成
クエリのデザインで、メニューバーより
「表示(V)」　→　「SQLビュー(Q)」
を選択し、メモ帳のような、画面に移動

以下のSQL文をコピペする(≪≫の中は、人を絞るときに必要)

SELECT 一時データ.人,一時データ.教化ID,一時データ.教化名,Count(データテーブル.教化ID) AS 件数
FROM (
SELECT データ.人,教化.教化ID,教化.教化名
FROM
(
SELECT DISTINCT 人
FROM データテーブル
≪絞りたいときは、ここに「WHERE 人 = 'A'」≫　←　不要なら削除
) AS データ
,教科マスター AS 教化
) AS 一時データ
LEFT JOIN データテーブル
ON 一時データ.人 = データテーブル.人
AND 一時データ.教化ID = データテーブル.教化ID
GROUP BY 一時データ.人, 一時データ.教化ID, 一時データ.教化名

メニューバーより
「表示(V)」　→　「データシートビュー(S)」
を選択し、抽出結果を確認する

お礼

回答ありがとうございます。
とりあえず人の抽出はややこしくなるので後回しにして、下のようにやってみました。
（回答の教科マスターの構造が違ってるように見えたので変えてみましたが、間違っていたら教えてください…。）

※データテーブル

[構造]
1.人[テキスト型]
2.教化ID[数値型]

[データ]
人 教科ID
A 1
B 2
A 3
A 3
B 3

※教科マスター

[構造]
1.人[テキスト型]　←　KEY設定
2.教化ID[数値型]

[データ]
教科ID 教科名
1 数学
2 社会
3 理科

できたクエリ↓

SELECT [データテーブル].[人], [データテーブル].[教科ID], Count([データテーブル].[教科ID]) AS 教科IDのカウント
FROM 教科マスター RIGHT JOIN データテーブル ON [教科マスター].[教科ID]=[データテーブル].[教科ID]
GROUP BY [データテーブル].[人], [データテーブル].[教科ID];

やはり、０件のデータがでないのですが…。
結合は逆もやってみたのですがうまくいきません。

回答No.2

DexMachina

クエリはデザインビューで編集されていますか?
でしたら、以下の操作をしてください：

1)テーブル表示領域で、AテーブルとBテーブルの間の結合線(リレーション)を右クリックします
2)表示されたメニューから「結合プロパティ(J)」を選択します
3)1～3の選択肢の中から、「Aテーブルの全レコードと・・・」と表示されているものを選択し、「OK」をクリックします

以上で、お望みの動作をすると思います。
(出来上がったクエリをビューボタン(デザインビューで表示してるとき、画面左上にある三角定規などのボタン)で「SQL」を選択すると、No.1の方が言われている「Left Join/Right Join」が含まれていることを確認できます)

お礼

＃１さんの回答のお礼に書きましたが、結合の仕方が反対かなと思って逆もやってみましたがだめでした…。

回答No.1

ppg-2

テーブル結合タイプを外部結合に変更したらよいでしょう。
デフォルトでは内部結合になっています。
sqlでは前者がleft join あるいはright joinで、後者がinner joinです。

お礼

回答ありがとうございます。
ですが、結合プロパティは「教科マスターの全レコードとデータテーブルの同じ結合フィールドのレコードだけを含める」になっているのですが、これが間違っているのでしょうか？リレーションは「教科マスター1→∞データテーブル」になっています。回答されているようにしているつもりですが何が間違っているのかわかりません…