如何将此SQL查询转换为MS Access查询？

I have Query in SQL

我在SQL中有Query

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:

我已将其转换为Access为：

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)'.

但它给了我一个错误：查询表达式'Count（DISTINCT Polling_Stations.P_ID）'中的语法错误（缺少运算符）。

2 个解决方案

#1

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

Access SQL不支持COUNT（DISTINCT ...），因此您需要这样做

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"
)

#2

Access doesn't support Count(DISTINCT ...). SELECT DISTINCT in a subquery, and do the counting from the parent query.

Access不支持Count（DISTINCT ...）。在子查询中选择DISTINCT，并从父查询中进行计数。

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

#1