Microsoft Access SQL: Distinct Count? 
Author Message
 Microsoft Access SQL: Distinct Count?

How can I modify the following SQL statement for the count to be
distinct based only on a given field? If I insert DISTINCT after
SELECT, it returns values based on distinct records, but I only want
to count the distinct values in that field (all else based on all the
records returned).

TRANSFORM Sum(A.A)
SELECT C.P, A.B, Count(A.C), Sum(A.A)
FROM (C INNER JOIN B ON C.P = B.P) INNER JOIN A ON B.C = A.C
GROUP BY C.P, A.B
PIVOT A.D;

Thanks for the help,
Eric



Wed, 27 Apr 2005 01:19:45 GMT  
 Microsoft Access SQL: Distinct Count?

Quote:

>How can I modify the following SQL statement for the count to be
>distinct based only on a given field? If I insert DISTINCT after
>SELECT, it returns values based on distinct records, but I only want
>to count the distinct values in that field (all else based on all the
>records returned).

>TRANSFORM Sum(A.A)
>SELECT C.P, A.B, Count(A.C), Sum(A.A)
>FROM (C INNER JOIN B ON C.P = B.P) INNER JOIN A ON B.C = A.C
>GROUP BY C.P, A.B
>PIVOT A.D;

You cannot. Access does not support the COUNT DISTINCT operation.

You'll need to create a Distinct Values query based on A and use it in
this query.

                  John W. Vinson[MVP]    
    Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public



Wed, 27 Apr 2005 08:06:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Does Access support SELECT Count(DISTINCT fieldname) ...

2. count( distinct col ) in Access ?

3. Does Access support SELECT Count(DISTINCT fieldname) ...

4. Distinct Count on a Field?

5. Why does SELECT COUNT(DISTINCT price) .. not work

6. "Count distinct"

7. select count distinct doesnt work

8. Get running count of distinct ID

9. distinct count

10. Distinct Count

11. Counting distinct records

12. How to COUNT() with DISTINCT predicate?

 

 
Powered by phpBB® Forum Software