0

I have been trying to convert a TD SQL query into its Access 2007 counterpart. However, it seems like coalesce and nullif functions are undefined in Access. How do I tackle this? I have been using the following query given below

SELECT 
BRAND_DESC, 
COUNT(IIF( PROMO_SUB_TYPE <> 'No Promo',SKU_ID))  AS PROMO_COUNT,
COUNT(IIF( PROMO_SUB_TYPE <> 'No Promo' AND ICMT_UNIT_LIFT> 0, SKU_ID))  AS COUNT_POS_LIFT, 
' '  AS BBY_SHARE, 
AVG(IIF( PROMO_SUB_TYPE <> 'No Promo' , COMP_PRICE ) ) AS PROMO_CPI, 
SUM(IIF( PROMO_SUB_TYPE <> 'No Promo' , AVG_BSKT_SIZE ) ) AS AVG_BSKT_SIZE, 
SUM(WKLY_NET_UNITS)  AS TOTAL_UNITS, 
SUM(IIF( PROMO_SUB_TYPE <> 'No Promo' , WKLY_NET_UNITS ) ) AS PROMO_UNITS, 
SUM(IIF( PROMO_SUB_TYPE <> 'No Promo' ,  INC_UNITS ) ) AS INC_UNITS, 

COALESCE(SUM(IIF( PROMO_SUB_TYPE <> 'No Promo' , INC_UNITS )/NULLIF(SUM(IIF( PROMO_SUB_TYPE <> 'No Promo' , ADJ_UNITS_BSLN ),0),0) )) AS ICMT_UNIT_LIFT, 

SUM(WKLY_NET_REV)  AS TOTAL_REV, 
SUM(IIF( PROMO_SUB_TYPE <> 'No Promo' , WKLY_NET_REV ) ) AS PROMO_REV, 
SUM(IIF( PROMO_SUB_TYPE <> 'No Promo' , INC_REV ) ) AS INC_REV, 

COALESCE(SUM(IIF( PROMO_SUB_TYPE <> 'No Promo' , INC_REV )/NULLIF(SUM(IIF( PROMO_SUB_TYPE <> 'No Promo' , ADJ_REV_BSLN ),0),0) )) AS REV_LIFT,

SUM(WKLY_NET_MRGN_VF)  AS TOTAL_MARGIN, 
SUM(IIF( PROMO_SUB_TYPE <> 'No Promo' , WKLY_NET_MRGN_VF ) ) AS PROMO_MARGIN, 

COALESCE(SUM(WKLY_NET_MRGN_VF)/NULLIF(SUM(WKLY_NET_REV),0),0)  AS TOTAL_MARGIN_PCT, 
COALESCE(SUM(IIF( PROMO_SUB_TYPE <> 'No Promo' , WKLY_NET_MRGN_VF )/NULLIF(SUM(IIF( PROMO_SUB_TYPE <> 'No Promo' , WKLY_NET_REV ),0),0) )) AS PROMO_MARGIN_PCT, 

SUM(IIF( PROMO_SUB_TYPE <> 'No Promo' , INC_MRGN_VF ) ) AS INC_MRGN_VF, 

COALESCE(SUM(IIF( PROMO_SUB_TYPE <> 'No Promo' , INC_MRGN_VF )/NULLIF(SUM(IIF( PROMO_SUB_TYPE <> 'No Promo' , ADJ_MRGN_BSLN_VF ),0),0)  )) AS ICMT_MRGN_LIFT_VF

FROM PRODBBYCIADHOCWRK_TBEN_PR_DSM_EVAL_MTRC_ALL 
 WHERE CLASS_ID = 16 
 AND SCLS_ID IN (408,409)
AND BRAND_DESC LIKE "ABCD"
AND PROMO_SUB_TYPE = 'EFGH'
AND AD_WK BETWEEN 2014012 AND 2015013
GROUP BY BRAND_DESC
ORDER BY BRAND_DESC
;

Can anyone please help me with this query?Also I found that Access does not allow aggregate functions in sub queries. How do I handle this part? Should I create different tables?

Thanks and regards, Nirvik

BitAccesser
  • 719
  • 4
  • 14
Nirvik Banerjee
  • 335
  • 5
  • 16
  • There is no `COALESCE` in `MS-Access`, `NULLIF` is `Nz` http://stackoverflow.com/questions/247858/coalesce-alternative-in-access-sql. If you need it you can use `MS-SQL` as backend and pass the query as passthrough query, then it gets executed on the ' MS-SQL` server. – BitAccesser Jun 17 '16 at 08:00
  • Try Nz() and IsNull() functions. Syntax is different, cannot replace directly. – Sergey S. Jun 17 '16 at 08:14
  • Switch() function can have few pairs of paramenters, can replace COALESCE – Sergey S. Jun 17 '16 at 08:17
  • 3
    @BitAccesser `NULLIF` is not `NZ` in access. `NZ` is basically `coalesce`. `NULLIF` is a function which returns null when a condition is met which is different than replacing a null value with an alternate value. – Brad Jun 17 '16 at 14:00
  • @Brad Thats true, my mistake. Got confused by the use of `Nz`in my link. It should be `IIf` to emulate `NULLIF`. Would you agree, that best advive for Nirvik is `MS-SQL` as backend? – BitAccesser Jun 17 '16 at 23:17

0 Answers0