概要
| この記事の結論 Accessの更新クエリでは、更新先フィールドの［レコードの更新］行に計算式を入れると、計算結果をテーブルへ保存できます。例えば［数量］*［単価］で金額を再計算できます。ただし実行後に通常の［元に戻す］では取り消せません。先に同じ条件の選択クエリで対象と更新後の値を確認し、バックアップを取ってから実行しましょう。本記事では、条件指定、Null、丸め、再実行で金額を増やし続けない設計まで解説します。 計算を使う更新クエリとは、条件に一致する既存レコードの値を、数式の結果で書き換えるクエリです。 画面名やリボンの配置はAccessの版で異なる場合があります。操作は変更前のコピーで試し、業務で利用する環境の結果を確認してください。 |
この記事でわかること
- 表示だけの計算と保存する更新を使い分ける
- 更新前の値と計算結果を選択クエリで確認する
- デザインビューで更新クエリを作成する
- SQLで条件を限定して計算結果を保存する
- Nullと端数と再実行を設計する
この記事の主な内容
- 表示だけの計算と保存する更新を使い分ける
- 更新前の値と計算結果を選択クエリで確認する
- デザインビューで更新クエリを作成する
- SQLで条件を限定して計算結果を保存する
- Nullと端数と再実行を設計する
- エラー対処と実行後の確認を行う
- よくある質問
想定読了時間：約18分～22分
1. 表示だけの計算と保存する更新を使い分ける
画面や帳票へ計算結果を表示するだけなら、まず選択クエリの計算フィールドを検討します。更新クエリは、計算結果を既存のテーブルへ書き込む必要がある場合に使います。保存済みの値を変更するため、単なる表示の計算よりも影響が大きくなります。『いつの時点の結果を残したいか』を先に決めましょう。
| 目的 | 選ぶ方法 | 設計時の注意 |
|---|---|---|
| 現在の数量と単価から毎回表示する | 選択クエリの計算フィールド | 元の値が変わると表示結果も変わる |
| 確定した時点の金額を記録する | 保存先へ結果を書き込む処理 | 計算時点と変更可否を管理する |
| 過去データの誤りを一括修正する | 条件付きの更新クエリ | 対象と更新前の値を記録する |
| 帳票の合計だけを表示する | 集計クエリやレポートの式 | 表示のためだけに元データを上書きしない |
例えば商品マスターの最新単価で過去の売上金額を毎回計算すると、値上げ後に過去の結果まで変わる可能性があります。売上時点の単価を明細へ保存するなど、何を固定するかを設計してください。一方、いつでも再計算できる値を重複保存すると、計算元と保存結果の不一致が起きやすくなるため、保存の理由が必要です。
今回使う説明用テーブル
本記事では『T_売上明細』の未確定レコードについて、数量×単価で金額を更新します。実際の業務データを直接使わず、コピー環境へ次の項目と少量のサンプルを用意してください。税率や割引は含めず、計算と条件指定の仕組みに絞った例です。金額は通常の保存フィールドとし、計算型のフィールドとは区別します。
| フィールド | データ型の例 | 用途 |
|---|---|---|
| 明細ID | オートナンバー型 | 主キー。各行を一意に識別 |
| 数量 | 数値型・長整数型 | 今回の例では整数の数量 |
| 単価 | 通貨型 | 明細に保存した単価 |
| 金額 | 通貨型 | 更新する保存先 |
| 確定 | Yes/No型 | Falseが更新候補。既定値はFalse |
以降のSQLは上表のテーブル名・フィールド名を前提としています。自分のファイルに合わせるときは、名前だけでなくデータ型も確認します。リンクテーブルを使っている場合、変更されるのは接続先の実データです。手元の画面ファイルをコピーしただけで、本番データから切り離されたとは限りません。
2. 更新前の値と計算結果を選択クエリで確認する
更新する前に、同じ抽出条件の選択クエリで、主キー・現在値・計算後の値を並べます。計算式が正しくても、抽出条件を間違えると別の行を変更してしまいます。対象件数と対象IDを確認し、更新対象から外れる行も意図どおりか調べてから、更新クエリへ進みます。
4件のサンプルで対象と除外を確かめる
| 説明用ID | 数量・単価 | 現在の金額・確定 | 期待する結果 |
|---|---|---|---|
| 1 | 3・120 | 300・False | 対象。金額は360になる |
| 2 | 2・250 | 0・False | 対象。金額は500になる |
| 3 | Null・100 | 900・False | 数量未入力のため除外。900のまま |
| 4 | 5・100 | 450・True | 確定済みのため除外。450のまま |
表のIDは説明用です。オートナンバーで実際に付く番号が異なる場合は、その番号で照合してください。Nullは『値が入っていない状態』であり、数量0とは意味が異なります。今回の例では数量・単価が両方入っている未確定の2件だけを更新します。未入力行を勝手に0円へ置き換えない設計です。
SELECT [明細ID], [数量], [単価],
[金額] AS [旧金額],
[数量]*[単価] AS [新金額]
FROM [T_売上明細]
WHERE [確定] = False
AND [数量] Is Not Null
AND [単価] Is Not Null
ORDER BY [明細ID];
［作成］の［クエリデザイン］からSQLビューへ切り替え、上記を入力して保存します。例えば名前を『Q_金額更新_事前確認』とします。データシートビューで対象が2件となり、新金額が360と500になれば、説明用データでは期待する結果と一致します。更新前の金額300と0も、確認用の記録へ残します。
確認結果とバックアップを別々に用意する
事前確認一覧は『何を変える予定か』の記録であり、データベース全体を復旧できるバックアップとは別です。実行前に対象ID、旧金額、新金額、件数、確認日時を保存し、実データのバックアップも取得します。分割データベースでは、フロントエンドだけでなく、テーブルを保存しているバックエンド側が対象です。
複数人で使用している場合、確認してから実行するまでに別の利用者が数量や単価を変更する可能性があります。確認一覧を見た時点と実行時点が同じとは限りません。業務を止める時間を決める、対象バッチを固定するなど、運用に応じた手順を用意してください。本記事の基本例だけで同時更新の競合を完全に防げるわけではありません。
3. デザインビューで更新クエリを作成する
デザインビューでは、更新するフィールドの［レコードの更新］行に式を指定します。今回なら金額列へ［数量］*［単価］を入れます。先頭にイコールは付けず、式そのものを記述します。数量・単価・確定の列は、更新するためではなく抽出条件を付けるために追加します。役割を分けて確認してください。
1. Q_金額更新_事前確認は残し、別の新規クエリにT_売上明細を追加します。
2. 金額、数量、単価、確定の4フィールドを下段のデザイングリッドへ追加します。
3. リボンでクエリの種類を［更新］に切り替えます。
4. 金額列の［レコードの更新］行に[数量]*[単価]を指定します。
5. 同じ［抽出条件］行へ、数量にIs Not Null、単価にIs Not Null、確定にFalseを指定します。
6. 数量・単価・確定の［レコードの更新］行は空欄のままにします。
7. Q_金額更新_実行として保存し、SQLビューで条件と更新先を再確認します。
8. バックアップと対象件数を確認してから［実行］し、確認メッセージの内容が想定と一致するときだけ進めます。
| 列 | レコードの更新 | 抽出条件 |
|---|---|---|
| 金額 | [数量]*[単価] | 今回の例では指定なし |
| 数量 | 空欄 | Is Not Null |
| 単価 | 空欄 | Is Not Null |
| 確定 | 空欄 | False |
同じ条件行の項目はANDで組み合わされます。別の『または』行へ分けると、ORの条件になり、対象が広がる場合があります。『未確定で、数量も単価も入っている』を条件にしたいなら、3条件を同じ行へ置いてください。迷ったときはSQLビューでWHERE句を確認し、事前確認クエリの条件と照合します。
更新クエリをデータシートビューで表示しただけで、更新後の結果を十分に確認できたとは判断しません。新金額を並べた事前確認クエリを別に残している理由はここにあります。また、実行後に通常のCtrl＋Zで戻せるとは考えないでください。確認メッセージを非表示にして操作を進める方法も、この基本手順では使いません。
4. SQLで条件を限定して計算結果を保存する
SQLでは、UPDATEで更新するテーブル、SETで更新先と式、WHEREで対象条件を指定します。次の例は、デザインビューで作成した条件と同じ考え方です。選択クエリの新金額として確認した式を、そのまま保存先の金額へ割り当てます。SQLが短くても、実行すれば複数レコードを書き換える点は変わりません。
UPDATE [T_売上明細]
SET [金額] = [数量]*[単価]
WHERE [確定] = False
AND [数量] Is Not Null
AND [単価] Is Not Null;
| SQLの部分 | 今回の指定 | 確認する意味 |
|---|---|---|
| UPDATE | T_売上明細 | どのテーブルを変えるか |
| SETの左側 | 金額 | どの保存フィールドを変えるか |
| SETの右側 | 数量×単価 | どの値をもとに計算するか |
| WHERE | 未確定かつ計算元が入力済み | どのレコードだけを変えるか |
WHEREを省くと、基本的にはそのテーブルの全レコードが更新対象になります。『画面で選択した行だけ』を自動で更新するわけではありません。フォームで顧客を絞っていても、その条件が更新クエリへ渡されていなければ別です。操作画面の見た目と、SQLが持つ条件を区別してください。
今回の式は、各明細に保存してある数量と単価を使います。別テーブルの最新単価へ結合して更新したい場合は、結合先が明細1件に対して一意に決まるかを確認します。同じ商品に複数の価格行が見つかる構造では、どの価格を採用するかを先に決める必要があります。結合を追加する前に、適用日や価格区分を含めた設計を見直しましょう。
更新対象や価格の参照元が複雑で、実行するたびに結果が変わる場合は、対象テーブル、抽出条件、更新前後のサンプルを整理してください。Accessの教科書へ相談する際も、『何件をどの値へ変えたいか』と『何を変えてはいけないか』を共有すると、確認と改修の範囲を具体化できます。
5. Nullと端数と再実行を設計する
計算式が動くことと、業務上正しい金額になることは別です。未入力の値をどう扱うか、どの桁で丸めるか、同じ処理を繰り返してよいかを決めます。特に一括更新では、小さな前提の違いが多くのレコードへ広がります。基本式を作った後に、例外と再実行を確認してください。
Nullを0へ置き換える前に意味を決める
数量または単価がNullなら、通常の掛け算結果もNullになります。NzはNullを別の値へ置き換える関数ですが、未入力の単価を0にすることが正しいとは限りません。本記事では計算元が未入力の行を除外し、別途入力を確認する設計です。数量0という確定した値と、数量が不明な状態を混同しません。
業務で『空欄は0として扱う』と明確に決めている場合は、例えばNz([数量],0)*Nz([単価],0)という式を検討できます。その場合も、結果として0が保存されることを事前確認一覧で確かめてください。Nzの置換値は省略せず、どの値にするかを明示すると意図が伝わりやすくなります。入力漏れを隠すためだけに使わないことが大切です。
表示桁数と保存する値は同じではない
小数点以下を表示しない書式にしても、保存値そのものを整数へ丸めたことにはなりません。今回の数量は整数、単価と金額は通貨型の例ですが、実務では小数数量や割引率が入る場合があります。保存先のデータ型と必要な精度、丸める時点を先に決めます。通貨型も無制限の精度を保存できるわけではありません。
Access/VBAのRoundは、ちょうど中間の値を近い偶数へ丸める、いわゆる銀行丸めの挙動があります。例えばRound(2.5,0)は2、Round(3.5,0)は4になる例です。常に0.5を上へ繰り上げる方法と同じではありません。請求や料金の計算では、適用する業務ルールを確認してから式を選びます。
端数処理は、正の値だけでなく負の値、小数桁の境界、明細ごとの丸めと合計後の丸めも比べます。関数の名前だけで判断せず、『入力値・期待する値・実際の結果』を表にして確認してください。四捨五入の方法を変える場合は、過去の計算との整合性や、どの時点から新ルールを使うかも決めます。
再計算と積み上げ更新を区別する
今回の金額=[数量]*[単価]は、数量・単価・対象条件が変わらなければ、再実行しても同じ金額になります。一方、金額=[金額]*1.1は、更新済みの金額を次回の計算元にします。説明例として1,000が1回目に1,100、2回目に1,210へ増えるため、同じ操作を繰り返してよい処理ではありません。
| 更新の考え方 | 再実行時の性質 | 必要な対策 |
|---|---|---|
| 数量×単価から再計算 | 計算元が同じなら同じ結果 | 計算元と対象条件の変化を確認 |
| 現在の金額へ率を掛ける | 実行するたびに増減する | 対象バッチ・処理済み状態を管理 |
| 最新マスターから値を取得 | マスター変更で結果が変わる | 基準日と参照する版を固定 |
『処理済みフラグを付ければ必ず安全』とも限りません。金額の更新とフラグの更新が別々に失敗すると、状態が食い違う可能性があります。複数処理を一体で確定するトランザクションや実行記録は、必要に応じて設計します。初心者の段階でも、同じボタンを2回押した場合をテスト項目へ入れることが大切です。
6. エラー対処と実行後の確認を行う
更新できない場合は、式を複雑にする前に、更新対象のテーブル自体を書き換えられるかを確認します。集計クエリなど、結果を元の1行へ対応付けられないクエリは更新できない場合があります。読み取り専用、権限、ロック、データ型、入力規則なども原因になるため、表示されたメッセージと対象件数を記録します。
| 症状・メッセージ | 主な確認対象 | 対処の方向 |
|---|---|---|
| 更新可能なクエリであることが必要 | 集計・クロス集計・結合の構造 | 更新元のテーブルと行の対応を確認 |
| 型変換やデータ型の不一致 | 文字列と数値、保存先の型 | 少数のサンプルで式と型を確認 |
| 入力規則・重複などの違反 | 必須、範囲、インデックス | 許される値と更新後の値を照合 |
| ロックや読み取り専用 | 他の利用者、ファイル権限、接続先 | 編集できる状態と実行時間を確認 |
| パラメーター入力が突然出る | フィールド名の誤記・参照切れ | 値を適当に入れず名前を確認 |
サンプルの期待値と照合する
実行後はT_売上明細を開き直すか、確認クエリを再実行して結果を読み直します。今回の説明用データでは、対象2件の金額が360と500になり、その合計は860です。除外した未入力行の900と、確定済み行の450は変わっていないことを確認します。対象の金額だけを見るのではなく、変えてはいけない行も照合してください。
更新メッセージの対象件数と、実際に値が変わった件数は、必ず同じ意味になるとは限りません。すでに計算結果と同じ金額が入っている行も条件へ一致し得ます。件数だけで成功と判断せず、実行前に残した対象IDと旧値・新値を使って確認します。大量データでは合計の照合と、境界例の抜き取り確認を組み合わせます。
誤更新に気付いたら続けて実行しない
誤った結果を見つけたら、同じクエリを繰り返して直そうとする前に、追加の更新を止めます。実行日時、対象条件、変更されたIDを記録し、バックアップまたは更新前の記録から復元方法を判断します。単純な逆算では、丸めやNullの扱いによって元の値を再現できない場合があります。
バックアップを丸ごと戻すと、その後に行われた正しい入力まで失う可能性があります。業務を止める範囲と復旧対象を確認し、別の場所へ復元して比較してから戻す方法も検討してください。実行前の準備、限定した更新、実行後の照合を一つの手順として扱えば、計算による一括更新を管理しやすくなります。
7. よくある質問
必ず保存する必要はありません。現在の数量と単価から画面や帳票へ表示するだけなら、選択クエリの計算フィールドで足りる場合があります。保存を検討するのは、確定時点の金額を残すなど、後から計算元が変わっても結果を固定したい場合です。ただし保存結果と計算元の整合性を保つ仕組みが必要になります。何をいつの時点で固定するかを先に決めてください。過去の売上を最新単価で再計算しないよう、単価の保存方法も含めて考えることが大切です。
今回のような計算式は、レコードの更新行へ[数量]*[単価]と記述し、先頭のイコールは付けません。一方、SQLビューではSET [金額] = [数量]*[単価]のように、更新先と式の間にイコールを書きます。画面の入力欄とSQL全体の書き方を混同しないでください。更新先は金額列にし、数量や単価を条件用に追加した場合は、それらの更新行を空欄にします。実行前には別の選択クエリで、新金額を並べて計算結果を確認してください。
WHERE句や抽出条件を付けなければ、基本的にはテーブル全体が更新対象になります。フォームで一部の行だけを表示していても、その条件が更新クエリへ渡されていなければ対象は限定されません。まず選択クエリで対象IDと件数を確認し、同じ条件を更新側にも指定してください。デザイン画面では、同じ条件行のANDと別行のORの違いにも注意します。確認メッセージの件数が想定と違う場合は進めず、条件と接続先のテーブルを見直します。
数量や単価のどちらかがNullなら、通常の掛け算結果もNullになります。Nullは値が入っていない状態であり、数量0とは異なります。本記事では計算元が未入力の行を更新対象から除外し、入力内容を確認する方法にしています。空欄を0として扱う業務ならNz([数量],0)などを検討できますが、未入力単価を0円へ変えてよいかを先に決めてください。Nzで表示上の空欄をなくすことと、保存データへ0を書き込むことは影響が異なります。
Access/VBAのRoundは、ちょうど中間の値を近い偶数へ丸める、いわゆる銀行丸めの挙動があります。例えばRound(2.5,0)は2、Round(3.5,0)は4となるため、常に0.5を上へ繰り上げる方法と同じではありません。請求金額などは、適用する業務ルールに合う式を選んでください。正の値だけでなく負の値や境界の値も試します。また、表示の小数桁数を変えただけでは保存値は丸められないため、書式と計算を分けて確認します。
式と計算元によって異なります。金額を数量×単価から求める式なら、計算元と対象条件が同じ限り、再実行しても同じ結果になります。一方、現在の金額へ1.1を掛ける式では、実行するたびに金額が増えます。マスターの単価が変わる場合も結果は同じとは限りません。対象バッチ、処理済み状態、実行記録などを設計し、二重実行を試してください。複数人が使う場合は確認から実行までの変更も考慮し、再計算できることだけで安全とは判断しません。
まず、更新対象のテーブルそのものが編集できるか確認します。集計やクロス集計などの結果は、元の1行へ対応付けられず更新できない場合があります。複数テーブルを結合しているなら、どの行を書き換えるかが一意に決まるかも確認してください。読み取り専用、権限、リンク先の条件、ロックなども原因になります。単純なテーブルと少数のサンプルに絞って切り分け、式だけを変え続けないようにします。警告の全文と対象のクエリ構造を記録すると調査に役立ちます。
更新クエリの結果は、通常のCtrl＋Zで取り消せるとは考えないでください。誤りに気付いたら追加の更新を止め、実行日時、対象ID、条件を記録します。実行前のバックアップや旧値の記録から、どの範囲を戻すか判断してください。逆算では丸めやNullによって元の値を再現できない場合があります。また、全体を戻すと更新後の正しい入力まで失う可能性があります。別の場所へ復元して比較するなど、復旧対象と業務への影響を確認してから戻します。
基本用語や関連操作を確認したい場合は、次の記事もあわせてご覧ください。
自分で設定できる範囲は、コピー環境で確認しながら進められます。複数の帳票・端末・業務データに影響する場合は、現在の状態と期待する結果、利用中のAccessの版を整理してご相談ください。
参考情報
Microsoft｜Create and run an update query
https://support.microsoft.com/en-us/access/create-and-run-an-update-query
Microsoft｜UPDATE statement
https://support.microsoft.com/en-us/access/update-statement
Microsoft｜Application.Nz
https://learn.microsoft.com/en-us/office/vba/api/access.application.nz
Microsoft｜Round function
https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/round-function