【ACCESS クエリ】ACCESSは、大量のレコードを一括更新できる！
ACCESSは、データベースのアプリです。
Excelは、表計算のアプリです。
ココが、完全に異なる点です。
ACCESSの原理原則を覚えた人は、以前に比べて、業務効率が上がるはずです。
ココが、ACCESSの壁を超える第一歩です。
目次
大量レコードを一括で更新する
こんにちは。伊川(@naonaoke)です。
ACCESS初心者の方から質問が来ました。
ネタがないわけではないですが、これも、MOS試験で問われるような基本の問題です。
本日は、この質問に回答します。
質問内容は、下記の通りです。
例えば、5月1日以降からの単価を変更したい。
5月1日以前に、テーブルに入力されている単価は変更したくない。
このような質問の内容だと思います。
仕事で在庫管理を担当しています。
今までは単純にエクセルで購入実績を積み上げデータ化していましたが、限界があると感じaccessを使い出しました。
商品マスタ「コード（主キー）、商品名、規格、単価（初期値）業者」
単価マスタ「コード、商品名、規格、単価、適応日」２つのテーブルを作成しました。
そこで質問ですが、商品の単価変更があった際に、過去の購入実績の単価は変えずに変更後は変更後の価格適用で購入実績を積み立てたい場合どうしたらいいか教えていただければと思います。
このブログはこんな人にお勧め
- ACCESSを覚えたての人
- ACCESSのアクションクエリを覚えたい人
- ACCESSの便利さがイマイチわからない人
このブログを、読み終わるころには・・・・
ACCESSが、Excelとは、全く別物ということが理解できると思います。その内の1つが、アクションクエリなのです。
このアクションクエリ実行して、レコードが、思い通りに変更されたとき、23秒放心状態になります。
24秒後から、感動が押し寄せてきます。
この感動を体感してください。
アクションクエリ
アクションクエリは、原則、テーブルに対して変化をもたらすクエリです。
テーブル作成クエリ
更新クエリ
追加クエリ
この中で、1番重要なのは、更新クエリです。
これを覚えると、VLOOPUP関数や、Index、Match関数を使うのが馬鹿らしくなるくらい便利です。
今回は、質問を、クエリで解決します。
ACCESSの書籍では、断片的にしか説明していないので、実務では、どのように利用するのかが、理解できません。
ACCESSの書籍が、お手元に1冊あれば、このブログを読んだ後に、ぜひ、読みなおしてください。
大量レコードを一括更新する 作業手順 その1 データベース確認
上のような、商品管理のテーブルがあります。
上のテーブルは、MT_注文です。
2月9日～6月9日までの注文データです。
この2つのテーブルにおいて、5月1日からの商品単価を変更したいと思います。
商品単価の変更は、A011～A017とします。
一律、50円アップとします。
９Hなんてエンピツは、見たことあります？
鉛筆の種類を表す記号として「B・H・F」のアルファベットが使用される。・BはBLACKの略字であり、鉛筆の黒さ（濃さ）を表す。Bに付く数字が大きいほど濃く柔らかい芯となる。
・HはHARDの略字であり、鉛筆の硬さを表す。Hに付く数字が大きいほど薄く硬い芯となる。
・FはFIRMの略字であり、「ひきしまった・しっかりとした」という意味がある。HとHBの中間の濃さ・硬さの芯である。
鉛筆の硬さ・濃さを表す17種類の単位
9H、8H、7H、6H、5H、4H、3H、2H、H、F、HB、B、2B、3B、4B、5B、6B鉛筆は9Hが最も硬く、6Bが最も柔らかい。鉛筆の硬さと濃さは反比例するため、9Hが最も薄く、6Bが最も濃い。
大量レコードを一括更新する 作業手順 その２ 商品単価を変更する
商品単価を変更しよう・・・・・・・ではないです。
アクションクエリを利用するときは、必ず、ACCESSのバックアップを作成しましょう。
失敗すると、ACCESSは、もとに戻りません。
たぶん、かなりの時間を使って、もとに戻すことになります。
私は、削除クエリで、メインとなるテーブルのデータを削除したことがあります。
単価変更などは、暗算でやらないで、上の図のようにExcelに計算させるか、クエリで計算したほうが賢明でしょう。
変更完了です。
大量レコードを一括更新する 作業手順 その3 5月1日以前のレコードを非難させる
MT_注文のダミーテーブルを作成します。
作成するダミーテーブルに、5月1日以前のレコードを非難させます。
テーブル構造のみで、MT_注文のダミーテーブルを作成します。
このように5月1日前のレコードを抽出します。
大量レコードを一括更新する 作業手順 その4 ダミーテーブルに5月1日前のレコードを追加
追加クエリを作成します。
作業手順 その3で作成した、選択クエリを下の図のように、追加クエリへ変更します。
追加クエリが、正しく作成される条件として、レコードの追加に、ダミーテーブルのフィールドが記載されます。
MT_注文のダミーテーブルを選択して、レコードを追加します。
これで、【はい】をクリックすると、レコードがダミーテーブルに追加されます。
大量レコードを一括更新する 作業手順 その5 5月1日以降のレコードを抽出する
このクエリをいったん、名前を付けて保存してください。
今回は、Q_testという名前で保存しました。
大量レコードを一括更新する 作業手順 その6 単価を更新する
では、更新クエリを作成します。
ここまでが、更新クエリの作成手順です。
Q_testの商品単価を更新したいので、MT_商品の単価をリレーションで参照します。
この考え方は、データベースを操作するに当たり、めちゃくちゃ重要です。
データベースは、一度入力された値は、二度と入力しない。
参照するが原則です。
本当に、単価が変更されたのかを、下記の選択クエリで確認します。
単価が変更されました。
大量レコードを一括更新する 作業手順 その7 再度、追加クエリで、レコードを追加する
これで完成です。
最後に、テーブルの名前を変更します。
これで、作業は完了です。
ここがポイント
私の会社では、このような作業をするときに、ACCESSのデータを、Excelへ転記して、VLOOPUP関数等で、価格を変更後に、ACCESSへレコードをコピペで貼っているのを見かけます。
ACCESSを理解していないなぁと思いながら、見て見ないふりをしています。
それは、ACCESSに興味のない人に教える必要がないからです。
このアクションクエリを理解するために、役立つのが、オートルックアップクエリなのです。
オートルックアップクエリは、主キーに基づいて、値を参照できる技です。
値を参照できるなら、値も更新できるはずと思ったのがキッカケでした。
更新クエリは、本当に知らない人が多いです。
私もそうでしたが、ACCESSの初心者には、更新クエリに関しては、質問もうまくできないのが現状ではないでしょうか？
ExcelのVLOOPUP関数等で更新するのもいいですが、ACCESSは、単純に、リレーションで、値を参照するので、Excelの参照系の関数より、断然、ミスも減るのです。
ACCESSの標準機能を覚えるだけでも、業務効率化ができます。
まとめ
近年では、パワークエリ、パワーピボット等、Excelがどんどん便利になっています。
しかし、あれは、データベースの知識がない人でも利用できるようになった、ACCESSの簡易版です。
Excelもデータベースという舞台に立ち、そして、データベースとして、認知されるようになってきました。
しかし、餅は餅屋です。
Officeの中で、データベースと言えば、ACCESSです。
今回も最後まで読んでいただき、ありがとうございました。
今回のサンプルファイルは、No235です。
わからない事を延々と考えるのは、無駄です。
- なんで作動かないの？
- もうやだ！VBAなんか嫌い！
- ネットで調べても情報がない！
必ず作動するコードが、ここにあります。
