Access (一般機能)

Accessの一般機能に関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10全般 : Access 2013)
累計計算
投稿日時: 21/07/09 10:33:41
投稿者: vaioyuki

いつもお世話になっております。
よくある質問だと思いますが検索しても近いもの?が出ないのでこちらに質問させていただきます。
 
現在、Accessで仕訳帳を作成しています。
「T_出納帳」テーブルに
ID
日付
得意先コード
借方勘定項目コード
貸方勘定科目コード
金額
備考
処理日
を設けています。
 
クエリで金額の調整、例えば借方に現金があればプラス計算、貸方であればマイナス計算をするテーブル作成クエリを作成し、
それを元にクエリで「累計: CCur(DSum("再計算金額","TQ_科目別集計表金額再計算","ID<=" & [ID]))」と累計ランを作りそれをもとにフォームを作成しています。
 
フォーム欄には年度初めの繰越金額を表示して、テキストボックスを別で作成し「=[繰越金額]-[累計]」で計算して出しています。
本来は累計計算として年度初めの繰越金額から計算していきたいのですがどうしていいかわかりません。
ここは一般機能のスレッドですが、
VBAで繰越金額を変数にして計算するのかな?とか思ったりもするのですがそこから先には進めません。
 
アドバイスをよろしくお願いします。

回答
投稿日時: 21/07/11 18:13:28
投稿者: hatena
投稿者のウェブサイトに移動

全体像が不明なので、
一般論として、
勘定項目に「繰越金」という値を追加しておいて、
年度初めのレコードを「繰越金」としてそこの金額に繰越金額を入力しておけば、
現状のDSumの式で繰越金額を含む累計になります。
  
それが一番、シンプルで分かり安いのでは。

投稿日時: 21/07/13 16:46:32
投稿者: vaioyuki

hatenaさん
 
せっかくコメントいただいたのに返事が遅くなってしまい申し訳ございません。
hatenaさんのブログも大変参考にしております。
 
テーブル勘定科目には繰越金額を設けています。
 

SELECT
  TQ_科目別集計表金額再計算.ID
  , TQ_科目別集計表金額再計算.日付
  , TQ_科目別集計表金額再計算.得意先コード
  , TQ_科目別集計表金額再計算.得意先名
  , TQ_科目別集計表金額再計算.借方勘定科目コード
  , TQ_科目別集計表金額再計算.借方勘定科目名
  , TQ_科目別集計表金額再計算.借方項目CD
  , TQ_科目別集計表金額再計算.貸方勘定科目コード
  , TQ_科目別集計表金額再計算.貸方勘定科目名
  , TQ_科目別集計表金額再計算.貸方項目CD
  , TQ_科目別集計表金額再計算.再計算金額
  , TQ_科目別集計表金額再計算.備考
  , TQ_科目別集計表金額再計算.処理日
  , CCur(DSum("再計算金額", "TQ_科目別集計表金額再計算", "ID<=" & [ID])) AS 累計
  , TQ_科目別集計表金額再計算.繰越金額 
FROM
  TQ_科目別集計表金額再計算 
ORDER BY
  TQ_科目別集計表金額再計算.日付;

 
SQLで表示するとこうなります。
フォームに繰越金額が表示したいためにクエリに追加しました。
 
現在の
 
累計: CCur(DSum("再計算金額","TQ_科目別集計表金額再計算","ID<=" & [ID])+[繰越金額])
 
こちらに繰越金額を追加するということでしょうか?

投稿日時: 21/07/13 16:50:05
投稿者: vaioyuki

ちなみに、
元にしているテーブル作成クエリはこのように作っています。
 

SELECT
  T_出納帳.ID
  , T_出納帳.日付
  , T_出納帳.得意先コード
  , M_得意先マスター.得意先名
  , T_出納帳.借方勘定科目コード
  , M_勘定科目マスター.勘定科目 AS 借方勘定科目名
  , M_勘定科目マスター.項目CD AS 借方項目CD
  , T_出納帳.貸方勘定科目コード
  , M_勘定科目マスター_1.勘定科目 AS 貸方勘定科目名
  , M_勘定科目マスター_1.項目CD AS 貸方項目CD
  , Switch( 
    ( 
      [借方勘定科目コード] = [Forms] ! [F_検索画面] ! [c_勘定科目] 
      And [借方項目CD] = 1
    ) 
    Or ( 
      [借方勘定科目コード] = [Forms] ! [F_検索画面] ! [c_勘定科目] 
      And [借方項目CD] = 5
    ) 
    , [金額]
    , ( 
      [借方勘定科目コード] = [Forms] ! [F_検索画面] ! [c_勘定科目] 
      And [借方項目CD] = 2
    ) 
    Or ( 
      [借方勘定科目コード] = [Forms] ! [F_検索画面] ! [c_勘定科目] 
      And [借方項目CD] = 3
    ) 
    Or ( 
      [借方勘定科目コード] = [Forms] ! [F_検索画面] ! [c_勘定科目] 
      And [借方項目CD] = 4
    ) 
    , [金額] * - 1
    , ( 
      [貸方勘定科目コード] = [Forms] ! [F_検索画面] ! [c_勘定科目] 
      And [貸方項目CD] = 1
    ) 
    Or ( 
      [貸方勘定科目コード] = [Forms] ! [F_検索画面] ! [c_勘定科目] 
      And [貸方項目CD] = 5
    ) 
    , [金額] * - 1
    , ( 
      [貸方勘定科目コード] = [Forms] ! [F_検索画面] ! [c_勘定科目] 
      And [貸方項目CD] = 2
    ) 
    Or ( 
      [貸方勘定科目コード] = [Forms] ! [F_検索画面] ! [c_勘定科目] 
      And [貸方項目CD] = 3
    ) 
    Or ( 
      [貸方勘定科目コード] = [Forms] ! [F_検索画面] ! [c_勘定科目] 
      And [貸方項目CD] = 4
    ) 
    , [金額]
  ) AS 再計算金額
  , T_出納帳.備考
  , T_出納帳.処理日
  , M_勘定科目マスター_2.勘定科目コード
  , M_勘定科目マスター_2.繰越金額 
