Access (一般機能)

Accessの一般機能に関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 7全般 : その他)
社員番号ごとに0を除いて平均値を計算
投稿日時: 20/04/27 17:26:25
投稿者: 和美

選択肢にありませんでしたが、バージョンはAccess365です。
 
Accessの勉強のため、以下のようなデータを使いました。
左端の通し番号を主キーに設定しています。

ID	社員番号 日付	朝の体温	夜の体温
1	1	3月30日	36.50		36.50
2	1	3月31日	36.40		36.70
3	2	3月30日	36.60		0.00
4	2	3月31日	36.70		0.00
5	1	4月1日	0.00		36.70
6	1	4月2日	0.00		0.00

 
朝の体温と夜の体温いずれも、記録がなかった時は0.00が入っています。
上記の表で社員番号ごとに平均体温を出したい場合、どうしたらいいのでしょうか。
 
DAvg("朝の体温", "データ","朝の体温>0")
だと社員全員の平均体温が表示されます。
 
クエリで
フィールド:朝の体温の平均: 朝の体温
集計:平均
抽出条件:>=0
を指定すると、0.0を除かない平均体温が出てしまいます。
 
DAvgの条件指定かな、とは思うのですが、どう指定したら0.00が除外できるのかが分かりません……。
宜しくお願い致します。

回答
投稿日時: 20/04/27 18:37:38
投稿者: sk

引用:
朝の体温と夜の体温いずれも、記録がなかった時は0.00が入っています

Null のままで良かったと思います。
 
引用:
上記の表で社員番号ごとに平均体温を出したい

[社員番号]のデータ型は何でしょうか。
 
また、具体的にどのオブジェクト(クエリ/フォーム/レポート)を用いて
最終的にどのような出力結果を得ようとなさっているのでしょうか。
 
引用:
DAvg("朝の体温", "データ","朝の体温>0")
だと社員全員の平均体温が表示されます。

[社員番号]に対する AND 条件の指定がないため。
 
引用:
クエリで
フィールド:朝の体温の平均: 朝の体温
集計:平均
抽出条件:>=0
を指定すると、0.0を除かない平均体温が出てしまいます。

それだと「[朝の体温]の平均が 0 以上」である集計結果を抽出する、
という HAVING 条件になります。
 
( SQL ビュー)
------------------------------------------------------------------
SELECT [テーブル名].[社員番号],
       Avg([テーブル名].[朝の体温]) AS [朝の体温の平均]
FROM [テーブル名]
WHERE [テーブル名].[朝の体温] > 0
GROUP BY [テーブル名].[社員番号]
ORDER BY [テーブル名].[社員番号];
-----------------------------------------------------------------
(テーブル名は適宜修正すること)
 
[朝の体温]の値が 0 を超えているレコードを抽出し、
(それらのレコードを集計の対象として)
[社員番号]ごとに[朝の体温]の平均を求めた結果を取得したい場合は
以上のような集計クエリを作成なさればよろしいでしょう。

投稿日時: 20/05/07 15:49:55
投稿者: 和美

sk様
ご教授ありがとうございます。
反応が遅くなり、すみません。
 
とりあえず0.00をNullにしようと置換を実行すると「数値型のデータに文字列は設定できません」とエラーが出てしまい、色々検索して詰まっているところです……。
Nullを0にする方法は出てくるのですが、
 
社員番号は数値型です。
 
特にどういうデータを作りたいとかはなく、練習で適当な手元のデータを見ていたら「平均体温ってどれぐらいなんだろう」と思って挑戦してみた次第です。
 
ご教示いただいたSQLはまだ実行できておりません。
すみません。

回答
投稿日時: 20/05/08 10:46:55
投稿者: sk

引用:
とりあえず0.00をNullにしようと置換を実行すると
「数値型のデータに文字列は設定できません」とエラーが出てしまい、
色々検索して詰まっているところです……。

次のような更新クエリを実行なさればよろしいでしょう。
 
( SQL ビュー)
-----------------------------------------------------------
UPDATE [テーブル名]
SET [テーブル名].[朝の体温] = Null
WHERE [テーブル名].[朝の体温] = 0;
-----------------------------------------------------------
(テーブル名は適宜修正すること)
 
引用:
社員番号は数値型です。

引用:
DAvg("朝の体温", "データ","朝の体温>0")

