クエリ 更新クエリ・集計クエリを元にした更新ができない [クエリ]
ACCESSのデータベースエンジンにある、限界の一つです。
更新クエリ（UPDATE）で項目を更新するクエリを作る場合に、更新する値に集計クエリの結果を使用しようとした場合、「更新可能なクエリであることが必要です。」というエラーがでます。
具体的な再現手順は、以下のとおり。
システム設計の話 その８・テーブル設計 売上伝票 [システム設計の話]
売上伝票データ・売上明細データテーブルの項目内容を決めていきます。
その前に、その７でも多少触れましたが、売上データは2つのテーブルに分けて管理します。なぜそのようにするのかについて、もう少し詳しく書いておきます。
顧客から注文があった、その内容を1つの売上内容として管理するとします。そして、1回の注文で複数の商品がされることを考慮します。
言葉で表現すると、「某日、A顧客から、A商品が1つ、B商品が5つ、C商品が3つ、注文があった」となります。
表で表現すると、
こんな感じでしょうか。
このイメージのまま、テーブルの項目を作ると、次のような状態になります。
明細の数だけレコードを作成するので、「注文番号」や「注文日」は、1つの注文に対して、1件あれば良いのですが、明細の行数分作成されてしまうことになります。
2つのテーブルに分けて管理するまでもない場合は、こういった構造のテーブルを作ることもありますが、だいだい件数の少ない小さなテーブルの場合です。売上データのような、件数が多くなるテーブルでは、無駄が大きくなります。
無駄な繰り返しを省くために、1つの注文に対して1件で良い部分と、複数件になる明細の部分を切り分けます。
明細側には、その明細がどの伝票のデータかわかるように、伝票番号を持たせておきます。
このような形でテーブルを作成することで、重複を省き効率の良いデータ管理が行えます。
では、売上伝票データテーブルの内容です。
盛り込みたい内容は、その４にあげているので、その内容から作成します。
| 項目名 | データ型 | 桁数 |
| 伝票番号 | 十進型 | 整数9桁 |
| 顧客CD | 十進型 | 整数5桁 |
| 注文日 | 日付時刻型 | |
| 納品日 | 日付時刻型 | |
| 担当者CD | 十進型 | 整数5桁 |
| 税抜合計 | 十進型 | 整数12桁 |
| 消費税額 | 十進型 | 整数12桁 |
| 総額合計 | 十進型 | 整数12桁 |
| 備考欄 | テキスト | 255 |
| メモ | テキスト | 255 |
| 登録日 | 日付時刻型 | |
| 変更日 | 日付時刻型 |
キーは「伝票番号」です。
数値の項目は十進型で桁数を指定する形にしました。十進型を使うのはあまり一般的では無いような気もしますが、仕事で扱ったシステムは十進型が多かったです。
コード項目は桁数が重要になるので、そこを意識する意味で十進型にしてみました。
伝票番号は連番を持たせる項目です。ACCESSだと、とりあえず「オートナンバー」にしてしまう事が多いようですが、オートナンバーにしてしまうと、値を自由に設定できなくなるので、後々やっかいです。
基本的にオートナンバーの使用はお勧めしません。
注文日は、注文を受けた日付、納品日は注文を受けたときの納品予定日（だいたい）とします。実際には、納品が完了するまでを管理できるといいのですが、今回はそこまで考えません。
税抜合計・消費税額・総額合計は、それぞれ明細を積み上げた合計を入れておきます。こうしておくと、合計値のみを見たい場合に、いちいち明細から計算しなくて済むので。
備考欄は、帳票に印刷することのできる項目で、メモは印刷されない内部用のメモです。こういった要望は、よくありました。
登録日・変更日は、入力する項目ではなく、プログラムから設定します。登録日は新規登録を行った日時、変更日は変更された日時を設定します。
次ぎに売上伝票明細データです。
| 項目名 | データ型 | 桁数 |
| 伝票番号 | 十進型 | 整数9桁 |
| 明細番号 | 十進型 | 整数3桁 |
| 商品CD | 十進型 | 整数5桁 |
| 数量 | 十進型 | 整数9桁 |
| 単価 | 十進型 | 整数9桁 |
| 金額 | 十進型 | 整数12桁 |
| 消費税額 | 十進型 | 整数12桁 |
| 総額 | 十進型 | 整数12桁 |
| 備考 | テキスト型 | 255 |
| 変更日 | 日付時刻型 |
キーは、「伝票番号」「明細番号」です。
明細番号は、明細ごとの連番です。1伝票で同一商品を複数入力出来ないようにすれば、必要ないのですが、明細を入力した順番通りに印刷したい、と言う要望もあるかもしれないので、わかりやすくこうしています。
単価は明細行に記録するようにしています。単価について考えていくと、とっても深いものがあるので、あまり掘り下げませんが、常に商品マスタから参照する造りにしてしまうと、商品マスタの単価が変更になった際、入力時の金額と変わってしまいます。伝票の内容は入力時の単価で固定されるべきなので、明細に保持します。
金額には、単価×数量の値を入れておきます。参照するときに計算する手間が省けます。
同様に、消費税額、総額（金額＋消費税額）にも、計算した値を入れておきます。
消費税額は、税額を計算し、端数を処理した整数を入れます。消費税の計上の仕方としては、伝票での合計額から消費税を計算する方法と、明細毎に消費税を出しておいて、それを積み上げて伝票の消費税とする方法があると思います。
前者は、簡単でわかりやすいです。
後者は、伝票の合計金額から計算した消費税額と、明細を積み上げた消費税額が一致しない場合があります。明細毎に端数を処理している為、このような事が起こります。
しかしながら、任意の明細の消費税を見ることができます。
前者の場合は、明細の消費税を見たいときに、不都合がでます。
上記を踏まえ、一般的には、明細毎に消費税を計算することが多いので、その方式にしてみようと思います。
ほとんどのスーパー・コンビニでも、明細ごとに消費税を計算していると思います。一度レシートをじっくり見てみてください。
備考は、明細毎の備考欄です。明細は印刷用のみにしました。
変更日は明細の登録・変更日時です。伝票みたいに登録日・変更日を別々に持たないのは･･･、持てないから。入力画面を作る際、持たない方が都合が良いので･･･。それはまたその時に説明します。
以上、売上伝票データ、売上明細データでした。
