'Convert SQL query to work in MS Access SQL

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
sqlms-access


Solution 1:[1]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions