金額の割り振り方について（ＡＣＣＥＳＳ＆ＶＢ）

現在、ＡＣＣＥＳＳのみで請求書を発行するシステムを作っていますが、ＶＢ使用経験なしで、使う必要がでてくることになってしまったので質問します。

氏名　　部門1　部門2　部門3　　
Ａさん：　10　　
Ｂさん：　10　　20　　　30

氏名　　金額
Ａさん：1000円
Ｂさん：1000円

という二つのテーブルがある時に

10→1333円　20→333円　30→334円
というふうに配分しなければなりません。

素人なりの考え方としては
1：Ｂの部門を取得
2：Ｂに割り振るべき部門数をカウント
3：1000円を2で得た部門数3で割る
（この時に、単純に割れば333.33となってしまうので、関数的に数値を丸めた後変数に代入して
a = 333, b = 333, c = 1000－（a+b)ということが可能？）
4：部門ごとに集計。というふうに考えいています。

4に関しては、「部門」で合計をだすだけなのでわかりますが、1・2・3に関しては可能でしょうか？ヒントだけでもいただければと思います。よろしくお願いいたします。

No.6 ベストアンサー 回答者： taseki 回答日時： 2005/07/31 20:45

その場合でも考え方は同じです。

ANo.3の担当部門マスターと担当別業績トランザクションから、「担当者別配分金額クエリー」を作ります。

担当者別配分金額クエリー
----------------------
氏名　　金額　　担当部門数　配分金額
Ａさん　1600円　　　2　　　　　800円
Ｂさん　500円 　　　1　　　　　500円
Ｃさん　1500円　　　1　　　　　1500円

担当部門数は、グループ化してカウント関数を使います。配分金額は
「金額 ÷ 担当部門数」
です。

あとは、このクエリーと担当部門マスターを関連付けて、

部門別合計金額クエリー（グループ化前）
----------------------
部門　氏名　　配分金額
10　　Ａさん　　　800円
20　　Ａさん　　　800円
30　　Ｂさん　　　500円
10　　Ｃさん　　　1500円

これを部門でグループ化して、集計関数を使って配分金額の合計を出せばいいとおもいます。

部門別合計金額クエリー（グループ化）
----------------------
部門　配分金額
10　　　2300円
20　　　800円
30　　　500円

これはあくまでも一例です。

この回答へのお礼

tasekiさん、ありがとうございます。とてもわかりやすい説明でした。実際の数字は１円単位だったり、割り切れない数だったりするのですが、すこし工夫してやってみたいと思います。

お礼日時：2005/07/31 22:30

No.5 回答者： imogasi 回答日時： 2005/07/31 20:43

＃２です。

補足ありがとうございました。後に読まれた方に役立つことを祈りつつ。
これであれば、Ｂさんのように３（複数）部門に従事した人がいる。
「Ｂさんの月給を各部門に３等分して、各部門に配分し、部門ごとに集計したい」とでも文章を、はじめの質問文に添えていただければ、現実の経験なりに照らして、すぐ分かったでしょう。
さて１レコードのフィールドが、部門名ごとに設けられていて、１職員に複数部門該当者がいるときは
（１）氏名なりによって両テーブルを結合し、新（クエリ）テーブルには職員の支払給与額を取ってくる。
（２）部門に部門コードが入力されてある数を数え、新しく設けるその部門コードフィールドに、１／（部門数）を掛けたものを演算式で計算して、出力するクエリを作る。新フィールドは部門の数だけ作りそこには配賦金額が入る。（多すぎてできないことはないでしょうね？。）
後は各新部門フィールドの縦合計を出せば、部門向け請求額が出るのではないですか。
たとえばＢさんは10万円が支払い給与として、
Ｂさん　１０００００円
部門1　部門２　部門3　部門１ＮＦ　　部門２ＮＦ　　部門３ＮＦ
●　　　●　　　●　　３３３３３円　３３３３３円　３３３３４円
３３３３３＝１０００００／（１＋１＋１）
これであれば、ＶＢＡを使わなくてもすみそうです。

この回答へのお礼