INTO TQ_科目別集計表金額再計算 
FROM
  M_勘定科目マスター AS M_勘定科目マスター_2
  , M_項目 
  INNER JOIN ( 
    M_勘定科目マスター 
      INNER JOIN ( 
        M_得意先マスター 
          INNER JOIN ( 
            M_項目 AS M_項目_1 
              INNER JOIN ( 
                M_勘定科目マスター AS M_勘定科目マスター_1 
                  INNER JOIN T_出納帳 
                    ON M_勘定科目マスター_1.勘定科目コード = T_出納帳.貸方勘定科目コード
              ) 
                ON M_項目_1.項目CD = M_勘定科目マスター_1.項目CD
          ) 
            ON (M_得意先マスター.得意先コード = T_出納帳.得意先コード) 
            AND (M_得意先マスター.得意先コード = T_出納帳.得意先コード)
      ) 
        ON M_勘定科目マスター.勘定科目コード = T_出納帳.借方勘定科目コード
  ) 
    ON (M_項目.項目CD = M_勘定科目マスター.項目CD) 
    AND (M_項目.項目CD = M_勘定科目マスター.項目CD) 
WHERE
  ( 
    ( 
      (T_出納帳.日付) Between [Forms] ! [F_検索画面] ! [txt_日付1] And [Forms] ! [F_検索画面] ! [txt_日付2]
    ) 
    AND ( 
      (T_出納帳.借方勘定科目コード) = [Forms] ! [F_検索画面] ! [c_勘定科目]
    ) 
    AND ( 
      (M_勘定科目マスター_2.勘定科目コード) = [Forms] ! [F_検索画面] ! [c_勘定科目]
    )
  ) 
  OR ( 
    ( 
      (T_出納帳.日付) Between [Forms] ! [F_検索画面] ! [txt_日付1] And [Forms] ! [F_検索画面] ! [txt_日付2]
    ) 
    AND ( 
      (T_出納帳.貸方勘定科目コード) = [Forms] ! [F_検索画面] ! [c_勘定科目]
    ) 
    AND ( 
      (M_勘定科目マスター_2.勘定科目コード) = [Forms] ! [F_検索画面] ! [c_勘定科目]
    )
  ) 
ORDER BY
  T_出納帳.日付;

 
資産、負債、費用、収益でプラスマイナス計算を行っています。

回答
投稿日時: 21/07/15 14:34:44
投稿者: hatena
投稿者のウェブサイトに移動

複雑すぎて実際のテーブルデータなしにSQLを解読するのは困難なので、
シンプルなモデルで累計を表示する例を提示しておきますので、
それを参考に実際のものに応用してください。
 
 

ID 日付 科目コード 金額
1  4/1   0         1000 '科目コード=0 が前期繰越金の意味
2   4/1   1          100
3   4/2   2         -100
4   4/3   1          100

 
累計: DSum("金額","テーブル名","ID<=" & [ID])
 
要するの一番最初のレコードに前期の繰越金額を入力しておくということ。
 
もし、それが不可でフォームに繰越金額を入力しておくということなら、
 
累計: Fprms!フォーム名!前期繰越金 + DSum("金額","テーブル名","ID<=" & [ID])
 
複雑な計算で件数も多くて重くなるということなら、VBAで累計フィールドへ入力することになりますね。
 
下記で累計値を自動入力する汎用関数を紹介していますので、それが使えかもしれません。
 
累計値をテーブルに自動入力する関数 - hatena chips https://hatenachips.blog.fc2.com/blog-entry-443.html

投稿日時: 21/07/16 16:46:18
投稿者: vaioyuki

hatenaさん
 
いつもありがとうございます。
参考にさせていただいて少し考えます。
また報告させていただきます。

トピックに返信