Jet Sql: Select Count Distinct

G

Gladimir

Well-known member
Joined
Mar 29, 2003
Messages
60
Location
San Diego, CA
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:
Code: 
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:
Code: 
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, 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)

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

ERROR: undefined function DISTINCT in expression

Code: 
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.
 
Its pretty ugly but try this:

SELECT COUNT(*) FROM
(SELECT MemID
FROM ReferenceADGroups
WHERE (Description="ARMS")
GROUP BY MemID) AS MyCount;
 
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
 
Btw, alternatively you can do:

SELECT COUNT(*) FROM
(SELECT DISTINCT MemID
FROM ReferenceADGroups
WHERE (Description="ARMS")
) AS MyCount;
 
Eureka !!!
Try this ( i tried it in Access and it worked)
Select Count (MemId) From
(Select Distinct MemId from RefernceADGroups WHERE Description="ARMS")
 
SOLUTION!

That is beautiful Mehyar.

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

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

Thanks for you help!
 
Back
Top