Printable View
Hello,
I am using MS Access 2010 I have a pass thru query getting data from db2 no issues here, my problem is if the results are zero I want to show the month field (SHSHPM) = 4 and the year (SHSHPY) = 2014 and the COST field would equal zero amount. If I ran the pass thru I get nothing, which is correct. Does anyone know how I can do that? Thank you in advance.
Sorry below is the code regarding show zero amount in pass thru query.
SELECT DISTINCT
ASODTANNA.ASOSHHM.SHSHPM, ASODTANNA.ASOSHHM.SHSHPY, ASODTANNA.ASOSHHM.SHTXCS AS State,
SUM(CASE WHEN ASODTANNA.ASOSHDM.SDSHPQ * AICDTANNA.AICITMM.ITAVGC IS NULL THEN 0 ELSE ASODTANNA.ASOSHDM.SDSHPQ * AICDTANNA.AICITMM.ITAVGC END) AS Cost
FROM ASODTANNA.ASOSHHM, ASODTANNA.ASOSHDM, AICDTANNA.AICITMM
WHERE ASODTANNA.ASOSHHM.SHSHPM = ASODTANNA.ASOSHDM.SDSHPM AND ASODTANNA.ASOSHHM.SHSHPY = ASODTANNA.ASOSHDM.SDSHPY AND
ASODTANNA.ASOSHHM.SHSON = ASODTANNA.ASOSHDM.SDSON AND ASODTANNA.ASOSHHM.SHSHPN = ASODTANNA.ASOSHDM.SDSHPN AND
ASODTANNA.ASOSHDM.SDICH = AICDTANNA.AICITMM.ITITM AND (ASODTANNA.ASOSHHM.SHPSTF = 'Y') AND (ASODTANNA.ASOSHHM.SHSHPM = '04') AND (ASODTANNA.ASOSHHM.SHSHPY = '2014') AND
(ASODTANNA.ASOSHHM.SHTXCS = 'CA') AND (ASODTANNA.ASOSHDM.SDUNTR = 0) AND (ASODTANNA.ASOSHDM.SDSHPQ > 0) AND (AICDTANNA.AICITMM.ITITM IN ('05119', '05121', '05122', '05123', '05124',
'05125', '05127', '05128', '05130', '05131', '05132', '05134', '05135', '05136', '05137', '05141', '05144', '05393', '05886', '06712'))
GROUP BY ASODTANNA.ASOSHHM.SHSHPM, ASODTANNA.ASOSHHM.SHSHPY, ASODTANNA.ASOSHHM.SHTXCS, ASODTANNA.ASOSHDM.SDUNTR, AICDTANNA.AICITMM.ITITM, ASODTANNA.ASOSHDM.SDSHPQ