Count Distincts

[Archangelpr]

Hi!

How do I construct a count distinct query in Access? What's the SQL statement?

Example:

Select Proj_ID, count(distinct(Supp_Num)) times1 from parts group by Proj_ID having veces = 1

Thanks!

 

[JonFer]

I think Access 2003 is supposed to support that if the option for SQL-92 is checked but I don't remember it working. You can do this if you have Access 2000 or higher:

Select p.Proj_ID, count(p.Supp_Num) times1
from (Select Distinct Proj_ID, Supp_Num from parts Where veces=1) p
group by p.Proj_ID

In Access 97 you have to save the subquery as a separate query and then write another query against it.


John

http://www.interautomateconsulting.com