Microsoft Access Club Access超初心者対象Forum Access初級者対象Forum Access初・中級者対象Forum Access VBA Tips Forum DAO、ADO、SQL Forum

     

リストへもどる

投稿記事の一括表示

タイトル保管料の計算の単価を日数によって変えて計算したい
記事No171799
投稿日: 2017/04/05(Wed) 17:26
投稿者ざき
解決済: ON
OS:WINDOWS 7 Professional
Access Version:2000

保管料の計算をしたいのですが、保管した日数に応じて単価を変えて
計算するにはどうしたらよいでしょうか?
お客様によっても単価が変わるのでテーブルでパターンを管理して計算したいのですが

例)
保管料単価テーブル 
貨物 保管日数:1-4日まで1日あたり 保管日数:5-9日まで1日あたり 保管日数:10日以上1日あたり
A   1000              1500           1800
B   500                  500            500
C   1500              1700           2000


(希望するクエリの計算結果)

貨物名    保管日数   保管料   

A 15     22300 (1000*4 + 1500*5 + 1800*6の結果)
B 5 2500 (500*4 + 500*1の結果)
C 8 12800 (1500*4 + 1700*4の結果)

クエリの計算式をどうしたらよいかわかりません。
できる方法がありましたら教えてください。
よろしくお願いいたします。

タイトルRe: 保管料の計算の単価を日数によって変えて計算したい
記事No171800
投稿日: 2017/04/05(Wed) 19:38
投稿者tkwan
解決済: ON
テーブル名をT,フィールド名をA・B・Cとした場合、

 DLookUp("A","T","貨物名='" & 貨物名 & "'")*IIf(保管日数>4,4,保管日数)
+DLookUp("B","T","貨物名='" & 貨物名 & "'")*IIf(保管日数>4,0,IIf(保管日数>9,5,保管日数-4))
+DLookUp("C","T","貨物名='" & 貨物名 & "'")*IIf(保管日数>9,0,保管日数-9)

でよろしいのでは?

タイトルRe^2: 保管料の計算の単価を日数によって変えて計算したい
記事No171801
投稿日: 2017/04/05(Wed) 19:43
投稿者tkwan
解決済: ON
> テーブル名をT,フィールド名をA・B・Cとした場合、
> 
>  DLookUp("A","T","貨物名='" & 貨物名 & "'")*IIf(保管日数>4,4,保管日数)
> +DLookUp("B","T","貨物名='" & 貨物名 & "'")*IIf(保管日数>4,0,IIf(保管日数>9,5,保管日数-4))
> +DLookUp("C","T","貨物名='" & 貨物名 & "'")*IIf(保管日数>9,0,保管日数-9)
> 
> でよろしいのでは?
ひさしぶりなので、間違いました。

 DLookup("A","T","貨物名='" & 貨物名 & "'")*IIf(保管日数>4,4,保管日数)
+DLookup("B","T","貨物名='" & 貨物名 & "'")*IIf(保管日数>4,IIf(保管日数>9,5,保管日数-4),0)
+DLookup("C","T","貨物名='" & 貨物名 & "'")*IIf(保管日数>9,保管日数-9,0)

タイトルRe^3: 保管料の計算の単価を日数によって変えて計算したい
記事No171804
投稿日: 2017/04/06(Thu) 14:58
投稿者ざき
解決済: ON
tkwan様

早速のご回答ありがとうございます。
参考になります。
やってみます!

タイトルRe^3: 保管料の計算の単価を日数によって変えて計算したい
記事No171805
投稿日: 2017/04/07(Fri) 17:03
投稿者ざき
解決済: ON
tkwan様

思った通りにできました!本当にありがとうございました。

タイトルRe: 保管料の計算の単価を日数によって変えて計算したい
記事No171802
投稿日: 2017/04/05(Wed) 22:29
投稿者mayu
解決済: ON
こんばんは。

> クエリの計算式をどうしたらよいかわかりません。

まずは、{ 保管料単価テーブル }の設計を見直し、
正規化することを強くお勧めします。

現状の構成では
------------------------------------------------------------------
全ての貨物において
料金体系が3パターン( 1〜4日 と 5〜9日 と 10日以上 )のみで
今後、増減することも無ければ、変更されることも無い
------------------------------------------------------------------
という条件で固定されている上、

パターンが増えたりすれば
{ 保管料単価テーブル }のフィールド構成も都度、変更になり、
このテーブルが使われている クエリ や SQL も
記述を全て修正しなくてはならなくなります。

以下に、構造変更の例を載せておきます。


■ DDL-SQL

CREATE TABLE 保管料単価テーブル
(
      料金ID    AUTOINCREMENT  NOT NULL PRIMARY KEY
    , 貨物      VARCHAR( 100 ) NOT NULL
    , 日数下限  SMALLINT       NOT NULL
    , 日数上限  SMALLINT       NOT NULL
    , 単価      CURRENCY       NOT NULL
);


< 保管料単価テーブル > データ例

料金ID  貨物  日数下限  日数上限   単価
---------------------------------------------------
 1    A     1      4    1,000
 2    A     5      9    1,500
 3    A     10     1095    1,800
 4    B     1     365     500
 5    C     1      4    1,500
 6    C     5      9    1,700
 7    C     10     730    2,000
 8    Z     1      2     500
 9    Z     3      7     600
 10    Z     8      10     900
 11    Z     11     1460    1,000


■ DDL-SQL

CREATE TABLE t_物件
(
      物件ID    AUTOINCREMENT  NOT NULL PRIMARY KEY
    , 貨物      VARCHAR( 100 ) NOT NULL
    , 保管日数  SMALLINT       NOT NULL
);


< t_物件 > データ例

物件ID  貨物  保管日数
-----------------------------
 1    A     15
 2    B     5
 3    C     8
 4    Z     12


■ SQL
-------------------------------------------------------------------

SELECT x.物件ID
     , x.貨物
     , x.保管日数
     , Sum( 
           y.単価 * IIf( x.保管日数 > y.日数上限
                       , y.日数上限 - y.日数下限 + 1
                       , x.保管日数 - y.日数下限 + 1 )
       ) As 保管料
FROM t_物件             x
   , 保管料単価テーブル y
WHERE x.貨物 = y.貨物
  AND x.保管日数 >= y.日数下限
GROUP BY x.物件ID
       , x.貨物
       , x.保管日数
ORDER BY x.物件ID ;

-------------------------------------------------------------------


■ 結果

物件ID  貨物  保管日数  保管料
--------------------------------------
 1    A     15   22,300
 2    B      5    2,500
 3    C      8   12,800
 4    Z     12    8,700

タイトルRe^2: 保管料の計算の単価を日数によって変えて計算したい
記事No171803
投稿日: 2017/04/06(Thu) 14:57
投稿者ざき
解決済: ON
MAYU様

早速ご回答いただき本当に有難うございます!
私の今のデータの作り方があまり良くないのですね…。
試してみます!

- 以下のフォームから自分の投稿記事を修正・削除することができます -
処理 記事No パスワード

ページの先頭へ 前ページへ戻る