Access (VBA)

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

 
(Windows 10 Home : Access 2013)
レポート上の数式入りテキストボックスの合計
投稿日時: 17/12/04 14:00:19
投稿者: hirohiro0130

宜しくお願い致します。
 
レポートの詳細部で別フィールド間の計算結果を表示するテキストボックス(評価単価)があります。
 
[入庫数] フィールド
[入庫金額] 〃
(評価単価) = [入庫金額]/[入庫数]
(月末在庫金額) = (評価単価)×[入庫数]合計
 
物品の種類でグループ化し、グループフッダーにで合計を表示するのですが、
(月末在庫金額) はグループフッダーで合計出来ないため、
他に方法は無いものか、ご教授頂きたくお願い致します。

回答
投稿日時: 17/12/04 14:17:13
投稿者: sk

引用:
[入庫数] フィールド
[入庫金額]
(評価単価)[入庫金額][入庫数]
(月末在庫金額) = (評価単価)×[入庫数]合計

ここで[月末在庫金額]に表示させるのは、単純に
[入庫金額]の合計でよいのではないでしょうか。
 
(グループフッター上の演算テキストボックス[月末在庫金額]のコントロールソース)
------------------------------------------------------------
 
=Sum([入庫金額])
 
------------------------------------------------------------

投稿日時: 17/12/04 14:31:33
投稿者: hirohiro0130

sk様、早速ありがとうございます。
 
 
説明不足がありました、失礼しました。
 
入庫データの他に、出庫データがあり(出庫データは数量のみ)
 
差し引き、
(在庫数量) = [入庫数]−[出庫数]
 
(月末在庫金額) = (評価単価)×(在庫数量)
 
となります。
 
種類により入庫単価が違うため、グループフッダーにて
 
(月末在庫金額_Grp) = (評価単価_Grp)×(在庫数量_Grp) としても
 
金額が合いません。
 
詳細での上記各結果を合計したいのですが、方法はあるでしょうか。
 
宜しくお願い致します。

回答
投稿日時: 17/12/04 14:48:08
投稿者: sk

引用:
(評価単価) = [入庫金額]/[入庫数]

引用:
(在庫数量) = [入庫数]−[出庫数]

引用:
(月末在庫金額) = (評価単価)×(在庫数量)

引用:
詳細での上記各結果を合計

=Sum(([入庫金額]/[入庫数])*([入庫数]-[出庫数]))
 
--------------------------------------------------------
 
ということでしょうか。

投稿日時: 17/12/05 16:21:51
投稿者: hirohiro0130

sk様 ありがとうございます。
 
グループフッダーにて上式様の数式としても、グループ合計から算出した単価×全体数の金額となり、詳細での各種類で単価×数量の結果を合計した値になりません。
 
種類 入庫数 入庫金額 評価単価 在庫数 金額
A 10 1,009 100.9 5 505
B 20 4,000 200.0 7 1,400
C 30 9,007 300.2 9 2,702
---------------------------------------------------
合計 60 14,016 21 4,607 ← 求めている結果
 
 
しかし結果は以下の値になります。
 
           14,016 / 60 = @233.6 × 21 = 4,906 ← ×
             
宜しくお願い致します。

投稿日時: 17/12/05 16:25:54
投稿者: hirohiro0130

表の数字が乱れていましたので、再度投稿します。
 
種類 入庫数 入庫金額 評価単価 在庫数 金額
A_______10_______1,009_____100.9_______5_____505
B_______20_______4,000_____200.0_______7___1,400
C_______30_______9,007_____300.2_______9___2,702
---------------------------------------------------
合計____60______14,016________________21___4,607 ← 求めている結果
 
うまく表示されるでしょうか・・・

回答
投稿日時: 17/12/05 17:16:39
投稿者: sk

引用:
グループフッダーにて上式様の数式としても、
グループ合計から算出した単価×全体数の金額となり、
詳細での各種類で単価×数量の結果を合計した値になりません。
  
