Geek asks and answers

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

I have a 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 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 this gives me an error: Syntax error (missing statement) in the query expression "Count (DISTINCT Polling_Stations.P_ID)".

+5
source share
2 answers

Access SQL does not support COUNT(DISTINCT ...), so instead you will need

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"
)
+5
source

Access is not supported Count(DISTINCT ...). SELECT DISTINCTin the subquery and perform the count from the parent query.

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;
+4
source

More articles:


All Articles