Access データベースの合理化:自動で正確なクロステーブル計算のための計算フィールドの追加

 

Microsoft Access は、手動によるデータ入力を減らし、エラーを最小限に抑え、データベースの一貫性をリアルタイムで維持することにより、リンク テーブル間の複雑な計算を自動化する強力な機能を提供します。計算フィールドを使用すると、データベースをより自動化できます。ユーザーに合計、割引、期日、利益値を手動で入力するよう求める代わりに、Access は既存のフィールドからそれらを計算できます。

このチュートリアルでは、自動クロスリンク計算のために計算フィールドを Access テーブルに追加する方法を示します。関連するテーブル全体で値を自動的に取得して計算する計算フィールドを構築しましょう。

高度なデータベースでは、計算フィールドをランダムに使用すべきではないことに注意してください。最も重要なルールは次のとおりです。

• 同じレコード内のフィールドに依存する値には計算フィールドを使用する
• リンクされたテーブルまたは複数のレコードに依存する計算にはクエリを使用する

ステップ 1:サンプル関連テーブルを設定する

複数のテーブルにまたがる計算フィールドは、強固な関係に依存します。単一の式を記述する前に、ここから始めてください。

関係を作成します:

• データベース ツールに移動します。 タブ>> 関係を選択します
• テーブルを追加します。
  • 顧客 ID をドラッグします Customers テーブルから CustomerID へ Orders テーブル内
  • 製品 ID をドラッグします Products テーブルから ProductID へ OrderDetails テーブル内
  • 注文IDをドラッグします Orders テーブルから OrderID へ OrderDetails テーブル内
• 参照整合性の強制を有効にします。
• [OK] をクリックします。

この設定により、相互リンクされた計算 (行合計の合計として小計を注文するなど) が可能になります。この連携により、テーブル間検索の信頼性が高まります。これがないと、参照されたレコードが削除されたり不一致になったりしたときに、計算フィールドが暗黙のうちに null を返す可能性があります。

ステップ 2:テーブルに単純な計算フィールドを追加する

• 注文の詳細を開きます デザイン ビューのテーブル
• 最初の空の行:
  • フィールド名:LineTotal
  • データ型:計算
• Access により式ビルダーが開きます。
• 数式を入力してください:

• または視覚的に作成します。OrderDetails を展開します。 テーブル>> 数量 をダブルクリックします。 と単価 、* を追加します。 演算子
• 結果のタイプを設定します。 (通貨、数値、テキストなど) 式が返すものと一致します
  • フィールドのプロパティを展開します。>> 通貨を選択します
• 保存 テーブル

割引を含む合計:

[Quantity] * [UnitPrice] * (1 - [DiscountRate])

Access では、すべてのレコードの LineTotal が自動的に計算されるようになりました。VBA や手動更新は必要ありません。データシート ビューで数量または単価を追加または編集するたびに、LineTotal が自動的に更新されます。

ステップ 3:クロスリンク計算の処理 — クエリでドメイン集計関数を使用する

テーブル計算フィールドでは、テーブル間の直接参照は許可されません。クエリまたは VBA を使用する必要があります。ドメイン集計関数は、別のテーブルまたはクエリから計算値を式に取り込むための Access の組み込みメカニズムです。最も便利なものは次のとおりです。

機能 目的 DLookup()別のテーブルから単一の値を返すDSum()条件に一致する別のテーブルの値を合計するDCount()別のテーブル内の一致するレコードを数えるDAvg()別のテーブルからの値を平均するDMax() / DMin()別のテーブルから最大値または最小値を返す

クエリを作成します:

• [作成] に移動します。 タブ>> SQL クエリ を選択します。
• テーブルの追加から ペインに、Orders テーブルと Customers テーブルを追加します
• フィールドを追加します:CustomerName、OrderID
• 空のフィールド列に、合計の計算フィールドを作成します。
• 次の式を挿入します。

Total: DSum("[LineTotal]","OrderDetails","[OrderID]=" & [OrderID])

• DSum() は、一致する OrderID の LineTotal を合計します (テーブル間で機能するドメイン集計関数)
• qryOrdersummary として保存
• [実行] をクリックします。

クエリは実行のたびに再計算されます。このクエリは、フォームやレポートのレコード ソースとして、またはさらなる計算の基礎として使用します。

通貨を使用した形式:

Total: CCur(DSum("[LineTotal]","OrderDetails","[OrderID]=" & [Orders].[OrderID]))

ステップ 4:計算ソースとして複数テーブルの計算クエリを構築する

より高度なシナリオ (あるテーブルから顧客の階層を計算し、別のテーブルから製品価格を計算するなど) の場合は、関連するすべてのテーブルを結合する基本クエリを作成し、そのクエリを計算フィールドまたはフォームで参照する必要があります。

手順:

• [作成] に移動します。 タブ>> SQL クエリ を選択します。
• 注文を追加します 、製品 、および注文の詳細 クエリに対して
• フィールドの追加:OrderID、ProductName
• 空のフィールド セルに計算列を追加します。

Profit: [DiscountedTotal] - [CostPrice]

• クエリを qryOrderProfit として保存します。
• [実行] をクリックします。

これで、すべての注文の製品名を含む利益レポートが作成されました。

これで、フォーム、レポート、またはダウンストリームの計算フィールドで、DLookup() または qryOrderProfit に対するサブクエリを使用して、完全に計算された値を取得できるようになります。すべて相互リンクされ、自動で行われます。