例えば、「[社員番号]の値が 1 であり、かつ[朝の体温]の値が
0 を超えているレコードの[朝の体温]の平均」を DAvg 関数によって
求めたい場合は、次のような式を記述します。
 
-----------------------------------------------------------
 
DAvg("朝の体温", "データ","[社員番号]=1 And [朝の体温]>0")
 
-----------------------------------------------------------
 
引用:
ご教示いただいたSQLはまだ実行できておりません。

とりあえず、集計クエリにおける WHERE 条件と HAVING 条件の違いに
ついて調べられることをお奨めします。

投稿日時: 20/05/27 17:59:45
投稿者: 和美

sk様
ご教授ありがとうございます。
仕事の合間にやっているため、反応が遅くて申し訳ございません。
  
ご教示いただいたSQL更新クエリで、なんとか体温の0.0をNullに変換まではできました。
  
WHEREとHAVINGも調べてみました。
WHERE:全ての行からの検索
HAVING:グループ化された行への検索、グループ関数の結果を検索条件にする場合
 

引用:
クエリで
フィールド:朝の体温の平均: 朝の体温
集計:平均
抽出条件:>=0
を指定すると、0.0を除かない平均体温が出てしまいます。

 
という自分の結果は、全員の朝の体温を集計グループに指定し、その中から抽出条件の0以上を指定しているということでしょうか。
  
DAvg("朝の体温", "データ","[社員番号]=1 And [朝の体温]>0")
を実行したところ、36.3になるべき数値が36.2999992370605と表示されるのは、データ型を単精度浮動小数点型にしているせいでしょうか。
(入っている数値は36.1、36.3、36.3、36.5の4件のみで残りはNull)
  
質問に質問を重ねて申し訳ありません。

回答
投稿日時: 20/05/29 09:46:21
投稿者: sk

引用:
クエリで
フィールド:朝の体温の平均: 朝の体温
集計:平均
抽出条件:>=0
を指定すると、0.0を除かない平均体温が出てしまいます。

引用:
という自分の結果は、全員の朝の体温を集計グループに指定し、
その中から抽出条件の0以上を指定しているということでしょうか。

全てのレコードを集計の対象として( WHERE 条件を指定しないで)、
それらのレコードを[社員番号]でグループ化して、
グループごとに[朝の体温]の平均を求めて、
更にその平均が 0 以上であるグループを抽出している
( HAVING 条件)、ということになります。
 
つまり、平均を求める前の段階で集計対象を絞り込むか、
グループ化/集計された結果に対して更に絞り込みに掛けるか、
の違いです。
 
例えば、「 2020/03/31 以前における[朝の体温]の平均が
36.5 度以上である社員」を抽出したい場合は、
次のような集計クエリを作成することになります。
 
( SQL ビュー)
-----------------------------------------------------------
SELECT [データ].[社員番号], 
       Avg([データ].[朝の体温]) AS [朝の体温の平均] 
FROM [データ] 
WHERE [データ].[日付]<=#2020/03/31#
GROUP BY [データ].[社員番号]
HAVING Avg([データ].[朝の体温])>=36.5;
-----------------------------------------------------------

(デザインビューで表示した場合)
-----------------------------------------------------------
フィールド:|社員番号
 テーブル:|データ
   集計:|グループ化
 並べ替え:|
   表示:| レ
 抽出条件:|
  または:|
-----------------------------------------------------------
フィールド:|朝の体温の平均: 朝の体温
 テーブル:|データ
   集計:|平均
 並べ替え:|
   表示:| レ
 抽出条件:|>=36.5
  または:|
-----------------------------------------------------------
フィールド:|日付
 テーブル:|データ
   集計:|Where 条件
 並べ替え:|
   表示:|
 抽出条件:|<=#2020/03/31#
  または:|
-----------------------------------------------------------

引用:
DAvg("朝の体温", "データ","[社員番号]=1 And [朝の体温]>0")
を実行したところ、36.3になるべき数値が36.2999992370605と表示されるのは、
データ型を単精度浮動小数点型にしているせいでしょうか。
(入っている数値は36.1、36.3、36.3、36.5の4件のみで残りはNull)

こちらに関してはおっしゃる通り、[朝の体温]に精度の低いデータが
格納されており、それらの平均を求めているからですね。

トピックに返信