Counting distinct records in Access

[phdmom]

I have a table with a field called PolicyNo, and I want to count the distinct policy numbers. This should be pathetically easy, right?

So in the query property box, I changed Unique Values to Yes, which creates the SQL statement "Select distinct count([table].PolicyNo)..." The result is the total number of records in the table, not what I want. :evil:

I want something like "select count(distinct [table].PolicyNo..." but Access doesn't like that syntax.

Help?

[Cho Da]

select count(distNo) from (
select distinct policyno as distNo from table)
;

[phdmom]

Thanks!