みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

条件指定の特定レコードのフィールド値の差をとりたい

お世話になります。
テーブル1(TBL_1)の社員コードそれぞれにおけるレコードのフィールド値のデータ1と別テーブル(TBL_2)のデータ2との差をとる方法が知りたいです。差をとりたいレコードは以下の条件によります。
・テーブル2の期間1と期間2の範囲にはいっているテーブル1のレコードのみ差をとる。
・データ1とデータ2の差がマイナス(-)になることはありません。
・社員コードは百程度、レコード数は数千にのぼります。
・フィールドの期間1と期間2は同一社員コードにおいて重複することはありません。
・テーブル2(TBL_2)は年度をまたいでの期間設定は基本しません。年度をまたぐ場合はレコードを増やします。

テーブル1名:TBL_1
社員コード 期間1   期間2   データ1
00123   2012/6/30  2014/3/31  30
00123   2014/4/1   2015/3/31  100  
00456   1995/9/15  2008/3/31   15
00789   2001/6/2  2013/3/31  224
 ・
00456   2008/4/1  2016/3/31  628
 ・
00789   2013/4/1  2015/3/31  1089
 ・
00123   2015/4/1  2017/3/31   95
 ・

テーブル2名:TBL_2
社員コード 期間1     期間2  データ2
00123   2012/11/6  2013/3/31   5
00123   2013/4/1   2013/11/5   13
00456   2013/6/12  2013/10/6   22
00789   1995/6/7   2000/2/11   6

具体的には、上記例においてテーブル1を下記の結果にしたいです。

テーブル1名:TBL_1
社員コード  期間1    期間2     データ1
00123   2012/6/30  2014/3/31    12 ←30-(5+13)=12
00123   2014/4/1   2015/3/31   100 ←変わらない
00456   1995/9/15  2008/3/31    15 ←変わらない
00789   2001/6/2   2013/3/31   224 ←変わらない
 ・
00456   2008/4/1   2016/3/31   606 ←628-22=606
 ・
00789   2013/4/1   2015/3/31  1089 ←変わらない
 ・
00123   2015/4/1   2017/3/31   95 ←変わらない
 ・

なにとぞお力添えをお願い致します。

投稿日時 - 2016-12-16 10:52:06

QNo.9268908

困ってます

質問者が選んだベストアンサー

【Where節を修正したDSum()バージョン!】

DLookup()、DSum()のどちらを利用するのかは好みの問題。

SELECT
 社員コード,
 期間1,
 期間2,
 データ1,
 DSUM("データ2","TBL_2","社員コード=" & [社員コード] & " AND 期間1 >= #" & [期間1] & "# AND 期間2 <= #" & [期間2] & "#") AS データ2
FROM TBL_1;

投稿日時 - 2016-12-18 11:44:42

お礼

f_a_007さん

追加ご回答ありがとうございました。
やはり・・私にはまだまだWHERE句のハードルが超えられません。
条件を書いているだけなのですが・・。
本当にありがとうございます。もっと勉強していきます。

投稿日時 - 2016-12-18 21:58:10

ANo.4

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.3

【訂正】

× 期間1 BETWEEN #" & [期間1] & "# AND #" & [期間2] & "#"

この条件文を目的に沿って書き直す必要があります。アップしてから「アレレッ!」と気付いた次第です。どのように書き直すのかは説明するまでもないので割愛します。

投稿日時 - 2016-12-16 11:59:05

ANo.2

Access であれば DLookup() を使えば簡単に目的を達成できますよ。

SELECT
 TBL_1.社員コード,
 TBL_1.期間1,
 TBL_1.期間2,
 TBL_1.データ1,
 Val(DBLookup("SELECT SUM(データ2) FROM TBL_2 WHERE 社員コード=" & [社員コード] & " AND 期間1 BETWEEN #" & [期間1] & "# AND #" & [期間2] & "#",0)) AS データ2
FROM TBL_1;

 ここでは、自作関数のDBLookup()を利用しています。しかし、働きは DLookup()と同じです。DLookup()よりADOで自作した方が、SQL文をそのまま書けるし、3倍速で動きます。でも、今はPCの性能も高いのでその必要はないかもです。

 で、ともかく DLookup() を使えば簡単に目的を達成できますよ。

投稿日時 - 2016-12-16 11:54:46

お礼

f_a_007さん

ご回答ありがとうございました。
DLookupでできるとのことで、発想ができませんでした。
またWHERE句がなかなか理解できておらず、自身で難しく考え
過ぎているのかもしれません。

まずはお礼申し上げます。

投稿日時 - 2016-12-18 21:49:49

ANo.1

こんにちは
Accessですよね?
クエリ作成してSQLビューで
SELECT TBL_1.社員コード, TBL_1.期間1, TBL_1.期間2, IIf([Q_1]![社員コード] Is Null,[TBL_1]![データ1],[TBL_1]![データ1]-[Q_1]![データ2の合計]) AS 式1
FROM
TBL_1
LEFT JOIN
(SELECT TBL_1.社員コード, TBL_1.期間1, TBL_1.期間2, Sum(TBL_2.[データ2]) AS データ2の合計
FROM TBL_1 INNER JOIN TBL_2 ON TBL_1.社員コード = TBL_2.社員コード
WHERE (((TBL_2.期間1)>=[TBL_1]![期間1]) AND ((TBL_2.期間2)<=[TBL_1]![期間2]))
GROUP BY TBL_1.社員コード, TBL_1.期間1, TBL_1.期間2) AS Q_1
ON (TBL_1.期間2 = [Q_1].期間2) AND (TBL_1.期間1 = [Q_1].期間1) AND (TBL_1.社員コード = [Q_1].社員コード);
とか。

投稿日時 - 2016-12-16 11:08:34

お礼

ushi2015さん

ご回答ありがとうございました。
SQLはまだ勉強できておらず、頂いた内容をSQLビューに書き実行したら
得たい結果が得られました。

社員コード、期間1、期間2がリレーションでつながるのは理解できました。
特にWHERE句がなかなか理解できず、苦慮しておりますが、読み解いてみます。

ありがとうございました。

投稿日時 - 2016-12-18 21:31:08

あなたにオススメの質問