種類 入庫数 入庫金額 評価単価 在庫数 金額
A    10      1,009    100.9     5       505
B    20      4,000    200.0     7     1,400
C    30      9,007    300.2     9     2,702
---------------------------------------------------
合計 60     14,016             21     4,607 ← 求めている結果

その結果はレポートフッター(もしくは[種類]より上位のグループフッター)に
出力すべき、レコードソース全体での(もしくは上位グループレベル内での)
集計結果ではないのでしょうか。
 
また、上記のサンプルはあくまで[種類]グループでの
それぞれの集計結果を簡略的にまとめただけであって、
レポートのレコードソースに上記の 3 件のレコードが
そのまま格納されているわけではありませんよね。
 
引用:
=Sum(([入庫金額]/[入庫数])*([入庫数]-[出庫数]))

少なくとも、レポートのレコードソースに
上記の 3 件のレコードしかない状態で
(表に出ていない[出庫数]の値も[在庫数]の式から逆算、入力して)、
上記の式を設定した演算テキストボックスを
レポートフッターに配置すれば、ご期待通りの結果が
得られることは、当方でも確認できています。
 
引用:
しかし結果は以下の値になります。
  
14,016 / 60 = @233.6 × 21 = 4,906 ← ×

それは以下の式に相当する計算処理を、
1 つの演算テキストボックスによって
行なわれているからではないでしょうか。
 
---------------------------------------------------------------
 
=Sum([入庫金額])/Sum([入庫数])*Sum([入庫数]-[出庫数])
 
---------------------------------------------------------------
 
[入庫金額]の合計を返す演算テキストボックスの値を
[入庫数]の合計を返す演算テキストボックスの値で割って、
更に ([入庫数]-[出庫数]) の合計を返す演算テキストボックスの値を掛ける、
みたいな式を記述されている場合も同様です。

回答
投稿日時: 17/12/05 21:22:00
投稿者: hatena
投稿者のウェブサイトに移動

skさんから的確な回答出ていますが、別の観点から回答させていただきます。
 
現状は、テキストボックスに式を設定しているのですよね。(演算コントロール)
テキストボックス名は 評価単価、在庫数、金額
 
この場合、フッターで集計する場合、このテキストボックス名は使えず設定してある式をSum関数内にいれる必用があります。それがskさんの回答の式です。
 
=Sum(([入庫金額]/[入庫数])*([入庫数]-[出庫数]))
 
評価単価の式→[入庫金額]/[入庫数]
在庫数の式→[入庫数]-[出庫数]
これで正しい結果になるはずです。
 
ただ、複雑に式になりがちですので、レコードソースのクエリの方で式を設定するという方法もあります。(演算フィールド)
 
クエリに、フィールドに下記の式を設定します。
 
評価単価: [入庫金額]/[入庫数]
 
在庫数: [入庫数]-[出庫数]
 
金額: [評価単価]*[在庫数]
 
このクエリをレコードソースにして、テキストボックスのコントロールソースに上記の演算フィールドの設定します。
そうすれば、フッターの集計テキストボックスのコントロールソースは、
=Sum([金額])
ですみます。
 
もう一つ、注意する点は端数処理です。
例えば、サンプル例示された
 
種類 入庫数 入庫金額 評価単価 在庫数 金額
A_______10_______1,009_____100.9_______5_____505
B_______20_______4,000_____200.0_______7___1,400
C_______30_______9,007_____300.2_______9___2,702
 
この C の場合は、
評価単価は 9007/30=300.23333・・・・
ですが、表示上は、300.2 になっています。これは、書式設定で 0.0 と設定、または小数点以下桁数を1に設定しているのだと思います。
金額も、9007/30*9=2702.1 の小数点以下を非表示にしているのだと思います。
 
この場合は、表示上は、2,702 でも、実際の数値は 2702.1 ですので、Sumで集計する場合、この実際の数値で集計されます。そうすると、見かけの数値の合計と合わない場合がでてきます。
この場合、集計は、実際の数値の集計でいいのか、それとも丸めた結果の数値の集計にするか、仕様を明確に決める必用があります。また、丸める方法も 切り捨て、切り上げ、四捨五入、銀行型丸め などいろいろありますのでその辺の仕様を明確に説明してください。
 
