Counting Queries

[rwilson32]

I need to write a query that will allow me to count distinct values. My SQL book says you can do this by:
SELECT count(DISTINCT *)

But it doesn't work in access, i get an error until i put another set of parentheses around distinct and then it doesn't work it still counts items that are the same. Does anyone have an answer to this.

 

[dcx693]

Just use SELECT DISTINCT Count(field) FROM table; You can also use DISTINCTROW instead of DISTINCT, but that will give a different result.

 

[rwilson32]

Tried that

I already tried
SELECT DISTINCT Count(field)

and it does not give an error but still counts duplicate rows

 

[rwilson32]

If I do
Select myfield, count(*) as mycount
from mytable
groupby myfield
all it does is count how many duplicates there are, What I want is to get a count of all unique values for my field and not to count the duplicate values any more than once