How can I convert this SQL query to MS Access query?

Question

I have Query in SQL

SELECT        COUNT(DISTINCT dbo.Polling_Stations.P_ID) AS [Male Stations]
FROM            dbo.Agent INNER JOIN
                     dbo.Polling_Stations ON dbo.Agent.P_ID = dbo.Polling_Stations.P_ID
GROUP BY dbo.Polling_Stations.Gender
HAVING        (dbo.Polling_Stations.Gender = N'Male')

I have converted it to Access as:

SELECT        COUNT(DISTINCT Polling_Stations.P_ID) AS [Male Stations]
FROM            Agent INNER JOIN
                     Polling_Stations ON Agent.P_ID = Polling_Stations.P_ID
GROUP BY Polling_Stations.Gender
HAVING        (Polling_Stations.Gender = 'Male') 

But it giving me an error : Syntax error(missing Operator) in query expression 'Count(DISTINCT Polling_Stations.P_ID)'.

Answer 1

Access SQL does not support COUNT(DISTINCT ...), so instead you'll need to do

SELECT COUNT(*) AS [Male Stations]
FROM
(
    SELECT DISTINCT Polling_Stations.P_ID
    FROM Agent INNER JOIN Polling_Stations 
        ON Agent.P_ID = Polling_Stations.P_ID
    WHERE Polling_Stations.Gender = "Male"
)