実際の数値の集計でいいのなら、skさんの回答か上記の方法でいいですが、丸め結果の集計なら別の式になります。

投稿日時: 17/12/06 13:12:15
投稿者: hirohiro0130

sk様、hatena様 ありがとう御座います。
 
実は、自身が理解出来るよう単純化して内容を説明しておりました。
失礼しました。
 
「詳細」部ですが、
 
   日付____形式____種類_____入庫数____入庫金額
   12/1_____甲______A_________2________200
   12/1_____甲______B_________1________200
   12/4_____甲______B_________3________301
   12/5_____乙______A_________1________100
                       ・
                       ・
                       ・
 
となっており、「甲」「乙」・・・各グループで、おのおの A・B・C を集計しています。
よって入庫数は、=Sum([入庫数])
 
ゆえに、先の説明での
 
A_______10_______1,009_____100.9_______5_____505
B_______20_______4,000_____200.0_______7___1,400
C_______30_______9,007_____300.2_______9___2,702
 
につきましては「甲」グループ内の「種類」グループ(A・B・C)の集計結果です。
 
これに、甲乙・・「形式」グループでの集計をしたいのです。
 
甲-A_______10_______1,009_____100.9_______5_____505
甲-B_______20_______4,000_____200.0_______7___1,400
甲-C_______30_______9,007_____300.2_______9___2,702
 
甲 合計____60______14,016________________21___4,607
 
 
教授頂いた、Sum([***])/Sum([***]・・・) → Sum([***]/[***]・・・)
 
と式を変更しましたが、結果は変わりませんでした。
 

投稿日時: 17/12/06 15:45:57
投稿者: hirohiro0130

sk様
 
引用:----------------------------------------------------------
=Sum(([入庫金額]/[入庫数])*([入庫数]-[出庫数]))
 
それは以下の式に相当する計算処理を、
1 つの演算テキストボックスによって
行なわれているからではないでしょうか。
  
=Sum([入庫金額])/Sum([入庫数])*Sum([入庫数]-[出庫数])
---------------------------------------------------------------
 
「詳細」において、レコードに出庫データのみの時、[入庫金額]/[入庫数]が計算出来ませんので、
Sum([入庫金額])/Sum([入庫数])を使用しないと集計出来ないことに今更気が付きました。
 
おっしゃるとおり、Sum([入庫金額])/Sum([入庫数])のため、平均の値になっていました。
 
基本から作り直さなければ無理なのでしょうか。

回答
投稿日時: 17/12/06 16:28:18
投稿者: hatena
投稿者のウェブサイトに移動

hirohiro0130 さんの引用:
「詳細」において、レコードに出庫データのみの時、[入庫金額]/[入庫数]が計算出来ませんので、
Sum([入庫金額])/Sum([入庫数])を使用しないと集計出来ないことに今更気が付きました。

 
同じ商品が入庫時期によって単価が変化する。
出庫は、同じ入庫時期のものだけとは限らない。(異なる単価のものがある)
 
という状況でしょうかね。普通によくあることです。
 
この場合の、評価額の決めた方にはいろいろな方法があります。
「先入先出法」「後入先出法」「移動平均法」の3つの方法は主なものです。
 
棚卸資産の評価方法とは?全7種類の評価方法をわかりやすく解説! - 節税や実務に役立つ専門家が監修するハウツー - 税理士ドットコム
https://www.zeiri4.com/c_1032/h_127/
 
先入先出法・後入先出法・移動平均法とはこんな感じだ | 会計SEのメモ
http://kanrikaikei-sys.com/2015/01/1717/
 
どの方法を採用するかをまずは決めないといけませんね。
それを元にテーブル設計から見直すことになりますね。

投稿日時: 17/12/15 10:29:50
投稿者: hirohiro0130

ありがとうございました。
 
基本から見直してみます。
お世話になりました。