Access (一般機能)

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

 
(Windows 7 Professional : Access 2013)
テーブルに合計を持たせたい
投稿日時: 19/08/29 11:59:51
投稿者: にーさん

お世話になっております、『にーさん』と申します。
 
受注テーブルと受注明細テーブルがあり、式のやり方(消費税の求め方)が変わることになりました。
今までの分はそのまま残しておきたい、かと言って計算式に当てはめると金額が変わってくるので受注テーブルに小計・消費税・伝票合計を設けて、今までの計算結果をテーブルに持たせるように考えました
 
今までは、受注明細テーブルの各行の小計を求めて、税率を掛けて消費税(切り捨て)を求めていました。
伝票合計をフォームのみで行って表示させていました。
 
今回から行ごとに【数量】×【単価】で【金額】を求め、【消費税】を求める方向に変わりました。
ので、端数の部分が変わってくるので、計算式でWK-項目に求めて何らかのタイミングで本項目に転送させる方法にしました。
 
その時に最初に今まで受注テーブルになかった項目を追加(金額の小計・消費税の小計・伝票合計)
 
そこで今やりたいのは、受注テーブルの【金額の小計】項目に受注明細テーブルの金額を一括で転送させて、消費税を計算して伝票合計を求めて付込みたいのです。
 
そこで私が行った方法は、クエリの更新を使おうと思いました。
受注テーブル・受注明細テーブルを使い
フィールド【金額の小計】
テーブル【受注テーブル】
レコードの更新【DSum("[金額]","受注明細テーブル")】
 
更新クエリですべてのレコードを更新できません
59789個のフィールドで型変換エラー、0件のレコードでキー違反、0件のレコードでロック違反、
0件のレコードで入力規則違反が発生したため、レコードを更新できませんでした。
※両方(受注明細テーブルの【金額】・受注テーブルの【金額の小計】)とも数値型の長整数型です。
 
何がおかしいのでしょうか?
教えてください、よろしくお願いします。

回答
投稿日時: 19/08/30 14:33:18
投稿者: sk

引用:
受注テーブルと受注明細テーブル

[受注テーブル]と[受注明細テーブル]とが1対多の関係にあるとして、
[受注テーブル]の主キー、及び[受注明細テーブル]の外部キーは
それぞれどのフィールドなのでしょうか。
また、それらのフィールドのデータ型は何でしょうか。
 
引用:
今まで受注テーブルになかった項目を追加
(金額の小計・消費税の小計・伝票合計)

引用:
受注テーブルの【金額の小計】項目に
受注明細テーブルの金額を一括で転送

引用:
そこで私が行った方法は、クエリの更新を使おうと思いました。
受注テーブル・受注明細テーブルを使い
フィールド【金額の小計】
テーブル【受注テーブル】
レコードの更新【DSum("[金額]","受注明細テーブル")

この場合、[受注テーブル]の全てのレコードの[金額の小計]の値を
「[受注明細テーブル]の全てのレコードの[金額]の合計」に更新する、
ということになってしまいます。
 
引用:
更新クエリですべてのレコードを更新できません
59789個のフィールドで型変換エラー、0件のレコードでキー違反、0件のレコードでロック違反、
0件のレコードで入力規則違反が発生したため、レコードを更新できませんでした。
※両方(受注明細テーブルの【金額】・受注テーブルの【金額の小計】)とも
数値型の長整数型です。

DSum 関数によって返された「[受注明細テーブル]の全てのレコードの[金額]の合計」が、
長整数型のフィールドに格納できる値の範囲( -2,147,483,648 〜 2,147,483,647 )を
超えているためでしょう。

投稿日時: 19/08/30 16:57:35
投稿者: にーさん

skさん、返信ありがとうございます。
 

引用:
受注テーブルと受注明細テーブルの関係

[受注テーブル]と[受注明細テーブル]とが1対多の関係にあります。
[受注テーブル]の主キー・・・
 @【フィールド:NO(オートナンバー型)インデックス:はい(重複なし)】です。
[受注明細テーブル]の主キー・・・
 @【フィールド:NO(数値/長整数型)インデックス:はい(重複あり)】
 A【フィールド:行(数値/長整数型)インデックス:はい(重複あり)】です。
 
引用:
[受注明細テーブル]の追加項目
 @【フィールド:金額(数値/長整数型)】

 
引用:
[受注テーブル]の追加項目
 @【フィールド:金額の小計(数値/長整数型)】
 A【フィールド:消費税の小計(数値/長整数型)】
 B【フィールド:伝票合計(数値/長整数型)】

 
例)
[受注明細テーブル]
NO:100 行:1 ・・・・・・・・・・・ 数量:1 単価:1000 金額:1000
NO:100 行:2 ・・・・・・・・・・・ 数量:2 単価: 800 金額:1600
NO:100 行:3 ・・・・・・・・・・・ 数量:5 単価: 250 金額:1250
ここまでは出来ています(金額計算をして[受注明細テーブル]への付け込み)
 
その後、[受注テーブル]金額の小計への付け込みが出来れば、消費税の小計・伝票合計は可能だと思います。
 
※やりたいことは、[受注テーブル]への付け込み
 [受注テーブル]のNO:100 に [受注明細テーブル]のNO:100 の3行分の金額の小計を計算して付け込みたいのです。
 その他も同じ伝票NOへの金額の小計を一括で計算して付込む方法を教えてください

回答
投稿日時: 19/08/30 17:34:25
投稿者: sk

引用:
[受注テーブル]と[受注明細テーブル]とが1対多の関係にあります。
[受注テーブル]の主キー・・・
 @【フィールド:NO(オートナンバー型)インデックス:はい(重複なし)】です。
[受注明細テーブル]の主キー・・・
 @【フィールド:NO(数値/長整数型)インデックス:はい(重複あり)】
 A【フィールド:行(数値/長整数型)インデックス:はい(重複あり)】です。

No は Access の SQL における予約語ですので、
フィールド名としてはあまり適していません。
 
可能であれば別の名前に直されることをお奨めします。
(いまさら無理、ということなら強制はしません)
 
では本題。
 
引用:
同じ伝票NOへの金額の小計を一括で計算して付込む方法

例えば次のような更新クエリを実行なさればよろしいでしょう。
 
( SQL ビュー)
-----------------------------------------------------
 
UPDATE [受注テーブル]
SET [受注テーブル].[金額の小計] = DSum("[金額]","受注明細テーブル","[NO]=" & [受注テーブル].[NO])
 
-----------------------------------------------------
 
もし、それでも型変換エラーが発生する(合計が大きくなるグループがある)
ようであれば、[受注テーブル]の[金額の小計]などのフィールドのデータ型を
通貨型や十進型などの、より大きな値を格納できる型に変更して下さい。

投稿日時: 19/09/02 10:25:10
投稿者: にーさん

skさん、返信ありがとうございます。
返信が遅くなり申し訳ございませんでした。
 
skさんが、おっしゃるようにすると出来ました。
私が思っていた結果がでました、完璧です。
 
ありがとうございました