Convert SQL query to work in MS Access SQL-CodePudding

I'm trying to convert SQL query to work in MS-Access, is there any suggested way? thank you

SELECT        
  colldet.college,    
  COUNT(DISTINCT manuscript.p_name) AS A,
  COUNT(DISTINCT CASE WHEN s_p = 'منجز' THEN p_name END) AS B,
  COUNT(DISTINCT CASE WHEN s_p = 'منجز منشور' THEN p_name END) AS C, 
  COUNT(DISTINCT CASE WHEN s_p = 'مخطط' THEN p_name END) AS D
FROM manuscript 
RIGHT OUTER JOIN colldet 
ON manuscript.coll_name = colldet.college
GROUP BY colldet.college

CodePudding user response：

As June7 notes, Access doesn't support COUNT DISTINCT so you'll need to make things distinct before counting. Also as June7 suggests use Iif() instead of CASE. This is a guess:

SELECT Q.College,
COUNT(Q.A1) AS A,
COUNT(Q.B1) AS B,
COUNT(Q.C1) AS C,
COUNT(Q.D1) AS D
FROM (SELECT DISTINCT colldet.college,
    manuscript.p_name AS A1,
    IIF(s_p = 'منجز',p_name,Null) AS B1,
    IIF(s_p = 'منجز منشور',p_name,Null) AS C1,
    IIF(s_p = 'مخطط',p_name,Null) AS D1
    FROM manuscript
    RIGHT OUTER JOIN colldet
    ON  manuscript.coll_name = colldet.college)
AS Q
GROUP BY Q.college