Combining two Access SQL queries - subquery? 1

[tbonehwd]

I have a problem trying to combine two querys into one. The problem I am facing is that I need to SUM total orders and total returns and they are both stored as regular orders, the only difference is the order number. Any order <90000000
is an order and any order that is >90000000 is a return. I want two fields in my query DOLLARS_PURCHASED and DOLLARS_RETURNED. Here is my SQL code:

SELECT SUM(PROORD_M.SHP_NET) AS ["DOLLARS_PURCHASED"]
FROM PROORD_M INNER JOIN CDSADR_M ON PROORD_M.CTM_NBR=CDSADR_M.CTM_NBR
WHERE PROORD_M.CTM_NBR='000080991906' And PROORD_M.INV_DATE>=#1/1/2005# And CDSADR_M.ADR_CDE='STANDARD' And CDSADR_M.ADR_FLG='0' And PROORD_M.ORD_STA='F' AND PROORD_M.ORD_NUM<'90000000'

SELECT SUM(PROORD_M.SHP_NET) AS ["DOLLARS_RETURNED"]
FROM PROORD_M INNER JOIN CDSADR_M ON PROORD_M.CTM_NBR=CDSADR_M.CTM_NBR
WHERE PROORD_M.CTM_NBR='000080991906' And PROORD_M.INV_DATE>=#1/1/2005# And CDSADR_M.ADR_CDE='STANDARD' And CDSADR_M.ADR_FLG='0' And PROORD_M.ORD_STA='F' AND PROORD_M.ORD_NUM>'90000000';

How can I combine these into one qeury with the two fields I want?

Thanks in advance,

Tbonehwd

 

[PHV]

Something like this ?
SELECT Sum(IIf(PROORD_M.ORD_NUM<'90000000',PROORD_M.SHP_NET,0)) AS DOLLARS_PURCHASED
, Sum(IIf(PROORD_M.ORD_NUM>'90000000',PROORD_M.SHP_NET,0)) AS DOLLARS_RETURNED
FROM PROORD_M INNER JOIN CDSADR_M ON PROORD_M.CTM_NBR=CDSADR_M.CTM_NBR
WHERE PROORD_M.CTM_NBR='000080991906' And PROORD_M.INV_DATE>=#2005-01-01# And CDSADR_M.ADR_CDE='STANDARD' And CDSADR_M.ADR_FLG='0' And PROORD_M.ORD_STA='F'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tbonehwd]

Thank you it worked perfect... Three cheers to PHV