Complicated Calculation Query that involves multiple tables

[tbonehwd]

First off I would like to thank PHV and dhookom for not killing me yet here it goes:

I have four tables PROOLN_M, PROORD_M, CDSADR_M, CDSITM_M

Sample data of PROOLN_M
ORD_NUM SHP_CTM OLN_STA ITM_NUM DSC_001 DSC_003 QTY_SHP ITM_NET
11119906 601248 Z 2351 GOLD 0-7573-0235-1 400 3951
11119907 601248 Z 2351 GOLD 0-7573-0235-1 400 3951
11124089 601248 Z 2661 HYPNO 0-7573-0266-1 94 937.65
11124090 601248 Z 2661 HYPNO 0-7573-0266-1 138 1376.55
90498530 601248 Z 2351 GOLD 0-7573-0235-1 1 9.88
90498532 601248 Z 2351 GOLD 0-7573-0235-1 3 29.63

Sample Data of PROORD_M
ORD_NUM CTM_NBR ORD_STA ACT_DTE
11119906 000000601248 F 4/30/2005
11119907 000000601248 F 4/30/2005
11124089 000000601248 F 5/31/2005
11124090 000000601248 F 5/31/2005
90498530 000000601248 F 6/30/2005
90498532 000000601248 F 6/30/2005

Sample data of CDSADR_M
CTM_NBR ADR_CDE ADR_FLG
000000601248 SHP 0001 0
000000601248 SHP 0002 0
000000601248 SHP 0003 0
000000601248 SHP 0004 0
000000601248 STANDARD 0
000000601248 STANDARD P

Sample Data of CDSITM_M
ITM_NUM CPR_YER ITM_SAC
2351 2005 SPR05
2661 2005 SPR05

Notes: PROOLN_M.SHP_CTM is the same as PROORD_M.CTM_NBR and any ORD_NUM <'90000000' is an order anything >'90000000' is a return.

I want to create a query that will give me one line for each ITM_NUM that includes DSC_001, DSC_003, CPR_YER, the sum of QTY_SHP as QTY_ORDERED and the sum of ITM_NET as DOLLARS_ORDERED when ORD_NUM <'90000000' and the sum of QTY_SHP as QTY_RET and the sum of ITM_NET as DOLLARS_RETURNED when ORD_NUM >'90000000' then I want to try and get NET_UNITS and NET_DOLLARS by another calculation QTY_ORDERED - QTY_RET as NET_QTY and DOLLARS_ORDERED - DOLLARS_RETURNED as NET_DOLLARS

In my where clause I must have CDSADR_M.ADR_CDE='STANDARD' And CDSADR_M.ADR_FLAG='0' And PROOLN_M.OLN_STA='Z' And

User Defined information such as Account number CTM_NBR, ITM_NUM, ACT_DTE and ITM_SAC

I hope this is enough information to get me started on my way.

Thanks in advance,

TBonehwd

 

[GingerR]

What is your existing query? What results is it giving that are "wrong", and what are your desired results?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[tbonehwd]

Here is how I planned on starting but I get an error 3141

strSQL = "SELECT Sum(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.QTY_SHP,0))AS UNIT_SHP, Sum(IIf(PROOLN_M.ORD_NUM>'90000000',PROOLN_M.QTY_SHP,0))AS UNIT_RET " & _
"(FROM PROOLN_M INNER JOIN CDSADR_M ON PROOLN_M.SHP_CTM=CDSADR_M.CTM_NBR) INNER JOIN PROORD_M ON PROOLN_M.ORD_NUM=PROORD_M.ORD_NUM " & _
"WHERE CDSADR_M.ADR_CDE='STANDARD' And CDSADR_M.ADR_FLG='0' And PROOLN_M.OLN_STA = 'Z' " & _
"AND PROOLN_M.SHP_CTM " & strADVACCT & _
"AND PROORD_M.ACT_DTE " & strFRMDATE & _
"AND PROORD_M.ACT_DTE " & strTODATE & _
";"
I have been able to figure out that something is wrong with my JOIN however I can't figure it out for the Life of me.

Thanks,

Tbonehwd

 

[lespaul]

I think your parens are wrong:

"FROM PROOLN_M (INNER JOIN CDSADR_M ON PROOLN_M.SHP_CTM=CDSADR_M.CTM_NBR (INNER JOIN PROORD_M ON PROOLN_M.ORD_NUM=PROORD_M.ORD_NUM)) " & _


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design