Access (一般機能)

Accessの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Professional : Access 2013)
セットのデータを展開して按分するクエリ
投稿日時: 19/06/12 13:08:22
投稿者: hato

お世話になります。
セットの商品の売上データをAccessに取り込んだときに
セット商品の値段に対して各商品の単価の割合で、商品単価を按分して
取り込みたいです。ただし割合を小数点2桁の四捨五入で計算したときの誤差は
セット内の商品コードを昇順にして最後の商品で調整したいです。
これをクエリで実現することは可能でしょうか?
よろしくお願いします。
 
・セット商品M
セット商品コード    商品コード    構成数
A001                A005        1
A001                A006        1
A001                A007        1
A002                A009        3
 
・商品M
商品コード    商品単価
A001        5000    ・・・(セット商品)
A002        3000    ・・・(セット商品)
A005        1800
A006        2200
A007        1740
A009        1250
 
●取り込む売上データ
日付    商品コード    数量    売上単価    売上金額
6/12    A001        2    5000        10000
6/12    A002        3    3000        9000
 
●作りたい変換クエリ
日付    商品コード    数量    売上単価    売上金額
6/12    A005        2    1550        3100
6/12    A006        2    1900        3800
6/12    A007        2    1550        3100
6/12    A009        9    1000        9000
 
★セット商品の単価の按分
例:A001の場合
商品コード    通常単価    セット単価割合    セット単価
A005        1800        0.31        5000 * 0.31 = 1550
A006        2200        0.38        5000 * 0.38 = 1900
A007        1740        0.3        5000 * 0.3 = 1500 ではあるが
                            商品コードの昇順に並んだ最後のは端数を考慮して
                            5000 - 1550 - 1900 = 1550
--------------------------------------------------------------------
合計        5740                 5000
 
 

投稿日時: 19/06/12 16:41:45
投稿者: hato

日付 セット商品コード    商品コード 数量 商品単価    商品合計    係数 売上金額
 6/12    A001         A005     2     1800        5740        0.31    3100
 6/12    A001         A006     2     2200        5740        0.38    3800
 6/12    A001         A007     2     1740        5740        0.30    3100
 6/12    A002         A009     9     1250        3750        0.33    9000
   
ここまでのクエリは作成できました。
セット商品コードを同じグループとして、グループの最終行の商品で金額調整するためには
 クエリでは限界がありますか?
  
よろしくお願いします。

回答
投稿日時: 19/06/12 18:17:48
投稿者: mayu.

■ セット商品M
 

セット商品コード  商品コード  構成数  セット単価割合
------------------------------------------------------------
  A001       A005     1      0.31
  A001       A006     1      0.38
  A001       A007     1      0.3
  A002       A009     3      1.0

だとして
( セット単価割合 の列が存在するのはこのテーブルしか考えられないため )
 
■ SQL
-------------------------------------------------------------------
SELECT z.セット商品コード
     , z.商品単価 As セット価格
     , y.商品コード
     , x.商品単価
     , CCur(
           ( Int( z.商品単価 * y.セット単価割合 ) / y.構成数 )
               +
           IIf( y.商品コード = z.按分コード
              , z.商品単価 - z.調整額
              , 0
           )
       ) As セット単価
     , y.構成数
FROM 
( 
               商品M       x
    INNER JOIN セット商品M y
            ON x.商品コード = y.商品コード
)
INNER JOIN
(
    SELECT y.セット商品コード
         , x.商品単価
         , Max( y.商品コード ) As 按分コード
         , Sum( Int( x.商品単価 * y.セット単価割合 ) ) As 調整額
    FROM       商品M       x
    INNER JOIN セット商品M y
            ON x.商品コード = y.セット商品コード
    GROUP BY y.セット商品コード
           , x.商品単価
) z
ON y.セット商品コード = z.セット商品コード
ORDER BY 1, 2 ;

■ 結果
セット商品コード  セット価格  商品コード  商品単価  セット単価  構成数
---------------------------------------------------------------------------------
  A001        5,000     A005     1,800    1,550    1
  A001        5,000     A006     2,200    1,900    1
  A001        5,000     A007     1,740    1,550    1
  A002        3,000     A009     1,250    1,000    3

なお、
引用:
●作りたい変換クエリ

引用:
●取り込む売上データ

回答したSQLの結果を結合すれば 簡単に算出できますから割愛します。

投稿日時: 19/06/13 09:59:31
投稿者: hato

mayu.さん、早速のご回答ありがとうございます!
試してみたらうまくいきました。
こんなこともクエリでできるんですね。
とても勉強になります。内容をよく考えてみようと思います。
 
同じ値段の別商品が3つセットになっている場合などは、1円単位の誤差があるので
四捨五入などをもう少し調整してみようと思います。
お忙しいなか、ありがとうございました!