count distinct records

[p27br]

Hi,

I am trying to achieve the following in Access :

SELECT COUNT(SELECT DISTINCT ID FROM myTable) AS DistinctCount

but i can't get the syntax right, any suggestions ?

thanks

 

[p27br]

Hi

I found an answer by doing a search in the forums

thread701-868551

 

[OhioSteve]

This Access sql returns two columns. The first column displays each unique value in field1. The second column displays totals for each unique value in field1.

SELECT
table1.field1,
Count(table1.field1) AS [total]
FROM table1
GROUP BY table1.field1;

With Access it is quite helpful to use the query design window, even if you already know sql.