imogasiさん、ありがとうございました。
とにかく中間テーブルを作って部門ごとに集計するという方法がいいようですね。なぜＶＢを使う必要があるのかと思ったかというと、やはり端数の問題です。例えば５０１円を２５０円と２５１円に分配するというケースですね。そこらへんがＳＱＬ文のみで作れそうならもう少し研究してみようと思います。ＶＢを使わないにこしたことはないので。

お礼日時：2005/07/31 23:04

No.4 回答者： taseki 回答日時： 2005/07/31 18:34

参考までに。

こういうのを「多対多」の関係と呼びます。
●１つの「部門」は複数の「人」と関連付けられる
●１つの「人」は複数の「部門」と関連付けられる

Accessのヘルプにも載っているので、「多対多」で検索するといいと思います。

別の例として、たとえば「映画作品と監督」データベースを考えると、
ある一人の監督は、いくつも映画を作るので、
●１つの「監督」は複数の「作品」と関連付けられる
●１つの「作品」は１つの「監督」と関連付けられる
（※実際は作品によって監督が複数のもありますが、例外として…）
これは「１対多」です。

これに対し、「映画作品と俳優」データベスを考えると、
ある一人の俳優は、いくつもの映画に出ますし、１つの作品にはたくさんの俳優が出ていますので、
●１つの「俳優」は複数の「作品」と関連付けられる
●１つの「作品」は複数の「俳優」と関連付けられる
これは「多対多」です。

No.3 回答者： taseki 回答日時： 2005/07/31 18:08

なんだか、正直、設計に無理のあるデータベースですね…。

特に1つ目のテーブルの意味が解りません。
「誰がどの部門を担当しているか？」を管理しているのだとしても、これでは利用する際に値を取り出すのが難しくなります。

最も自然な設計としては、以下の感じです。というか普通はこうしないと何かと大変苦労するし、実現不可能なのもでてきてしまうかもしれません。

部門マスター
----------------------
部門コード　部門名
　　10　　　　Ａ部門
　　20　　　　Ｂ部門
　　30　　　　Ｃ部門

業績トランザクション
----------------------
氏名　　部門　金額
Ａさん　10　　1000円
Ｂさん　30　　500円
Ａさん　20　　600円
Ｃさん　10　　1500円

もちろん、「氏名」ではなく「担当者ID」などで一意に管理するべきなのは言うまでもありません。

で、こうしておくと、
部門でグループ化して合計すれば、部門別の合計が
氏名でグループ化して合計すれば、氏名別の合計が
簡単に出せます。

あるいは、氏名別に金額を管理したいなら、

担当部門マスター
----------------------
氏名　　部門
Ａさん　10
Ａさん　20
Ｂさん　30
Ｃさん　10

と、

担当別業績トランザクション
----------------------
氏名　　金額
Ａさん　1600円
Ｂさん　500円
Ｃさん　1500円

という2つを追加し、それぞれ関連付けます。
そして氏名でグループ化して担当する部門数が簡単に出せるので、それをもとに担当別業績トランザクションから取り出した金額をそれぞれ分配すれば良いことになります。

ちなみに、VB（VBA）は必要ないと思います。もちろん何らかの関数を定義するなどなら別ですが。
これはむしろ、データベースの基礎の部分と思います。

この回答への補足

tasekiさん、早速のご返信ありがとうございます。
詳細はＮｏ2の方への補足を参照していただきたいのですが、
＞部門マスター
今回、部門名は必要がないのでつくっていません。あえていえば所属Ｍといったほうがいいでしょうか。

＞業績トランザクション
請求書のデータ（Ｔ2）をそのまま所属Ｍ（Ｔ1）と氏名でくっつけています。ここでのＡさんの
＞Ａさん　10　　1000円
＞Ｂさん　30　　500円
＞Ａさん　20　　600円
は、Ｔ2では
Ａさん　1600円
Ｂさん　500円
というデータになっています。それを所属Ｍをもとに
10→800円　20→800円　30→500円というふうな結果をだしたいというのが現状です。
tasekiさんが後半におっしゃっているようなやり方ならうまくいくかもしれません。

補足日時：2005/07/31 18:19

No.2 回答者： imogasi 回答日時： 2005/07/31 14:00

＞二つのテーブルがある

