Jet Sql: Select Count Distinct

[Gladimir]

I found this thread on the same subject by Cassio, but the question seems to remain unanswered.

 

I have two fields, MemID and Description, in a table named ReferenceADGroups.

 

The following SQL statement produces a Count of 670:

SELECT COUNT(MemID)
FROM ReferenceADGroups
WHERE (Description="ARMS");

 

However, some users are duplicated in these groups, so I need to add the DISTINCT function. The following SQL statement produces 665 rows:

SELECT DISTINCT MemID
FOM ReferenceADGroups
WHERE (Description="ARMS");

 

How do I combine these two functions to write a statement that only produces a COUNT of the DISTINCT Member IDs?

 

Thanks in advance for your help.

[Mehyar]

Do this :

SELECT COUNT (DISTINCT MemID)

FROM RefernceADGroups

WHERE (Description="ARMS")

[Gladimir]

Mehyar, thanks for you reply, but that produces an error in MS Access SQL Builder:

 

ERROR: syntax error (missing operator) in query expression 'COUNT (DISTINCT ReferenceADGroups.MemID)'

 

SELECT COUNT (DISTINCT ReferenceADGroups.MemID) AS CountA
FROM ReferenceADGroups
WHERE ((Not (ReferenceADGroups.MatchCode)="0000") 
AND ((ReferenceADGroups.Description)="ARMS"));

 

ERROR: undefined function 'DISTINCT' in expression

 

SELECT COUNT (DISTINCT (ReferenceADGroups.MemID)) As CountA
FROM ReferenceADGroups
WHERE ((Not (ReferenceADGroups.MatchCode)="0000") 
AND ((ReferenceADGroups.Description)="ARMS"));

 

I know we have to be close to something that works. Thanks for the help.

[JABE]

It's pretty ugly but try this:

 

SELECT COUNT(*) FROM

(SELECT MemID

FROM ReferenceADGroups

WHERE (Description="ARMS")

GROUP BY MemID) AS MyCount;

[Mehyar]

Gladimir,

 

what i am sure of is that it works in SQL Server cause i tried it

 

before replying to you but i dont know about MS Access i will try

 

to look into it

[JABE]

SELECT COUNT(DISTINCT fieldname) ... is valid in SQL Server but not in MS Access :(

[JABE]

Btw, alternatively you can do:

 

SELECT COUNT(*) FROM

(SELECT DISTINCT MemID

FROM ReferenceADGroups

WHERE (Description="ARMS")

) AS MyCount;

[Mehyar]

Eureka !!!

Try this ( i tried it in Access and it worked)

Select Count (MemId) From

(Select Distinct MemId from RefernceADGroups WHERE Description="ARMS")

[Gladimir]

SOLUTION!

 

That is beautiful Mehyar.

 

It is working perfectly in the Access Query Builder and here it is in my code:

 

string strScalar = "SELECT COUNT(MemID) AS CountA " +
"FROM (SELECT DISTINCT MemID FROM ReferenceADGroups " +
"WHERE (Description='" + strDescription + "') " +
"AND NOT(MatchCode) = '" + strMatchCode + "')";

 

Thanks for you help!