ステップ 5:データ マクロを使用して更新を自動化する

計算結果を (表示するだけでなく) 保存する必要がある場合、たとえば、OrderDetails レコードが変更されるたびに、計算された合計を Orders テーブルに書き戻す場合は、子テーブルにアタッチされたデータ マクロを使用します。

注文テーブルに合計フィールドを追加します。

• まず、注文を開きます。 デザイン ビューのテーブル
  • フィールド名:合計
  • データ型:通貨

これは計算フィールドではなく、通常の通貨フィールドである必要があります。

セットアップ:

• 注文の詳細を開きます デザインビューで
• テーブルツールに移動します。 タブ>> テーブルを選択します タブ>> 挿入後 / 更新後を選択します。

• マクロ エディタで、SetField を使用します。 とルックアップレコード アクション:
• レコードの検索を選択します

Lookup Up A Record In: Orders
Where Condition: [Orders].[OrderID] = [OrderDetails].[OrderID]

• レコードの編集を選択します>> フィールドの設定を選択します

Name: [Orders].[Total]
Value: DSum("[LineTotal]","OrderDetails","[OrderID]=" & [OrderID])

• [保存] をクリックします。

これは OrderDetails への挿入または更新のたびに自動的に実行され、再計算された合計が親の Orders テーブルに戻されます。完全に自動化されており、VBA は必要ありません。

更新後も繰り返します:

挿入後 マクロは、新しい詳細行が追加された場合にのみ合計を更新します。ユーザーが数量、単価、または割引率を変更した場合、合計も更新する必要があります。

同じマクロを追加します。

Look Up A Record In Orders
Where Condition: [Orders].[OrderID]=[OrderDetails].[OrderID]
 EditRecord
 SetField
 Name: [Orders].[Total]
 Value: DSum("[LineTotal]","OrderDetails","[OrderID]=" & [OrderDetails].[OrderID])

• 保存 それ

今後は、OrderDetails でレコードが挿入または更新されるたびに、Access によって注文合計が自動的に再計算され、Orders テーブル内の一致するレコードに格納されます。

ステップ 6:計算の表示と使用

• データシート ビューの場合: 計算フィールドが表示され、ライブで更新されます。
• フォーム/レポート内: 完全な相互リンク結果を得るには、フォームまたはレポートをクエリ (qryOrdersummary) に基づいて作成します。式を含む非バインド テキスト ボックスを追加します。
• フィルタリング/並べ替え: 計算フィールドはクエリ条件または並べ替えで使用できます。

ヒント: 後でテーブル計算フィールドを編集するには:

• データシート ビュー:列を選択します。>> フィールドを選択します。 タブ>> 式を変更します
• またはデザインビューに戻ります>> プロパティを選択します>> 式を選択します .

ベスト プラクティスとパフォーマンスに関する考慮事項

• テーブル計算フィールドよりもクエリを優先する 複数のテーブル、集計、または将来の変更に関係するもの。クエリは移植性が高く（例:SQL Server へ）、柔軟性が高くなります。
• 計算の保存を避ける パフォーマンスが要求しない限り (たとえば、オンザフライ合計が遅い非常に大規模なデータセット)。代わりにクエリで再計算します。
• 正規化: 生の入力のみを保存します。出力を動的に計算します。
• テスト: 変更後は、特にリレーションシップを追加した後は必ずサンプル データで検証してください。
• パフォーマンス: 計算フィールドが多すぎる場合や、大規模なテーブルでの複雑な DSum() 呼び出しが多すぎる場合、処理速度が低下する可能性があります。外部キーのインデックスを作成します。
• 制限事項の要約:
  • 他のテーブルのフィールドを直接テーブル計算式に含めることはできません。
  • テーブル計算フィールドの関数が制限されています（完全な VBA アクセスにはクエリを使用します）。
  • 結果は読み取り専用です。
• スケーリングのヒント: 非常に高度なニーズの場合は、ロジックを SQL ビューまたは SQL Server などのバックエンドに移行することを検討してください。そこでは、計算列がより強力な機能を提供します。

よくある落とし穴とトラブルシューティング

• #エラーまたは#名前?: フィールド名が括弧 [] で囲まれていること、データ型が一致していること、リレーションシップが有効であることを確認してください。
• 循環参照: 独自の式で計算フィールドを参照したり、ループを作成したりしないでください。
• データ型の不一致: 結果タイプを明示的に設定します (例:お金フィールドの通貨)。
• クロステーブルの失敗: ロジックを結合を含むクエリに移動するか、DSum() を使用します。
• リンク テーブル（SharePoint や別のデータベースなど）を使用している場合、計算フィールドに行数制限や更新の問題が発生する可能性があります。

結論

上記の手順に従って、計算フィールドを Access テーブルに追加して、自動クロスリンク計算を行うことができます。計算フィールドは、Microsoft Access で行レベルの計算を自動化するのに役立ちます。計算フィールドは、明細行の合計、割引、期日、注文明細ごとの利益などの値に適しています。高度な相互リンク計算には、クエリが適切なツールです。クエリはテーブル間の関係を追跡し、注文合計、顧客売上合計、在庫残高、利益の概要を計算します。 Access で計算フィールドと相互リンクされた計算をマスターすると、静的データ ストアが生きた自己保守システムに変わります。

ソリューション付きの高度な Excel 演習を無料で入手しましょう!