はわかります。テーブル１とテーブル２とします。
＞部門１の１０とは何か。
金額か、使用時間のようなもの？
部門２、部門3についても同じ。単位を言及すれば、全体の内容が判りやすいはず。
＞1：Ｂの部門を取得
Ｂとは「Ｂさん」ですね。Ｂさんの行はテーブル１内に１回しか出てこないのですか。（後の他の行にも出てこないのか）
請求書だからまとめる必要があろうと思うから。
＞10→1333円　20→333円　30→334円
というふうに配分しなければなりません。
これはＡさんとＢさんが、部門１で１０だけ、あがっているが、両者で分担するとかをする必要があるのか（Ａさんの部門１の１０はＢさんに影響するのか）。？
合計が２０００であるところから、どうも分担するようですね。
Ａ，Ｂさん以外のひとにも部門1のあがっている可能性があると、全レコード
調べる必要があるのですね。
それとも１０の単価が１３３３円？、２０の単価が３３３円、３０の単価が３３３円と利用の多少にかかわらず、決まっているのか。
＞1：Ｂの部門を取得
2：Ｂに割り振るべき部門数をカウント
3：1000円を2で得た部門数3で割る
と「Ｂさん」だけ取り上げての表現だが、なぜ合計２０００になるの。そのうち１０００はＡさんのものでないの。
＞1・2・3に関しては可能でしょうか
アクセスの関数は基本的に同一レコードのフィールド間のもの。
ＳＱＬの集合関数はまた別だが。
レコード間をまたいだ計算は、アクセスでは難しいと思うべし。

●もう少し、請求額計算方法をすっきり説明してほしい。

それとレコードの持ち方はすでにきまっているのですか。
＃１のご回答にも、それらしき記述があるが、レコードの持ち方と、計算するロジックの簡単か煩雑は大いに影響しますから。
今からでも、できればレコードのフィールドの持ちかたを変えるとか、ＡＤＯで一旦変えてしまったほうが、後の処理が簡単になる場合がありそう。

この回答への補足

osamuvさん、imogasiさん、さっそくのご回答ありがとうございます。わかりずらいところを補足説明させていただきます。やりたいことは、二つのテーブルを氏名でつないで、部門コード別に金額の集計をだしたいのです。

＞部門１の１０とは何か。
部門コードです。
＞Ｂとは「Ｂさん」ですね。Ｂさんの行はテーブル１内に１回しか出てこないのですか。（後の他の行にも出てこないのか）
「Ｂさん」です。一回しかでません。

もともとは人に対して部門コードが一つだけだったので、
テーブル1　　　　　　　　テーブル2
氏名　　部門コード　　　　　氏名　　　金額
Ａさん　　10　　　　　　　　Ａさん　　1000円
Ｂさん　　20　　　　　　　　Ｂさん　　1000円
Ｃさん　　10　　　　　　　　Ｃさん　　500円
の場合、部門コード10→1500円　部門コード20→1000円とただ部門コードでグループ化して金額を合計すれば済んでいた話なんですが、Ｂさんの部門コードが複数に渡ることになったので悩んでいます。
＞それとレコードの持ち方はすでにきまっているのですか。
上記テーブル1と2は決まっています。最初の質問の
氏名　　部門ＣＤ1　部門ＣＤ2　部門ＣＤ3　　
Ａさん：　10　　
Ｂさん：　10　　20　　　30
は、決まっているわけではありません。
氏名　　　部門ＣＤ
Ａさん　　10
Ｂさん　　10
Ｂさん　　20
としたほうが管理しやすいでしょうか？

補足日時：2005/07/31 16:20

No.1 回答者： osamuy 回答日時： 2005/07/31 12:56

＞ 1・2・3に関しては可能でしょうか？

それなりのロジックを組む事は可能ですが、

＞ 氏名　　部門1　部門2　部門3　　
＞ Ｂさん：　10　　20　　　30

あんまりこういうテーブル構造はとらないのでは。

レコードID 氏名　部門名 なんかの値
1： Aさん 部門1 10
2： Bさん 部門1 10
3： Bさん 部門2 20
4： Bさん 部門3 30

――みたいなテーブル構造の方が処理しやすいかと。
こういう形式だと、Bさんに紐づく部門数は

select count(部門名) from テーブル where 氏名='Bさん'

――のように計算できます。