Using word distinct in Count function.

[Dawn]

For example:
SELECT Count(table1.[field1])
FROM table1;
But table1.[field1] is duplicated, how do I use word distinct, or other way
to count distinct number of table1.[field1]?
Many thanks.

 

[Dale Fye]

Select Count(T.Field1)
FROM (SELECT Distinct Table1.Field1
FROM Table1) as T

Access may rewrite this as:

SELECT Count(T.Field1)
FROM [SELECT Distinct Table1.Field1
FROM Table1]. as T

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Dawn]

Dale,
Further question.
If the distinct problem is considering put into a query,like:
SELECT t2.province, Sum(t1.Accountbalance) ASsumofAccountbal, Count(t1.
personID) AS countofpersonID
FROM t1 INNER JOIN t2 ON t1.city = t2.city
GROUP BY t2.province;
The sum of account balance under province is right, but the countofpersonID
is duplicated, because a personID may have several accounts. In this way how
to use word distinct to calculate the unique
countofpersonID under province.
Many thanks.
Dawn